Linq 2 SQL查询优化

Question

我在使用linq to sql查询时遇到了真正的问题，这可能是因为我的技能有点欠缺，但如果有人能看看我如何优化查询，我将不胜感激，因为生成的SQL太可怕了

该查询要做的是尝试获取sales details表中连接的位置的库存概览，以根据部门id (即部门中的所有产品)找出最后一次销售的时间和该位置的库存数量，因此使用Max()和Sum()

var query1 = (
            from sl in slRepo
            where sl.Product.bsDepartmentId < 90 && sl.Product.SubDepartment.Department.Id == departmentId
            group sl by sl.Location into gbsl
            select new
            {
                ProductId = gbsl.FirstOrDefault().Product.Id,
                LocationId = gbsl.FirstOrDefault().Location.Id,
                Location = gbsl.FirstOrDefault().Location.Name,
                Quantity = gbsl.Sum(x => x.CurrentStock),
                LastInvoice = gbsl.Max(x => x.LastInvoice)
            });

var query2 = (
            from sd in sdRepo
            where sd.Product.SubDepartment.Department.Id == departmentId
            group sd by sd.Location.Id into g
            select new
            {
                LocationId = g.FirstOrDefault().Location.Id,
                LastSale = g.Max(x => x.TransactionDate)
            });

var query3 = (
            from q1 in query1
            join q2 in query2 on q1.LocationId equals q2.LocationId into temp
            from j in temp.DefaultIfEmpty()
            select new XStockOverviewDto
            {
                Location = q1.Location,
                Quantity = q1.Quantity,
                LastInvoice = q1.LastInvoice,
                LastSale = j.LastSale
            });

        return query3;

然后从这里生成的SQL是...这似乎是使用相同的数据集来分别查询MAX和SUM，而实际上它应该一起完成，这使得查询变得非常复杂，并且实际查询需要很长时间

SELECT 
1 AS [C1], 
[Project10].[Name] AS [Name], 
[Project10].[C2] AS [C2], 
[Project10].[C3] AS [C3], 
 CAST( [Project16].[C2] AS datetime2) AS [C4]
