替换实体框架6生成的慢速SQL的选项

Question

我首先使用Entity Framework 6代码对我的域模型进行了一些简单的CRUD操作，到目前为止，它的执行令人钦佩。

我现在遇到了这样一种情况：我正在执行一个相当复杂的查询，其中包括对结果的过滤和分页。EF6生成的查询非常糟糕，与我自己手动创建的查询相比效率非常低。以下是生成的SQL，它在大约17秒内执行：

SELECT TOP ( 15 )
[Project1].[Branch] AS [Branch] ,
[Project1].[Salesman] AS [Salesman] ,
[Project1].[Status] AS [Status] ,
[Project1].[OrderID] AS [OrderID] ,
[Project1].[DateCreated] AS [DateCreated] ,
[Project1].[DateCompleted] AS [DateCompleted] ,
[Project1].[RegNumber] AS [RegNumber] ,
[Project1].[Make] AS [Make] ,
[Project1].[Model] AS [Model] ,
[Project1].[Spec] AS [Spec] ,
[Project1].[Title] AS [Title] ,
[Project1].[Firstname] AS [Firstname] ,
[Project1].[Surname] AS [Surname] ,
[Project1].[Address1] AS [Address1] ,
[Project1].[Address2] AS [Address2] ,
[Project1].[Address3] AS [Address3] ,
[Project1].[Town] AS [Town] ,
[Project1].[County] AS [County] ,
[Project1].[Postcode] AS [Postcode] ,
[Project1].[HomePhone] AS [HomePhone] ,
[Project1].[WorkPhone] AS [WorkPhone] ,
[Project1].[MobilePhone] AS [MobilePhone] ,
[Project1].[EMailAddress] AS [EMailAddress] ,
[Project1].[AllowMarketing] AS [AllowMarketing] ,
[Project1].[Manager] AS [Manager] ,
[Project1].[FK_BranchID] AS [FK_BranchID]
FROM    ( SELECT    [Project1].[Branch] AS [Branch] ,
[Project1].[Salesman] AS [Salesman] ,
[Project1].[Status] AS [Status] ,
[Project1].[OrderID] AS [OrderID] ,
[Project1].[DateCreated] AS [DateCreated] ,
[Project1].[DateCompleted] AS [DateCompleted] ,
[Project1].[RegNumber] AS [RegNumber] ,
[Project1].[Make] AS [Make] ,
[Project1].[Model] AS [Model] ,
[Project1].[Spec] AS [Spec] ,
[Project1].[Title] AS [Title] ,
[Project1].[Firstname] AS [Firstname] ,
[Project1].[Surname] AS [Surname] ,
[Project1].[Address1] AS [Address1] ,
[Project1].[Address2] AS [Address2] ,
[Project1].[Address3] AS [Address3] ,
[Project1].[Town] AS [Town] ,
[Project1].[County] AS [County] ,
[Project1].[Postcode] AS [Postcode] ,
[Project1].[HomePhone] AS [HomePhone] ,
[Project1].[WorkPhone] AS [WorkPhone] ,
[Project1].[MobilePhone] AS [MobilePhone] ,
[Project1].[EMailAddress] AS [EMailAddress] ,
[Project1].[AllowMarketing] AS [AllowMarketing] ,
[Project1].[Manager] AS [Manager] ,
[Project1].[FK_BranchID] AS [FK_BranchID] ,
ROW_NUMBER() OVER ( ORDER BY [Project1].[DateCreated] DESC ) AS [row_number]
FROM      ( SELECT    [Extent1].[Branch] AS [Branch] ,
[Extent1].[Salesman] AS [Salesman] ,
[Extent1].[Status] AS [Status] ,
[Extent1].[OrderID] AS [OrderID] ,
[Extent1].[DateCreated] AS [DateCreated] ,
[Extent1].[DateCompleted] AS [DateCompleted] ,
[Extent1].[RegNumber] AS [RegNumber] ,
[Extent1].[Make] AS [Make] ,
[Extent1].[Model] AS [Model] ,
[Extent1].[Spec] AS [Spec] ,
[Extent1].[Title] AS [Title] ,
[Extent1].[Firstname] AS [Firstname] ,
[Extent1].[Surname] AS [Surname] ,
[Extent1].[Address1] AS [Address1] ,
[Extent1].[Address2] AS [Address2] ,
[Extent1].[Address3] AS [Address3] ,
[Extent1].[Town] AS [Town] ,
[Extent1].[County] AS [County] ,
[Extent1].[Postcode] AS [Postcode] ,
[Extent1].[HomePhone] AS [HomePhone] ,
[Extent1].[WorkPhone] AS [WorkPhone] ,
[Extent1].[MobilePhone] AS [MobilePhone] ,
[Extent1].[EMailAddress] AS [EMailAddress] ,
[Extent1].[AllowMarketing] AS [AllowMarketing] ,
[Extent1].[Manager] AS [Manager] ,
[Extent1].[FK_BranchID] AS [FK_BranchID]
FROM      ( SELECT    [vw_CS_OrderDetails].[Branch] AS [Branch] ,
[vw_CS_OrderDetails].[Salesman] AS [Salesman] ,
[vw_CS_OrderDetails].[Status] AS [Status] ,
[vw_CS_OrderDetails].[OrderID] AS [OrderID] ,
[vw_CS_OrderDetails].[DateCreated] AS [DateCreated] ,
[vw_CS_OrderDetails].[DateCompleted] AS [DateCompleted] ,
[vw_CS_OrderDetails].[RegNumber] AS [RegNumber] ,
[vw_CS_OrderDetails].[Make] AS [Make] ,
[vw_CS_OrderDetails].[Model] AS [Model] ,
[vw_CS_OrderDetails].[Spec] AS [Spec] ,
[vw_CS_OrderDetails].[Title] AS [Title] ,
[vw_CS_OrderDetails].[Firstname] AS [Firstname] ,
[vw_CS_OrderDetails].[Surname] AS [Surname] ,
[vw_CS_OrderDetails].[Address1] AS [Address1] ,
[vw_CS_OrderDetails].[Address2] AS [Address2] ,
[vw_CS_OrderDetails].[Address3] AS [Address3] ,
[vw_CS_OrderDetails].[Town] AS [Town] ,
[vw_CS_OrderDetails].[County] AS [County] ,
[vw_CS_OrderDetails].[Postcode] AS [Postcode] ,
[vw_CS_OrderDetails].[HomePhone] AS [HomePhone] ,
[vw_CS_OrderDetails].[WorkPhone] AS [WorkPhone] ,
[vw_CS_OrderDetails].[MobilePhone] AS [MobilePhone] ,
[vw_CS_OrderDetails].[EMailAddress] AS [EMailAddress] ,
[vw_CS_OrderDetails].[AllowMarketing] AS [AllowMarketing] ,
[vw_CS_OrderDetails].[Manager] AS [Manager] ,
[vw_CS_OrderDetails].[FK_BranchID] AS [FK_BranchID]
FROM      [dbo].[vw_CS_OrderDetails] AS [vw_CS_OrderDetails]
) AS [Extent1]
WHERE     UPPER([Extent1].[RegNumber]) LIKE '%SD59BBO%'
ESCAPE N'~'
) AS [Project1]
) AS [Project1]
WHERE   [Project1].[row_number] > 0
ORDER BY [Project1].[DateCreated] DESC

这个手动版本要小得多，不到一秒钟就完成了。

考虑到第一个查询的效率非常低，我有没有办法在它创建的查询中影响EF6？

我可能不得不求助于存储过程，有什么好的模式可以先将EF代码与存储过程集成在一起吗？

编辑：根据Wahid Bitar的请求，这里是我用来创建上述SQL的LINQ。

var query = _dbSet
.Where(o => o.RegNumber.ToUpper().Contains(searchTerm))
.OrderByDescending(c => c.DateCreated)
.Skip(skip)
.Take(pageSize);

事实上，它在一些辅助方法中有点分散，但主要是这样。ToList()最终在query上被调用，枚举结果集。

编辑：根据请求手动启动SQL:

SELECT * 
FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY DateCreated DESC ) AS RowNum
,[Branch]
,[Salesman]
,[Status]
,[OrderID]
,[DateCreated]
,[DateCompleted]
,[RegNumber]
,[Make]
,[Model]
,[Spec]
,[Title]
,[Firstname]
,[Surname]
,[Address1]
,[Address2]
,[Address3]
,[Town]
,[County]
,[Postcode]
,[HomePhone]
,[WorkPhone]
,[MobilePhone]
,[EMailAddress]
,[AllowMarketing]
,[Manager]
,[FK_BranchID]
FROM [SalesmanOffice2].[dbo].[vw_CS_OrderDetails]
WHERE RegNumber LIKE '%SD59BBO%'
) AS NumberedRows
WHERE NumberedRows.RowNum BETWEEN 1 AND 15 ORDER BY RowNum

Answer 1

我注意到在EF查询中您调用了ToUpper，但在手动查询中没有这样做。如果RegNumber是一个索引字段，在进行比较之前将其传递给SQL的UPPER函数将使其无法使用该索引。这可能是性能较差的原因。

您可能有ToUpper，因为.Net的字符串比较区分大小写。然而，当执行EF查询时，它只是将其转换为SQL比较。因此，由于SQL不区分大小写(默认情况下)，EF不区分大小字母。

去掉对ToUpper的调用，看看这是否能提高性能。

编辑

看起来EF也将SD57WBO视为unicode。这也会打乱你的索引。

RegNumber的数据类型是什么？如果是varchar/char而不是nvarchar/nchar，则可能需要指定列不是unicode。在EF Code First，Fluent API中，您需要在属性上使用命令IsUnicode(false)。

Answer 2

让我补充一下，order by是不同的。如果有一个top子句，它可能会强制在top之前实现。手动查询中没有订单依据。我相信，如果你通过删除订单，你会得到更简单的SQL。。。

到目前为止，这基本上意味着您真正比较了两个不同的查询。对不起，既然cadrell0和我指出了语义上的差异，这个问题还剩下什么？一个更慢——是的。SQL是垃圾——是的。但是，嘿，你做不同的事情，这就是速度差异的来源。

Answer 3

正如@Jim Woolley所强调的，我对试图进行部分匹配的列没有全文索引。鉴于视图非常大，这意味着在没有索引的情况下对每一行进行比较。我还没有在启用文本索引的视图上测试这些查询，所以不能肯定，但我已经测试了当搜索词与列完全匹配时，我使用的是相等运算符，例如：

WHERE RegNumber = 'SD59BBO'

在所有其他事物都相同的情况下，查询在更理想的时间执行。