JQuery datatables with the large amount of data?

dmilos89

I have developed some tabs in my application where I use JQuery Datatables to load the data. I use JavaScript/JQuery, HTML5, Bootstrap 3 and ColdFusion 2016 on the back end to communicate with SQL 2008 database. For example user will be able to search through Users table. They can filter by Name, Username, Email. I use AJAX to call ColdFusion function then retrieve the data and load int he table. All data will be in JSON format. Reason why I started looking over pagination was one of my tables has around 3600 records. User want filter option where they can request all records to be displayed in the table. To be honest this is not that bad with my current code, it takes around 4 seconds to load the data and show in the table. However, I'm not sure if this time of 4 seconds is good enough or it's considered inefficient? However, I'm planning to use datatables to display some data for reports. In that situation I might end up with large amount of data (for example annual reports might have over 5000 records). My question is does this logic that I currently use is good enough for handling large amount of data in datatables or I should consider using Server-side processing? Also my question is if I use server-side approach and lets say my table has 7000 records, I would show only 25 records per page. User might end up wanting to export data to Excel, CSV or PDF file (that is an option in datatables) would they still get all 7000 records exported in that file or they only will get 25 records that are loaded in the page? Is there a way to export all 7000 records in one of the file formats that I mentioned? Here is my current code example:

var usersStorage = {};
$('#frmUsers').on('submit', findRecords);
function findRecords(e) {
  e.preventDefault();
  var formData = $(this).serialize(),
    fldMessage = $(this).find(".message-submit"),
    recordsBox = $("#user_records"),
    frmButton = $(this).find(":button");

  if (formData) {
    frmButton.prop('disabled', true);
    $.ajax({
      type: 'POST',
      url: 'Components/myFunctions.cfc?method=findUsers',
      data: formData,
      dataType: 'json'
    }).done(function(obj) {
      if (obj.STATUS === 200) {
        usersStorage = obj.DATA;
        showUsers(usersStorage);
        frmButton.prop('disabled', false);
      } else {
        recordsBox.empty(); // Clear the table.
        fldMessage.show().addClass(obj.CLASS).html(obj.MESSAGE).delay(8000).fadeOut('slow').queue(function() {
          $(this).removeClass(obj.CLASS).dequeue();
          frmButton.prop('disabled', false);
        });
      }
    }).fail(function(jqXHR, textStatus, errorThrown) {
      alert('Error: ' + errorThrown);
    });
  }
}

function showAccounts(usersStorage) {
    var usersTbl = "<table id='usersTbl' class='table table-bordered'><thead><tr><th>Last</th><th>First</th><th>Email</th><th class='text-center'>Edit</th></tr></thead><tbody>";
    if(usersStorage){
        for (var key in usersStorage) {
            usersTbl += "<tr id='"+$.trim(usersStorage[key].RECORDID)+"'><td>"+$.trim(accountsStorage[key].LASTNAME)+"</td>";
            usersTbl += "<td>"+$.trim(usersStorage[key].FIRSTNAME)+"</td>";
            usersTbl += "<td>"+$.trim(usersStorage[key].EMAIL)+"</td>";
            usersTbl += "<td class='text-center'><button class='btn btn-default btn-sm users_edit' data-toggle='collapse'></span></button></td></tr>";
        }
    }
    
    usersTbl += "</tbody></table><div class='row'><div class='col-xs-12 col-sm-12 col-md-12 col-lg-12'><div id='users_message' class='alert message-submit'></div></div></div>";
    $('#user_records').empty().append(usersTbl).show();
    buildDataTable('usersTbl',[6,7,8],10);
}

function buildDataTable(tblID,columnsArray,displayLength) {
    $("#"+tblID).DataTable({
        //deferRender: true,
        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ],
        "iDisplayLength": Number(displayLength),
        "aoColumnDefs": [
            { 'bSortable': false, 'aTargets': columnsArray }
        ],
        "language": {
            "emptyTable": "No records were found."
        }
    });
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script language="javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<form name="frmUsers" id="frmUsers">
  <div class="row">
    <div class="form-group col-xs-12 col-sm-12 col-md-3 col-lg-3">
      <select class="form-control" name="frm_filterby" id="frm_filterby" required>
        <option value="">--Search By--</option>
        <option value="1">Username</option>
        <option value="2">Email</option>
        <option value="3">Name</option>
      </select>
    </div>
    <div class="form-group col-xs-12 col-sm-12 col-md-3 col-lg-3">
      <input type="text" class="form-control" name="frm_search" id="frm_search" placeholder="Select Search Criteria" disabled>
    </div>
  </div>
  <div class="row">
    <div class="form-group col-xs-12 col-sm-12 col-md-12 col-lg-12">
      <button class="btn btn-block btn-primary">
        <span class="glyphicon glyphicon-search"></span> Search
      </button>
    </div>
  </div>
  <div class="row">
    <div class="form-group col-xs-12 col-sm-12 col-md-12 col-lg-12">
      <div class="alert message-submit"></div>
    </div>
  </div>
