c# - LINQ 查询子句的顺序是否会影响 Entity Framework 的性能？

Question

我正在使用 Entity Framework (代码优先)并发现我在 LINQ 查询中指定子句的顺序对性能有巨大影响，例如:

using (var db = new MyDbContext())
{
    var mySize = "medium";
    var myColour = "vermilion";
    var list1 = db.Widgets.Where(x => x.Colour == myColour && x.Size == mySize).ToList();
    var list2 = db.Widgets.Where(x => x.Size == mySize && x.Colour == myColour).ToList();
}

如果(稀有)颜色子句在(常见)尺寸子句之前，它的速度很快，但反之则慢几个数量级。该表有几百万行，有问题的两个字段是 nvarchar(50)，因此没有规范化，但它们都被索引了。这些字段以代码优先方式指定，如下所示:

    [StringLength(50)]
    public string Colour { get; set; }

    [StringLength(50)]
    public string Size { get; set; }

我真的应该在 LINQ 查询中担心这些事情吗，我认为这是数据库的工作？

系统规范是:

• Visual Studio 2010
• .NET 4
• EntityFramework 6.0.0-beta1
• SQL Server 2008 R2 网络版(64 位)

更新:

是的，对于任何贪吃的人来说，效果都可以复制如下。这个问题似乎对许多因素非常敏感，所以请忍受其中一些人为的性质:

通过 nuget 安装 EntityFramework 6.0.0-beta1，然后生成代码优先样式:

public class Widget
{
    [Key]
    public int WidgetId { get; set; }

    [StringLength(50)]
    public string Size { get; set; }

    [StringLength(50)]
    public string Colour { get; set; }
}

---

public class MyDbContext : DbContext
{
    public MyDbContext()
        : base("DefaultConnection")
    {
    }

    public DbSet<Widget> Widgets { get; set; }
}

---

使用以下 SQL 生成虚拟数据:

---

insert into gadget (Size, Colour)
select RND1 + ' is the name is this size' as Size,
RND2 + ' is the name of this colour' as Colour
from (Select top 1000000
CAST(abs(Checksum(NewId())) % 100 as varchar) As RND1,
CAST(abs(Checksum(NewId())) % 10000 as varchar) As RND2
from master..spt_values t1 cross join master..spt_values t2) t3

---

Color 和 Size 各加一个索引，然后查询:

---

string mySize = "99 is the name is this size";
string myColour = "9999 is the name of this colour";
using (var db = new WebDbContext())
{
    var list1= db.Widgets.Where(x => x.Colour == myColour && x.Size == mySize).ToList();
}
using (var db = new WebDbContext())
{
    var list2 = db.Widgets.Where(x => x.Size == mySize && x.Colour == myColour).ToList();
}

---

该问题似乎与生成的 SQL 中 NULL 比较的钝化集合有关，如下所示。

exec sp_executesql N'SELECT 
[Extent1].[WidgetId] AS [WidgetId], 
[Extent1].[Size] AS [Size], 
[Extent1].[Colour] AS [Colour]
FROM [dbo].[Widget] AS [Extent1]
WHERE ((([Extent1].[Size] = @p__linq__0) 
AND ( NOT ([Extent1].[Size] IS NULL OR @p__linq__0 IS NULL))) 
OR (([Extent1].[Size] IS NULL) AND (@p__linq__0 IS NULL))) 
AND ((([Extent1].[Colour] = @p__linq__1) AND ( NOT ([Extent1].[Colour] IS NULL 
OR @p__linq__1 IS NULL))) OR (([Extent1].[Colour] IS NULL) 
AND (@p__linq__1 IS NULL)))',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',
@p__linq__0=N'99 is the name is this size',
@p__linq__1=N'9999 is the name of this colour'
go

---

将 LINQ 中的相等运算符更改为 StartWith() 可以解决问题，将两个字段之一更改为在数据库中不可为空也是如此。

我绝望了!

更新 2:

对任何赏金猎人的一些帮助，问题可以在干净的数据库中的 SQL Server 2008 R2 Web(64 位)上重现，如下所示:

