5
\$\begingroup\$

My current code takes 2.7-4.5 seconds to complete, is there any way to make it faster?

I need to return a list of the income in all days in a month. My idea was to loop through the days of one month, but this takes so long should not be a hard task.

What can I improve? I was thinking on GroupBy a date range.

    public IEnumerable<GetSellInfo> GetSellInvoices(int month, int year, int offset)
{
    DateTime startDate = new DateTime(year, month, 1, 0, 0, 0).AddMinutes(offset);
    DateTime endDate = new DateTime(year, month, DateTime.DaysInMonth(year, month) , 23, 59, 59, 999).AddMinutes(offset);
    DateTime utc = DateTime.UtcNow.AddMinutes(-offset);
    DateTime maxDate = new DateTime(utc.Year, utc.Month, utc.Day, 23, 59, 59, 999).AddMinutes(offset);

    if (endDate > maxDate) endDate = maxDate;

    var invoices =
        db.SellInvoices.Where(invoice => invoice.Date >= startDate && invoice.Date <= endDate);

    List<GetSellInfo> result = new List<GetSellInfo>();
    DateTime counterDate = startDate;

    while (counterDate <= endDate)
    {
        DateTime iDate = new DateTime(counterDate.Year, counterDate.Month, counterDate.Day, 0, 0, 0).AddMinutes(offset);
        DateTime jDate = new DateTime(counterDate.Year, counterDate.Month, counterDate.Day, 23, 59, 59, 99).AddMinutes(offset);
        var dayInvoices = invoices.Where(invoice => invoice.Date >= iDate && invoice.Date <= jDate);

        decimal cfCard = dayInvoices.Select(x => x.CardSettlementSum).DefaultIfEmpty(0).Sum();
        decimal cfCash = dayInvoices.Select(x => x.CashSettlementSum).DefaultIfEmpty(0).Sum();
        decimal depositsCard = dayInvoices.Select(x => x.CardDepositSum).DefaultIfEmpty(0).Sum();
        decimal depositsCash = dayInvoices.Select(x => x.CashDepositSum).DefaultIfEmpty(0).Sum();
        decimal notSyledsDecimal = dayInvoices.Select(x => x.NotStyledsSum).DefaultIfEmpty(0).Sum();
        decimal discountsDecimal = dayInvoices.Select(x => x.DiscountsSum).DefaultIfEmpty(0).Sum();

        result.Add(new GetSellInfo
        {
            Date = counterDate,
            TotalCard = cfCard + depositsCard,
            TotalCash = cfCash + depositsCash,
            DepositsInCard = depositsCard,
            DepositsInCash = depositsCash,
            NotStyleds = notSyledsDecimal,
            Discounts = discountsDecimal,
            Returns = cfCard - depositsCard + cfCash + depositsCash,
            Total = cfCard + depositsCard + cfCash + depositsCash,
        });
        counterDate = counterDate.AddDays(1);
    }

    return result;
}
\$\endgroup\$
9
  • \$\begingroup\$ Put some metrics in there to figure out exactly what is taking the time. It may be at the database level, in which case this code doesn't matter. \$\endgroup\$
    – Guillaume CR
    Sep 17, 2014 at 19:46
  • \$\begingroup\$ You're hitting the database 6*N times when going through the loop - no wonder it takes a while. IMO, you should either grab a full dataset between the dates in one go (e.g. by adding ToList at the end of invoices definition) and then partition/aggregate it on the client side or write a stored procedure that will do the whole analysis for you, which might be the only option if the data volume is large. \$\endgroup\$ Sep 17, 2014 at 19:46
  • \$\begingroup\$ @PatrykĆwiek I just hit database once when var invoices = db.SellInvoices.Where .... \$\endgroup\$
    – bto.rdz
    Sep 17, 2014 at 19:49
  • 3
    \$\begingroup\$ @bto.rdz If it's LINQ To Entities, you're not because you're not materializing the query. It's deferred until you run Sum on it 6 times inside the loop each and every time. If you do .ToList(), you'll grab the full dataset and the rest will be done client-side. \$\endgroup\$ Sep 17, 2014 at 19:51
  • \$\begingroup\$ @bto.rdz blogs.msdn.com/b/charlie/archive/2007/12/09/… explains what Patryk is saying \$\endgroup\$
    – Rhumborl
    Sep 17, 2014 at 19:53

1 Answer 1

6
\$\begingroup\$

I'm going to elaborate on what I mentioned in comments.

Assuming you're using LINQ to Entities, this line:

var invoices =
        db.SellInvoices
          .Where(invoice => invoice.Date >= startDate && invoice.Date <= endDate);

does not actually execute the query. It just builds the query, execution is deferred until you call one of the methods that force the materialization of the result set. One of those methods is Sum.

That means you're calling the database 6 times per loop.

If you add .ToList():

var invoices =
        db.SellInvoices
          .Where(invoice => invoice.Date >= startDate && invoice.Date <= endDate)
          .ToList();

You'll just hit the database once, grab the full result set and the rest of the processing will be done in-memory.

In case of not-too-big datasets, that is definitely a viable way to go. Otherwise, you'd have to write a stored procedure to do the processing.

\$\endgroup\$
2
  • \$\begingroup\$ Well done. You have 2 options: .ToList() to avoid hammering the db and "build a stored procedure". \$\endgroup\$
    – robrich
    Sep 17, 2014 at 20:09
  • \$\begingroup\$ @robrich Well, sometimes - if the problem allows - you could build one huge LINQ query to pull everything you need in one go and do the processing on the DB side, but SQL generated by a chain of nested LINQ operators can be awful... \$\endgroup\$ Sep 17, 2014 at 20:12

Your Answer

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