DataTable takes too much time

alex_envbulle · September 2015

Hello,

I'm using DataTables to display a table with 2000-6000 rows.
My problem is that it takes too much time (>30seconds).

Here you can see my JS code :

var mytable = $('#mytable').dataTable( {
        "bSortClasses":false,
        "pageLength": 50,
        "order": [[ 3, "desc" ]],
        "columns": [
        {"orderable":false},
        {"orderable":false},
        {"orderable":false},
        null,
        {"orderable":false},
        null,
        {"orderable":false},
        {"orderable":false},
        {"orderable":false},
        {"orderable":false},
        {"orderable":false}
        ],
    });

I retrieves data from a database and I treat them in PHP.
To include the data into DataTable, i'm doing like this (a simple example) :

print "<table id=\"mytable\" class=\"display\" align=center cellpadding=3 cellspacing=1>";
    print "<thead>";
        print "<th>MP</th>";
        print "<th>Date</th>";
    print "</thead>";
    print "<tbody>";
        print "<tr><td>Hello</td><td>hello2</td></tr>";
    print "</tbody>";
print "</table>";

So, my question is : is there an easier way to do this job and which takes a shorter time ?

I saw in another post that it is possible that the table is redrawn with each new line. how to see if this is the case ?

Unfortunately, I can't give you any website to show you how it works because it's a local website.
I do not know if I'm quite explicit, so ask me if you want more details.

Thanks for you help,
AL

allan · September 2015

I would recommend reading over this FAQ in the first instance.

I think the biggest benefit you'll see if from moving to DOM loaded tables to Ajax with defer render enabled. Creating all 6000 rows up front and then having DataTables read that data from the DOM is a really slow process. With Ajax loading it can create only the rows needed for the first display and then when required by user interaction, and it doesn't need to read from the DOM.

Allan

alex_envbulle · October 2015

Thank you very much Allan, I'll try with an Ajax call.

EDIT: I'm sorry but I don't understand how I can use that. Could you give me an example of the uses of Ajax in this case please ?
How could I get back the data ?
For the moment I did this :
JS:

var mytable = $('#mytable').dataTable( {
        "pageLength": 50,
        "order": [[ 3, "desc" ]],
        "columns": [
        {"orderable":false},
        {"orderable":false},
        {"orderable":false},
        null,
        {"orderable":false},
        null,
        {"orderable":false},
        {"orderable":false},
        {"orderable":false},
        {"orderable":false},
        {"orderable":false}
        ],
        "deferRender": true,
        "ajax": {
            "url": "recup_cmd.php",
            "type": "GET",
            "dataType": "html",
            "dataSrc":"data"
        }
    });

PHP:

print "<table id=\"mytable\" class=\"display\" align=center cellpadding=3 cellspacing=1>";
    print "<thead>";
    print "<tr align=center><th width=5px></th>";
    print "<th width=40px></th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:50px;\">MP</th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:30px;\">Date</th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:140px;font-size:12px;\">Commande / Nom</th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:90px;\">Statut</th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:40px;\">Total</th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:30px;\">Frais de port</th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:130px;\">Transporteur</th>";
    print "<th style=\"border-right:1px solid #8D8D8D;width:250px;\">Détails</th>";
    print "<th style=\"width:100px;\">Action</th>";
    print "</tr></thead>";
    print "<tbody>";



print "</FORM></tbody></table></div>";

allan · October 2015

Hi,

There are lots of Ajax examples available on this site.

Allan

alex_envbulle · October 2015

Thanks.

I have looked these examples, but they only use simple values in JSON.
In my case, I want to give HTML like :

print "<tr id=\"".$id."_gen\" $op><td><div class=squaredThree style=\"position:relative;top:-2px;\"><input type=checkbox id=\"checkbox_".$id."\" name=\"commandes[]\" value=\"order".$id."\" autocomplete=\"off\"><label for=\"checkbox_".$id."\"></div></td>";

