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\$
4
  • \$\begingroup\$ Please add a language tag. \$\endgroup\$
    – Heslacher
    Mar 28, 2017 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, 2017 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, 2017 at 9:12
  • \$\begingroup\$ my tables are not indexed \$\endgroup\$ Mar 28, 2017 at 9:34

1 Answer 1

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\$
4
  • \$\begingroup\$ Good point, but it wont fully solve the problem \$\endgroup\$ Mar 28, 2017 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\$ Mar 28, 2017 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, 2017 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, 2017 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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