优化 LINQ-to-SQL 查询

Question

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

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

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

查询如下：

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
              });