LINQ-to-SQL Query Optimization

Question

I have a very heavy LINQ-to-SQL query that performs multiple joins on different tables to return an anonymous type. The problem is that if the number of returned rows is quite large (> 200), then the request becomes very slow and ends in a timeout. I know that I can increase the setting of the data context timeout, but this is a last resort.

I'm just wondering if my query will work better if I decompose it and make my comparisons as LINQ-to-Objects queries, so I can even use PLINQ to maximize processing power. But for me this is a foreign concept, and I can’t make out how to separate it. Can anyone offer any advice? I am not asking you to write code for me, just some general recommendations on how I could improve this would be great.

Note. I guaranteed that the database has all the correct keys that I connect to, and I guaranteed that these keys are updated.

Request below:

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

This generates SQL on request:

  {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

Try moving the subcontractor up and click the where clause with it. This way, you are not making unnecessary unification attempts that will ultimately fail. I would also like to change the selection for the subcontractor identifier, so you will not get a potentially null identifier.

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