Post
Ask Question

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.
  1. ASP.NET MVC
  2. HTML
  3. JavaScript
  4. AJAX
  5. CSS
  6. Bootstrap
  7. C# Programming
  8. C# LINQ
  9. jQuery
Note
Before going through the session, I suggest you first visit the previous datatable in MVC related article with a back-end section.
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
  1. public ActionResult serverpagination()  
  2.         {  
  3.             return View();  
  4.         }  

Step 2

Add a View for the action (here "serverpagination") and design. 
 
Code ref
  1. @{  
  2.     ViewBag.Title = "Satyaprakash";  
  3. }  
  4.   
  5. <h2 style="color: blue">Satyaprakash-jQuery Datatable server side pagination</h2>  
  6.   
  7. <style>  
  8.     table {  
  9.         font-family: arial, sans-serif;  
  10.         border-collapse: collapse;  
  11.         width: 100%;  
  12.     }  
  13.   
  14.     td, th {  
  15.         border: 1px solid #dddddd;  
  16.         text-align: left;  
  17.         padding: 8px;  
  18.     }  
  19.   
  20.     tr:nth-child(even) {  
  21.         background-color: #dddddd;  
  22.     }  
  23.   
  24.     .custom-loader-color {  
  25.         color: #fff !important;  
  26.         font-size: 40px !important;  
  27.     }  
  28.   
  29.     .custom-loader-background {  
  30.         background-color: #f60 !important;  
  31.     }  
  32.   
  33.     .custom-middle-align {  
  34.         vertical-align: middle !important;  
  35.     }  
  36. </style>  
  37.   
  38. <div style="width:90%; margin:0 auto;">  
  39.     <table id="myTable" align="center" border="1" cellpadding="4" cellspacing="4">  
  40.         <thead>  
  41.             <tr>  
  42.                 <th style="background-color: Yellow;color: blue">First Name</th>  
  43.                 <th style="background-color: Yellow;color: blue">Last Name</th>  
  44.                 <th style="background-color: Yellow;color: blue">Age</th>  
  45.                 <th style="background-color: Yellow;color: blue">Address</th>  
  46.                 <th style="background-color: Yellow;color: blue">City</th>  
  47.                 <th style="background-color: Yellow;color: blue">State</th>  
  48.             </tr>  
  49.         </thead>  
  50.     </table>  
  51. </div>  
  52.   
  53. @* Load bootstrap datatable css *@  
  54. <link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" />  
  55. <link href="//cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" />  
  56.   
  57. @* Load normal datatable css *@  
  58. @*<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />*@  
  59.   
  60. @* Load bootstrap datatable js *@  
  61. @section Scripts{  
  62.     <script src="//code.jquery.com/jquery-3.3.1.js"></script>  
  63.     <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>  
  64.     <script src="//cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>  
  65.   
  66.     @* Load normal datatable js *@  
  67.     @*<script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>*@  
  68.   
  69.     <script>  
  70.         $(document).ready(function () {  
  71.             $("#myTable").DataTable({  
  72.                 "language":  
  73.                 {  
  74.                     "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"  
  75.                 },  
  76.                 "processing": true, // for show progress bar  
  77.                 "serverSide": true, // for process server side  
  78.                 "filter": true, // this is for disable filter (search box)  
  79.                 "orderMulti": false, // for disable multiple column at once  
  80.                 "ajax": {  
  81.                     "url": "/home/LoadData",  
  82.                     "type": "POST",  
  83.                     "datatype": "json"  
  84.                 },  
  85.                 "columns": [  
  86.                     { "data": "FirstName", "name": "FirstName", "autoWidth": true },  
  87.                     { "data": "LastName", "name": "LastName", "autoWidth": true },  
  88.                     { "data": "Age", "name": "Age", "autoWidth": true },  
  89.                     { "data": "Address", "name": "Address", "autoWidth": true },  
  90.                     { "data": "City", "name": "City", "autoWidth": true },  
  91.                     { "data": "State", "name": "State", "autoWidth": true }  
  92.                 ]  
  93.             });  
  94.         });  
  95.     </script>  
  96. }  

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.
  1. "processing": true, // for show progress bar  
  2.                 "serverSide": true, // for process server side  
  3.                 "filter": true, // this is for disable filter (search box)  
  4.                 "orderMulti": false, // for disable multiple column at once  
  5.                 "ajax": {  
  6.                     "url": "/home/LoadData",  
  7.                     "type": "POST",  
  8.                     "datatype": "json"  
  9.                 },  
  10.                 "columns": [  
  11.                     { "data": "FirstName", "name": "FirstName", "autoWidth": true },  
  12.                     { "data": "LastName", "name": "LastName", "autoWidth": true },  
  13.                     { "data": "Age", "name": "Age", "autoWidth": true },  
  14.                     { "data": "Address", "name": "Address", "autoWidth": true },  
  15.                     { "data": "City", "name": "City", "autoWidth": true },  
  16.                     { "data": "State", "name": "State", "autoWidth": true }  
  17.                 ]  
  18.             });  
  19.         });  

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.
  1. .custom-loader-color {  
  2.         color: #fff !important;  
  3.         font-size: 40px !important;  
  4.     }  
  5.   
  6.     .custom-loader-background {  
  7.         background-color: #f60 !important;  
  8.     }  
  9.   
  10.     .custom-middle-align {  
  11.         vertical-align: middle !important;  
  12.     }  
