实体框架查询慢，但SqlQuery中相同的SQL是快速的 [英] Entity Framework query slow, but same SQL in SqlQuery is fast

问题描述

我看到一些非常简单的查询，使用.NET Framework 4版本的Entity Framework Code-First进行了非常简单的查询。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屋！