FROM   (SELECT 
    [Project9].[Name] AS [Name], 
    [Project9].[C1] AS [C1], 
    [Project9].[C2] AS [C2], 
    (SELECT 
        MAX([Filter9].[LastInvoice]) AS [A1]
        FROM ( SELECT [Extent18].[LastInvoice] AS [LastInvoice], [Extent20].[Department_Id] AS [Department_Id], [Extent21].[Id] AS [Id1]
            FROM    [dbo].[XStockLevels] AS [Extent18]
            INNER JOIN [dbo].[XProducts] AS [Extent19] ON [Extent18].[Product_Id] = [Extent19].[Id]
            LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent20] ON [Extent19].[SubDepartment_Id] = [Extent20].[Id]
            LEFT OUTER JOIN [dbo].[XLocations] AS [Extent21] ON [Extent18].[Location_Id] = [Extent21].[Id]
            WHERE [Extent19].[bsDepartmentId] < 90
        )  AS [Filter9]
        WHERE ([Filter9].[Department_Id] = @p__linq__0) AND (([Project9].[Id] = [Filter9].[Id1]) OR (([Project9].[Id] IS NULL) AND ([Filter9].[Id1] IS NULL)))) AS [C3]
    FROM ( SELECT 
        [Project8].[Id] AS [Id], 
        [Project8].[Name] AS [Name], 
        [Project8].[C1] AS [C1], 
        (SELECT 
            SUM([Filter7].[CurrentStock]) AS [A1]
            FROM ( SELECT [Extent14].[CurrentStock] AS [CurrentStock], [Extent16].[Department_Id] AS [Department_Id], [Extent17].[Id] AS [Id2]
                FROM    [dbo].[XStockLevels] AS [Extent14]
                INNER JOIN [dbo].[XProducts] AS [Extent15] ON [Extent14].[Product_Id] = [Extent15].[Id]
                LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent16] ON [Extent15].[SubDepartment_Id] = [Extent16].[Id]
                LEFT OUTER JOIN [dbo].[XLocations] AS [Extent17] ON [Extent14].[Location_Id] = [Extent17].[Id]
                WHERE [Extent15].[bsDepartmentId] < 90
            )  AS [Filter7]
            WHERE ([Filter7].[Department_Id] = @p__linq__0) AND (([Project8].[Id] = [Filter7].[Id2]) OR (([Project8].[Id] IS NULL) AND ([Filter7].[Id2] IS NULL)))) AS [C2]
        FROM ( SELECT 
            [Project7].[Id] AS [Id], 
            [Extent13].[Name] AS [Name], 
            [Project7].[C1] AS [C1]
            FROM   (SELECT 
                [Project5].[Id] AS [Id], 
                [Project5].[C1] AS [C1], 
                (SELECT TOP (1) 
                    [Filter5].[Location_Id] AS [Location_Id]
                    FROM ( SELECT [Extent9].[Location_Id] AS [Location_Id], [Extent11].[Department_Id] AS [Department_Id], [Extent12].[Id] AS [Id3]
                        FROM    [dbo].[XStockLevels] AS [Extent9]
                        INNER JOIN [dbo].[XProducts] AS [Extent10] ON [Extent9].[Product_Id] = [Extent10].[Id]
                        LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent11] ON [Extent10].[SubDepartment_Id] = [Extent11].[Id]
                        LEFT OUTER JOIN [dbo].[XLocations] AS [Extent12] ON [Extent9].[Location_Id] = [Extent12].[Id]
                        WHERE [Extent10].[bsDepartmentId] < 90
                    )  AS [Filter5]
                    WHERE ([Filter5].[Department_Id] = @p__linq__0) AND (([Project5].[Id] = [Filter5].[Id3]) OR (([Project5].[Id] IS NULL) AND ([Filter5].[Id3] IS NULL)))) AS [C2]
                FROM ( SELECT 
                    [Project4].[Id] AS [Id], 
                    [Project4].[C1] AS [C1]
                    FROM ( SELECT 
                        [Project2].[Id] AS [Id], 
                        (SELECT TOP (1) 
                            [Filter3].[Location_Id] AS [Location_Id]
                            FROM ( SELECT [Extent5].[Location_Id] AS [Location_Id], [Extent7].[Department_Id] AS [Department_Id], [Extent8].[Id] AS [Id4]
                                FROM    [dbo].[XStockLevels] AS [Extent5]
                                INNER JOIN [dbo].[XProducts] AS [Extent6] ON [Extent5].[Product_Id] = [Extent6].[Id]
                                LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent7] ON [Extent6].[SubDepartment_Id] = [Extent7].[Id]
                                LEFT OUTER JOIN [dbo].[XLocations] AS [Extent8] ON [Extent5].[Location_Id] = [Extent8].[Id]
                                WHERE [Extent6].[bsDepartmentId] < 90
                            )  AS [Filter3]
                            WHERE ([Filter3].[Department_Id] = @p__linq__0) AND (([Project2].[Id] = [Filter3].[Id4]) OR (([Project2].[Id] IS NULL) AND ([Filter3].[Id4] IS NULL)))) AS [C1]
                        FROM ( SELECT 
                            [Distinct1].[Id] AS [Id]
                            FROM ( SELECT DISTINCT 
                                [Filter1].[Id5] AS [Id], 
                                [Filter1].[bsLocationId] AS [bsLocationId], 
                                [Filter1].[Name] AS [Name], 
                                [Filter1].[Code1] AS [Code1], 
                                [Filter1].[Code2] AS [Code2], 
                                [Filter1].[Code3] AS [Code3], 
                                [Filter1].[Code4] AS [Code4], 
                                [Filter1].[Code5] AS [Code5], 
                                [Filter1].[Code6] AS [Code6], 
                                [Filter1].[Code7] AS [Code7], 
                                [Filter1].[Code8] AS [Code8], 
                                [Filter1].[bsCompanyId] AS [bsCompanyId], 
                                [Filter1].[Group] AS [Group]
                                FROM ( SELECT [Extent3].[Department_Id] AS [Department_Id], [Extent4].[Id] AS [Id5], [Extent4].[bsLocationId] AS [bsLocationId], [Extent4].[Name] AS [Name], [Extent4].[Code1] AS [Code1], [Extent4].[Code2] AS [Code2], [Extent4].[Code3] AS [Code3], [Extent4].[Code4] AS [Code4], [Extent4].[Code5] AS [Code5], [Extent4].[Code6] AS [Code6], [Extent4].[Code7] AS [Code7], [Extent4].[Code8] AS [Code8], [Extent4].[bsCompanyId] AS [bsCompanyId], [Extent4].[Group] AS [Group]
                                    FROM    [dbo].[XStockLevels] AS [Extent1]
                                    INNER JOIN [dbo].[XProducts] AS [Extent2] ON [Extent1].[Product_Id] = [Extent2].[Id]
                                    LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent3] ON [Extent2].[SubDepartment_Id] = [Extent3].[Id]
                                    LEFT OUTER JOIN [dbo].[XLocations] AS [Extent4] ON [Extent1].[Location_Id] = [Extent4].[Id]
                                    WHERE [Extent2].[bsDepartmentId] < 90
                                )  AS [Filter1]
                                WHERE [Filter1].[Department_Id] = @p__linq__0
                            )  AS [Distinct1]
                        )  AS [Project2]
                    )  AS [Project4]
                )  AS [Project5] ) AS [Project7]
            LEFT OUTER JOIN [dbo].[XLocations] AS [Extent13] ON [Project7].[C2] = [Extent13].[Id]
        )  AS [Project8]
    )  AS [Project9] ) AS [Project10]
