如何提升ASP.NET Core MVC中Ajax DataTable的搜索性能？

我构造了一个用于加载审批列表的Ajax DataTable后端代码，但遇到了性能瓶颈：

• 加载10万行数据耗时过长
• 2.9万行数据搜索耗时50秒，首次加载耗时5秒
• 已尝试移除Include、使用Skip/Take和索引，但无明显改善

以下是我的代码：

private async Task<JsonResult> getApproverList(UserModel currentuser, string tab, Datatable_Ajax Datatable_Ajax)
{
    string[] appType = { "Approver", "Reporting Manager", "Team Approver", "Approval Matrix" };

    IQueryable<TicketModel> tickets = _context.Ticket.AsQueryable();

    switch (tab)
    {
        case "Upcoming":
            tickets = tickets.Where(tp => tp.TicketProcess.Any(tp1 => appType.Contains(tp1.Type) && (tp1.UserId == currentuser.Id || tp1.approver.UserId == currentuser.Id || tp1.approver.JTApprover.Any(tps => tps.UserId == currentuser.Id)) && tp1.Sequence > tp.CurrentProcess && tp1.Status == "Pending" && tp1.Status != "Approved" && tp1.Status != "Rejected" && tp.Status == "Pending" && tp1.TicketId == tp.Id));
            break;

        case "My Approval":
            tickets = tickets.Include(tp => tp.TicketProcess).Where(tp => tp.TicketProcess.Any(tp1 => appType.Contains(tp1.Type) && (tp1.UserId == currentuser.Id || tp1.approver.UserId == currentuser.Id || tp1.approver.JTApprover.Any(tps => tps.UserId == currentuser.Id)) && tp1.Sequence == tp.CurrentProcess && tp1.Status == "Pending" && tp1.Status != "Approved" && tp1.Status != "Rejected" && tp.Status == "Pending" && tp1.TicketId == tp.Id));
            break;

        case "History":
            tickets = tickets.Include(tp => tp.TicketProcess).Where(tp => tp.TicketProcess.Any(tp => (tp.Status == "Approved" || tp.Status == "Rejected") && tp.UserId == currentuser.Id));
            break;
    }
    
    var totalCount = await tickets.CountAsync(); /*tickets.Select(ticketid => ticketid.Id).Count()*/

    var filteredCount = 0;

    if (Datatable_Ajax.search != null && !string.IsNullOrEmpty(Datatable_Ajax.search.value))
    {
        var search = Datatable_Ajax.search.value;

        tickets = tickets.Where(x =>
            x.Title == search
            || x.Id == search
            || x.User != null && x.User.Fullname == search);

        filteredCount = await tickets.CountAsync();
    }
    else
    {
        filteredCount = totalCount;
    }

    if (Datatable_Ajax.order.Any())
        switch (Datatable_Ajax.order.First().column)
        {
            case 0:
                if (Datatable_Ajax.order.First().dir == "asc")
                {
                    tickets = tickets.OrderBy(t => Convert.ToInt32(t.Id));
                }
                else if (Datatable_Ajax.order.First().dir == "desc")
                {
                    tickets = tickets.OrderByDescending(t => Convert.ToInt32(t.Id));
                }
                break;

            case 1:
                if (Datatable_Ajax.order.First().dir == "asc")
                {
                    tickets = tickets.OrderBy(t => Convert.ToInt32(t.Id));
                }
                else if (Datatable_Ajax.order.First().dir == "desc")
                {
                    tickets = tickets.OrderByDescending(t => Convert.ToInt32(t.Id));
                }
                break;

            case 2:
                tickets = Datatable_Ajax.order.First().dir == "asc" ? tickets.OrderBy(x => x.Title) : tickets.OrderByDescending(x => x.Title);
                break;

            case 3:
                if (Datatable_Ajax.order.First().dir == "asc")
                {
                    tickets = tickets.OrderBy(t => t.StartDateTime);
                }
                else if (Datatable_Ajax.order.First().dir == "desc")
                {
                    tickets = tickets.OrderByDescending(t => t.StartDateTime);
                }
                break;

            case 4:
                if (Datatable_Ajax.order.First().dir == "asc")
                {
                    tickets = tickets.OrderBy(t => t.StartDateTime);
                }
                else if (Datatable_Ajax.order.First().dir == "desc")
                {
                    tickets = tickets.OrderByDescending(t => t.StartDateTime);
                }
                break;

            case 5:
                if (Datatable_Ajax.order.First().dir == "asc")
                {
                    tickets = tickets.OrderBy(t => t.User.Fullname);
                }
                else if (Datatable_Ajax.order.First().dir == "desc")
                {
                    tickets = tickets.OrderByDescending(t => t.User.Fullname);
                }
                break;

            case 6:
                if (Datatable_Ajax.order.First().dir == "asc")
                {
                    tickets = tickets.OrderBy(t => t.User != null ? t.User.Fullname : "");
                }
                else if (Datatable_Ajax.order.First().dir == "desc")
                {
                    tickets = tickets.OrderByDescending(t => t.User != null ? t.User.Fullname : "");
                }
                break;

            case 7:
                tickets = Datatable_Ajax.order.First().dir == "asc" ? tickets.OrderBy(x => x.TicketProcess.First().TaskLabel) : tickets.OrderByDescending(x => x.TicketProcess.First().TaskLabel);
                break;

            default:
                if (Datatable_Ajax.order.First().dir == "asc")
                {
                    tickets = tickets.OrderBy(t => Convert.ToInt32(t.Id));
                }
                else if (Datatable_Ajax.order.First().dir == "desc")
                {
                    tickets = tickets.OrderByDescending(t => Convert.ToInt32(t.Id));
                }
                break;
        }

        tickets = tickets.Skip(Datatable_Ajax.start).Take(Datatable_Ajax.length);

        var data = tickets
         .Include(a => a.TicketProcess).ThenInclude(a => a.approver).ThenInclude(a => a.JTApprover).ThenInclude(a => a.User).AsSplitQuery()
         .Include(a => a.User);

        var datatabl = data
            .Select(tp => new
            {
                id = tp.Id,
                status = tp.Status,
                remarks = tp.TicketProcess.First().Remarks,
                process = tp.CurrentProcess,
                title = tp.Title,
                date = tp.StartDateTimeZone,
                created = tp.User.Fullname,
                createdbyimg = tp.CreatedBy != null ? $"/home/ProfileImage?id={tp.CreatedBy}" : null,
                approvers = tp.TicketProcess
                    .SelectMany(process => process.TicketLogs)
                    .Where(log => log.User != null)
                    .GroupBy(log => log.UserId)
                    .Select(group => new
                    {
                UserId = group.Key,
                Name = group.First().User.Fullname,
                Photo = $"/home/ProfileImage?id={group.Key}"
            }).ToArray(),
                tasklabel = "Approval",
                approvedby = tp.User != null && tp.TicketProcess.Any(tp => (tp.Status == "Approved" || tp.Status == "Rejected") && tp.UserId == currentuser.Id) ? currentuser.Fullname : null,
                approvedbyimg = tp.User != null && tp.TicketProcess.Any(tp => (tp.Status == "Approved" || tp.Status == "Rejected") && tp.UserId == currentuser.Id) ? $"/home/ProfileImage?id={currentuser.Id}" : null,
                currentsequence = tp.CurrentProcess
            });

    return Json(new { draw = Datatable_Ajax.draw, recordsTotal = totalCount, recordsFiltered = filteredCount, data = datatabl });
}

