c# - 优化 LINQ-to-SQL 查询

Question

我有一个非常繁重的 LINQ-to-SQL 查询，它对不同的表进行多次连接以返回匿名类型。问题是，如果返回的行数相当大(> 200)，那么查询会变得非常慢并最终超时。我知道我可以增加数据上下文超时设置，但这是最后的手段。

我只是想知道，如果我将查询拆分并作为 LINQ-to-Objects 查询进行比较，我的查询是否会工作得更好，这样我什至可以使用 PLINQ 来最大化处理能力。但我对我来说是一个陌生的概念，我无法理解如何拆分它。谁能提供任何建议？我不是要求为我编写代码，只是一些关于我如何改进它的一般指导会很棒。

请注意，我已确保数据库具有我要加入的所有正确 key ，并且我已确保这些 key 是最新的。

查询如下:

var cons = (from c in dc.Consignments
            join p in dc.PODs on c.IntConNo equals p.Consignment into pg
            join d in dc.Depots on c.DeliveryDepot equals d.Letter
            join sl in dc.Accounts on c.Customer equals sl.LegacyID
            join ss in dc.Accounts on sl.InvoiceAccount equals ss.LegacyID
            join su in dc.Accounts on c.Subcontractor equals su.Name into sug
            join sub in dc.Accountsubbies on ss.ID equals sub.AccountID into subg
            where (sug.FirstOrDefault() == null
                || sug.FirstOrDefault().Customer == false)
            select new
            {
                 ID = c.ID,
                 IntConNo = c.IntConNo,
                 LegacyID = c.LegacyID,
                 PODs = pg.DefaultIfEmpty(),
                 TripNumber = c.TripNumber,
                 DropSequence = c.DropSequence,
                 TripDate = c.TripDate,
                 Depot = d.Name,
                 CustomerName = c.Customer,
                 CustomerReference = c.CustomerReference,
                 DeliveryName = c.DeliveryName,
                 DeliveryTown = c.DeliveryTown,
                 DeliveryPostcode = c.DeliveryPostcode,
                 VehicleText = c.VehicleReg + c.Subcontractor,
                 SubbieID = sug.DefaultIfEmpty().FirstOrDefault().ID.ToString(),
                 SubbieList = subg.DefaultIfEmpty(),
                 ScanType = ss.PODScanning == null ? 0 : ss.PODScanning
            });

这是按要求生成的 SQL:

    {SELECT [t0].[ID], [t0].[IntConNo], [t0].[LegacyID], [t6].[test], [t6].[ID] AS [ID2], [t6].[Consignment], [t6].[Status], [t6].[NTConsignment], [t6].[CustomerRef], [t6].[Timestamp], [t6].[SignedBy], [t6].[Clause], [t6].[BarcodeNumber], [t6].[MainRef], [t6].[Notes], [t6].[ConsignmentRef], [t6].[PODedBy], (
    SELECT COUNT(*)
    FROM (
        SELECT NULL AS [EMPTY]
        ) AS [t10]
    LEFT OUTER JOIN (
        SELECT NULL AS [EMPTY]
        FROM [dbo].[PODs] AS [t11]
        WHERE [t0].[IntConNo] = [t11].[Consignment]
        ) AS [t12] ON 1=1 
    ) AS [value], [t0].[TripNumber], [t0].[DropSequence], [t0].[TripDate], [t1].[Name] AS [Depot], [t0].[Customer] AS [CustomerName], [t0].[CustomerReference], [t0].[DeliveryName], [t0].[DeliveryTown], [t0].[DeliveryPostcode], [t0].[VehicleReg] + [t0].[Subcontractor] AS [VehicleText], CONVERT(NVarChar,(
    SELECT [t16].[ID]
    FROM (
        SELECT TOP (1) [t15].[ID]
        FROM (
            SELECT NULL AS [EMPTY]
            ) AS [t13]
        LEFT OUTER JOIN (
            SELECT [t14].[ID]
            FROM [dbo].[Account] AS [t14]
            WHERE [t0].[Subcontractor] = [t14].[Name]
            ) AS [t15] ON 1=1 
        ORDER BY [t15].[ID]
        ) AS [t16]
    )) AS [SubbieID], 
    (CASE 
        WHEN [t3].[PODScanning] IS NULL THEN @p0
        ELSE [t3].[PODScanning]
     END) AS [ScanType], [t3].[ID] AS [ID3]
FROM [dbo].[Consignments] AS [t0]
INNER JOIN [dbo].[Depots] AS [t1] ON [t0].[DeliveryDepot] = [t1].[Letter]
INNER JOIN [dbo].[Account] AS [t2] ON [t0].[Customer] = [t2].[LegacyID]
INNER JOIN [dbo].[Account] AS [t3] ON [t2].[InvoiceAccount] = [t3].[LegacyID]
LEFT OUTER JOIN ((
        SELECT NULL AS [EMPTY]
        ) AS [t4]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t5].[ID], [t5].[Consignment], [t5].[Status], [t5].[NTConsignment], [t5].[CustomerRef], [t5].[Timestamp], [t5].[SignedBy], [t5].[Clause], [t5].[BarcodeNumber], [t5].[MainRef], [t5].[Notes], [t5].[ConsignmentRef], [t5].[PODedBy]
        FROM [dbo].[PODs] AS [t5]
        ) AS [t6] ON 1=1 ) ON [t0].[IntConNo] = [t6].[Consignment]
