Help with performance for 1Hz table refreshing

mtmacdonald

I'm developing a small web app to list the values of signals on an embedded controller. Here's the spec:

- the table can be moderately large (e.g. 4000 rows, 7 columns)
- a single column of the data (all rows) needs to be refreshed once per second (i.e. the signal values)
- data format is JSON (array of objects), and I'm currently loading the whole table and processing client-side
- preference is to use the scroller plugin over pagination but this isn't mandatory

Everything works nicely apart from the performance of refreshing the values (particularly on IE8). I have two questions:

1. how can I improve the 1Hz refresh performance? Any other design methods I can try?
2. how do I get the datatable to remember the pagination state after refreshing (ideally noting preference for scroller plugin)?

So far I've tried several methods.

Method 1 - recreate the entire table once per second (too slow to cope with 1Hz refresh)

[code]
var dataTable = $('#example').dataTable(dataTableOptions);
[/code]

Method 2 - use fnUpdate on every row once per second (much too slow to cope with 1Hz refresh)

[code]
$.get('./table.json', "getValues=true", function(result){
for(i = 0; i < result.aaData.length ; ++i)
{
dataTable.fnUpdate( result.aaData[i].Value, i, 4 );
}
}, "json");
}
[/code]

Method 3 - use the fnReloadAjax plugin once per second (closest so far but still CPU heavy, and barely useable in IE8)

[code]
dataTable.fnReloadAjax();
[/code]

Method 4 - attempt to refresh only visible rows once per second (so far not better than method 3)

[code]
var oSettings = dataTable.fnSettings();
for (var i = oSettings._iDisplayStart; i < oSettings._iDisplayEnd; ++i)
{
//either method 4.1 (just too slow):
dataTable.fnUpdate( result.aaData[i].Value, i, 4 );

//or method 4.2 (there's no fast method like fnClearTable() to remove blocks of rows? fnDeleteRow() is slow)
dataTable.fnDeleteRow(i);
dataTable.fnAddData(result.aaData[i]);
}
[/code]

My remaining ideas are:

- find other ways to refresh the values only for rows that are visible to the user
- cache the changes on the server and refresh only those rows which have changed (but I can't guarantee many rows won't change)
- switch to server side processing (but then I lose many of the gains of use Datatables, plus my server is an embedded controller)
- are there others?

Many thanks!

kktos

Hi,

not a direct answer to your question nevertheless I noticed quite a difference of speed between the quirks mode and the normal mode of IE.
Some info here : http://msdn.microsoft.com/en-us/library/cc288325(v=vs.85).aspx

I'm using the meta:

when I'm displaying dataTables. it makes quite a difference.

HTH,
/tm

mtmacdonald

Thanks kktos. I do already use this, having spotted that IE ignores standards mode for pages served from localhost without it.

mtmacdonald

Thanks kktos. I do already use this, having spotted that IE ignores standards mode for pages served from localhost without it.

allan

Hi mtmacdonald,

Excellent question this, thanks for posing it so clearly. It's an important topic as well as rapid updates are very useful for a number of different scenarios, although I think this is the first use case I've seen for hardware state display. Very cool :-)

So regarding your question, I think your method 2 is very close, but I would suggest a couple of things. Firstly make use of the fourth parameter for fnUpdate, which disables the table for redrawing on every call - that's really expensive and this little change should help a lot:

[code]
$.get('./table.json', "getValues=true", function(result){
for(i = 0; i < result.aaData.length ; ++i)
{
dataTable.fnUpdate( result.aaData[i].Value, i, 4, false );
}
dataTable.fnDraw();
}, "json");
}
[/code]

Another thing that will really help, if you haven't got it enabled already, is derferred rendering, particularly if you are using an Ajax source and Scroller: http://datatables.net/ref#bDeferRender .

Hopefully with these two things the speed increase will prove to be enough for the performance you need. I'll detail a few other options which are a little more complex if you like to make it even faster (mainly removing the TR find loop from DataTables).

Regards,
Allan

