Performance - How to pass all rows to other function on large table

mattnthat

Ive got a large table that im looking to improve performance on - Right now its at 13,000 rows but could potentially grow to 200,000 rows.

I have 2 major performance issues at present:

1) I need to be able to update a database field for all rows selected

Im currently using:
[code]var sData = oTable.$('input').serialize();[/code]

To gather all rows in table (filtered) and i then pass sData via AJAX call to a PHP page that does the DB update.

The above line takes 5-10 seconds to complete for 13K rows. Any other options to gather all rows?


2) Checkall checkboxes

I currently have a checkbox column as my first column. My header has a "check all" checkbox above that column.

Im currently using the following code but it takes about 15 seconds to respond for 13K rows

[code]
$('input', oTable.$('tr', {"filter": "applied"} )).prop('checked',this.checked);

oTable.fnDraw();[/code]

I was thinking about using fnUpdate to see if its faster but struggling with JQuery syntax (not my native language!)

[code]
for(i = 0; i < $dataTable.aaData.length ; ++i)
{
dataTable.fnUpdate( $dataTable.aaData[i].Value='checked', i, 0, false, false );
}
dataTable.fnDraw();
[/code]

Any help would be much appreciated. Other option i can see is to move from AJAX source to full server side processing but this would again be a rewrite to try to get filtering, updates and tracking state of checkboxes across multiple pages working (Unless there is a better way to manage checkboxes etc?)

Matt

allan

How long do these things take on your 13k table without DataTables? DataTables will certainly add a performance hit, but it would be interesting to know the baseline for what we are working with.

Allan

mattnthat

Hey Allan,

Thanks for quick response!

Thats a very good question. I might be a little dense but im using AJAX source, how can i baseline without datatables?
Recreating everything with DOM is possible but would be some work!

Ive been doing some measuring eg:

Tag code:
[code]
var d = new Date();
var n = d.getTime();

var sData = oTable.$('input').serialize();

var d2 = new Date();
var n2 = d2.getTime();
var t = ((n2 - n)/1000);
console.log("Time after tag: " + t);
[/code]
Output - Time after tag: 41.322
41seconds!

Check all code:
[code]
var d = new Date();
var n = d.getTime();

$('input', oTable.$('tr', {"filter": "applied"} )).prop('checked',this.checked);

var d2 = new Date();
var n2 = d2.getTime();
var t = ((n2 - n)/1000);
console.log("Time after check all: " + t);

oTable.fnDraw();
[/code]
Time after check all: 42.575
42 seconds!!

My data in each row consists of checkbox, id number, 2 x urls, date, 2 x text columns (~10chars)


Matt

mattnthat

Hey Allan,

I just realised you could have meant "How long does Check All take without the oTable element inside"

Ive commented out the otable section like this:

[code]
var d = new Date();
var n = d.getTime();

$('input').prop('checked',this.checked);
//, oTable.$('tr', {"filter": "applied"} )

var d2 = new Date();
var n2 = d2.getTime();
var t = ((n2 - n)/1000);
console.log("Time after check all: " + t);

[/code]

But unfortunately my check all checkbox now only selects the first pages results (Subsequent pages are not checked).

Im not using deffered processing either.. not sure why JQuery cant see all DOM elements.

My setup snip for reference:

[code]
oTable = $('#overview_results').dataTable( {

"bProcessing": true,
"sAjaxSource": 'searchoverview_ajaxdata.php?runid='+qs('runid'),
"sPaginationType": "full_numbers",
"aoColumnDefs": [
{"aTargets": [ 0 ],
"mRender": function ( data, type, full ) {
return '';
}},
[/code]


Thanks for your help so far,
Matt

allan

If you call fnDestroy on the table it will create a plain HTML table based on the data available in the table. So that would probably be the easiest way to profile it without DataTables - use DataTables and then destroy it :-)

Allan

mattnthat

Nice one. Ive added fnDestroy before i make the call:

[code]
oTable.fnDestroy();

var d = new Date();
var n = d.getTime();

$('input').prop('checked',this.checked);
//, oTable.$('tr', {"filter": "applied"} )

var d2 = new Date();
var n2 = d2.getTime();
var t = ((n2 - n)/1000);
console.log("Time after check all: " + t);
[/code]

Results in: Time after check all: 10.461

Just to double check my results again, I commented out the destroy and re added the data tables prop change:
Time after check all: 23.885
Time after check all: 23.487
Time after check all: 24.657

Looks like its taking less time than original report but still data tables is adding 13-14seconds.

I guess there are 2 part here:
1) How far can JQuery scale dealing with large numbers of elements (or is there a better approach)
The 10 seconds without data tables

2) Can we tweak datatables to cut into the 13-14 seconds of datatables time


thanks,
Matt

mattnthat

Hey Allan,

The more i think about it.. this feels like a case for server side processing.

Its struggling at 13K rows, Im going to be heading towards 200K rows..

Ill see how i go with checkall functions and filters on server side. No doubt much fun to come there.

Matt