Linq group by with multiple where, sum clauses in select causes a timeout

Question

I have linq query that extracts a summary of my sales quantities item group wise. This query causes an sql timeout. From the what i have found out, timeout indicates that my query is poorly designed and needs to be optimized.

Below is the linq query:

 (from inv in Dbcontext.tblSalesInvoices
 join det in Dbcontext.tblSalesInvoiceDetails on inv.SalesInvoiceId equals det.SalesInvoiceId
 join item in Dbcontext.tblItems on det.ItemId equals item.ItemId
 join itemGroup in Dbcontext.tblItemGroups on item.ItemGroupId equals itemGroup.ItemGroupId
 join loc in Dbcontext.tblLocations on inv.LocationId equals loc.LocationId
 where inv.InvoiceDate >= FeedSearchCriteria.FromDate && inv.InvoiceDate <= FeedSearchCriteria.ToDate
 && inv.InvoiceStatusId != Constants.REVERSED_INVOICE
 group new { det.Quantity, itemGroup.ItemGroupCode, item.Weight, item.ItemCode, det.LineTotal } by loc.LocationName into g
 select new LocationCount()
 {
 LocationName = g.Key,
 FeedBagCount = (int?)g.Where(x => x.ItemGroupCode == "FEED").Sum(x => x.Quantity),
 SupremeKg = (decimal?)g.Where(x => x.ItemGroupCode == "ABATTOIR").Sum(x => x.Weight * x.Quantity),
 SupremeValue = (decimal?)g.Where(x => x.ItemGroupCode == "ABATTOIR").Sum(x => x.LineTotal),
 DocCount = (int?)g.Where(x => x.ItemCode == "DOC").Sum(x => x.Quantity)
 }).OrderBy(x=>x.LocationName).ToListAsync();

---

I think my issue arises because of the where/sum clause in select. How can i re-write below query in more optimized manner.

Edit

Time out occurs when a date range is more than or equal to 1 month of transactions.

• Heslacher: Please add a language tag.
• t3chb0t: You can try to narrow down the timeout problem by first executing the joins with a simple select, then add the where, then the group etc. At some point you should notice a performance drop so you'll know what is not performing well. Can you add the generated sql too?
• BCdotWEB: Are your tables properly indexed? Also, please add the conditions when this timeout occurs; don't just post that as a comment to an answer.
• JustLearning: my tables are not indexed

Answer 1

You could try to set the joins on the already filtered table:

(from inv in Dbcontext.tblSalesInvoices.Where(inv => inv.InvoiceDate >= FeedSearchCriteria.FromDate && inv.InvoiceDate <= FeedSearchCriteria.ToDate
 && inv.InvoiceStatusId != Constants.REVERSED_INVOICE)
 join det in Dbcontext.tblSalesInvoiceDetails on inv.SalesInvoiceId equals det.SalesInvoiceId
 join item in Dbcontext.tblItems on det.ItemId equals item.ItemId
 join itemGroup in Dbcontext.tblItemGroups on item.ItemGroupId equals itemGroup.ItemGroupId
 join loc in Dbcontext.tblLocations on inv.LocationId equals loc.LocationId
 group new { det.Quantity, itemGroup.ItemGroupCode, item.Weight, item.ItemCode, det.LineTotal } by loc.LocationName into g
 select new LocationCount()
 {
 LocationName = g.Key,
 FeedBagCount = (int?)g.Where(x => x.ItemGroupCode == "FEED").Sum(x => x.Quantity),
 SupremeKg = (decimal?)g.Where(x => x.ItemGroupCode == "ABATTOIR").Sum(x => x.Weight * x.Quantity),
 SupremeValue = (decimal?)g.Where(x => x.ItemGroupCode == "ABATTOIR").Sum(x => x.LineTotal),
 DocCount = (int?)g.Where(x => x.ItemCode == "DOC").Sum(x => x.Quantity)
 }).OrderBy(x=>x.LocationName).ToListAsync();

• JustLearning: Good point, but it wont fully solve the problem
• JustLearning: For example, sometimes the date range can be set to a day (so small number of transactions), but when it is set to a year or from beginning of operation to present day, the timeout will occur inevitably.
• yu_ominae: Then surely this is a DB problem, with your query taking too long? Have you tried raising the timeout or checked the query with a profiler? Alternatively you could try to break up your date range and fetch the data in chunks?
• Martin: You could also add the where clauses from the select to the from part to reduce the joins: join itemGroup in Dbcontext.tblItemGroups.Where(x => x.ItemGroupCode == "FEED" || x.ItemGroupCode == "ABATTOIR") on item.ItemGroupId equals itemGroup.ItemGroupId