CREATE TABLE [dbo].[Widget](
    [WidgetId] [int] IDENTITY(1,1) NOT NULL,
    [Size] [nvarchar](50) NULL,
    [Colour] [nvarchar](50) NULL,
 CONSTRAINT [PK_dbo.Widget] PRIMARY KEY CLUSTERED 
(
    [WidgetId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Widget_Size ON dbo.Widget
    (
    Size
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Widget_Colour ON dbo.Widget
    (
    Colour
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


insert into Widget (Size, Colour)
select RND1 + ' is the name is this size' as Size,
RND2 + ' is the name of this colour' as Colour
from (Select top 1000000
CAST(abs(Checksum(NewId())) % 100 as varchar) As RND1,
CAST(abs(Checksum(NewId())) % 10000 as varchar) As RND2
from master..spt_values t1 cross join master..spt_values t2) t3
GO

然后比较以下两个查询的相对性能(您可能需要调整参数测试值以获得返回几行的查询以观察效果，​​即第二个查询id慢得多).

exec sp_executesql N'SELECT 
[Extent1].[WidgetId] AS [WidgetId], 
[Extent1].[Size] AS [Size], 
[Extent1].[Colour] AS [Colour]
FROM [dbo].[Widget] AS [Extent1]
WHERE ((([Extent1].[Colour] = @p__linq__0) 
AND ( NOT ([Extent1].[Colour] IS NULL 
OR @p__linq__0 IS NULL))) 
OR (([Extent1].[Colour] IS NULL) 
AND (@p__linq__0 IS NULL))) 
AND ((([Extent1].[Size] = @p__linq__1) 
AND ( NOT ([Extent1].[Size] IS NULL 
OR @p__linq__1 IS NULL))) 
OR (([Extent1].[Size] IS NULL) AND (@p__linq__1 IS NULL)))',
N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',
@p__linq__0=N'9999 is the name of this colour',
@p__linq__1=N'99 is the name is this size'
go

exec sp_executesql N'SELECT 
[Extent1].[WidgetId] AS [WidgetId], 
[Extent1].[Size] AS [Size], 
[Extent1].[Colour] AS [Colour]
FROM [dbo].[Widget] AS [Extent1]
WHERE ((([Extent1].[Size] = @p__linq__0) 
AND ( NOT ([Extent1].[Size] IS NULL 
OR @p__linq__0 IS NULL))) 
OR (([Extent1].[Size] IS NULL) 
AND (@p__linq__0 IS NULL))) 
AND ((([Extent1].[Colour] = @p__linq__1) 
AND ( NOT ([Extent1].[Colour] IS NULL 
OR @p__linq__1 IS NULL))) 
OR (([Extent1].[Colour] IS NULL) 
AND (@p__linq__1 IS NULL)))',
N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',
@p__linq__0=N'99 is the name is this size',
@p__linq__1=N'9999 is the name of this colour'

您可能还会像我一样发现，如果您重新运行虚拟数据插入，现在有 200 万行，问题就会消失。

Answer 1

问题的核心不是“为什么顺序对 LINQ 很重要？”。 LINQ 只是按字面翻译而不重新排序。真正的问题是“为什么这两个 SQL 查询有不同的性能？”。

我能够通过仅插入 10 万行来重现该问题。在这种情况下，优化器中的一个弱点被触发:由于复杂的条件，它无法识别它可以对 Colour 进行查找。在第一个查询中，优化器确实识别了模式并创建了索引查找。

没有语义上的理由说明为什么会这样。即使在 NULL 上查找时，也可以在索引上查找。这是优化器中的弱点/错误。这是两个计划:

EF 试图在这里提供帮助，因为它假定列和过滤器变量都可以为空。在这种情况下，它会尝试为您提供匹配项(根据 C# 语义，这是正确的做法)。

我尝试通过添加以下过滤器来撤消它:

Colour IS NOT NULL AND @p__linq__0 IS NOT NULL
AND Size IS NOT NULL AND @p__linq__1 IS NOT NULL

希望优化器现在使用该知识来简化复杂的 EF 过滤器表达式。它没有设法这样做。如果此方法有效，则可以将相同的筛选器添加到 EF 查询中，从而提供简单的修复。

以下是我按照您应该尝试的顺序推荐的修复方法:

1. 使数据库中的数据库列不为空
2. 使 EF 数据模型中的列不为空，希望这样可以防止 EF 创建复杂的过滤条件
3. 创建索引:Colour, Size 和/或 Size, Colour。他们还消除了他们的问题。
4. 确保过滤以正确的顺序完成并留下代码注释
5. 尝试使用 INTERSECT/Queryable.Intersect 组合过滤器。这通常会导致不同的计划形状。
6. 创建一个执行过滤的内联表值函数。 EF 可以将这样的函数用作更大查询的一部分
7. 下降到原始 SQL
8. 使用计划指南更改计划

所有这些都是解决方法，而不是根本原因修复。

最后，我对这里的 SQL Server 和 EF 都不满意。两种产品都应该是固定的。唉，他们可能不会，你也等不及了。

这是索引脚本:

CREATE NONCLUSTERED INDEX IX_Widget_Colour_Size ON dbo.Widget
    (
    Colour, Size
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Widget_Size_Colour ON dbo.Widget
    (
   Size, Colour
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]