'jQuery DataTable Server Side Processing Performance
My current datatable solution takes 1.5 - 2 seconds to grab 5k+ records and display them.
Current Action/Method:
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;
}
Current DataTable Script:
<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>
Instead of grabbing all 5k+ values, I want to only get the first 10. I tried the following solution, but I do not see a performance improvement. I verified that it does grab the first draw (10 records).
New Action:
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);
}
New DataTable Script:
<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>
Solution 1:[1]
You are calling .ToList() when you load the data.
This paging on a whole dataset after it is loaded in memory isn't going to help performance.
This the entire Table is loaded. .AsQueryable is your friend here. It loads the query without executing it.
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |