LINQ查询速度慢，创建超时；生成的SQL是否正常
本文关键字：SQL 是否 速度慢 查询 创建 超时 LINQ | 更新日期: 2023-09-27 18:01:29
我有一个相当复杂的LINQ查询，它通常非常慢，它会创建一个System.Data.SqlClient.SqlException："等待操作超时"。
但是，当我记录生成的SQL(通过将TextWriter分配给DataContext的Log(，并直接在SQL Server上执行它时，它在大约4秒内完成，这很好。
差异来自哪里，如何调试？
编辑：我还注意到，在SqlServerManagementStudio的活动监视器中，当从.NET执行查询时，处理器时间会飙升至100%，但当我执行生成的Sql查询时，只有3%左右。
我不确定发布我的代码会有什么帮助，但由于它是被请求的，下面是包含查询的代码：
var Db = MyProject.GetDataContext();
var statusPaymentSuccess = new string[] { "SUCCESS", "REMBOURS", "AFTERPAY" };
var items = Db.Orders.Where(item =>
(siteid == null || item.SiteId == siteid) &&
(ls_list.Contains(item.OrderOrderLifeCycles.OrderByDescending(it => it.Id).First().OrderLifeCycleId)) &&
(item.OrderOrderPaymentStatus.Any(ops => statusPaymentSuccess.Contains(ops.OrderPaymentStatus.Code)) &&
(CycleID == null || item.OrderOrderLifeCycles.First().OrderLifeCycleId == CycleID) &&
(LocationID == null || item.SaleLocationId == LocationID) &&
(string.IsNullOrEmpty(SalesPerson) || item.EmployeeName.ToLower() == SalesPerson.ToLower()))
);
var betweenorders = items.Select(it => new OrderBetween()
{
FirstPayDate = it.OrderOrderPaymentStatus.FirstOrDefault(ops => statusPaymentSuccess.Contains(ops.OrderPaymentStatus.Code)).DateTime,
OrderTotal = it.TotalAmount,
VatTotal = it.OrderItems.Sum(it2 => it2.BTWAmount ?? 0),
Quantity = it.OrderItems.Count,
SiteId = it.SiteId
});
return betweenorders.Where(item => item.FirstPayDate >= start && item.FirstPayDate < stop)
.GroupBy(item => item.FirstPayDate.Value.Year + "-" + item.FirstPayDate.Value.Month).Select(
item =>
new SaleTotal()
{
Count = item.Sum(sub => sub.Quantity),
Month = item.FirstOrDefault().FirstPayDate.Value.Year + "-" + item.FirstOrDefault().FirstPayDate.Value.Month.ToString().PadLeft(2, '0'),
Total = item.Sum(sub => sub.OrderTotal),
VAT = item.Sum(sub => sub.VatTotal)
}).OrderBy(item => item.Month).ToArray();
其中CCD_ 5是包含CCD_ 7 ID的CCD_。
从日志中提取的生成的SQL查询：
DECLARE @p0 NVarChar(4000) = 'SUCCESS'
DECLARE @p1 NVarChar(4000) = 'REMBOURS'
DECLARE @p2 NVarChar(4000) = 'AFTERPAY'
DECLARE @p3 Decimal(31,2) = '0'
DECLARE @p4 NVarChar(4000) = '-'
DECLARE @p5 DateTime = '2016-06-01'
DECLARE @p6 DateTime = '2016-09-01'
DECLARE @p7 Int = '4'
DECLARE @p8 Int = '5'
DECLARE @p9 Int = '8'
DECLARE @p10 NVarChar(4000) = 'SUCCESS'
DECLARE @p11 NVarChar(4000) = 'REMBOURS'
DECLARE @p12 NVarChar(4000) = 'AFTERPAY'
DECLARE @p13 NVarChar(4000) = '-'
DECLARE @p14 NVarChar(4000) = '-'
DECLARE @p15 Int = '2'
DECLARE @p16 NChar(1) = '0'
SELECT [t64].[value] AS [Month], [t64].[value2] AS [Count], [t64].[value22] AS [Total], [t64].[value3] AS [VAT]
FROM (
SELECT ((CONVERT(NVarChar,DATEPART(Year, (
SELECT [t23].[value]
FROM (
SELECT TOP (1) [t18].[value]
FROM (
SELECT (
SELECT [t17].[DateTime]
FROM (
SELECT TOP (1) [t15].[DateTime]
FROM [dbo].[OrderOrderPaymentStatus] AS [t15]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t16] ON [t16].[Id] = [t15].[OrderPaymentStatusId]
WHERE ([t16].[Code] IN (@p0, @p1, @p2)) AND ([t15].[OrderId] = [t14].[Id])
) AS [t17]
) AS [value], [t14].[Id]
FROM [dbo].[Order] AS [t14]
) AS [t18]
WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t18].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t18].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t18].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t18].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t18].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t18].[value]))))))) AND ([t18].[value] >= @p5) AND ([t18].[value] < @p6) AND (((
SELECT [t20].[OrderLifeCycleId]
FROM (
SELECT TOP (1) [t19].[OrderLifeCycleId]
FROM [dbo].[OrderOrderLifeCycle] AS [t19]
WHERE [t19].[OrderId] = [t18].[Id]
ORDER BY [t19].[Id] DESC
) AS [t20]
)) IN (@p7, @p8, @p9)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[OrderOrderPaymentStatus] AS [t21]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t22] ON [t22].[Id] = [t21].[OrderPaymentStatusId]
WHERE ([t22].[Code] IN (@p10, @p11, @p12)) AND ([t21].[OrderId] = [t18].[Id])
))
) AS [t23]
)))) + @p14) + (
(CASE
WHEN (CONVERT(Int,DATALENGTH(CONVERT(NVarChar,DATEPART(Month, (
SELECT [t63].[value]
FROM (
SELECT TOP (1) [t58].[value]
FROM (
SELECT (
SELECT [t57].[DateTime]
FROM (
SELECT TOP (1) [t55].[DateTime]
FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
) AS [t57]
) AS [value], [t54].[Id]
FROM [dbo].[Order] AS [t54]
) AS [t58]
WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
SELECT [t60].[OrderLifeCycleId]
FROM (
SELECT TOP (1) [t59].[OrderLifeCycleId]
FROM [dbo].[OrderOrderLifeCycle] AS [t59]
WHERE [t59].[OrderId] = [t58].[Id]
ORDER BY [t59].[Id] DESC
) AS [t60]
)) IN (@p7, @p8, @p9)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
))
) AS [t63]
)))) / 2)) >= @p15 THEN CONVERT(NVarChar,DATEPART(Month, (
SELECT [t63].[value]
FROM (
SELECT TOP (1) [t58].[value]
FROM (
SELECT (
SELECT [t57].[DateTime]
FROM (
SELECT TOP (1) [t55].[DateTime]
FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
) AS [t57]
) AS [value], [t54].[Id]
FROM [dbo].[Order] AS [t54]
) AS [t58]
WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
SELECT [t60].[OrderLifeCycleId]
FROM (
SELECT TOP (1) [t59].[OrderLifeCycleId]
FROM [dbo].[OrderOrderLifeCycle] AS [t59]
WHERE [t59].[OrderId] = [t58].[Id]
ORDER BY [t59].[Id] DESC
) AS [t60]
)) IN (@p7, @p8, @p9)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
))
) AS [t63]
)))
ELSE REPLICATE(@p16, @p15 - (CONVERT(Int,DATALENGTH(CONVERT(NVarChar,DATEPART(Month, (
SELECT [t63].[value]
FROM (
SELECT TOP (1) [t58].[value]
FROM (
SELECT (
SELECT [t57].[DateTime]
FROM (
SELECT TOP (1) [t55].[DateTime]
FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
) AS [t57]
) AS [value], [t54].[Id]
FROM [dbo].[Order] AS [t54]
) AS [t58]
WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
SELECT [t60].[OrderLifeCycleId]
FROM (
SELECT TOP (1) [t59].[OrderLifeCycleId]
FROM [dbo].[OrderOrderLifeCycle] AS [t59]
WHERE [t59].[OrderId] = [t58].[Id]
ORDER BY [t59].[Id] DESC
) AS [t60]
)) IN (@p7, @p8, @p9)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
))
) AS [t63]
)))) / 2))) + (CONVERT(NVarChar,DATEPART(Month, (
SELECT [t63].[value]
FROM (
SELECT TOP (1) [t58].[value]
FROM (
SELECT (
SELECT [t57].[DateTime]
FROM (
SELECT TOP (1) [t55].[DateTime]
FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
) AS [t57]
) AS [value], [t54].[Id]
FROM [dbo].[Order] AS [t54]
) AS [t58]
WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
SELECT [t60].[OrderLifeCycleId]
FROM (
SELECT TOP (1) [t59].[OrderLifeCycleId]
FROM [dbo].[OrderOrderLifeCycle] AS [t59]
WHERE [t59].[OrderId] = [t58].[Id]
ORDER BY [t59].[Id] DESC
) AS [t60]
)) IN (@p7, @p8, @p9)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
))
) AS [t63]
))))
END)) AS [value], [t13].[value] AS [value2], [t13].[value2] AS [value22], [t13].[value3]
FROM (
SELECT SUM([t8].[value3]) AS [value], SUM([t8].[TotalAmount]) AS [value2], SUM([t8].[value22]) AS [value3], [t8].[value] AS [value4]
FROM (
SELECT ((CONVERT(NVarChar,DATEPART(Year, [t7].[value]))) + @p4) + (CONVERT(NVarChar,DATEPART(Month, [t7].[value]))) AS [value], [t7].[value] AS [value2], [t7].[Id], [t7].[value3], [t7].[TotalAmount], [t7].[value2] AS [value22]
FROM (
SELECT (
SELECT [t3].[DateTime]
FROM (
SELECT TOP (1) [t1].[DateTime]
FROM [dbo].[OrderOrderPaymentStatus] AS [t1]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t2] ON [t2].[Id] = [t1].[OrderPaymentStatusId]
WHERE ([t2].[Code] IN (@p0, @p1, @p2)) AND ([t1].[OrderId] = [t0].[Id])
) AS [t3]
) AS [value], [t0].[TotalAmount], (
SELECT SUM([t5].[value])
FROM (
SELECT COALESCE([t4].[BTWAmount],@p3) AS [value], [t4].[OrderId]
FROM [dbo].[OrderItem] AS [t4]
) AS [t5]
WHERE [t5].[OrderId] = [t0].[Id]
) AS [value2], (
SELECT COUNT(*)
FROM [dbo].[OrderItem] AS [t6]
WHERE [t6].[OrderId] = [t0].[Id]
) AS [value3], [t0].[Id]
FROM [dbo].[Order] AS [t0]
) AS [t7]
) AS [t8]
WHERE ([t8].[value2] >= @p5) AND ([t8].[value2] < @p6) AND (((
SELECT [t10].[OrderLifeCycleId]
FROM (
SELECT TOP (1) [t9].[OrderLifeCycleId]
FROM [dbo].[OrderOrderLifeCycle] AS [t9]
WHERE [t9].[OrderId] = [t8].[Id]
ORDER BY [t9].[Id] DESC
) AS [t10]
)) IN (@p7, @p8, @p9)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[OrderOrderPaymentStatus] AS [t11]
INNER JOIN [dbo].[OrderPaymentStatus] AS [t12] ON [t12].[Id] = [t11].[OrderPaymentStatusId]
WHERE ([t12].[Code] IN (@p10, @p11, @p12)) AND ([t11].[OrderId] = [t8].[Id])
))
GROUP BY [t8].[value]
) AS [t13]
) AS [t64]
ORDER BY [t64].[value]
改进查询的一个明显方法在第一部分中立即引人注目：
var items = Db.Orders.Where(item =>
(siteid == null || item.SiteId == siteid)
&& (ls_list.Contains(item.OrderOrderLifeCycles.OrderByDescending(it => it.Id).First().OrderLifeCycleId))
&& (item.OrderOrderPaymentStatus.Any(ops => statusPaymentSuccess.Contains(ops.OrderPaymentStatus.Code))
&& (CycleID == null || item.OrderOrderLifeCycles.First().OrderLifeCycleId == CycleID)
&& (LocationID == null || item.SaleLocationId == LocationID)
&& (string.IsNullOrEmpty(SalesPerson) || item.EmployeeName.ToLower() == SalesPerson.ToLower()))
);
请记住，整个LINQ语句都被转换为SQL，包括所有这些null检查。这使得SQL查询变得不必要地复杂，查询优化器更难处理。(顺便说一下，您显示了一个属于另一个LINQ语句的SQL查询(。
处理可为null条件的推荐方法是编写查询：
IQueryable<Order> items = var items = Db.Orders;
if(siteid != null)
{
items = items.Where(item => item.SiteId == siteid);
}
if (CycleID != null)
{
items = items.Where(item => item.OrderOrderLifeCycles.First().OrderLifeCycleId == CycleID);
}
// etc.
另一件事是
item.EmployeeName.ToLower() == SalesPerson.ToLower()
这将在应用搜索条件之前转换EmployeeName字段值。这意味着EmployeeName上的任何索引都不能使用(也称为不可搜索(。我认为您可以删除ToLower()调用。在SQL查询中，使用EmployeeName字段的数据库排序规则，默认情况下很可能不区分大小写(CI(。
最后，您可以考虑执行分组。。。
GroupBy(item => item.FirstPayDate.Value.Year + "-" + item.FirstPayDate.Value.Month)
在内存中(LINQ到对象(，而不是在数据库中。即：
return betweenorders.Where(item => item.FirstPayDate >= start && item.FirstPayDate < stop)
.AsEnumerable() // Switch to LINQ to objects
.GroupBy(...
分组被翻译为ORDER BY(由于超出本答案范围的原因而不是GROUP BY(，并且数据库字段FirstPayDate的转换再次禁用索引。它还降低了SQL查询的复杂性，而且在内存中执行此操作可能不是一个繁重的操作。
SA回答中解释了部分差异：
SSMS通常使用ARITHABORT ON，代码通常使用ARITHABORT OFF-这基本上是一个如何处理如果代码中的数学行出现错误，就会发生这种情况，例如除法零。
不过，这里最重要的是，这两种方法都有不同的执行计划-这就是为什么同样的事情可能(随机(花费很多在网站上的时间比在SSMS中的时间长。
执行计划是根据第一次的估计编制的它被使用，所以你随机发现执行计划是以一种糟糕的方式缓存，适合您的第一个查询，但很糟糕用于后续查询。这就是这里发生的事情，也是为什么它突然又开始工作了——创建了一个新的查询计划在存储过程更改之后。
使用SET ARITHABORT OFF在SSMS中执行查询已经大大降低了查询的执行速度。不过，它在代码上仍然比LINQ到SQL版本快至少300%，所以我会更新这个答案，如果我找到它的话
编辑：SSMS不必像LINQ那样处理对象跟踪，因此在处理只进行读取(相对于写入(的查询时，我们可以通过禁用对象跟踪来加速LINQ到SQL的执行。然后，您必须手动指定要加载的对象，而这些对象(据我所知(只能通过反复尝试来确定
对于我的查询，可以使用以下代码来处理跟踪和对象加载的禁用：
db.ObjectTrackingEnabled = false;
var lo = new DataLoadOptions();
lo.LoadWith<Order>(x => x.OrderOrderPaymentStatus);
lo.LoadWith<OrderOrderPaymentStatus>(x => x.OrderPaymentStatus);
db.LoadOptions = lo;