Paging...

Paging...

brianmmbrianmm Posts: 41Questions: 0Answers: 0
edited May 2009 in General
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...?

Replies

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    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?
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    edited May 2009
    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
    };
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    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
    } );
    },
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    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?
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    edited May 2009
    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
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    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!).
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    it appears to be only one call....
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    If it ain't broken... :-)
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    typicall :)

    What I done works in all but IE!!!

    Can you spare 5 mins to help me do the change you suggested?
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    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)...
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    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...
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    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!
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • brianmmbrianmm Posts: 41Questions: 0Answers: 0
    OK well all seems well now :) - again!
This discussion has been closed.