jQuery DataTable 服务器端处理性能

Question

我当前的数据表解决方案需要 1.5 - 2 秒才能获取 5k+ 记录并显示它们。

当前操作/方法：

    public JsonResult LoadDrawings()
    {
        return Json(new { data = GetDrawings("") }, JsonRequestBehavior.AllowGet);
    }

    private IEnumerable GetDrawings(string keyword)
    {
        var drawings = from d in _db.Drawings
                       where d.DrawingNumber.ToString().Contains(keyword) 
                           || d.Drawer.Contains(keyword)
                           || d.DrawingDate.ToString().Contains(keyword)
                           || d.DrawingCategories.Any(c => c.Label.Contains(keyword))
                           || d.Room.Label.Contains(keyword)
                           || d.Notes.Contains(keyword)
                           || d.Streets.Any(s => s.Street_.Contains(keyword))
                           || d.Streets.Any(s => s.StreetCategories.Any(c => c.Label.Contains(keyword)))
                           || d.Trs.Any(s => s.TrsSection.Label.Contains(keyword))
                           || d.Trs.Any(t => t.TrsTownship.Label.Contains(keyword))
                           || d.Trs.Any(r => r.TrsRanx.Label.Contains(keyword))
                       select new 
                       { 
                           d.DrawingNumber, d.Drawer, d.DrawingDate, 
                           DrawingCategories = d.DrawingCategories.Select(c => c.Label).ToList(), 
                           Room = d.Room.Label, d.Notes, Streets = d.Streets.Select(s => s.Street_).ToList(), 
                           StreetCategories = d.Streets.Select(s => s.StreetCategories.Select(c => c.Label)).ToList(), 
                           TrsSections = d.Trs.Select(s => s.TrsSection.Label).ToList(),
                           TrsTownships = d.Trs.Select(t => t.TrsTownship.Label).ToList(),
                           TrsRanges = d.Trs.Select(r => r.TrsRanx.Label).ToList(), d.Id 
                       };

        return drawings;
    }

当前数据表脚本：

<script>
    $(document).ready(function () {
        $("#DrawingDataTable").DataTable({
            autoWidth: false,
            deferRender: true,
            order: [0, "desc"],
            ajax: {
                url: '@Url.Action("LoadDrawings", "Drawing")',
                datatype: "json",
                type: "GET"
            },
            columnDefs: [
                {
                    targets: [3, 6, 7, 8, 9, 10],
                    searchable: true,
                    visible: false
                },
                {
                    targets: 11,
                    searchable: false,
                    visible: false
                },
                {
                    targets: [12, 13],
                    orderable: false,
                    searchable: false,
                    width: "1%"
                },
                {
                    targets: [1, 4, 5],
                    className: "uppercase"
                }
            ],
            columns: [
                {
                    data: "DrawingNumber",
                    render: function (data, type, row) {
                        var drawingDetails = '@Url.Action("Details", "Drawing")/' + row.Id;
                        return '<a href=\"' + drawingDetails + '">' + data + '</a>';
                    }
                },
                { data: "Drawer" },
                {
                    data: "DrawingDate",
                    render: function (data) {
                        return moment(data).format("MM/DD/YYYY");
                    }
                },
                { data: "DrawingCategories" },
                { data: "Room" },
                { data: "Notes" },
                { data: "Streets" },
                { data: "StreetCategories" },
                { data: "TrsSections" },
                { data: "TrsTownships" },
                { data: "TrsRanges" },
                { data: "Id" },
                {
                    data: null,
                    title: "",
                    render: function (data, type, row) {
                        var drawingEdit = '@Url.Action("Edit", "Drawing")/' + row.Id;
                        return '<a href=\"' + drawingEdit + '\" class=\"btn btn-warning\">Edit</a>';
                    }
                },
                {
                    data: null,
                    title: "",
                    render: function (data, type, row) {
                        var drawingDelete = '@Url.Action("Delete", "Drawing")/' + row.Id;
                        return '<a href=\"' + drawingDelete + '\" class=\"btn btn-danger\">Delete</a>';
                    }
                }
            ],
            stateDuration: 0,
            stateSave: true,
            stateSaveCallback: function (settings, data) {
                localStorage.setItem(`DataTables_${settings.sInstance}`, JSON.stringify(data));
            },
            stateLoadCallback: function (settings) {
                return JSON.parse(localStorage.getItem(`DataTables_${settings.sInstance}`));
            }
        }),
    });