Is it possible ?
Per example, if I do :

"data": [
    {
      "name": "<tr id=\"".$id."_gen\" $op><td><div class=squaredThree style=\"position:relative;top:-2px;\"><input type=checkbox id=\"checkbox_".$id."\" name=\"commandes[]\" value=\"order".$id."\" autocomplete=\"off\"><label for=\"checkbox_".$id."\"></div></td>",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Edinburgh",
      "extn": "5421"
    }
]

allan · October 2015

You can't give a tr row, since the enter object describes the data for the row (i.e. DataTables will create the tr for you. Likewise the td cells - but you can put whatever HTML you want inside the cells - such as input elements.

Allan

alex_envbulle · October 2015

Aww, so I can't give an id as attribute to each tr and td ?

Because I need to give them an id

allan · October 2015

Yes you can - DataTables will automatically use the value from the DT_RowId property (if one is defined) as the row id. Alternatively use createdRow to assign it manually.

If you need to also set cell elements with an ID you can use the columns.createdCell option.

Allan

alex_envbulle · October 2015

Oh my god !

This seems really too complicated for me.
I do not see how to combine all of what you have told me before.

I do not use datatable to make a simple table, I use it to make a complete order management system with many features.

Thank you for you help Allan, but I'll give up for now and try again maybe later.

allan · October 2015

Understood. Switching from DOM to Ajax is about the biggest change you can make in DataTables!

Allan

alex_envbulle · October 2015

I think that would solve the problem of slow, but switching from DOM to Ajax takes a long time and I don't have it at the moment :(

However, thank you for your help Allan, I'll apply your tricks later !

alex_envbulle · October 2015

I had a little time to try your advice Allan.

First, I managed to pass HTML in JSON. But at some point I'd like to execute javascript code in the middle of PHP. I guess that's not possible?

Then, I use such QTIP (http://qtip2.com/demos) or Fancybox (http://fancyapps.com/fancybox/) that no longer work. There is a trick to make them work?

EDIT: I have solved the problem for QTIP and Fancybox with this post : http://datatables.net/forums/discussion/2879/datatables-and-fancybox

jLinux · October 2015

I also thought switching from DOM to AJAX would take too long, and it took me less than a day really.. and id bet my DT instance is way more advanced than yours... lol.

I heavily tie the jQuery x-editable plugin into my DataTables instance, so since they both rely on Ajax calls, it gets rather complicated..

Heres the HTML/PHP page that constructed the table used by DataTables back when it was DOM Sourced: http://code.linuxdigest.org/view/ad5e9499 (Ugly, messy, dirty.. ewww)

Then heres the same file, just revised since its now AJAX Sourced: http://code.linuxdigest.org/view/d3eadcc3 (Sweet, simple, sleek!..)

All that PHP that was executed when I had the DataTables instance using the DOM got converted to jQuery code, executed via DataTables options/events (initComplete, createdRow, etc): http://code.linuxdigest.org/view/baf35b5d#L299 (The DataTables magic starts around line 299)

So it did take a bit of work, but its so much better! The table now can be viewed live, so any updates made by someone else will be reflected to whoevers viewing the same content, and its a lot faster too...

DOM Sourced: http://d.pr/i/1cmCS
AJAX Sourced: http://d.pr/i/1eCJe
Both have the same amount of data being pulled.

So... switching from DOM to AJAX might take a bit, but its well worth it...

alex_envbulle · October 2015

Thank you jLinux, I now have proof that AJAX Sourced is much faster than the DOM Sourced (I really had no doubt)
However I think there is a difference between you and me: experience.
Indeed, I'm still a junior, so all this is still "new" to me and it is sometimes difficult to understand all this.

But thank you for your very interesting speech.

You said : " The table now can be viewed live, so any updates made by someone else will be reflected to whoevers viewing the same content"

This means that if someone on a computer makes a change, another guy on another computer will see the change without refreshing the page?

EDIT: Fancybox no longer working. It appears under the viewport and when I scroll I can see the top of it, but he puts on again under the viewport. Wtf ? Do you know why ?

jLinux · October 2015

This means that if someone on a computer makes a change, another guy on another computer will see the change without refreshing the page?

In my code? Yes. This doesn't mean it will work by default if you just switch to ajax.

I can explain how I do it tho, and you can use my code if you want. The content of my DT is basically just the values in a MySQL table, which has two columns containing an epoch timestamp, created and modified. So I basically just check through the data for those values, if any of those are greater than the last time xhr was fired, then I ajax.reload the table....

Heres the relevant parts of the code that I use to reload the table when new data is found, follow along :) ....

