jQuery DataTables Interaction with Server Side Caching

• Download source - 762.1 KB

Introduction

While you are engaged with comprehensive data and trying to attach in jQuery Datable or any other jQuery based data table/grid, in most cases, it will slow down and ultimately you will face a performance issue.

Here, I have listed few cases where you might 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 below:

• 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 as you need to search text from multiple column and sometime on specific/individual column.

Whenever you face this, 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 kinds of server-side processes on your grid or table, e.g., Server side pagination, Global Search as well as multi column search.

Concept/Logic to Boost DataTables Grids

We know very well that once the data has come to IIS Server or In Memory, we can easily handle server side process.

1. Global Search (Search Text in all column from entity)
2. Individual Column Search (Search Text in specific column)
3. Order By (Ordering on specific column Ascending or Descending Order)
4. 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 the following module to execute my concept:

Cache Engine Manager (Utility Class/helper Class) - It is the 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 functions here.

• Add() - For Adding Object to Cache Memory using specific key. Same key will be 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 interact with SQL database using Code First Approach. I have taken a 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 requests 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:

1. QuickResponse(DTParameters param): This action method initially will help to get object availability from caching. If it is not available from Cache Engine, then it perform SQL Server interaction/database operation to retrieve data, then it will store to Cache Engine.

   So next onwards, every request it will not lookup or perform SQL database query. It saves a lot of time to serve each and every request from client side (Jquery DataTable, e.g., Pagination, Global Search, Individual Column Search, Sorting). There are many benefits to using this caching integration approach. So every request from client (jquery Datatable) server will not execute SQL database operation. Suppose there is N-number of client using the same datatable, then all clients 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 disadvantages to use this approach, if somebody has performed some SQL operations, e.g., Insert Record, Update Record or Delete Record. So this data table will go out of date or record will display old data because it is coming from Caching memory. It takes time to initialize, e.g., retrieve data from SQL Server and then save to caching. Every stored object in Caching has a limited life span. Cache will automatically clean after it passes this time span. You can control the life span of store cache memory as per our requirements.

   In such a 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 automatically again loads a fresh copy of data from SQL Server. Finally, we need some separate method to clear memory from Cache Engine. Here, I have written a function for the same job named as RefreshContainer().

   It will be better to show somewhere last modified or cached store time so that the end user gets to know the last update on cached engine. If he/she needs up to date information from server, they need to click Refresh Container button from Server.

2. RefreshContainer(): This function's 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 initialize jquery datatable, then it is very easy to understand. I have just keep all settings and information in JqDTConfig. E.g. All Columns, Ajax URL, Buttons. It is important to know that I have taken one more value LastModifiedOn on each Ajax request. This value is helpful to display the last updated cached container.

• ReloadCacheEngine(): This function is going to execute while end user wants to clear cache container from server. It is a 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 outcome from complex query is needed to utilize Cache Utility to store data.

/// <summary>
    /// Cache Engine Utility Manager. This utility is helpful to store data in system memory.
    /// </summary>
    public class CacheManager
    {
        static int CacheExpiryTime = Convert.ToInt32(ConfigurationManager.AppSettings["CacheTimeOut"]);
        /// <summary>
        /// Adding data or model to store System
        /// </summary>
        /// <param name="key">Need to set some key with while storing data to system. 
        /// This key will help to retrieve the same information</param>
        /// <param name="o">Data or Model</param>
        public void Add(string key, object o)
        {
            HttpRuntime.Cache.Insert(key, o, null,
                DateTime.Now.AddMinutes(CacheExpiryTime),//in minutes
                System.Web.Caching.Cache.NoSlidingExpiration);
        }
        /// <summary>
        /// Clear or release data from system
        /// </summary>
        /// <param name="key"></param>
        public void Clear(string key)
        {
            HttpRuntime.Cache.Remove(key);
        }
        /// <summary>
        /// Check Model/Data is already stored or not in system
        /// </summary>
        /// <param name="key">Your pre defined key while storing data or model to system</param>
        /// <returns></returns>
        public bool Exists(string key)
        {
            return HttpRuntime.Cache[key] != null;
        }
        /// <summary>
        /// Fetching/retrieve data from Cached Memory. 
        /// Note it return type is object that's why you need to deserialize it before use.
        /// </summary>
        /// <param name="key">Your pre defined key while storing data or model to system</param>
        /// <returns>Model or data as object data type</returns>
        public object Get(string key)
        {
            try
            {
                return HttpRuntime.Cache[key];
            }
            catch
            {
                return null;
            }
        }
    }