LEFT OUTER JOIN  (SELECT 
    [Project15].[C1] AS [C1], 
    (SELECT 
        MAX([Extent28].[TransactionDate]) AS [A1]
        FROM   [dbo].[XSalesDetails] AS [Extent28]
        LEFT OUTER JOIN [dbo].[XProducts] AS [Extent29] ON [Extent28].[Product_Id] = [Extent29].[Id]
        INNER JOIN [dbo].[XSubDepartments] AS [Extent30] ON [Extent29].[SubDepartment_Id] = [Extent30].[Id]
        WHERE ([Extent30].[Department_Id] = @p__linq__1) AND (([Project15].[Location_Id] = [Extent28].[Location_Id]) OR (([Project15].[Location_Id] IS NULL) AND ([Extent28].[Location_Id] IS NULL)))) AS [C2]
    FROM ( SELECT 
        [Project14].[Location_Id] AS [Location_Id], 
        [Project14].[C1] AS [C1]
        FROM ( SELECT 
            [Project12].[Location_Id] AS [Location_Id], 
            (SELECT TOP (1) 
                [Extent25].[Location_Id] AS [Location_Id]
                FROM   [dbo].[XSalesDetails] AS [Extent25]
                LEFT OUTER JOIN [dbo].[XProducts] AS [Extent26] ON [Extent25].[Product_Id] = [Extent26].[Id]
                INNER JOIN [dbo].[XSubDepartments] AS [Extent27] ON [Extent26].[SubDepartment_Id] = [Extent27].[Id]
                WHERE ([Extent27].[Department_Id] = @p__linq__1) AND (([Project12].[Location_Id] = [Extent25].[Location_Id]) OR (([Project12].[Location_Id] IS NULL) AND ([Extent25].[Location_Id] IS NULL)))) AS [C1]
            FROM ( SELECT 
                @p__linq__1 AS [p__linq__1], 
                [Distinct2].[Location_Id] AS [Location_Id]
                FROM ( SELECT DISTINCT 
                    [Extent22].[Location_Id] AS [Location_Id]
                    FROM   [dbo].[XSalesDetails] AS [Extent22]
                    LEFT OUTER JOIN [dbo].[XProducts] AS [Extent23] ON [Extent22].[Product_Id] = [Extent23].[Id]
                    INNER JOIN [dbo].[XSubDepartments] AS [Extent24] ON [Extent23].[SubDepartment_Id] = [Extent24].[Id]
                    WHERE [Extent24].[Department_Id] = @p__linq__1
                )  AS [Distinct2]
            )  AS [Project12]
        )  AS [Project14]
    )  AS [Project15] ) AS [Project16] ON ([Project10].[C1] = [Project16].[C1]) OR (([Project10].[C1] IS NULL) AND ([Project16].[C1] IS NULL))