</form>
<div id="user_records" class="table-responsive"></div>
 Run code snippetExpand snippet
Here is ColdFusion function:

<cffunction name="findUsers" access="remote" output="false" returnformat="JSON">
    <cfargument name="frm_filterby" type="string" required="yes">
        <cfargument name="frm_search" type="string" required="yes">
        <cfset local.fnResults = structNew()>
        <cfset local.fnRecUsers = structNew()>

        <cftry> 
            <cfquery name="qryUsers" datasource="#dsn#">
                SELECT RecID, FirstName, LastName, Email
                FROM Users AS A
                WHERE 1=1 AND
                <cfswitch expression="#arguments.frm_filterby#">
                    <cfcase value="1"><!--- Username --->
                        UserName = <cfqueryparam value="#trim(arguments.frm_search)#" cfsqltype="cf_sql_varchar" maxlength="50" />
                    </cfcase>
                    <cfcase value="2"><!--- Email --->
                        Email = <cfqueryparam value="#trim(arguments.frm_search)#" cfsqltype="cf_sql_varchar" maxlength="80" />
                    </cfcase>
                    <cfdefaultcase><!--- Last or First or Last & First Name --->
                        FirstName + LastName LIKE <cfqueryparam value="%#trim(arguments.frm_search)#%" cfsqltype="cf_sql_varchar" maxlength="50" />
                    </cfdefaultcase>
                </cfswitch>
                ORDER BY Name
            </cfquery>

            <cfif qryUsers.recordcount NEQ 0>
                <cfloop query="qryUsers">
                    <cfset local.fnRecUsers[RecID] = {
                        RecordID : qryUsers.RecID,
                        FirstName : qryUsers.FirstName,
                        LastName : qryUsers.LastName,
                        Email : qryUsers.Email
                    }>
                </cfloop>
            </cfif>

            <cfset local.fnResults = {status : "200" , data : fnRecUsers}>

            <cfcatch type="any">
                <cfset local.fnResults = {status : "400", class : "alert-danger", message : "Error! Please contact your administrator."}>
            </cfcatch>
        </cftry>

        <cfreturn fnResults>
    </cffunction>

Here is small sample of data after function has been returned to findRecord function obj.DATA:

"DATA":{
    "FB3064A7-A43D-4261-8B5C-285A637D82B9":{
        "FIRSTNAME":"Mike",
        "EMAIL":"mhart@yahoo.org",
        "RECORDID":"23",
        "LASTNAME":"Hart"
    },
    "BBD1669E-4FFB-4F45-A1CC-EB7CBD330A3E":{
        "FIRSTNAME":"Timmy",
        "EMAIL":"tbuck@gmail.com",
        "RECORDID":"61",
        "LASTNAME":"Draca"
    },
    "AF0243E5-E664-4E0C-B057-3389A06EF6F4":{
        "FIRSTNAME":"Mark",
        "EMAIL":"mjohnes@gmail.com",
        "RECORDID":"26",
        "LASTNAME":"Johnes"
    },
    "4B06553D-86E9-46BB-9B5B-8580E9027BE0":{
        "FIRSTNAME":"Tammy",
        "EMAIL":"thill@yhaoo.org",
        "RECORDID":"43",
        "LASTNAME":"Hill"
    }
}

Then I store that data in global JavaScript object. Then if user decides to update the record data we can use data from JS object. This works fine so far but I would like to here any suggestions if this can be improved or it's a good fit for server-side processing?

colin

Hi @dmilos89 ,

Yep, as you said, if you have server-side processing enabled, the client will only have access to the data being displayed - so you won't be able to export all the data with the Buttons export functionality, you'll need to do something on the server side.

Personally, I'd say 4 seconds isn't much of a wait, but it would be worth getting a judgement from the other stakeholders.

Cheers,

Colin