mtmacdonald

Hi Allan,

Thank you for such a helpful answer. So far I have:

- used your solution but with fnDraw(false), in order to preserve the scroller position
- checked bDeferRender (it was already enabled)
- disabled bSort and bSortClasses (I only need filtering)

This is great progress and close to usable. Testing with 2500 rows I get:

- excellent results for Google Chrome (responsive UI, short 15% CPU spikes)
- nearly passable results on IE8 (sluggish UI, CPU sits between 40-50%)

If further improvements are possible (either as a patch or a config option in future datatables versions) I would be most interested.

allan

Great to hear that makes it almost usable in IE. one of the performance improvements that can normally be done, you've actually already done since you are using a loop over aaData in your json return and giving fnUpdate an index for each row, this allows DataTables to do a very fast lookup.

As such I think the next step will be for me to write a modification to fnUpdate which will cache the DOM elements for the cells you want to update, thus saving a DOM lookup on every call to fnUpdate, which will also be a real hit in IE. I'll post this tomorrow evening if that's okay :-). I'll also look to see if any other improvements can be made (I'm wondering if the building for the searh array can be optimised a bit for example).

Regards,
Allan

allan

I've just realised that I missed one obvious option for speeding things up a bit - sorry about that! There is actually a 5th parameter for fnUpdate which will make a real difference as well - like the redraw option, this one allows or stops the update to recalculate the column sizes. Doing this on each one of your updates so going to be a horrible performance hit - so try passing in 'false' as a fifth parameter and I suspect that will help a lot. I'm also going to have a look just now at creating a version of fnUpdate for your specific use case which should speed things up a bit more :-)

Regards,
Allan

allan

Here we go. Basically this is a modification of fnUpdate which is built into DataTables, designed to be a bit faster in your case here - specifically I've removed the stuff you don't need or want in the function and it caches the TD nodes for each row, so you only get that hit on the first update - not the subsequent ones.

[code]
$.fn.dataTableExt.oApi.fnFastUpdate = function( oSettings, sDisplay, iRow, iColumn )
{
var iVisibleColumn, i, iLen, sDisplay;
var oApi = oSettings.oApi;

/* Update the cell in the internal cache */
oApi._fnSetCellData( oSettings, iRow, iColumn, sDisplay );

/* Render if needed */
if ( oSettings.aoColumns[iColumn].fnRender !== null ) {
sDisplay = oSettings.aoColumns[iColumn].fnRender( {
"iDataRow": iRow,
"iDataColumn": iColumn,
"aData": oSettings.aoData[iRow]._aData,
"oSettings": oSettings
} );

if ( oSettings.aoColumns[iColumn].bUseRendered ) {
oApi._fnSetCellData( oSettings, iRow, iColumn, sDisplay );
}
}

/* Do the actual HTML update */
if ( oSettings.aoData[iRow].nTr !== null ) {
if ( typeof oSettings.aoData[iRow].__tds == 'undefined' ) {
oSettings.aoData[iRow].__tds = oApi._fnGetTdNodes( oSettings, iRow );
}
oSettings.aoData[iRow].__tds[iColumn].innerHTML = sDisplay;
}

/* Modify the search index for this row */
var iDisplayIndex = $.inArray( iRow, oSettings.aiDisplay );
oSettings.asDataSearch[iDisplayIndex] = oApi._fnBuildSearchRow( oSettings,
oApi._fnGetRowData( oSettings, iRow, 'filter' ) );
};
[/code]

And this is an example of how it might be used:

[code]
$(document).ready(function() {
var otable = $('#example').dataTable();
otable.fnFastUpdate( "hello!", 0, 0 );
} );
[/code]

Let me know how you get on with it :-)

Regards,
Allan

mtmacdonald

Thank you very much for these suggestions. I have now tried both.

