Performance Issue In jQuery DataTable About Fetching Records And Steps To Fix It

Introduction

In this session, we will learn how to resolve a performance issue while fetching a large amount of data from the server-side. I am going to show you a DataTable's server-side paging, sorting, and filtering in ASP.NET MVC application. Server-side means using C# code behind the file in the Controller section.

Description

DataTable is a most powerful and easy-to-use jQuery plugin for displaying tabular data with features like pagination, searching, state saving, and multi-column sorting with data type detection and lots more with ZERO or minimal configuration.

The prerequisites include knowledge of the following technologies.

ASP.NET MVC
HTML
JavaScript
AJAX
CSS
Bootstrap
C# Programming
C# LINQ
jQuery

Note

Before going through the session, I suggest you first visit the previous datatable in MVC related article with a back-end section.

Retrieve Records In jQuery Datatable Using Entity Framework And Bootstrap

Steps to be followed.

Step 1

Add a new action into the Controller to get the View where we will implement the jQuery DataTable with server-side paging and sorting.

Code ref

public ActionResult serverpagination()
{
return View();
}

Step 2

Add a View for the action (here "serverpagination") and design.

Code ref

@{
ViewBag.Title = "Satyaprakash";
}
<h2 style="color: blue">Satyaprakash-jQuery Datatable server side pagination</h2>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
.custom-loader-color {
color: #fff !important;
font-size: 40px !important;
}
.custom-loader-background {
background-color: #f60 !important;
}
.custom-middle-align {
vertical-align: middle !important;
}
</style>
<div style="width:90%; margin:0 auto;">
<table id="myTable" align="center" border="1" cellpadding="4" cellspacing="4">
<thead>
<tr>
<th style="background-color: Yellow;color: blue">First Name</th>
<th style="background-color: Yellow;color: blue">Last Name</th>
<th style="background-color: Yellow;color: blue">Age</th>
<th style="background-color: Yellow;color: blue">Address</th>
<th style="background-color: Yellow;color: blue">City</th>
<th style="background-color: Yellow;color: blue">State</th>
</tr>
</thead>
</table>
</div>
@* Load bootstrap datatable css *@
<link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" />
<link href="//cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" />
@* Load normal datatable css *@
@*<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />*@
@* Load bootstrap datatable js *@
@section Scripts{
<script src="//code.jquery.com/jquery-3.3.1.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
@* Load normal datatable js *@
@*<script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>*@
<script>
$(document).ready(function () {
$("#myTable").DataTable({
"language":
{
"processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
},
"processing": true, // for show progress bar
"serverSide": true, // for process server side
"filter": true, // this is for disable filter (search box)
"orderMulti": false, // for disable multiple column at once
"ajax": {
"url": "/home/LoadData",
"type": "POST",
"datatype": "json"
},
"columns": [
{ "data": "FirstName", "name": "FirstName", "autoWidth": true },
{ "data": "LastName", "name": "LastName", "autoWidth": true },
{ "data": "Age", "name": "Age", "autoWidth": true },
{ "data": "Address", "name": "Address", "autoWidth": true },
{ "data": "City", "name": "City", "autoWidth": true },
{ "data": "State", "name": "State", "autoWidth": true }
]
});
});
</script>
}

Code description

Then, I have added the jQuery code to load records and fetch them into the datatable. Here, myTable is the ID of HTML table and I have added the Home Controller with controller action method to fetch the records in the URL section, and it contains all the properties of database objects in the columns section.

The following piece of code will enable the data loading from the server-side. The path "/home/LoadData" is the function that will be returning data from server side. The columns here are the exact name of the properties that we have created in the table and uploaded using the Entity Data Model file.

"processing": true, // for show progress bar
"serverSide": true, // for process server side
"filter": true, // this is for disable filter (search box)
"orderMulti": false, // for disable multiple column at once
"ajax": {
"url": "/home/LoadData",
"type": "POST",
"datatype": "json"
},
"columns": [
{ "data": "FirstName", "name": "FirstName", "autoWidth": true },
{ "data": "LastName", "name": "LastName", "autoWidth": true },
{ "data": "Age", "name": "Age", "autoWidth": true },
{ "data": "Address", "name": "Address", "autoWidth": true },
{ "data": "City", "name": "City", "autoWidth": true },
{ "data": "State", "name": "State", "autoWidth": true }
]
});
});

Below is the code snippet for processing the loader using CSS and script.

For CSS

Customize the processing message that will appear when the data is being loaded. I have used the following custom styling here.

.custom-loader-color {
color: #fff !important;
font-size: 40px !important;
}
.custom-loader-background {
background-color: #f60 !important;
}
.custom-middle-align {
vertical-align: middle !important;
}

For script,

<script>
$(document).ready(function () {
$("#myTable").DataTable({
"language":
{
"processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
},
"processing": true,

Step 3

Add reference to System.Linq.Dynamic.

Here, I have added System.Linq.Dynamic reference from NuGet packages. Go to Solution Explorer > right click on References > Manage NuGet packages > Search with "System.Linq.Dynamic" > Install.

You can find out the System.Linq.Dynamic dll file under the References folder.

Performance Issue In jQuery Datatable About Fetching Records

Step 4

Add another action (here "LoadData") for fetching the data from the database and implement the logic for server-side paging and sorting.

Code ref

[HttpPost]
public ActionResult LoadData()
{
// Initialization.
JsonResult result = new JsonResult();
try
{
// Initialization.
var search = Request.Form.GetValues("search[value]")[0];
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
//Find Order Column
var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
int pageSize = length != null ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt32(start) : 0;
int recordsTotal = 0;
// Loading.
using (SatyaDBEntities dc = new SatyaDBEntities())
{
// dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key
var v = (from a in dc.employees select a);
//SORT
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
v = v.OrderBy(sortColumn + " " + sortColumnDir);
}
// Total record count.
recordsTotal = v.Count();
// Verification.
if (!string.IsNullOrEmpty(search) &&
!string.IsNullOrWhiteSpace(search))
{
// Apply search
v = v.Where(p => p.FirstName.ToString().ToLower().Contains(search.ToLower()) ||
p.LastName.ToLower().Contains(search.ToLower()) ||
p.State.ToString().ToLower().Contains(search.ToLower()) ||
p.City.ToLower().Contains(search.ToLower()) ||
p.Age.ToString().Contains(search.ToLower()) ||
p.Address.ToString().ToLower().Contains(search.ToLower()));
}
// Filter record count.
int recFilter = v.Count();
// Apply pagination.
var data = v.Skip(skip).Take(pageSize).ToList();
// Loading drop down lists.
result = this.Json(new { draw = draw, recordsFiltered = recFilter, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet);
}
}
catch (Exception ex)
{
// Info
Console.Write(ex);
}
// Return info.
return result;
}

Code description

I have described the code using the comment line in every line of code. It will be easy for a quick understanding of the code flow. In this piece of code, which is based on searching, sorting, and pagination information sent from the DataTable plugin, the following has been done: The data is being loaded first. It is being churned out based on the search criteria. Data is then sorted by a provided column in a provided order. Lastly, it is paginated and returned.

The "LoadData" function will be executed each time the table is being searched, sorted, or a new page is accessed. Have a look at the following two lines which are important.

// Total record count.
recordsTotal = v.Count();
// Filter record count.
int recFilter = v.Count();

The first line determines the actual amount of records that exist in the list and the second line determines the number of records that are left after applying this filter.

Step 5

Open the "_Layout.cshtml" file under "Views, Shared" folder and replace the existing code with the following to alter the existing layout and incorporated links to require the scripts and styles.

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title</title>
@Styles.Render("~/Content/css") @Scripts.Render("~/bundles/modernizr")
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.4.0/css/font-awesome.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.10/css/dataTables.bootstrap.min.css " /> @* Custom *@ @Styles.Render("~/Content/css/custom-style")
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<center>
</center>
</footer>
</div>
@Scripts.Render("~/bundles/jquery") @Scripts.Render("~/bundles/bootstrap")
<script src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js" type="text/javascript"></script>
@Scripts.Render("~/bundles/custom-datatable") @RenderSection("scripts", required: false)
</body>
</html>

OUTPUT

During the initial load, the processing loader will look like below.

It supports Server-side Pagination, Searching, State-saving, Multi-column sorting with data type detection.

If the search text is invalid or the data is not matching, then the below message is shown on the screen.

SUMMARY

In this write-up, we have learned how to -

Implement jQuery DataTable using MVC and Bootstrap.
Integrate server-side data with the DataTable plugin.
Get searching, sorting, and pagination information with the DataTable plugin.
Get Server-side integration of DataTable plugin with ASP.NET MVC 5.

7 Comments

Join the conversation! Your thoughts help the community grow.

Susmita Budhe5y ago
I have one issue with this code. for each sort or search or changing page data will load. every time we call this. var v = (from a in dc.employees select a);
Fadoua Destin8y ago
Nice job Satyaprakach.
- Satyaprakash Samantaray8y ago
  Thanks for the feedback
Fadoua Madane8y ago
Very nice article.
- Satyaprakash Samantaray8y ago
  Thanks for the feedback
Sarathlal Saseendran8y ago
Described well Satya. Keep it up.
- Satyaprakash Samantaray8y ago
  Thanks for the feedback and keep reading...