</script>

我只想获取前 10 个值，而不是获取所有 5k+ 值。我尝试了以下解决方案，但没有看到性能改进。我验证了它确实抓住了第一次抽奖（10 条记录）。

新操作：

    public JsonResult LoadDrawings()
    { 
        var search = Request.Form.GetValues("search[value]")[0];
        var draw = Request.Form.GetValues("draw")[0];
        var order = Request.Form.GetValues("order[0][column]")[0];
        var orderDir = Request.Form.GetValues("order[0][dir]")[0];
        var startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
        var pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
        var data = _db.Drawings.ToList();
        var totalRecords = data.Count;

        if (!string.IsNullOrEmpty(search) && !string.IsNullOrWhiteSpace(search))
        {  
            data = data.Where(d => d.DrawingNumber.ToString().Contains(search)
                           || d.Drawer.Contains(search)
                           || d.DrawingDate.ToString().Contains(search)
                           || d.DrawingCategories.Any(c => c.Label.Contains(search))
                           || d.Room.Label.Contains(search)
                           || d.Notes.Contains(search)
                           || d.Streets.Any(s => s.Street_.Contains(search))
                           || d.Streets.Any(s => s.StreetCategories.Any(c => c.Label.Contains(search)))
                           || d.Trs.Any(s => s.TrsSection.Label.Contains(search))
                           || d.Trs.Any(t => t.TrsTownship.Label.Contains(search))
                           || d.Trs.Any(r => r.TrsRanx.Label.Contains(search))).ToList();
        }

        /*if (!(string.IsNullOrEmpty(order) && string.IsNullOrEmpty(orderDir)))
        {
            data = data.OrderBy(order + " " + orderDir).ToList();
        }*/

        var recFilter = data.Count;

        data = data.Skip(startRec).Take(pageSize).ToList();

        var modifiedData = data.Select(d =>
            new { d.DrawingNumber, d.Drawer, d.DrawingDate,
                DrawingCategories = d.DrawingCategories.Select(c => c.Label).ToList(),
                Room = d.Room.Label, d.Notes, Streets = d.Streets.Select(s => s.Street_).ToList(),
                StreetCategories = d.Streets.Select(s => s.StreetCategories.Select(c => c.Label)).ToList(),
                TrsSections = d.Trs.Select(s => s.TrsSection.Label).ToList(),
                TrsTownships = d.Trs.Select(t => t.TrsTownship.Label).ToList(),
                TrsRanges = d.Trs.Select(r => r.TrsRanx.Label).ToList(), d.Id });

        return Json(new
        {
            draw = Convert.ToInt32(draw),
            recordsTotal = totalRecords,
            recordsFiltered = recFilter,
            data = modifiedData
        }, JsonRequestBehavior.AllowGet);
    }       

新数据表脚本：

