Elham Azadfar
Reputation: 737
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();
Upvotes: 5
Views: 216
Answers (3)
Ali Bayat
Reputation: 4106
try this code :
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 =>
{
var filteredDocuments = normalDocuments.Where(x => x.DetailId == g.Key.DetailId);
var m1 = filteredDocuments.Where(x => x.PersianMonth == 1);
var m2 = filteredDocuments.Where(x => x.PersianMonth == 3);
var m3 = filteredDocuments.Where(x => x.PersianMonth == 4);
var m4 = filteredDocuments.Where(x => x.PersianMonth == 5);
var m5 = filteredDocuments.Where(x => x.PersianMonth == 6);
var m6 = filteredDocuments.Where(x => x.PersianMonth == 7);
var m7 = filteredDocuments.Where(x => x.PersianMonth == 8);
var m8 = filteredDocuments.Where(x => x.PersianMonth == 9);
var m9 = filteredDocuments.Where(x => x.PersianMonth == 2);
var m10 = filteredDocuments.Where(x => x.PersianMonth == 10);
var m11 = filteredDocuments.Where(x => x.PersianMonth == 11);
var m12 = filteredDocuments.Where(x => x.PersianMonth == 12);
return new AccountsAgingViewModel
{
DetailId = g.Key.DetailId,
DetailCode = g.Key.DetailCode,
DetailDescription = g.Key.DetailDescription,
FarvardinDebit = m1.Sum(x => x.Debit),
OrdibeheshtDebit = m2.Sum(x => x.Debit),
KhordadDebit = m3.Sum(x => x.Debit),
TirDebit = m4.Sum(x => x.Debit),
MordadDebit = m5.Sum(x => x.Debit),
ShahrivarDebit = m6.Sum(x => x.Debit),
MehrDebit = m7.Sum(x => x.Debit),
AbanDebit = m8.Sum(x => x.Debit),
AzarDebit = m9.Sum(x => x.Debit),
DeyDebit =m10.Sum(x => x.Debit),
BahmanDebit = m11.Sum(x => x.Debit),
EsfandDebit = m12.Sum(x => x.Debit),
FarvardinCredit =m1.Sum(x => x.Credit),
OrdibeheshtCredit = m2.Sum(x => x.Credit),
KhordadCredit = m3.Sum(x => x.Credit),
TirCredit = m4.Sum(x => x.Credit),
MordadCredit = m5.Sum(x => x.Credit),
ShahrivarCredit = m6.Sum(x => x.Credit),
MehrCredit = m7.Sum(x => x.Credit),
AbanCredit = m8.Sum(x => x.Credit),
AzarCredit = m9.Sum(x => x.Credit),
DeyCredit = m10.Sum(x => x.Credit),
BahmanCredit = m11.Sum(x => x.Credit),
EsfandCredit = m12.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();
Update
Christos code is much better than this. I have tested by 400 recrod Christos code takes only 18839 ms, my code takes 102975ms.
this is best solution:
var normalDocuments = allDocumentsWithPersianMonth
.GroupBy(x => new { x.DetailId, x.DetailCode, x.DetailDescription, x.PersianMonth })
.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, true),
OrdibeheshtDebit = getValueFunc(2, true),
KhordadDebit = getValueFunc(3, true),
TirDebit = getValueFunc(4, true),
MordadDebit = getValueFunc(5, true),
ShahrivarDebit = getValueFunc(6, true),
MehrDebit = getValueFunc(7, true),
AbanDebit = getValueFunc(8, true),
AzarDebit = getValueFunc(9, true),
DeyDebit = getValueFunc(10, true),
BahmanDebit = getValueFunc(11, true),
EsfandDebit = getValueFunc(12, true),
FarvardinCredit = getValueFunc(1, false),
OrdibeheshtCredit = getValueFunc(2, false),
KhordadCredit = getValueFunc(3, false),
TirCredit = getValueFunc(4, false),
MordadCredit = getValueFunc(5, false),
ShahrivarCredit = getValueFunc(6, false),
MehrCredit = getValueFunc(7, false),
AbanCredit = getValueFunc(8, false),
AzarCredit = getValueFunc(9, false),
DeyCredit = getValueFunc(10, false),
BahmanCredit = getValueFunc(11, false),
EsfandCredit = getValueFunc(12, false),
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),
};
Upvotes: 1
Christos
Reputation: 53958
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;
}
}
Upvotes: 7
Backs
Reputation: 24913
Some options to check:
- Look at generated SQL query, maybe you just need to add some indexes (with help of execution plan).
- 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.
Upvotes: 3
Related Questions
- How could I make this LINQ query more efficient?
- Is there any efficient way to optimize following Linq
- how do I make this LINQ query faster?
- How I can Optimize this linq query
- How do I improve the performance of this LINQ query?
- Optimizing slow LINQ query
- Improving performance of linq query
- Optimize this LINQ Query
- How can I optimize this LINQ statement for speed?
- How to optimize this linq query?