优化方案 #

1. 数据库索引精准优化 #

• 针对高频过滤/排序字段创建复合索引：
  • Ticket表：Status, CurrentProcess, StartDateTime, Id, Title
  • TicketProcess表：TicketId, Type, Status, Sequence, UserId, approverId
  • TicketLogs表：TicketProcessId, UserId
• 移除排序时的函数转换：如果Id本身是数值类型，直接用t.Id排序，不要用Convert.ToInt32(t.Id)，避免索引失效

2. 查询逻辑精简 #

• 完全移除所有Include：最终用Select投影到匿名对象，EF Core会自动按需加载关联数据，提前Include只会加载冗余数据，拖慢查询速度
• 简化Where条件：
  • tp1.Status == "Pending" && tp1.Status != "Approved" && tp1.Status != "Rejected"可简化为tp1.Status == "Pending"，逻辑完全一致
  • tp1.TicketId == tp.Id在Any关联中是冗余的，EF Core会自动处理外键关联
• 复用重复判断逻辑：approvedby和approvedbyimg的判断逻辑重复，提前计算布尔变量复用：

  var isCurrentUserApproved = tp.TicketProcess.Any(tp => (tp.Status == "Approved" || tp.Status == "Rejected") && tp.UserId == currentuser.Id);
  

3. 搜索逻辑优化 #

• 如果是模糊搜索需求，替换精确匹配为全文索引查询：
  • 给Title、User.Fullname字段创建全文索引，用EF.Functions.FreeText替代==或Contains，大幅提升模糊搜索性能
• 分类型匹配：如果搜索内容是数字，优先匹配Id，再匹配其他字段，减少不必要的字段比对

4. 排序逻辑优化 #

• 合并冗余排序分支：case 0、1、3、4都是排序Id或StartDateTime，可以合并为同一逻辑
• 移除排序中的三元表达式：比如case 6直接用OrderBy(t => t.User.Fullname)，EF Core会自动处理null值
• 排序字段优先选择已有索引的字段，避免全表扫描

5. EF Core配置优化 #

• 添加AsNoTracking()：查询仅用于展示，不需要实体状态跟踪，可减少内存开销和处理时间
• 提前投影：不要先加载完整实体再投影，直接在查询初始阶段就用Select只取需要的字段，EF Core会生成更精简的SQL，减少数据传输量
• 移除AsSplitQuery()：非超大关联数据场景下，该方法会触发多次数据库查询，反而增加耗时

6. 分页与数据量控制 #

• 确保DataTable的length参数设置合理（建议10-20条/页），避免一次性加载过多数据
• 首次加载时可默认过滤最近N天的数据，或提供日期范围筛选，减少初始查询的数据量

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