Model and Context Preparation

public class CountryData
   {
       //Taking long data type (instead of int) for strong large number of records
       public long Id { get; set; }
       public string Country { get; set; }
   }
   public class StateData
   {
       public long Id { get; set; }
       public string State { get; set; }
       //Set ForeignKey as CountryId
       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; }

       //Set ForeignKey as StateId
       public long StateId { get; set; }
       [ForeignKey("StateId")]
       public virtual StateData StateData { get; set; }
   }

//Context preparation
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 is as follows:

1. Enable-Migrations
2. Add-Migration IntialCreate
3. Update-Database

Server Side - Ajax Post Request Payload from Jquery DataTable

Jquery DataTable will call server and try getting data. There are many kinds of operations needed 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();
        //Check in Cache Storage availability of data or model object
        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");
            //Storing data or model in Cache Storage
            CacheManager.Add(CityCacheKey, CityData);
            CacheManager.Add(LastUpdateOnCacheKey, LastUpdateOn);
        }
        //Taken DataTable Container...
        IEnumerable<CityResult> DataTableContainer = CityData;
        string SearchText = param.Search != null ? param.Search.Value : string.Empty;
        //Global Search (Search in all columns).
        if (!string.IsNullOrEmpty(SearchText))
            DataTableContainer = GlobalSearchInCityData
                                 (DataTableContainer, SearchText.Trim().ToLower());

        //Individual Column Search (Search Text in Specific Column)
        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());

        //Set Total Record in Database/Cache Engine
        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;
    }
    //Searching in all columns
    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;
    }
    //Sorting for City Records....
    private IEnumerable<CityResult> SortList(IEnumerable<CityResult> Container,
                      string sortColumn, string sortColumnDir)
    {
        return Container.OrderBy(sortColumn + " " + sortColumnDir); ;
    }
    //Column wise Searching....
    private IEnumerable<CityResult> MultiColumnSearch
              (DTParameters Param, IEnumerable<CityResult> Container)
    {
        string SearchText = Param.Search != null ? Param.Search.Value : string.Empty;
        //Essential for Multi Column Search
        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;
    }
    /// <summary>
    /// Clear or release data from Cache memory. Data can not be available longer.
    /// </summary>
    /// <returns></returns>
    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);
        }
    }
}

/// <summary>
/// Handle Pagination on model or data object
/// </summary>
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">
    //To Refresh Cache Container
    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);
            }
        });
    }
    //Jquery DataTable Object.It has Ajax,Columns and Button details
    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 () {
        //Jquery DataTable Initialization
        var table = $('#example').DataTable({
            "lengthMenu": [[10, 20, 50, 100, 250, 500], [10, 20, 50, 100, 250, 500]],
            "oLanguage": {
                "sLengthMenu": "_MENU_ &nbsp;"
            },
            "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 + '" />');
        })
        //INIT EVENTS
        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/Output

Points of Interest

• In most cases, it is not required to display live records, in such scenarios, we can store fetched data or final outcome into Caching memory.
• I have taken 5 minutes of Caching memory life span for demonstration purposes. You can set accordingly as per project's need. Even you can get fresh copy or live fresh record by clicking on refresh button from grid.
• Customize DTParameters (JQuery Datatable posting model) added field as LastModifiedOn for updated time details. It will update timings of grid for 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 database server.