如何提高 Ajax 中的搜索功能？ASP.NET Core MVC、C#、Ajax 数据表

问：

这是我构造ajax数据表的方法

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

我尝试删除每个包含，但它不起作用，它仍然需要时间来加载 100,000 行数据，这是一大组数据，但由于我已经跳过并使用索引，它仍然没有改进。

在 29,000 行数据中的搜索时间为 50 秒;首次加载最多需要 5 秒的 29,000 行数据