Introduction
While you are engage with comprehensive data and trying to attach in jQuery Datable or any other jQuery based data table/grid, most of cases it will slow down and ultimately you will face performance issue.
Here, I have listed few of cases, you might be find challenges in optimization on render data table. Suppose you need to display more than millions of records along with multiple columns using as mentioned following:
- Linq/Lambda Expression for complex query along with multiple tables.
- Time Consuming Business Logic or Complex Query in Store Procedure.
We have to perform server side chunk processing of data. I mean to say, We need to handle pagination, ordering of data on specific column (Ascending/Descending Order) as well you need to search text from multiple column and sometime on specific/individual column.
Whenever you face, out of controls to optimizing on data source whether data comes from Linq/Lambda expression using Entity Framework or Sql Store Procedure.
Or
If there is heavy traffic on your sql server database then it is advisable to store final data source into Caching In Memory.
To keep in caching memory, your final data source or model object after executed complex query then you can easily handle all kind of server-side process on your grid or table E.g. Server side pagination, Global Search as well multi column search.
Concept/Logic to boost DataTables grids
We know very well, once the data has come to IIS Server or In Memory, we can easily handle server side process.
- Global Search (Search Text in all column from entity)
- Individual Column Search (Search Text in specific column)
- Order By (Ordering on specific column Ascending or Descending Order)
- Page Size (Loading N numbers of rows in each page change request. Here we need to supply Page Row Index and N-numbers of records to display)
I have separated following module to execute my concept:
Cach Engine Manager (Utility Class/helper Class) - It is core module of this concept. Its main job is to store in memory of retrieved data from Entity framework or Ado.net Sql operations. I have used some specific key to maintain save or retrieve object from Cache Engine. I have used multiple function here
Add() - For Adding Object to Cache Memory using specific key. Same key will essential to Clear object from Cache Engine, Check(Exist) availability of object from Caching as well Get from Caching.
Clear() - To Release Cache Memory using specific key
Exists() - Check availability of object in Cache using specific key
Get() - To retrieve object from Cached using specific key
Model or Context Preparation- In this section I am trying to create model and mapped with database. I have tried to interaction with sql database using Code First Approach. I have taken very simple entity sample E.g.CountryData, StateData, CityData. We can store as much as possible records to store in sql server to check performance.
Server Side - Ajax post request payload from Jquery DataTable - Here we are going to write a code for Side operation which is requested from Jquery DataTabes. I have used two major methods
A. QuickResponse(DTParameters param) : This action methods initially will helps to get object availablity from caching. If there is not available from Cache Engine then it perform sql server interaction/database operation to retreive data then after it will store to Cache Engine.
So next onward every request it will not lookup or perform sql database query. It save lots of time to serve each and every request from client side(Jquery DataTable E.g. Pagination, Gloabal Search, Individual Column Search, Sorting). There are many benefit to use this caching integration approach. So every request from client (jquery Datatable) server will not execute sql database operation. Suppose there is N-number is client is using same datatable then all client will serve quickly with same cached object/retrieve from server using Cache Engine. These are the main benefits to use such caching approach. It reduces traffic or load from Sql Server.
but there are also disadvantaging to use this approach, if somebody has performed some sql operate e.g Insert Record, Update Record or Delete Record. So this data table will go out of dated or record will display old data because it is coming from Caching memory. It takes time to initialization. E.g. Retrieve data from Sql Server and then save to caching. Every stored object in Caching has limited life span. Cache will automatically clean after passes this time span. You can control life span of store cache memory as per our requirements.
Such scenario, We need to put some button on grid E.g. Refresh button. Its main job is to clear the cache container. Once clear the container from Cache Memory it automatically again load fresh copy of data from sql server. Finally, we need some separate method to clear memory from Cache Engine. Here I have written function for same job named as RefreshContainer()
It will better to show some where last modified or cached store time. So end user comes to know last update on cached engine. If he/she needs to up to date information from server, need to click Refresh Container button from Server.
B. RefreshContainer() : This function main job is to clear object from cached memory using some specific key.
Client Side - Html Page/Razor Page contains with Jquery DataTable- If you are familiar to initiliaze jquery datatable then it is very easy to understand. I have juest keep all setting and information in JqDTConfig E.g. All Columns, Ajax Url, Buttons. It is important to know I have taken one more value LastModifiedOn on each ajax request. This value is helful to display last updated cached container.
ReloadCacheEngine() : This function is going to execute while end user want to clear cache container from server. It is simple ajax request which is going to call RefreshContainer()
Now, I have started to write code with self-explanation along with comment summary. Hope this module can help one level up to boost your comprehensive jquery datatable.
Cache Engine Manager Integration
Fetched data source or final out come from complex query, need to utilize Cache Utility to store data.
public class CacheManager
{
static int CacheExpiryTime = Convert.ToInt32(ConfigurationManager.AppSettings["CacheTimeOut"]);
public void Add(string key, object o)
{
HttpRuntime.Cache.Insert(key, o, null,
DateTime.Now.AddMinutes(CacheExpiryTime),
System.Web.Caching.Cache.NoSlidingExpiration);
}
public void Clear(string key)
{
HttpRuntime.Cache.Remove(key);
}
public bool Exists(string key)
{
return HttpRuntime.Cache[key] != null;
}
public object Get(string key)
{
try
{
return HttpRuntime.Cache[key];
}
catch
{
return null;
}
}
}
Model and Context Preparation
public class CountryData
{
public long Id { get; set; }
public string Country { get; set; }
}
public class StateData
{
public long Id { get; set; }
public string State { get; set; }
public long CountryId { get; set; }
[ForeignKey("CountryId")]
public virtual CountryData CountryData { get; set; }
}
public class CityData
{
public long Id { get; set; }
public string City { get; set; }
public long StateId { get; set; }
[ForeignKey("StateId")]
public virtual StateData StateData { get; set; }
}
public class EntityContext : DbContext
{
public EntityContext() : base("name=CacheManager") { }
public DbSet<CountryData> ScientistData { get; set; }
public DbSet<StateData> StateData { get; set; }
public DbSet<CityData> CityData { get; set; }
}
Set connection string with catalog "CacheManager" which is mentioned in Context construction
Execute command for code first approach as following :
- Enable-Migrations
- Add-Migration IntialCreate
- Update-Database
Server Side - Ajax post request payload from Jquery DataTable
Jquery DataTable will call to server and trying get data. There are many kind of operation need to perform here.
E.g. Handle Server Side Pagination, Global Search, Column wise search etc.
[HttpPost]
public ActionResult QuickResponse(DTParameters param)
{
int count;
string LastUpdateOn;
var CityData = new List<CityResult>();
var CacheManager = new CacheManager();
if (CacheManager.Exists(CityCacheKey))
{
CityData = (List<CityResult>)CacheManager.Get(CityCacheKey);
LastUpdateOn = (string)CacheManager.Get(LastUpdateOnCacheKey);
}
else
{
CityData = new QueryHelper().LoadCity();
LastUpdateOn = DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss");
CacheManager.Add(CityCacheKey, CityData);
CacheManager.Add(LastUpdateOnCacheKey, LastUpdateOn);
}
IEnumerable<CityResult> DataTableContainer = CityData;
string SearchText = param.Search != null ? param.Search.Value : string.Empty;
if (!string.IsNullOrEmpty(SearchText))
DataTableContainer = GlobalSearchInCityData(DataTableContainer, SearchText.Trim().ToLower());
DataTableContainer = MultiColumnSearch(param, DataTableContainer);
if (!(string.IsNullOrEmpty(param.SortOrder) && string.IsNullOrEmpty(param.Order[0].Dir.ToString())))
DataTableContainer = SortList(DataTableContainer, param.SortOrder, param.Order[0].Dir.ToString());
count = DataTableContainer.Count();
CityData = DataTableContainer.ToPaged(param.Start, param.Length).ToList();
DTResult<CityData> result = new DTResult<CityData>
{
draw = param.Draw,
data = CityData,
recordsFiltered = count,
recordsTotal = count,
LastModifiedOn = LastUpdateOn
};
var JsonResult = Json(result, "application/json", JsonRequestBehavior.AllowGet);
JsonResult.MaxJsonLength = int.MaxValue;
return JsonResult;
}
private IEnumerable<CityResult> GlobalSearchInCityData(IEnumerable<CityResult> Container, string SearchText)
{
Container = Container.Where(u =>
(u.City != null && u.City.ToLower().Contains(SearchText)) ||
(u.State != null && u.State.ToLower().Contains(SearchText)) ||
(u.Country != null && u.Country.ToLower().Contains(SearchText)));
return Container;
}
private IEnumerable<CityResult> SortList(IEnumerable<CityResult> Container, string sortColumn, string sortColumnDir)
{
return Container.OrderBy(sortColumn + " " + sortColumnDir); ;
}
private IEnumerable<CityResult> MultiColumnSearch(DTParameters Param, IEnumerable<CityResult> Container)
{
string SearchText = Param.Search != null ? Param.Search.Value : string.Empty;
Param.Columns.Where(w => w.Search != null && !string.IsNullOrEmpty(w.Search.Value)).ToList().ForEach(f =>
{
var SearchKey = HttpUtility.UrlDecode(f.Search.Value.Trim().ToLower());
if (f.Data == "City")
{
Container = Container.Where(w => w.City != null && w.City.ToLower().Contains(SearchKey));
}
else if (f.Data == "State")
{
Container = Container.Where(w => w.State != null && w.State.ToLower().Contains(SearchKey));
}
else if (f.Data == "Country")
{
Container = Container.Where(w => w.Country != null && w.Country.ToLower().Contains(SearchKey));
}
});
return Container;
}
public ActionResult RefreshContainer()
{
try
{
new CacheManager().Clear(CityCacheKey);
return Json(new { status = true }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(new { status = false, message = ex.ToString() }, JsonRequestBehavior.AllowGet);
}
}
}
public static class EntityExtension
{
public static IEnumerable<T> ToPaged<T>(this IEnumerable<T> table, int start = 0, int length = 0)
{
if (length > 0)
return table.Skip(start).Take(length).ToList();
else return table.Skip(start);
}
}
Client Side - Html Page/Razor Page contains with Jquery DataTable
<link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css " rel="stylesheet" />
<link href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<script type="text/javascript">
var ReloadCacheEngine = function () {
$.ajax({
url: "/Cached/RefreshContainer", success: function (response) {
if (response.status == true) {
alert("Cache Engine has successfully updated!");
location.reload();
}
else
bootbox.alert(response.message);
}
});
}
var JqDTConfig = {
Ajax: {
"url": '/Cached/QuickResponse/',
"type": "Post",
"datatype": "json",
dataFilter: function (response) {
var JsonResponse = JSON.parse(response);
$("#LastUpdatedOn").attr("title", "Click to refresh the Cach Engine. Last Updated on " + JsonResponse.LastModifiedOn);
return response;
},
},
columns: [
{ "data": "City" },
{ "data": "State" },
{ "data": "Country" }
],
buttons: [
{
text: "<i id='LastUpdatedOn' aria-hidden='true' style='color:green'><img height='15 px' src='/Content/refreshIcon.png'/></i>",
action: function (e, dt, node, config) {
ReloadCacheEngine();
}
}]
};
$(function () {
var table = $('#example').DataTable({
"lengthMenu": [[10, 20, 50, 100, 250, 500], [10, 20, 50, 100, 250, 500]],
"oLanguage": {
"sLengthMenu": "_MENU_ "
},
"order": [[0, "desc"]],
"processing": true,
"serverSide": true,
dom: 'Bfrtip',
dom: 'lBfrtip',
buttons: JqDTConfig.buttons,
"searchHighlight": true,
"ajax": JqDTConfig.Ajax,
"columns": JqDTConfig.columns
});
$('#example tfoot th').each(function () {
var title = $(this).text().trim();
$(this).html('<input type="text" style="width:100%;" title="' + title + '" placeholder="' + title + '" />');
})
table.columns().every(function () {
$('input', this.footer()).on('keyup change', function () {
table.column($(this).parent().index() + ':visible')
.search(this.value)
.draw();
});
});
});
</script>
<h2>Server Side Caching with Pagination - Jquery DataTable</h2>
<table class="table" id="example">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.State)
</th>
<th>
@Html.DisplayNameFor(model => model.Country)
</th>
</tr>
</thead>
<tfoot>
<tr>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.State)
</th>
<th>
@Html.DisplayNameFor(model => model.Country)
</th>
</tr>
</tfoot>
</table>
Result/Out Put
Points of Interest
- Most of cases not required to display live records, such scenario we can store fetched data or final outcome into Caching memory.
- I have taken 5 Minutes of Caching memory life span for demonstration purpose. You can set accordingly as per project's need. Even you can get fresh copy or live fresh record by click on refresh button from grid.
- Cutomize DTParameters (JQuery Datatable posting model) added feild as LastModifiedOn for updated time details. It will update timings of grid each and every request E.g. Pagination or Searching text.
- This mechanism can boost your compressive jQuery DataTable and Reduce load or traffic on your ms sql datatbase server
