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>

我不想获得所有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);
}       

新数据表脚本：

<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