c# - LINQ 查询速度慢，创建超时；生成的 SQL 没问题？

Question

我有一个相当复杂的 LINQ 查询，它通常很慢，它创建了一个 System.Data.SqlClient.SqlException : "等待操作超时"。

但是，当我记录生成的 SQL(通过将 TextWriter 分配给 DataContext 的 Log )，并直接在 SQL Server 上执行时，它在大约 4 秒内完成，这很好。

差异从何而来，如何调试？

编辑:我还注意到，在 Sql Server Management Studio 的事件监视器中，当从 .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();

哪里ls_list是 List<int>包含 OrderOrderLifeCycles ID。

从日志中提取的生成的 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]

Answer 1

改进查询的一个明显方法在第一部分立即引起了人们的注意:

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 查询)。

处理可空条件的推荐方法是编写查询:

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 上的任何索引(也称为 not sargable)。我认为您可以删除 ToLower() 调用。在 SQL 查询中，使用了 EmployeeName 字段的数据库排序规则，默认情况下很可能不区分大小写 (CI)。

最后，你可以考虑执行分组...

GroupBy(item => item.FirstPayDate.Value.Year + "-" + item.FirstPayDate.Value.Month)

... 在内存中(LINQ to objects)而不是在数据库中。即:

return betweenorders.Where(item => item.FirstPayDate >= start && item.FirstPayDate < stop)
.AsEnumerable() // Switch to LINQ to objects
.GroupBy(...

分组被翻译为 ORDER BY(不是 GROUP BY，原因超出了这个答案的范围)，而且，数据库字段的转换 FirstPayDate 禁用索引。它还使 SQL 查询不那么复杂，并且在内存中执行此操作可能不是繁重的操作。