Paging...

brianmm

Hi Allan...

Just wondering if I would have 50,000 -plus rows, how is the performance of datatables?

Is it possible to page by retrieving the data as you go i.e. ultimatley a query using Limit... Just thinking for the future (you knwo what I'm working on!) so if it's got 10's of touhsands of items, loading the page could be slow...?

allan

Hi Brian,

It depends how you are using DataTables - if you have everything done on the client-side (i.e. all information read from HTML) then it's going to be borderline shocking (you might just get away with 50'000, but not much more). If however you are using server-side processing, then it's not going to be an issue, even millions of rows will be fine then because all of the data processing will be done by a database engine designed for that kind of thing. So if you are planning for lots of rows, I'd encourage the use of server-side processing. You can even do things such as pipelining to reduce the number of Ajax calls.

http://datatables.net/1.5-beta/examples/data_sources/server_side.html
http://datatables.net/1.5-beta/examples/server_side/pipeline.html

Allan

brianmm

the pipeline code (about to try) it does'nt look like it needs any customisation is that correct?

I do however have:

"fnServerData": function ( sSource, aoData, fnCallback ) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
},

as we had to do POST not GET :)

so do i keep this and change "success": to the fnDataTablesPipeline function?

brianmm

OK cool all done... works a treat!

Only one comment, in your code ( http://datatables.net/1.5-beta/examples/server_side/pipeline.html ) you have:

$('#exampvar oCache = {
iCacheLower: -1
};

but needed to change it to (as you have in page source):

var oCache = {
iCacheLower: -1
};

brianmm

except :)

Need to set this lot somewhere again? where?

"fnServerData": function ( sSource, aoData, fnCallback ) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnDataTablesPipeline
} );
},

allan

Hi Brian,

Thanks for spotting the typo in the example - fixed now.

I'm not sure I quite understand your last comment though - what lot do you need to set where?

Allan

brianmm

sorted... just changed the above to:

"fnServerData": function ( sSource, aoData, fnDataTablesPipeline) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnDataTablesPipeline
} );
},

and works great.. might be handy to point out incase others need to use post and not get?

allan

Oh I see! Does this really work?! A little bit surprised because don't you now have two Ajax requests for each table draw - the $.ajax() POST one you've got assigned to fnServerData and the one inside fnServerData...

I would have thought it would have been easier to just modify the fnDataTablesPipeline() function to use POST rather than GET?

Allan

brianmm

yes it is working fine... pages thru 5 pages.. then on 6th a slight pause (while it fetches) and plods on :)

if your other way is better though what should I change... (sorry being dumb again!).

allan

If you look at your XHRs in Firebug, does it fire off two Ajax calls on the first get?

I'd recommend basically just using the same as in the example: http://datatables.net/1.5-beta/examples/server_side/pipeline.html but changing the line "$.getJSON( sSource, aoData, function (json) {" in fnDataTablesPipeline() to be your $.ajax() call with POST. Just wrap the return up in an anonymous function (a bit like the POST example).

Allan

brianmm

it appears to be only one call....

allan

If it ain't broken... :-)

brianmm

typicall :)

What I done works in all but IE!!!

Can you spare 5 mins to help me do the change you suggested?

allan

hehe - see that IE...

Okay so what I would suggest is basically implementing the same as I've got in my pipelining example, but change the $.getJSON(...) call to be $.ajax(...) and give it a POST. That should then work in IE (well... saying that without knowing the original problem in IE but also knowing IE is like falling on one's own sword...).

Allan

brianmm

hmmm weird it does seem to work... but returns 'no mathcing rows', however the queries it creates are good, and the JSON seems fine (as it is allw orking in FF/OPERA)...

brianmm

OK so make it...

$.ajax( sSource, aoData, function (json) {
/* Callback processing */
oCache.lastJson = jQuery.extend(true, {}, json);

if ( oCache.iCacheLower != oCache.iDisplayStart ) {
json.aaData.splice( 0, oCache.iDisplayStart-oCache.iCacheLower );
}
json.aaData.splice( oCache.iDisplayLength, json.aaData.length );

fnCallback(json)
} );

not sure what you mean there by give it a post...

brianmm

for god sake!

I hate IE :) Right... the problem was (a stupid one right enough....) in my JSON i had a trailing , at the end of the row...

Weird tho FF/Opera did'nt care about it... but IE does!

allan

Ah yes the old trailing comma... It's not actually valid JSON to have that, so I would argue that the IE behaviour is actually correct here (not often I would say that). jsonlint.com is a superb site for seeing if you json is valid or not and can help catch this kind of error.

What I was meaning with the $.ajax change was to use something like this:

$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );

rather than the getJSON helper function (note you'll probably need to modify that - I've just coped it form my POST example).

Allan

brianmm

OK well all seems well now :) - again!