logo标签列表

如何在Linq中提高查询性能？

c#performancelinq
5
我有一张大表格，想知道是否有可能提高查询性能。 allDocumentsWithPersianMonth 具有 25000000 条记录。
    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();
2
也许可以按行索引获取数据，例如1-10、10-20等。 - Manoz
1
当然，不要使用ORM。 - user585968
1
@User453465436754 不是非常有用的建议。ORM在许多情况下非常好用。 - Backs
1
@Backs可能不是OP想听到的，但ORM在高容量数据应用程序中没有任何用处。 - user585968
1
@Backs，这不是其中之一。 - paparazzo
3个回答
7
你应该先消除相同查询的执行。例如，请查看以下方法。我们不是每个月都针对 normalDocuments 迭代，而是只迭代一次并按月份分组文档。 然后，我们创建一个词典，以 PersianMonth 为键，以相应的 Debit 总和为值。
这样做，我们将从24次迭代减少到1次。然后，我们定义一个函数，用于从该字典中获取某个月的借方或贷方值。根据你的模型，存款可能是借方或贷方。因此，通过传递布尔参数 isDebit，您可以确定是否要读取 Debit 或 Credit 的值。
由于我不知道 Debit 和 Credit 的类型，并且我只假设它是一个 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;
    }
}
感谢您的回复，但仍然非常缓慢。 - Elham Azadfar
1
@ElhamAzadfar，非常欢迎您。您能否在更改之前和之后发布测量结果？ - Christos
3

一些可以检查的选项：

  1. 查看生成的SQL查询，也许你只需要添加一些索引（通过执行计划进行帮助）。
  2. 将所有数据加载到内存中的更具体结构中（例如字典），然后进行选择。看起来你有很多对数据库的查询。
1
这是内存数据。 - Elham Azadfar
1
@ElhamAzadfar 尝试将 normalDocuments 转换为具有键 DetailId 或 new { DetailId，PersianMonth } 的 LookUp。 - Backs
1

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();

更新

Christos的代码比这个好得多。 我已经测试了400个记录，Christos的代码只需要18839毫秒， 而我的代码需要102975毫秒。

这是最佳解决方案：

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),
                     };
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接