1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Please add a language tag. \$\endgroup\$ – Heslacher Mar 28 '17 at 7:30
  • 2
    \$\begingroup\$ 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? \$\endgroup\$ – t3chb0t Mar 28 '17 at 8:08
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – BCdotWEB Mar 28 '17 at 9:12
  • \$\begingroup\$ my tables are not indexed \$\endgroup\$ – JustLearning Mar 28 '17 at 9:34
1
\$\begingroup\$

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();
\$\endgroup\$
  • \$\begingroup\$ Good point, but it wont fully solve the problem \$\endgroup\$ – JustLearning Mar 28 '17 at 8:28
  • \$\begingroup\$ 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. \$\endgroup\$ – JustLearning Mar 28 '17 at 8:29
  • \$\begingroup\$ 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? \$\endgroup\$ – yu_ominae Mar 28 '17 at 10:42
  • \$\begingroup\$ 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 \$\endgroup\$ – Martin Mar 28 '17 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.