<script>
    $(document).ready(function () {
        $("#DrawingDataTable").DataTable({
            autoWidth: false,
            deferRender: true,
            order: [0, "desc"],
            processing: true,
            serverSide: true,
            ajax: {
                url: '@Url.Action("LoadDrawings", "Drawing")',
                datatype: "json",
                type: "POST"
            },
            columnDefs: [
                {
                    targets: [3, 6, 7, 8, 9, 10],
                    searchable: true,
                    visible: false
                },
                {
                    targets: 11,
                    searchable: false,
                    visible: false
                },
                {
                    targets: [12, 13],
                    orderable: false,
                    searchable: false,
                    width: "1%"
                },
                {
                    targets: [1, 4, 5],
                    className: "uppercase"
                }
            ],
            columns: [
                {
                    data: "DrawingNumber",
                    render: function (data, type, row) {
                        var drawingDetails = '@Url.Action("Details", "Drawing")/' + row.Id;
                        return '<a href=\"' + drawingDetails + '">' + data + '</a>';
                    }
                },
                { data: "Drawer" },
                {
                    data: "DrawingDate",
                    render: function (data) {
                        return moment(data).format("MM/DD/YYYY");
                    }
                },
                { data: "DrawingCategories" },
                { data: "Room" },
                { data: "Notes" },
                { data: "Streets" },
                { data: "StreetCategories" },
                { data: "TrsSections" },
                { data: "TrsTownships" },
                { data: "TrsRanges" },
                { data: "Id" },
                {
                    data: null,
                    title: "",
                    render: function (data, type, row) {
                        var drawingEdit = '@Url.Action("Edit", "Drawing")/' + row.Id;
                        return '<a href=\"' + drawingEdit + '\" class=\"btn btn-warning\">Edit</a>';
                    }
                },
                {
                    data: null,
                    title: "",
                    render: function (data, type, row) {
                        var drawingDelete = '@Url.Action("Delete", "Drawing")/' + row.Id;
                        return '<a href=\"' + drawingDelete + '\" class=\"btn btn-danger\">Delete</a>';
                    }
                }
            ],
            stateDuration: 0,
            stateSave: true,
            stateSaveCallback: function (settings, data) {
                localStorage.setItem(`DataTables_${settings.sInstance}`, JSON.stringify(data));
            },
            stateLoadCallback: function (settings) {
                return JSON.parse(localStorage.getItem(`DataTables_${settings.sInstance}`));
            }
        });
    });
</script>

Answer 1

加载数据时，您正在调用 .ToList()。
加载到内存后对整个数据集进行分页不会提高性能。

这样整个表就被加载了。 .AsQueryable 是你的朋友。它加载查询而不执行它。

public JsonResult LoadDrawings()
    { 
        var search = Request.Form.GetValues("search[value]")[0];
        var draw = Request.Form.GetValues("draw")[0];
        var order = Request.Form.GetValues("order[0][column]")[0];
        var orderDir = Request.Form.GetValues("order[0][dir]")[0];
        var startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
        var pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
        //var data = _db.Drawings.ToList();//This loads the entire table in memory
        var data = _db.Drawings.AsQueryable(); //This builds a query.


        if (!string.IsNullOrEmpty(search) && !string.IsNullOrWhiteSpace(search))
        {  
            data = data.Where(d => d.DrawingNumber.ToString().Contains(search)
                           || d.Drawer.Contains(search)
                           || d.DrawingDate.ToString().Contains(search)
                           || d.DrawingCategories.Any(c => c.Label.Contains(search))
                           || d.Room.Label.Contains(search)
                           || d.Notes.Contains(search)
                           || d.Streets.Any(s => s.Street_.Contains(search))
                           || d.Streets.Any(s => s.StreetCategories.Any(c => c.Label.Contains(search)))
                           || d.Trs.Any(s => s.TrsSection.Label.Contains(search))
                           || d.Trs.Any(t => t.TrsTownship.Label.Contains(search))
                           || d.Trs.Any(r => r.TrsRanx.Label.Contains(search)))
                       //Replace this
                      .ToList()
                       //WIth this
                       .AsQueryable();
                 //Still A Query not executed to memory
        }

        var totalRecords = data.Count(); //Gets total Count of The query, This just executes a Select Count() from Table 

        var result = data.OrderBy(c => c.Drawer).Skip(startRec).Take(pageSize).ToList(); //The whole query is executed only on this line