# Optimizing Datatables table of tens of thousands of client-provided rows

We have this employees table, that looks like this:

that contains 16,530 employee rows. As expected, this table takes more than 30 seconds to load. I have been tasked with speeding it up somehow.

Thus, our flow is as follows:

1. Get all the employees from the API, for a store
2. For each employee
• convert it into an Employee object
• get its HTML representation (via template rendering)
• push that HTML representation into a collection
3. initialize the Datatables object with that collection
4. adjust the Datatables columns and draw the table

Implementation

On $(document).ready, we have the following table setup logic: var addbutton = '<button onclick="PopModel()" class="btn btn-success float-sm-left rounded"><i class="fa fa-user-plus" aria-hidden="true"></i> Add Employee</button>' var table_height =$('#with-content')
.height() - 175
var table = InitializeDatatable('#data-table', table_height, addbutton, 1, {
paging: true,
pageLength: 50,
deferRender: true
})

let start = Date.now()
GetEmployees(function (result, success) {
if (success) {
var secondStep = Date.now()
var hundredBatchStep = Date.now()
let tableRows  = []
console.log("Fetching the data from the server took %.3f seconds",
(secondStep - start) / 1000)
var ran = 0;
for (let i = 0; i < result.length; i++) {
const element = result[i];
// progress bar logic
setTimeout(function () {
ran++;
// adjust the progress bar state if it is defined
if ($progressBar != null) { var percentValue = (ran / result.length) * 100$progressBar
.css('width', percentValue + "%")
if (percentValue == 100) {
$('.dataTables_scrollBody') .LoadingOverlay('hide') } } // extract an Employee object and add its HTML representation to datatables var employee = new Employee() .ExtractFrom(element),$employeeRow = employee.ToHTML()
tableRows.push($employeeRow[0]) if (ran == result.length) { let intermediateStep = Date.now() table.rows.add($(tableRows))
let thirdStep = Date.now()
console.log("adding the table rows to the Datatables API took %.3f seconds", (thirdStep - secondStep) / 1000)
.draw();
console.log("took %.3f seconds to draw", (Date.now() - thirdStep) / 1000)
}
if (ran == 50) {
$('.dataTables_scrollBody') .LoadingOverlay("show", { image: "", custom:$progressBarDiv
});
$progressBar =$('#progressbar .progress-bar')
}
}, 1)
}

if (result.length == 0 && $('#task-selectpicker option') .length == 0) { Alert("It looks like there are no tasks created, would you like to create them before creating your employees?", "This can make things easier when setting up your employees.", function () { window.location = '/task/index' }) } } else { var response = new ErrorResponse(result.responseText) response.Show() } })  Our InitializeDatatable is defined to be: // Datatables function InitializeDatatable(selector, table_height, addbutton, autoColumn = 0, customOptions = {}) { var randomButtonID = RandomID() var defaultOptions = { dom: <"${randomButtonID}-addbutton">frtip,
scrollY: table_height,
scrollCollapse: true,
paging: true,
info: false,
order: [
[autoColumn, 'asc']
],
deferRender : true
}
$.extend(defaultOptions, customOptions) var table =$(selector)
.DataTable(defaultOptions)

$(div.${randomButtonID}-addbutton)
return table
}


Note that, once employees are fetched from the database, we convert those into Employee objects, which have the following HTML representation logic:

ToHTML() {
// load in the employee template
if (employeeTemplate == null) {
employeeTemplate = FetchTemplate('employee/employee.html', "employee-template");
}
// render it with this
var $element = employeeTemplate.tmpl(this); // get the picture and attach it directly to the view element that's being rendered this.GetPicture(function (picture) {$element.find('.person-image')
.attr('src', picture.Picture)
.data(picture);
});
// attach this model data to the view element and return it.
return \$element.data("model", this);
}


NOTE: I inserted benchmark logic at all the major steps of this.

Benchmark data

I have been benchmarking each step of this process (save the other API hits (not shown, because irrelevant to this)), and compiling that data into a spreadsheet. (I cannot share that spreadsheet directly here, so I screenshot it.)

As I expected, the HTML data creation part of the process is the most expensive as it takes, on average, 17.6 seconds to complete!

I have been arguing about how to fix this: to only load a chunk of the server data in at once, and create API endpoints to serve specific part of that data. (After all, as implemented, it costs the client an average of 3.7 seconds of load time, and seems inconsistent with the use case of the page itself.)

However, I was told "the API is working just fine, and that we should find other way to speed up the page". I have tried the infinite scrolling APIs, but as of the time of writing this, the client-side scrolling API is broken, and the server-side one, for some reason, either doesn't want to show in the view, or becomes straight up unresponsive. I changed the options to the following:

In InitializeDatatables:

paging : false


In the main function on the employee page:

  // paging: true,
// pageLength: 50,
ajax : function(data, callback, settings) {
console.log(data),

setTimeout( function () {
callback( {
draw: data.draw
} );
}, 50 );
},
scroller : {

• Ya, I'm not the original author of the code. The application is quite complicated to explain on here, but is more or less a centralized dashboard for company/store owners and their managers. I was able to get it "working" by just pushing the data to table, and in rowCallback, perform the custom rendering. (It works, except for that I get error because it's expecting data to be array, and I gave it object. I define columns, and it breaks completely.) – Mike Warren Jan 22 at 14:21