jQuery DataTable 服务器端处理性能

jQuery DataTable Server Side Processing Performance

提问人： 提问时间：3/16/2022 更新时间：3/16/2022 访问量：531

问：

我目前的数据表解决方案需要 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;
    }

当前 DataTable 脚本：

<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>

我不想抓住所有 5k+ 值，而是只想获得前 10 个值。我尝试了以下解决方案，但我没有看到性能改进。我验证了它确实抓住了第一次抽奖（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);
    }

新的 DataTable 脚本：

<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>
C# jQuery ASP.NET-MVC 数据表 服务器端

评论

答：

0赞 Zee 3/16/2022 #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

评论

0赞 Zee 3/16/2022
更新了 Answer，单个查询，在调用 sql 时，获取 totalRecords 和记录。希望对你有所帮助。IQueryable 是用于分页、查询（尤其是使用多个参数）的绝佳工具
0赞 3/16/2022
对于 var myResult，我收到一条错误消息，在声明它之前无法使用局部变量 totalRecords。以及 myData 和记录在当前上下文中不存在。
0赞 Zee 3/16/2022
更新后，单个调用 LINQ 可能不起作用，它应该执行两个 SQL 语句，一个是 Select COunt（），另一个是分页查询。应该非常快
0赞 3/16/2022
是否有任何其他代码应该在 var 结果行下方？要退回什么？
0赞 Zee 3/16/2022
是的，您原始帖子中的其余代码。 用结果替换数据

上一个：Java 中 DataTable 的服务器端处理

下一个：使用 ajax C 在数据表中删除#