我实际上尝试转换为链接的查询是这样的

DECLARE @DeptId INT = 1;

SELECT Location_Id, XLocations.Name, CurrentStock, LastInvoice, LastSale
FROM
(SELECT Location_Id, SUM(CurrentStock) AS CurrentStock, MAX(LastInvoice) AS LastInvoice, MAX(LastSale) AS LastSale
   FROM XStockLevels
LEFT JOIN 
(SELECT XProducts.bsDepartmentId, XProducts.bsSubDepartmentId, XProducts.bsItemId, XProducts.Id, LastSale
   FROM XProducts 
  INNER JOIN XSubDepartments ON XProducts.SubDepartment_Id = XSubDepartments.Id
   LEFT JOIN (SELECT Product_Id, MAX(TransactionDate) AS LastSale FROM XSalesDetails GROUP BY Product_Id) XSD ON XSD.Product_Id = XProducts.Id
  WHERE XProducts.bsDepartmentId<90 AND XSubDepartments.Department_Id = @DeptId) XP
ON XStockLevels.Product_Id = XP.Id
GROUP BY Location_Id) Z 
INNER JOIN XLocations ON Z.Location_Id = XLocations.Id

因此，我认为需要进行一些真正的优化，而我似乎真的找不出我在linq查询中做错了什么。

干杯乔。

Answer 1

是的，当您的查询变得复杂时，linq to sql和其他类似的生成器往往会失败。

您的选择是绕过linq手动执行查询，或者在数据库上创建一个函数并执行表单linq。

Answer 2

通常，SQL server查询优化器会对其进行排序。

首先，通过将“生成的”SQL放入SQL management studio并请求查询计划，来查看结果查询计划。如果您不习惯使用查询计划，则需要对它有所了解，但这项技能是值得学习的。

然后，如果您仍然发现生成的SQL有问题，请检查索引是否正确等。

然后，如果所有其他方法都失败了，您需要创建一个存储的proc。或者对数据库执行多个Ling to SQL查询，并将结果组合到C#中。

Answer 3

首先要改进的是替换所有的表达式，比如...

gbsl.FirstOrDefault().Location.Id

由...

gbsl.Key.Id

因为它有相同的效果，但是FirstOrDefault()被转换成非常昂贵的SQL。

此外，在第一个LINQ查询中，您应该从投影中删除ProductId和Location属性，因为它们不会在任何地方使用。

我非常确定，如果你做了这些事情，你会更接近你最终实现的查询，并且它更具可读性。

稍微离题(因为它在这里还没有发挥作用，但经过微小的更改后可能会起作用)是LINQ-to-SQL在分组方面的效率可能非常低。如果您操作“错误”(见下文)，它可能会执行GROUP BY查询，然后对每个组执行查询，以填充这些组。

这是因为在SQL中，group by是“破坏性的”：它不会从查询表中返回完整的记录。如果你这样做了

SELECT a, SUM(b)
FROM   tableA
GROUP BY a

您将得到一个由两个值组成的结果集，而不是tableA中的其他值。然而，在LINQ中，您通常不仅对聚合感兴趣，而且可能只想按某些属性对完整实体进行分组，如下面这个简单的示例所示：

from a in As
group a by a.Type into g
select new { Type = g.Key, As = g }

这将首先执行一个GROUP BY a.Type查询，然后执行尽可能多的查询，以获得每个组的实体。对于这些类型的分组，最好先获取数据(一个查询)，然后在内存中进行分组。

(这是Entity Framework击败LINQ-to-SQL wrt查询生成的罕见领域之一)