Linq到Entity查询执行时间超过2分钟。

Question

我试图使用LINQ执行以下查询，并出现了性能问题。结果大约需要2-3分钟才能返回。我需要关于如何提高这些查询性能的建议。

这是我正在使用的方法，用于在特定日期之间获取订单总数。这里的数据被分成两个表。我需要从一个表中获取事务号，然后使用这些事务提取订单并对它们进行汇总，但是对于在第一个查询中返回的10k+记录的某些条件，查询太慢了。

public int GetOrderQuantity(List<int> TransactionNumbers, DateTime FromDate, DateTime ToDate)
{
    List<int> transactions = _context.RP_PART_TRANSACTIONS.Where(trd => TransactionNumbers.Contains(trd.TRANSACTION_NUMBER) && trd.TRANSACTION_DATE >= FromDate && trd.TRANSACTION_DATE <= ToDate).Select(tr => tr.TRANSACTION_NUMBER).ToList();

    return _context.RP_PART_TRANSACTION_DETAILS.Where(trd => transactions.Contains(trd.TRANSACTION_NUMBER)).Select(tr => tr.PART_QTY).ToList().Sum();
}

Answer 1

这段代码至少有两个问题。

List<int> transactions = _context.RP_PART_TRANSACTIONS.Where(trd => TransactionNumbers.Contains(trd.TRANSACTION_NUMBER) && trd.TRANSACTION_DATE >= FromDate && trd.TRANSACTION_DATE <= ToDate).Select(tr => tr.TRANSACTION_NUMBER).ToList();

return _context.RP_PART_TRANSACTION_DETAILS.Where(trd => transactions.Contains(trd.TRANSACTION_NUMBER)).Select(tr => tr.PART_QTY).ToList().Sum();

首先，使用两个查询，这些查询可以使用join操作符组合在一起。考虑到第一个查询可以返回很多记录(正如您刚才提到的10K+)，并且在数据库端使用查询中的内存Contains不能有效地处理，这可能会给您带来很大的改进。

其次，在后面的查询中，首先使用ToList()，然后使用Sum，这需要从数据库读取所有记录并将它们加在内存中。让数据库做之和将是非常有效的。

说了这么多话，下面这些都值得一试

var result =
    (from td in _context.RP_PART_TRANSACTION_DETAILS
     join t in _context.RP_PART_TRANSACTIONS
         on td.TRANSACTION_NUMBER equals t.TRANSACTION_NUMBER
     where TransactionNumbers.Contains(t.TRANSACTION_NUMBER)
         && t.TRANSACTION_DATE >= FromDate && t.TRANSACTION_DATE <= ToDate
     select td.PART_QTY)
    .Sum();

更新：从注释中注意到您的TransactionNumbers包含~16K项。EF将将TransactionNumbers.Contains(t.TRANSACTION_NUMBER)部件转换为具有IN子句中列出的16K数字的SQL t.TRANSACTION_NUMBER IN (...)子句，这将导致Oracle选择完整的表扫描而不是索引扫描。您可以尝试强制索引范围扫描，方法是包括列表的上下界，如下所示

var minNumber = TransactionNumbers.Min();
var maxNumber = TransactionNumbers.Max();
var result =
    (from td in _context.RP_PART_TRANSACTION_DETAILS
     join t in _context.RP_PART_TRANSACTIONS
         on td.TRANSACTION_NUMBER equals t.TRANSACTION_NUMBER
     where t.TRANSACTION_NUMBER >= minNumber && t.TRANSACTION_NUMBER <= maxNumber
         && TransactionNumbers.Contains(t.TRANSACTION_NUMBER)
         && t.TRANSACTION_DATE >= FromDate && t.TRANSACTION_DATE <= ToDate
     select td.PART_QTY)
    .Sum();

如果速度仍然慢，我最后能想到的就是尽量在数据库中进行过滤/聚合(w/o TransactionNumbers过滤器)，然后在内存中进行最后的筛选/聚合，如下所示

var query =
    from td in _context.RP_PART_TRANSACTION_DETAILS
    join t in _context.RP_PART_TRANSACTIONS
        on td.TRANSACTION_NUMBER equals t.TRANSACTION_NUMBER
    where t.TRANSACTION_DATE >= FromDate && t.TRANSACTION_DATE <= ToDate
    group td by t.TRANSACTION_NUMBER into g
    select new { TRANSACTION_NUMBER = g.Key, PART_QTY = g.Sum(td => td.PART_QTY) };

var filter = new HashSet<int>(TransactionNumbers); // For efficient lookup

var result = query.AsEnumerable() // Important! Switch to in memory context
    .Where(td => filter.Contains(td.TRANSACTION_NUMBER))
    .Sum(td => td.PART_QTY);