WHERE ((NOT (EXISTS(
    SELECT TOP (1) NULL AS [EMPTY]
    FROM [dbo].[Account] AS [t7]
    WHERE [t0].[Subcontractor] = [t7].[Name]
    ORDER BY [t7].[ID]
    ))) OR (NOT (((
    SELECT [t9].[Customer]
    FROM (
        SELECT TOP (1) [t8].[Customer]
        FROM [dbo].[Account] AS [t8]
        WHERE [t0].[Subcontractor] = [t8].[Name]
        ORDER BY [t8].[ID]
        ) AS [t9]
    )) = 1))) AND ([t2].[Customer] = 1) AND ([t3].[Customer] = 1)
ORDER BY [t0].[ID], [t1].[ID], [t2].[ID], [t3].[ID], [t6].[ID]
}

Answer 1

尝试将分包商联合向上移动，并将 where 子句与其一起推。这样你就不会不必要地进行最终会失败的连接。我还会修改分包商 ID 的选择，这样您就不会获得可能为空值的 ID。

var cons = (from c in dc.Consignments
            join su in dc.Accounts on c.Subcontractor equals su.Name into sug
            where (sug.FirstOrDefault() == null || sug.FirstOrDefault().Customer == false)
            join p in dc.PODs on c.IntConNo equals p.Consignment into pg
            join d in dc.Depots on c.DeliveryDepot equals d.Letter
            join sl in dc.Accounts on c.Customer equals sl.LegacyID
            join ss in dc.Accounts on sl.InvoiceAccount equals ss.LegacyID                   
            join sub in dc.Accountsubbies on ss.ID equals sub.AccountID into subg
            let firstSubContractor = sug.DefaultIfEmpty().FirstOrDefault()
            select new
            {
                           ID = c.ID,
                           IntConNo = c.IntConNo,
                           LegacyID = c.LegacyID,
                           PODs = pg.DefaultIfEmpty(),
                           TripNumber = c.TripNumber,
                           DropSequence = c.DropSequence,
                           TripDate = c.TripDate,
                           Depot = d.Name,
                           CustomerName = c.Customer,
                           CustomerReference = c.CustomerReference,
                           DeliveryName = c.DeliveryName,
                           DeliveryTown = c.DeliveryTown,
                           DeliveryPostcode = c.DeliveryPostcode,
                           VehicleText = c.VehicleReg + c.Subcontractor,
                           SubbieID = firstSubContractor == null ? "" : firstSubContractor.ID.ToString(),
                           SubbieList = subg.DefaultIfEmpty(),
                           ScanType = ss.PODScanning == null ? 0 : ss.PODScanning
              });