Combining DataTable’s Ajax and Javascript sources

I often use DataTables as it provides a lot of out-of-the-box functionality for searching, ordering, paginating and use of Ajax data sources. However, using the server-side processing example means the HTML page will load, and then there is a short wait until DataTables fetches the data from the server using Ajax. This can make an application feel slower, since the user has to wait for the page to load, and then wait again for the data to arrive.

While DataTables can load data from a Javascript source, it seems this option is ignored if we want to use server-side processing.

We can eliminate the second Ajax call by embedding the Javascript object into the HTML that would result from the second call. The object should have exactly the same structure that would normally be returned by the server-side processing source, including the recordsFiltered and recordsTotal attributes to give correct counts:

var intialData = {
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [
    [
      "Airi",
      "Satou",
      "Accountant",
      "Tokyo",
      "28th Nov 08",
      "$162,700"
    ],
    [
      "Angelica",
      "Ramos",
      "Chief Executive Officer (CEO)",
      "London",
      "9th Oct 09",
      "$1,200,000"
    ],
...
}

We then need to use the Ajax option as a function and check if we are performing the first or a subsequent render.

$('#example').dataTable({
  processing: true,
  serverSide: true,
  ajax: function(data, callback, settings) {
    if (typeof initialData.done === 'undefined') {
      callback(initialData);
      indexData.done = true;
    } else {
      $.ajax({
          url: "../server_side/scripts/server_processing.php"
        })
        .done(function(data) {
          callback(data);
        });
    }
  }
});

Optimising index view performance

I recently came across a scenario where my index views in Rails were taking a long time to render when there were large data sets. The problem stemmed having a lot of data presented that were from models/tables other than the primary model/table of interest.

For example, an “asset” index view may have the following columns:

  • Name (from asset.name)
  • Type (from asset.type.name)
  • Site (from asset.site.name)
  • Organisation (from asset.site.organisation.name)
  • System (from asset.system.name)
  • Status (from asset.status.name)

The first (naive) approach I used was to query using eager loading:

Asset.includes(:type, {site: :organisation}, :system, :status)
     .references(:type)
     .search_by('types.name', 'Pump')
     .order(:name).page(1).per_page(1000)

Using a data set of around 500,000 items, rack-mini-profiler indicated this took about 6.7 seconds to render the action with 5.6 seconds of that consumed by the SQL query.

When investigating, I noticed this was using a single query with LEFT OUTER JOINs. I then forced ActiveRecord to use separate queries to load the related models by using Rails 4’s preload instead of includes and used joins instead of references to force ActiveRecord to use a more performant INNER JOIN:

Asset.preload(:type, {site: :organisation}, :system, :status)
     .joins(:type)
     .search_by('types.name', 'Pump')
     .order(:name).page(1).per_page(1000)

This dropped the action render time to 1.7 seconds with 0.7 seconds consumed by the six SQL queries.

For my index views, I actually don’t need the entire object graph to be hydrated – I only really needed the names of the related object. So I tried using pluck, which returns an 2-dimensional array of values:

Asset.includes(:type, {site: :organisation}, :system, :status)
     .joins(:type).search_by('types.name', 'Pump')
     .order(:name).page(1).per_page(1000)
     .pluck(:name, 'types.name', 'sites.name', 'organisations.name', 'systems.name', 'statuses.name')

This actually increased the action render time to 2.1 seconds with 1.7 seconds consumed by ActiveRecord and the SQL query. The SQL query now uses LEFT OUTER JOINs again, which as we saw in the original case, is slow. But the differential between the total and SQL had decreased since ActiveRecord models did not have to be created.

While using the second approach would provide a significant speed boost without changing any code, I wondered if we could use a combination of the second and third approaches – use separate SQL queries instead of a single with LEFT OUTER JOINs and simply pass back an array and not have to hydrate ActiveRecord models.

The result: Preload Pluck.

On the same data set, Preload Pluck drops the action render time to 1.5 second with 0.7 seconds consumed by the SQL queries.

YMMV as the results above are for that particular data set, the queries I’ve used and the way my PostgreSQL database is setup.