实体框架数据库查询非常慢 [英] Entity Framework database query very slow

问题描述

在我的数据库中有 Document 和 DocumentFile 表。主键 - 列 Uid （在两个表中）。 DocumentFile 引用文档通过列 DocumentUid 。

In my database there are Document and DocumentFile tables. Primary key - column Uid (in both tables). DocumentFile references Document via column DocumentUid.

我知道文件文件，我想选择带有文件的文档（左连接），EF生成此查询：

I know uid of document file, and I want to select document with files (with left join), and EF generates this query:

DECLARE @p__linq__0 uniqueidentifier,@p__linq__1 uniqueidentifier,@p__linq__2 varchar(max) ,@p__linq__3 nvarchar(max) ,@p__linq__4 uniqueidentifier

SELECT @p__linq__0=NULL,@p__linq__1=NULL,@p__linq__2=NULL,@p__linq__3=NULL,@p__linq__4='8670AD28-9FA6-41F3-94B9-6B91FD2AE110'

SELECT 
*
FROM  [dbo].[Document] AS [Extent1]
LEFT OUTER JOIN [dbo].[DocumentFile] AS [Extent2] ON [Extent1].[Uid] = [Extent2].[DocumentUid]
WHERE ((([Extent1].[EntityUid] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)) OR (@p__linq__1 IS NULL)) 
        AND ((([Extent1].[EntityTypeCode] = @p__linq__2) AND ( NOT ([Extent1].[EntityTypeCode] IS NULL OR @p__linq__2 IS NULL))) OR (([Extent1].[EntityTypeCode] IS NULL) AND (@p__linq__2 IS NULL)) OR (@p__linq__3 IS NULL) OR (( CAST(LEN(@p__linq__3) AS int)) = 0)) 
        AND ((([Extent2].[Uid] = @p__linq__4) AND ( NOT ([Extent2].[Uid] IS NULL OR @p__linq__4 IS NULL))) OR (([Extent2].[Uid] IS NULL) AND (@p__linq__4 IS NULL)) )

（长列表中的星号替换为顶部声明的参数，但无关紧要）

(long list of columns replaced with asterisk and parameters declared at top, but it does not matter)

这个查询工作非常慢，复杂的查询计划（〜20秒）。如果我在查询结束时评论这个条件：

This query works very slow with complicated query plan (~20 seconds). If I comment this condition at end of query:

 /*OR (([Extent2].[Uid] IS NULL) AND (@p__linq__4 IS NULL))*/

它以闪电般的速度运行（几毫秒）。 Extent2是DocumentFile，Uid是主键，它不会是 NULL 。

it run with lightning speed (few milliseconds). Extent2 is DocumentFile, column Uid is primary key and it never be NULL.

在C＃代码列中，Uid声明为Guid：

In C# code column Uid declared as Guid:

public class DocumentFile
{
    public const string EntityType = "DocumentFile";

    [Key]
    public Guid Uid { get; set; }

    public Guid DocumentUid { get; set; }
    ...
}

如何修复查询或告诉SQL Server使用简单的查询计划，如查询带有注释的条件？

How to fix query or tell SQL Server to use simple query plan, like for query with commented condition?

推荐答案

这是因为EF默认为.net语义为null值。也就是说：如果一个字符串的值不等于null：

This is because EF by default mimics .net semantics for null values. That is: if a string has a value it never equals null:

stringValue != null

...评估为true。

... evaluates to true.

在SQL语义中，这个方程是未定义的。如果用作谓词，它永远不会产生任何结果。 （与正确的语法相反： stringValue IS NOT NULL ）。即使 stringValue 是 null ，在SQL中， stringValue = null 不评估为真！

In SQL semantics this equation is undefined. If used as predicate it never yields any result. (Contrary to the correct syntax: stringValue IS NOT NULL). Even if stringValue is null, in SQL, stringValue = null doesn't evaluate as true!

您可以告诉EF使用SQL空语法，但是我们来看一个简单的例子，这可以导致意想不到的结果。我有一个连接在Linqpad的上下文，并使用此代码来比较两个语义：

You can tell EF to use SQL null semantics, but let's look at a simple example how this can lead to unexpected results. I have a context connected in Linqpad and use this code to compare both semantics:

string code = null;
this.Configuration.UseDatabaseNullSemantics = false; // the default
Companies.Where(c => c.Code == code).Dump();

this.Configuration.UseDatabaseNullSemantics = true;
Companies.Where(c => c.Code == code).Dump();

第一个查询给了我公司，其中代码是 null 。第二个查询... none。

The first query gives me companies where Code is null. The second query... none.

从执行的SQL语句可以看出这个原因：

The reason for this is apparent from the executed SQL statements:

-- Region Parameters
DECLARE @p__linq__0 VarChar(1000) = null
-- EndRegion
SELECT ...
    FROM [dbo].[Company] AS [Extent1]
    WHERE ([Extent1].[Code] = @p__linq__0)
       OR (([Extent1].[Code] IS NULL) AND (@p__linq__0 IS NULL))
GO

vs

SELECT ...
    FROM [dbo].[Company] AS [Extent1]
    WHERE [Extent1].[Code] = @p__linq__0

c $ c> WHERE [Extent1]。[Code] = @ p__linq__0 未定义，查询不返回任何结果。

There it is, WHERE [Extent1].[Code] = @p__linq__0 is undefined, the query doesn't return any result.

所以你可以转而使用数据库null语义，但这是一个谨慎的决定。如果空值不起作用（即，在非空值之间总是存在比较），则可以安全地执行。

So you can turn to database null semantics, but that's a decision to be taken with caution. If null values don't play a role (i.e. there will always be comparisons between non-null values), you can do it safely.

这篇关于实体框架数据库查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！