百万级数据下C# Linq非主键列排序性能优化求助

我们基于AngularJS、C#、Linq开发的Web应用，生产环境存有近20万条数据，支持各列升降序排序、分页（每页25条），但仅主键ID列排序响应快速，其他列（含关联表字段、C#动态计算列）排序速度极慢。核心排序逻辑代码如下：

//Do the sorting
if (!string.IsNullOrWhiteSpace(r.sortColumn))
{
    switch (r.sortColumn)
    {
        case "id":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.TransactionID) : t = t.OrderBy(x => x.TransactionID);
            break;
        case "created":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.StatusDate ?? x.DateCreated) : t = t.OrderBy(x => x.StatusDate ?? x.DateCreated);
            break;
        case "salePrice":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.SalePrice) : t = t.OrderBy(x => x.SalePrice);
            break;
        case "agent":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.tblTransactionAgentDetails.Where(z => z.isPrimary).FirstOrDefault().tblAgent.LastName) : t = t.OrderBy(x => x.tblTransactionAgentDetails.Where(z => z.isPrimary).FirstOrDefault().tblAgent.LastName);
            break;
        case "state":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.Property_State) : t = t.OrderBy(x => x.Property_State);
            break;
        case "sob":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.SOBid) : t = t.OrderBy(x => x.SOBid);
            break;
        case "branch":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.tblBranch.BranchName) : t = t.OrderBy(x => x.tblBranch.BranchName);
            break;
        case "addr1":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.Property_Address1) : t = t.OrderBy(x => x.Property_Address1);
            break;
        case "mls":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.MLSno) : t = t.OrderBy(x => x.MLSno);
            break;
        case "side":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.SideRepresentingID) : t = t.OrderBy(x => x.SideRepresentingID);
            break;
        case "statusName":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.CurrentStatusId) : t = t.OrderBy(x => x.CurrentStatusId);
            break;
        case "sentToAccountingDate":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.tblStatusTransactions.Where(y => x.CurrentStatusId == 5 && y.NewStatusID == 5).OrderByDescending(z => z.EntryID).FirstOrDefault().StatusChangeDateTime) : t = t.OrderBy(x => x.tblStatusTransactions.Where(y => x.CurrentStatusId == 5 && y.NewStatusID == 5).OrderByDescending(z => z.EntryID).FirstOrDefault().StatusChangeDateTime);
            break;
        case "settleDate":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.SettlementDate) : t = t.OrderBy(x => x.SettlementDate);
            break;
        case "settlementActual":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.SettlementDate_Actual) : t = t.OrderBy(x => x.SettlementDate_Actual);
            break;
        case "remitAmount":
            if (r.sortDirection.ToUpper() == "DESC")
            {
                t = t.OrderByDescending(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().Agent_ServiceFee)
                    .ThenByDescending(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().Agent_GrossGCC)
                    .ThenByDescending(x => x.AdminFeeAmtClient)
                    .ThenByDescending(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().ParnershipPercentage)
                    .ThenByDescending(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().Agent_Bonus);
            }
            else
            {
                t = t.OrderBy(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().Agent_ServiceFee)
                    .ThenBy(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().Agent_GrossGCC)
                    .ThenBy(x => x.AdminFeeAmtClient)
                    .ThenBy(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().ParnershipPercentage)
                    .ThenBy(x => x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault().Agent_Bonus);
            }
            break;
        case "listPrice":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.ListPrice) : t = t.OrderBy(x => x.ListPrice);
            break;
        case "lender":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.tblCompany.CompanyName) : t = t.OrderBy(x => x.tblCompany.CompanyName);
            break;
        case "title":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.tblCompany.CompanyName) : t = t.OrderBy(x => x.tblCompany.CompanyName);
            break;
        case "commissionStatus":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.CommissionStatus) : t = t.OrderBy(x => x.CommissionStatus);
            break;
        case "fAssignedTo":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.FAssignedTo) : t = t.OrderBy(x => x.FAssignedTo);
            break;
        case "EMDStatus":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.EMDStatus) : t = t.OrderBy(x => x.EMDStatus);
            break;
        case "processingSchedule":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.ProcessingSchedule) : t = t.OrderBy(x => x.ProcessingSchedule);
            break;
        case "fileManagement":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.FileManagement) : t = t.OrderBy(x => x.FileManagement);
            break;
        case "trackerStatus":
            t = (r.sortDirection.ToUpper() == "DESC") ? t = t.OrderByDescending(x => x.TrackerStatus) : t = t.OrderBy(x => x.TrackerStatus);
            break;
        default:
            break;
    }
}