var last_reload = null;

$('#data-table')
    // For every AJAX request (including the first one), cache the time of the query
    .on( 'xhr.dt', function ( e, settings, json, xhr )  {
        // Update the last_reload timestamp, used primarily by monitor_updates()
        last_reload = Math.floor(Date.now() / 1000);
    } ).DataTable( {
    ajax: {
        url: "/REST/partition/asset_list_dt/format/json",
        dataSrc: "assets"
    },
    columns: columns,
    deferRender: true,
    initComplete: function( settings, json ) {
        monitor_updates(this);
    }
} );

As you can see, before DataTables is even initialized, I attach an event to the xhr, which will update the last_reload value to the current timestamp every time DataTables reloads the table.

Then, when I initialize DataTables, I take advantage of the initComplete, which will execute the monitor_updates() function, and hands the DataTables instance to it via this.

Heres the value of the monitor_updates() function:

function monitor_updates($dt){
    var pause = false;

    // Put any code here to pause or un-pause the updates, if need be.
    
    // Make sure DataTables was handed over
    if( ! $.fn.DataTable.isDataTable( $dt ))
        throw new Error('Method assets.list() -> monitor_updates() was called without providing a DataTables instance');

    var $api = $dt.api();

    // Make sure its ajax
    if( ! $api.init().ajax)
        throw new Error('Method assets.list() -> monitor_updates() was called on a DT instance that does not use AJAX');
    
    var ajax = false;
    var i = 0;
    // Check for updates ever N seconds
    setInterval(function(){
        i++;
        if( ! ajax && ! $pause) {
            //console.debug(i+') Executing - No ajax is running');
            ajax = true;
            $.ajax( {
                type: "GET",
                dataType: 'json',
                url: $api.ajax.url(),
                success: function ( response ) {
                    // Get the proper data source
                    var data_src = ($api.init().ajax.dataSrc
                        ? response[ $api.init().ajax.dataSrc ]
                        : response);

                    // Reload table if any changes were found
                    _.find( data_src, function ( asset ) {
                        return ( last_reload && (asset.created > last_reload || asset.modified > last_reload));
                    } ) && $api.ajax.reload( null, false );
                },
                error: function ( xhr, ajaxOptions, thrownError ) {
                    throw new Error( 'Error: ' + thrownError );
                }
            } ).done( function () {
                //console.debug(i+') Setting ajax var to false');
                ajax = false;
            } );
        }
    }, 5000);
}

I tried to add as detailed comments as I could. but it basically looks at the DT config settings used when DT was initialized, and executes an AJAX request every 5 seconds, then if any timestamp in the created or modified columns is greater than last_reload timestamp, execute ajax.reload(), (and notice I set the resetPaging value to false, so it wont send the viewer back to the first page).

Also, keep in mind that I use UnderscoreJS for the part used to check for timestamps, its _.find function was the easiest and fastest way to do this. It will return the first value found matching any condition, which in this case, is just a "greater than or greater than" conditional statement.

This to me seems like the best way to make the table "live", updating it when anythings out of date, however, @ThomD says hes got a better way of doing this, saying he will update only the rows that have been updated in the back end, check this post. The issue I have with that, is I haven't found a way to delete/add/update rows that havent yet been rendered to the page. When using AJAX, it pulls the JSON and caches it, then renders it when needed. So if the row that needs updated isnt on the current page, How would I update the cached JSON data? No idea.. Maybe @Allan can help me out with that.. lol. It would definitely be better to only update the rows that need updating

