Orthogonal data - performance and browser memory for 1000s of rows

PaulMoment

Hey all - really liking the flexibility of Datatables for our SaaS product. However, I'm running into performance issues when adding 1000s of rows via Ajax into the table object.

I unfortunately can't set up a live example, but here's a walk through my process:

1) Most of our users have less than 2000 records, but some have as many as 30,000. I retrieve a multi-dimensional array of 1000 records at a time via Ajax from our server cache. (Multi-dimensional array saves a lot of fat on the wire by trimming out repetitive keyed maps - much faster). Array looks like:

[[
    [useridid,fullname,firstname,lastname],
    [contactid,fullname,firstname,lastname],
    [sourceid,sourcelabel],
    [etc...]
],
[
    [useridid,fullname,firstname,lastname],
    [contactid,fullname,firstname,lastname],
    [sourceid,sourcelabel],
    [etc...]
]]

2) I'm transforming that multi-dimensional array on the client side into an array of key/value maps, like:

$varMappedData = [{
    user: { id:arr[0][0], fullname: arr[0][1], firstname: arr[0][2], lastname: arr[0][3] },
    contact: { id:arr[1][0], fullname: arr[1][1], firstname: arr[1][2], lastname: arr[1][3] },
    source: { id:arr[2][0], label: arr[2][1] },
    etc: { ... }
},
{
    user: { id:arr[0][0], fullname: arr[0][1], firstname: arr[0][2], lastname: arr[0][3] },
    contact: { id:arr[1][0], fullname: arr[1][1], firstname: arr[1][2], lastname: arr[1][3] },
    source: { id:arr[2][0], label: arr[2][1] },
    etc: { ... }
}];

3) If table is not created, it is instantiated for the first 1000 rows with this configuration:

var dataTableOptions = {
    data: $varMappedData, //first 1000 records
    searching: true,
    columns: $varCustomColumnDefinitions,
    order: [[$varSortIndex,$varSortDirection]],
    lengthMenu: [ 5, 10, 25, 50, 75, 100, 125, 150, 175, 200 ],
    pageLength: $varPageLength,
    destroy: true,
    autoWidth: false,
    bAutoWidth: false,  
    stateSave: false,
    deferRender: true,
    pagingType: 'full_numbers',
    info: true
};

I'm using the orthogonal data render method for each row with data-specific output, like this:

$varCustomColumnDefinitions = [
{
    data: 'user',
    type: 'string',
    render: function(data,type,row) {
        if (type === 'display') {
            return '<a href="#contactlink" data-id="'+data.id+'">'+$.trim(data.firstname + ' ' + data.lastname)+'</a>';
        }
        return data.fullname;
    },
    orderable: true,
    sortable: true,
    export: true
},
{
    // another column definition
}];

4) The data map is inserted via the rows() API call, paged to a locally saved length, and redrawn so that the record counts and paging links are updated. The $tableObj was instantiated with "deferRender: true".

$tableObj.rows.add( $varMappedData ).page( $varPageLength ).draw( false );

The key-mapped data is also being saved locally for use as a cache. I'm re-rendering the table based on data columns that the users can select themselves, so I'm keeping the full data cache available to quickly redraw the table with new columns based on their selections without having to go back to the server. We have between 30-50 columns possible based on user choice, but most users only display 15-20 columns at at time. Local memory cache size is probably between 5-15Mb depending on the user.

FIRST ISSUE: There is a noticeable DOM freeze as the 1000 new records are inserted and redrawn.

SECOND ISSUE: Performance wise, the browser starts to feel sluggish and memory is impacted when we start to get above 5000 records added to the Datatables object. We've had to temporarily limit the local cache to 10K records and it doesn't feel peppy.

THIRD ISSUE: Even with "deferRender: true", redrawing the table feels so sluggish that we can't do live search filtering. We had to use a custom search filter field that only redraws on manual submit.

QUESTIONS: Am I doing something that's horribly un-optimal here or am I using the API correctly for inserting data? This is my first time using the orthogonal data approach. Is there a performance hit when using the more dynamic orthogonal render method? We have another page that creates a 15K+ row HTML table in the DOM (10 columns), gets Datatable'd, and it feels really fast and performant. Should I just render to the DOM instead of using the .render() method (which I greatly prefer)?

Hoping to keep using the super-flexible orthogonal approach, but have to solve this performance issue.

Thx in advance for your help!

allan

I don't immediately see anything that stands out as going to seriously impact performance. Obviously the more records you have the harder it is on the client - tens of thousands of rows will be the point that you certainly start to notice slow downs, but it sounds surprisingly sluggish.

I'm sorry to say I'd really need a link to a page showing the issue to understand what the problem is. Aside from that, all I can suggest is that you run the performance tools in Chrome to see where the time is going.

Allan

p.s.

export: true

Genius!

PaulMoment

Thanks for the response, Allan!

In the interim, I did a test where I rendered the 5500 records as a flat DOM table, and then ran the plugin on it. There is still some sluggishness, but the table is much faster. I can even do live filtering in the search field which was very slow in the previous version. So it feels like the flat DOM table is more performant at this point.

I'll see if I can get you a live link for a look-see.

allan

That would be odd if it were! The DOM render has additional steps - the page needs to be fully downloaded and rendered, then DataTables needs to read the data back from the DOM, which is always slow (relatively speaking).

I'll see if I can get you a live link for a look-see.

That'd be awesome - thanks. You can send me a PM by clicking my name above and then Send message.

Regards,
Allan