Unfortunately, this time neither seems to improve the performance (maybe a 2% difference when I use the IE8 profiler, but it's not perceptible).

So I spent some time with the profiler, and also commenting out code blocks. It seems that fnDraw (and perhaps _fnScrollDraw too?) might in fact be the bigger bottleneck (even at only one call per second).

My current test case has 600 rows. Below is the CPU situation when updating the table once per second, with different parts of the update function commented out (I'm using the method from your first suggestion on 25th Oct). Particularly when adding fnDraw, the interface becomes rather unresponsive:

Code block-------------------------CPU idles at

AJAX + FOR LOOP ONLY----------------------3%
WITH FNUPDATE / FNFASTUPDATE ALSO-----19-22%
WITH FNDRAW ALSO------------------------50%
WITH FNDRAW ONLY------------------------50%

Do you think there is anything else we could try? Would it help if I code and upload a test case?

allan

Its good that we've got it down as much as possible - but certainly fnDraw is a little bit of a killer since it does a full resort and refilter. You could try fnDraw( false ) and see how that performs (that does not do a resort or refilter - so that might not be acceptable, but it should help with performance).

Failing that, yes a test case would be extremely useful, but I wonder if we might be hitting a performance boundary now.

Thanks,
Allan

mtmacdonald

I've created a test case that's one HTML file with the JavaScript embedded. It's basically the same as my real code, but I removed the AJAX and JSON to keep things simple (not needed to illustrate the problem).

The file can be downloaded here (let me know if there's a better place to post this):
http://www.box.net/shared/p13eu5xribrnpeoqu8c4

Any further suggestions are much appreciated, and if we've got as far as we can, thanks again for all the help.

GregP

Am I understanding correctly that you are updating 600 rows once per second (and as per original post, up to 4000)? Is it out of line for me to mention that the best optimizations for you will be moving to server-side processing? Then only 1 page of results will be processed in the DOM at a time.

Since you're monitoring a controller and not accessing an SQL database, it might be an awful suggestion; maybe the controller would be even slower...?

allan

@Greg: Server-side processing is not a bad idea, but I would imagine it would mean building another layer in between the two? Also since deferred rendering is enabled, a lot of the advantage of server-side processing is actually in there already :-)

@mtmacdonald - I think your updateDataTable method could be optimised in two areas:

1. Its calling fnGetData() on every loop, since its re-evaluating that every time.
2. Try passing parameter five as false to fnUpdate

[code]
function updateDataTable(){
var rows = dataTable.fnGetData().length;
for(i = 0; i < rows ; ++i)
{
dataTable.fnUpdate( "Sample Data", i, 3, false, false );
}
dataTable.fnDraw(false);
}
[/code]

Does that help much? :-)

Allan

mtmacdonald

Thank you for these replies.

Greg: server side processing would resolve the browser performance problems (with the added bonus of possibly reducing data transfer rates). It would also mean I'd have to implement the filtering and pagination / scroll handling myself (not needing to do this was a big reason for wanting to use DataTables). Seems like there are tradeoffs that I'll have to look into at a later date (a performance hit just in IE8 would seem preferable to shifting more processing load onto the embedded controller, but I'd have to examine the design).

Allan: I had already tried these suggestions. I've appreciated your help - it seems we've taken this as far as we can.

For now I'm tolerating the IE8 performance issues and sticking with client-side processing. I'll come back to server-side processing, or possibly alternative designs, if I have time in future.

GregP

@allan: well, except for retrieving and sorting up to 4000 records per second. ;-) You're mainly right, though; until you forced me to look at the Deferred Rendering option again I hadn't totally grokked it. I get it now and I can see how it's a huge speed boost.

wazza

Allan, thanks for the solution with fnFastUpdate, I also need 1Hz refreshing. In my case it worked faster than fnUpdate with both false bool parameters

Why don't you just put this function as a plugin on your website - someone else might also find it very useful

allan

@wazza: Do you mean my updateDataTable function above? Yes I could wrap that up into an API plug-in - I will look at doing so. If anyone is interested in creating API plug-ins, it is actually really easy to do: http://datatables.net/blog/Creating_feature_plug-ins ("API method" section).

Allan