//Execute the data
var final = t.Skip(r.perPage * r.index).Take(r.perPage).ToList();

优化方案 #

1. 强制排序逻辑在数据库端执行 #

当前部分关联表查询（如agent、sentToAccountingDate列）因复杂表达式触发内存排序（全量数据拉取到内存后再排序），这是核心性能瓶颈：

• 保持t为IQueryable<T>类型，避免提前调用ToList()/ToArray()触发数据加载；
• 预加载关联数据，简化排序表达式：

  // 预加载Primary Agent关联数据
  t = t.Include(x => x.tblTransactionAgentDetails.Where(z => z.isPrimary).Select(z => z.tblAgent));
  // 排序直接访问预加载属性
  bool isDescending = string.Equals(r.sortDirection, "DESC", StringComparison.OrdinalIgnoreCase);
  t = isDescending 
      ? t.OrderByDescending(x => x.tblTransactionAgentDetails.First(z => z.isPrimary).tblAgent.LastName) 
      : t.OrderBy(x => x.tblTransactionAgentDetails.First(z => z.isPrimary).tblAgent.LastName);
  

• 重构sentToAccountingDate的复杂嵌套查询，转为数据库可解析的表达式：

  var statusDateSubQuery = t.Select(x => new {
      x.TransactionID,
      TargetDate = x.tblStatusTransactions
          .Where(y => y.NewStatusID == 5 && x.CurrentStatusId == 5)
          .OrderByDescending(z => z.EntryID)
          .Select(z => z.StatusChangeDateTime)
          .FirstOrDefault()
  });
  t = isDescending
      ? t.OrderByDescending(x => statusDateSubQuery.Where(s => s.TransactionID == x.TransactionID).Select(s => s.TargetDate).FirstOrDefault())
      : t.OrderBy(x => statusDateSubQuery.Where(s => s.TransactionID == x.TransactionID).Select(s => s.TargetDate).FirstOrDefault());
  

2. 数据库层面添加针对性索引 #

主键ID排序快是因为默认有聚集索引，其他列需添加对应索引：

• 对单字段排序列（如SalePrice、Property_State、SettlementDate）添加非聚集索引；
• 对关联表字段排序（如agent列的LastName）添加复合/包含索引：
  • 在tblTransactionAgentDetails表创建复合索引：(isPrimary, TransactionID)包含AgentID；
  • 在tblAgent表创建索引：(AgentID)包含LastName；
• 对sentToAccountingDate创建过滤索引：

  CREATE NONCLUSTERED INDEX IX_tblStatusTransactions_NewStatusID 
  ON tblStatusTransactions (NewStatusID, EntryID DESC)
  INCLUDE (StatusChangeDateTime, TransactionID)
  WHERE NewStatusID = 5;
  

3. 简化动态计算列的重复逻辑 #

remitAmount列多次重复查询tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault()，导致重复计算：

• 先投影需要的字段到临时对象，再排序：

  var projectedData = t.Select(x => new {
      Transaction = x,
      PrimaryAgent = x.tblTransactionAgentDetails.Where(y => y.isPrimary).FirstOrDefault(),
      AdminFee = x.AdminFeeAmtClient
  });
  t = isDescending
      ? projectedData.OrderByDescending(p => p.PrimaryAgent.Agent_ServiceFee)
                   .ThenByDescending(p => p.PrimaryAgent.Agent_GrossGCC)
                   .ThenByDescending(p => p.AdminFee)
                   .ThenByDescending(p => p.PrimaryAgent.ParnershipPercentage)
                   .ThenByDescending(p => p.PrimaryAgent.Agent_Bonus)
                   .Select(p => p.Transaction)
      : projectedData.OrderBy(p => p.PrimaryAgent.Agent_ServiceFee)
                   .ThenBy(p => p.PrimaryAgent.Agent_GrossGCC)
                   .ThenBy(p => p.AdminFee)
                   .ThenBy(p => p.PrimaryAgent.ParnershipPercentage)
                   .ThenBy(p => p.PrimaryAgent.Agent_Bonus)
                   .Select(p => p.Transaction);
  

4. 减少冗余计算 #

• 提前将排序方向转为布尔变量，避免多次调用ToUpper()：

  bool isDescending = string.Equals(r.sortDirection, "DESC", StringComparison.OrdinalIgnoreCase);
  

• 合并lender和title列的相同排序逻辑，减少分支冗余。

5. 缓存高频排序结果 #

对用户高频使用的排序列（如created、salePrice），用MemoryCache缓存不同页码、排序条件下的结果，有效期根据数据更新频率设置为5-10分钟。

内容的提问来源于stack exchange，提问作者Nimesh khatri