If you have any questions, let me know.

jLinux · October 2015

Also, it might be a good idea to instead of monitoring timestamps.. just diff the json array that we just retrieved and the json source of ajax.json ().

Less work, and it should work just as well. (Would also account for deleted entries, which my code currently does not), and you wouldnt require or rely on any columns with epoch timestamps..

allan · October 2015

So if the row that needs updated isnt on the current page, How would I update the cached JSON data?

I haven't read through in detail - but row().data() can be used to update a row which hasn't been rendered yet. The id selector options in the latest versions of DataTables can make this easier since you can select based on an id, even for rows that haven't been rendered.

Allan

alex_envbulle · October 2015

Aww that's nice jLinux ! Thanks for giving your code, I'm gonna try a day when I've the time.

@Allan : Thank you again for all your tips, the result is very nice and much faster ! But I've now a new problem: fancybox is no longer working. It appears under the viewport and when I scroll I can see the top of it, but he puts on again under the viewport. Wtf ? Do you know why ?

EDIT: But when there is only one row in the DT, I can see the fancybox :/

EDIT2: Problem solved, I declared once too often the fancybox

jLinux · October 2015

What in your opinion would be the easiest, and best way to make a table 100% "Live", meaning everything is up-to-date without having to refresh the page at all, and preferably, update only relevant data?

Would it be to use the serverSide capability? I know that will update any new data pulled into the table, so that would only require you to monitor and update the data thats currently on the page.

Or would it be to use the ajax option, without serverSide, and try to use row().data() to update each row?

The id selector options in the latest versions of DataTables can make this easier

What versions?

P.S. @alex_envbulle I updated my post above with the code, mainly the monitor_updates() function, it now wont overlap AJAX requests

allan · October 2015

Use a web socket and send the changed data then use the API to add, edit and remove rows as required. The other other option is long polling and having the server send the list of changes.

What versions?

1.10.8 with fixes in 1.10.9.

Allan

jLinux · October 2015

@allan using serverSide and ajax? Or just ajax? Or local json array?

jLinux · October 2015

Use a web socket and send the changed data then use the API to add, edit and remove rows as required.

Would something like this suffice?

The other other option is long polling

What is "long" polling

And lets assume theres anywhere between 1k to 100k items being managed by the table :)

jLinux · October 2015

@alex_envbulle I made a change in the code posted above, the monitor_updates() function. I cant change the post now, since its too old.

If you use it, then the success callback for the $.ajax request, change it from..

success: function ( response ) {
    // Get the proper data source
    var data_src = ($api.init().ajax.dataSrc
        ? response[ $api.init().ajax.dataSrc ]
        : response);

    // Reload table if any changes were found
    _.find( data_src, function ( asset ) {
        return ( last_reload && (asset.created > last_reload || asset.modified > last_reload));
    } ) && $api.ajax.reload( null, false );
},

to:

success: function ( response ) {
    // Get the proper data source
    var data_src = ($api.init().ajax.dataSrc
        ? response[ $api.init().ajax.dataSrc ]
        : response);

    // Get the data stored by DataTables to compare
    var dt_json = ($api.init().ajax.dataSrc
        ? $api.ajax.json()[ $api.init().ajax.dataSrc ]
        : $api.ajax.json());

    // Check for updated timestamps, or length change (Deleted rows)
    _.find( data_src, function ( asset ) {
        return ( last_reload  && ( asset.created > last_reload || asset.modified > last_reload ) );
    } ) || data_src.length !== dt_json.length && $api.ajax.reload( null, false );

},

This will reload the table if any rows were deleted from the data source as well.

allan · October 2015