For script,
  1. <script>  
  2.         $(document).ready(function () {  
  3.             $("#myTable").DataTable({  
  4.                 "language":  
  5.                 {  
  6.                     "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"  
  7.                 },  
  8.                 "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
  1. [HttpPost]  
  2.         public ActionResult LoadData()  
  3.         {  
  4.             // Initialization.     
  5.             JsonResult result = new JsonResult();  
  6.   
  7.             try  
  8.             {  
  9.                 // Initialization.  
  10.                 var search = Request.Form.GetValues("search[value]")[0];  
  11.                 var draw = Request.Form.GetValues("draw").FirstOrDefault();  
  12.                 var start = Request.Form.GetValues("start").FirstOrDefault();  
  13.                 var length = Request.Form.GetValues("length").FirstOrDefault();  
  14.                 //Find Order Column  
  15.                 var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();  
  16.                 var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();  
  17.   
  18.   
  19.                 int pageSize = length != null ? Convert.ToInt32(length) : 0;  
  20.                 int skip = start != null ? Convert.ToInt32(start) : 0;  
  21.                 int recordsTotal = 0;  
  22.                 // Loading.  
  23.                 using (SatyaDBEntities dc = new SatyaDBEntities())  
  24.                 {  
  25.                     // dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key  
  26.                     var v = (from a in dc.employees select a);  
  27.   
  28.                     //SORT  
  29.                     if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))  
  30.                     {  
  31.                         v = v.OrderBy(sortColumn + " " + sortColumnDir);  
  32.                     }  
  33.   
  34.                     // Total record count.  
  35.                     recordsTotal = v.Count();  
  36.   
  37.                     // Verification.  
  38.                     if (!string.IsNullOrEmpty(search) &&  
  39.                !string.IsNullOrWhiteSpace(search))  
  40.                     {  
  41.                         // Apply search     
  42.                         v = v.Where(p => p.FirstName.ToString().ToLower().Contains(search.ToLower()) ||  
  43.                             p.LastName.ToLower().Contains(search.ToLower()) ||  
  44.                             p.State.ToString().ToLower().Contains(search.ToLower()) ||  
  45.                             p.City.ToLower().Contains(search.ToLower()) ||  
  46.                             p.Age.ToString().Contains(search.ToLower()) ||  
  47.                             p.Address.ToString().ToLower().Contains(search.ToLower()));  
  48.                     }  
  49.                     // Filter record count.  
  50.                     int recFilter = v.Count();  
  51.   
  52.                     // Apply pagination.     
  53.                     var data = v.Skip(skip).Take(pageSize).ToList();  
  54.   
  55.                     // Loading drop down lists.  
  56.                     result = this.Json(new { draw = draw, recordsFiltered = recFilter, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet);  
  57.                 }  
  58.             }  
  59.             catch (Exception ex)  
  60.             {  
  61.                 // Info     
  62.                 Console.Write(ex);  
  63.             }  
  64.             // Return info.     
  65.             return result;  
  66.         }  

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.
  1. // Total record count.  
  2.  recordsTotal = v.Count();  
  3.   
  4. // Filter record count.  
  5.  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.
  1. <!DOCTYPE html>  
  2. <html>  
  3.   
  4. <head>  
  5.     <meta charset="utf-8" />  
  6.     <meta name="viewport" content="width=device-width, initial-scale=1.0">  
  7.     <title>@ViewBag.Title</title>  
  8.     @Styles.Render("~/Content/css") @Scripts.Render("~/bundles/modernizr")  
  9.     <!-- Font Awesome -->  
  10.     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.4.0/css/font-awesome.min.css" />  
  11.     <!-- Data table -->  
  12.     <link rel="stylesheet" href="https://cdn.datatables.net/1.10.10/css/dataTables.bootstrap.min.css " /> @* Custom *@ @Styles.Render("~/Content/css/custom-style")  
  13. </head>  
  14.   
  15. <body>  
  16.     <div class="navbar navbar-inverse navbar-fixed-top">  
  17.         <div class="container">  
  18.             <div class="navbar-header">  
  19.                 <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">  
  20.                     <span class="icon-bar"></span>  
  21.                     <span class="icon-bar"></span>  
  22.                     <span class="icon-bar"></span>  
  23.                 </button>  
  24.             </div>  
  25.         </div>  
  26.     </div>  
  27.     <div class="container body-content">  
  28.         @RenderBody()  
  29.         <hr />  
  30.         <footer>  
  31.             <center>  
  32.                 <p><strong>Copyright © @DateTime.Now.Year - <a href="https://satyaprakash-samantaray.firebaseapp.com/">My Profile</a>.</strong> All rights reserved.</p>  
  33.             </center>  
  34.         </footer>  
  35.     </div>  
  36.     @Scripts.Render("~/bundles/jquery") @Scripts.Render("~/bundles/bootstrap")  
  37.     <!-- Data Table -->  
  38.     <script src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js" type="text/javascript"></script>  
  39.     <script src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js" type="text/javascript"></script>  
  40.     @Scripts.Render("~/bundles/custom-datatable") @RenderSection("scripts", required: false)  
  41. </body>  
  42.   
  43. </html>  

OUTPUT

During the initial load, the processing loader will look like below.
 
Performance Issue In jQuery Datatable About Fetching Records
 
It supports Server-side Pagination, Searching, State-saving, Multi-column sorting with data type detection. 
 
Performance Issue In jQuery Datatable About Fetching Records 
 
If the search text is invalid or the data is not matching, then the below message is shown on the screen.
 
Performance Issue In jQuery Datatable About Fetching Records
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. 


Similar Articles