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();
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.
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.
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.
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;
}
}
Some options to check:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With