You could implement server-side processing with a web-socket, but I'd imagine Ajax would cover most cases. A bit like normal client-side processing, there will come a point when there would be just too much data for the client-side.

Long polling is defined in this wikipedia article. It was a way of doing "server-side push" before web sockets.

jLinux · October 2015

@allan, I had another question about the

The id selector options in the latest versions of DataTables can make this easier

Are you talking about setting the rowId option? Then just using a jQuery CSS selector to select the rows that way?

And is that value whats returned by the row().id()?

I wanted to use the rowId option, but the only thing I would set as the ID's is the asset ID's in the table, which are numeric, and start at 1, and since rowId literally sets the id attribute in the DOM, which needs to be unique, that really wasn't a good option, atleast imo.

But if I can reference the DT rows using asset ID's.. that would save me a lot of work..

Heres an example, in the initComplete, I execute manage_selects(this), which is this:

// Function to manage the selected rows, which need to be re-selected when the table is redrawn
function manage_selects($dt) {
    $dt = tools.get_dtapi($dt);

    $dt
        // When rows are selected ..
        .on( 'select', function ( e, dt, type, indexes ) {
            // Update the selected rows
            selected_rows = assets.get_selected_ids($dt);
        } )

        // When rows are Deselected ..
        .on( 'deselect', function ( e, dt, type, indexes ) {
            // Update the selected rows
            selected_rows = assets.get_selected_ids($dt);
        } )

        // When the DT is redrawn .. (Deletes, sorts, etc)
        .on( 'draw.dt', function () {
            // Re-select the rows with the correct asset ID's
            $dt.rows( function ( idx, data, node ) {
                return $.inArray(data.asset_id, selected_rows) !== -1;
            } ).select();
        } );
}

assets = {
    /**
     * Get Selected Assets
     *
     * Get the ID's of the selected assets in the assets list table
     *
     * @param   {Object} $dt    DataTables API
     * @returns {Array}
     */
    get_selected_ids: function ($dt) {

        var rows = $dt.rows( { selected: true } ).data();
        var asset_ids = [];

        $.each(rows, function(i,v){
            asset_ids.push( v.asset_id );
        });

        return asset_ids;
    }
};

So I would think that if I can assign the asset ID's, then the lines where I execute:

$dt.rows( function ( idx, data, node ) {
    return $.inArray(data.asset_id, selected_rows) !== -1;
} ).select()

could easily be reduced, as well as the code in get_selected_ids() used to retrieve the ID's.

P.S. The code above is to make sure that whatever is selected, stays selected, since I do a lot of re-draws

jLinux · October 2015

I see that I can specify the rowId, and I got rid of all instances of assets.get_selected_ids() and replaced any calls to it with just $dt.rows( { selected: true } ).ids(), and it seems to work great, is there a way to select rows by those IDs? When I pass the ID's to rows(), it obviously grabs the DT row ID index, not the ID I assigned

jLinux · October 2015

Ohhh.. I would have to pass true to the row().id() or rows().ids() to return the ID's with hashes, then that can be used as the row-selector, right?

Hmm... I dont want to pass them around with hashes in-front of them, but id like to use them to select the rows in DT (and for other tasks), would you suggest this is the best way?

$dt.rows( _.map(selected_rows, function(a_id){ return '#'+a_id; }) ).select();

allan · October 2015

Are you talking about setting the rowId option? Then just using a jQuery CSS selector to select the rows that way?

In part - I made a number of large changes to how the ids are handled internally - you can id select while deferRender is enabled for example.

Ohhh.. I would have to pass true to the row().id() or rows().ids() to return the ID's with hashes, then that can be used as the row-selector, right?

Yes.

If you don't what the hashes, but you don't want to select by id you would either need to use the row-selector as a function, use a map as you have done to add the hashes just put the hash in front in the first place :-).

Allan

DataTable takes too much time

DataTable takes too much time

Answers

Howdy, Stranger!

Categories

DataTables

DataTable takes too much time

DataTable takes too much time

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables