实体框架查询很慢,但 SqlQuery 中的相同 SQL 很快 [英] Entity Framework query slow, but same SQL in SqlQuery is fast
问题描述
我看到一些与使用实体框架代码优先和 .NET 框架版本 4 的非常简单的查询相关的非常奇怪的性能.LINQ2Entities 查询如下所示:
I'm seeing some really strange perf related to a very simple query using Entity Framework Code-First with .NET framework version 4. The LINQ2Entities query looks like this:
context.MyTables.Where(m => m.SomeStringProp == stringVar);
这需要超过 3000 毫秒来执行.生成的 SQL 看起来很简单:
This takes over 3000 milliseconds to execute. The generated SQL looks very simple:
SELECT [Extent1].[ID], [Extent1].[SomeStringProp], [Extent1].[SomeOtherProp],
...
FROM [MyTable] as [Extent1]
WHERE [Extent1].[SomeStringProp] = '1234567890'
此查询在通过 Management Studio 运行时几乎立即运行.当我将 C# 代码更改为使用 SqlQuery 函数时,它在 5-10 毫秒内运行:
This query runs almost instantaneously when run through Management Studio. When I change the C# code to use the SqlQuery function, it runs in 5-10 milliseconds:
context.MyTables.SqlQuery("SELECT [Extent1].[ID] ... WHERE [Extent1].[SomeStringProp] = @param", stringVar);
因此,完全相同的 SQL,结果实体在两种情况下都进行了更改跟踪,但两者之间的性能差异很大.什么给?
So, exact same SQL, the resulting entities are change-tracked in both cases, but wild perf difference between the two. What gives?
推荐答案
找到了.事实证明这是 SQL 数据类型的问题.数据库中的 SomeStringProp
列是一个 varchar,但 EF 假定 .NET 字符串类型是 nvarchars.在查询期间为 DB 进行比较的结果转换过程需要很长时间.我认为 EF 教授在这里让我误入歧途,正在运行的查询的更准确表示如下:
Found it. It turns out it's an issue of SQL data types. The SomeStringProp
column in the database was a varchar, but EF assumes that .NET string types are nvarchars. The resulting translation process during the query for the DB to do the comparison is what takes a long time. I think EF Prof was leading me astray a bit here, a more accurate representation of the query being run would be the following:
SELECT [Extent1].[ID], [Extent1].[SomeStringProp], [Extent1].[SomeOtherProp],
...
FROM [MyTable] as [Extent1]
WHERE [Extent1].[SomeStringProp] = N'1234567890'
所以最终的修复是对代码优先模型进行注释,指示正确的 SQL 数据类型:
So the resulting fix is to annotate the code-first model, indicating the correct SQL data type:
public class MyTable
{
...
[Column(TypeName="varchar")]
public string SomeStringProp { get; set; }
...
}
这篇关于实体框架查询很慢,但 SqlQuery 中的相同 SQL 很快的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!