Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to improve this query performance in Linq?

I have a large table and would like to know if it's possible to improve performance for queries.

allDocumentsWithPersianMonth has 25000000 record

    var normalDocuments = allDocumentsWithPersianMonth.Where(x => x.DocumentTypeId != 8 && x.DocumentTypeId != 9);
    var debitOpening = allDocumentsWithPersianMonth.Where(x => x.DocumentTypeId == 8);
    var creditOpening = allDocumentsWithPersianMonth.Where(x => x.DocumentTypeId == 8);
    var debitClosing = allDocumentsWithPersianMonth.Where(x => x.DocumentTypeId == 9);
    var creditClosing = allDocumentsWithPersianMonth.Where(x => x.DocumentTypeId == 9);

    return allDocumentsWithPersianMonth
        .GroupBy(x => new { x.DetailId, x.DetailCode, x.DetailDescription, x.PersianMonth })
        .Select(g => new AccountsAgingViewModel
        {
            DetailId = g.Key.DetailId,
            DetailCode = g.Key.DetailCode,
            DetailDescription = g.Key.DetailDescription,
            FarvardinDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 1).Sum(x => x.Debit),
            OrdibeheshtDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 2).Sum(x => x.Debit),
            KhordadDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 3).Sum(x => x.Debit),
            TirDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 4).Sum(x => x.Debit),
            MordadDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 5).Sum(x => x.Debit),
            ShahrivarDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 6).Sum(x => x.Debit),
            MehrDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 7).Sum(x => x.Debit),
            AbanDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 8).Sum(x => x.Debit),
            AzarDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 9).Sum(x => x.Debit),
            DeyDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 10).Sum(x => x.Debit),
            BahmanDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 11).Sum(x => x.Debit),
            EsfandDebit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 12).Sum(x => x.Debit),
            FarvardinCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 1).Sum(x => x.Credit),
            OrdibeheshtCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 2).Sum(x => x.Credit),
            KhordadCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 3).Sum(x => x.Credit),
            TirCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 4).Sum(x => x.Credit),
            MordadCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 5).Sum(x => x.Credit),
            ShahrivarCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 6).Sum(x => x.Credit),
            MehrCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 7).Sum(x => x.Credit),
            AbanCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 8).Sum(x => x.Credit),
            AzarCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 9).Sum(x => x.Credit),
            DeyCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 10).Sum(x => x.Credit),
            BahmanCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 11).Sum(x => x.Credit),
            EsfandCredit = normalDocuments.Where(x => x.DetailId == g.Key.DetailId && x.PersianMonth == 12).Sum(x => x.Credit),
            DebitSumOpening = debitOpening.Where(y => y.DetailId == g.Key.DetailId).Sum(x => x.Debit),
            DebitSumClosing = debitClosing.Where(y => y.DetailId == g.Key.DetailId).Sum(x => x.Credit),
            CreditSumOpening = creditOpening.Where(y => y.DetailId == g.Key.DetailId).Sum(x => x.Debit),
            CreditSumClosing = creditClosing.Where(y => y.DetailId == g.Key.DetailId).Sum(x => x.Credit),
        })

    .ToList();
like image 977
Elham Azadfar Avatar asked Jun 13 '18 06:06

Elham Azadfar


People also ask

Are LINQ queries faster?

Most of the times, LINQ will be a bit slower because it introduces overhead. Do not use LINQ if you care much about performance. Use LINQ because you want shorter better readable and maintainable code.

Is LINQ good for performance?

It is slightly slowerLINQ syntax is typically less efficient than a foreach loop. It's good to be aware of any performance tradeoff that might occur when you use LINQ to improve the readability of your code. And if you'd like to measure the performance difference, you can use a tool like BenchmarkDotNet to do so.

How do I optimize SQL query to run faster?

Reduce nested views to reduce lags This nesting causes too many data returns for every single query, which either makes the database crawl, or completely give up and give no returns. Minimizing nesting is a simple way to make your SQl query efficient and significantly improve speeds.


2 Answers

You should first eliminate the execution of the same queries. For instance check the following approach. Instead of iterating through the normalDocuments each time for each month, we iterate just once and group the documents based on month. Then we create a dictionary with key the PersianMonth and value the corresponding sum of Debit.

Doing so we will go from 24 iterations to just 1. Then we define a func to get the value of debit or credit for a month from this dictionary. From your model a deposit might either a debit or a credit. So by passing the boolean parameted isDebit you would identify if you want to read the value of Debit or Credit.

This defintion may need some change, since I don't know the type of Debit and Credit and I just assumed that this is an decimal.

 .Select(g => 
 {
     var groupedDocuments = normalDocuments
                                .Where(x => x.DetailId == g.Key.DetailId)
                                .GroupBy(x => x.PersianMonth)
                                .ToDictionary(x => x.Key, 
                                              x => new DepositTypes(x.Sum(y=>y.Debit), x.Sum(y=>y.Credit));

     Func<int, bool, decimal> getValueFunc = (id, isDebit) 
         => groupedDocuments.TryGetValue(id, out var value) 
                ? (isDebit ? value.Debit ?? value.Credit) 
                : 0;

     return new AccountsAgingViewModel
     {
        DetailId = g.Key.DetailId,
        DetailCode = g.Key.DetailCode,
        DetailDescription = g.Key.DetailDescription,
        FarvardinDebit = getValueFunc(1, isDeposit: true);
        OrdibeheshtDebit = getValueFunc(2, isDeposit: true);
        // etc.
     };
}

private class DepositTypes
{
    public decimal Debit { get; }
    public decimal Credit {get; }

    public DepositTypes(decimal debit, decimal credit)
    {
        Debit = debit;
        Credit = credit;
    }
}
like image 194
Christos Avatar answered Nov 03 '22 09:11

Christos


Some options to check:

  1. Look at generated SQL query, maybe you just need to add some indexes (with help of execution plan).
  2. Load all data to the memory in more specific structures (Dictionary, for example) and then select. Looks like you have a lot of queries to DB.
like image 40
Backs Avatar answered Nov 03 '22 08:11

Backs