Performance issue on a (not so) big table

olivierG

Hello,
I have a table with a little more then 5,000 rows.
It takes around 14 seconds to render. From what I have read, it should be less than 1 second.
The search in a column is also very slow.
To speed up the row creation, I add all the rows in one shot (table.rows.add(my_rows).draw()), which has already speeded the rendering a lot.But obviously not enough.
I tried deferRender : no effect;
I tried the scroller but the scrollY parameter generates "Uncaught TypeError: f is undefined".

The table params are :

{
            order: [ ],
            oLanguage: {
                (Language parameters)
            },
            columns : [
                {"data" : "c0a"},
                {"data" : "c0b"},
                {"data" : "c1"},
                {"data" : "c2"},
                {"data" : "c3"},
                {"data" : "c4"},
                {"data" : "c5"},
                {"data" : "c6"}
            ],
            columnDefs:[
                {"orderData": 0, "targets": 3},
                {"visible": false, "targets": [0,1]},
                {"width": "10%", "targets": 7 },
                {"className": "dt-body-right", "targets": 7 },
                {"searchable": false,"targets": [2,3,6,7]}
            ],
            rowCallback: function (row, data) {
                           (various operations)
                        },
            pageLength: 20,
            responsive : true,
            dom : 'rtip'
        }

The table can be seen here : https://cpts-france.fr/cpts-paris12/annuaires/

Thanks for your help.
Olivier

kthorngren

It takes around 14 seconds to render. From what I have read, it should be less than 1 second.

I would say that 1 second is subjective and depends on what you are doing with the table. I have a table of 5000 or so records and it takes a few seconds to load.

I ran a couple tests and this is the time I see for rows.add() to run which is less than 6 seconds.

(index):2218 Time to fill table 1: 5811.100000023842 milliseconds

(index):2218 Time to fill table 1: 5860.700000047684 milliseconds

You have this for that Datatable:

            rowCallback: function (row, data) {
               row.setAttribute("name",data.name);
               row.dataset.nom_commune = data.com;
               row.dataset.inpp = data.inpp;
               row.dataset.id_user = data.idUser;
               row.dataset.ids = data.ids;
               row.dataset.cme = data.cme;
               row.children[1].firstChild.firstChild.addEventListener("click",(e)=>{
                    let rowNodeData = (e.target.nodeName == "I") ? e.target.parentNode.parentNode.parentNode.parentNode.dataset : e.target.parentNode.parentNode.parentNode.dataset;
                    let inpp = rowNodeData.inpp;
                    let ids = rowNodeData.ids;
                    let cme = rowNodeData.cme;
                    afficher_complements(inpp,ids,cme);
                });

I wonder if adding the event listener is part of the delay. Instead of adding 5000 event listeners creating one delegated event will be more efficient. A quick test would be to remove the -option rowCallback` to see if the table load time increases significantly. If it does then look at using delegated events.

Start there and let us know what you find.

Kevin

olivierG

Thanks for this answer, and the suggestion about delegated event. I will definitely try it.
For now, I tried to remove the rowCallback option.
Strangely, the load time is a little bit longer (16s).
Olivier

kthorngren

Actually I think rowCallback is only called for the rows on the page, not all.
Making the change I suggested probably won't help with Datatable load time as you mentioned. But using deferRender should. Please update your example to use deferRender for the first table s we can see if it helps. You mentioned it didn't.

Kevin

kthorngren

Additionally if you use rowCallback, which is called every time the row is displayed, you could create a new additional click event for the element. Another reason to use delegated events.

Kevin

olivierG

I have added deferRender:true in the table options. THis time it seems to work well. The rendering time drops to 5 seconds.
I have also suppressed the addEventListener in the rowCallBack function and replaced it by a delegated event on the table body. It works well but does not improve the speed.
I have also noticed that when I trigger a redraw after having searched the table, with the command

table.column(colIndex).search( "" ).draw();

it takes much longer to draw (>9 seconds) than at the initial loading.

Olivier

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

olivierG

Sorry Colin. I have given a link in my first question and did not repeat it in the following comments.
It is still available here :https://cpts-france.fr/cpts-paris12/annuaires/ .
Thanks.
Olivier

kthorngren

You have some search plugins but that don't look like they are doing too much. Please provide the steps needed to replicate the issue.

Kevin

olivierG

Hi,
Summarizing what I did to speed things up :
-Using the "deferRender" option,
-Using a delegated event on the table body instead of adding a eventListener on each cell,
-removing a table redraw wich was only meant to add a red border around a search input when it is used,
-using jQuery.fn.dataTable.util.throttle to avoid a redraw for each letter entered in the search input,
-splitting the table in 2 parts (one with 4,488 entries, and the other with 695)

While it is becoming acceptable, there are still some issues especially on the big table :
1
* click on the button named "Professionnels de santé (salariés et bénévoles)"
* enter a search string in the input called "Nom", for example "aaron"
* the input freezes after the first "a" although I added a throttle of 1 second. Then the throttle delay works well for the following letters.
2
* click on the red cross to re-initialize the field and redraw the table
* the redraw time seems very long (around 14 seconds). The command executed is table.column(colIndex).search( "" ).draw().
3
*I have added the processing:true option to indicate to the user that the redraw is pending but it as no effect (note that "r" is present in the dom option).

Thanks,

olivier

colin

For 1, I'm not seeing a searchDelay in your configuration - that's a built-in mechanism to throttle the searches.

For 2, I'm not seeing that, have you fixed this? I searched for "aaron", and the red cross clears in less than a second.

For 3, processing is only shown for server-side processing. As Allan said in this thread, "with client-side processing the search is actually performed synchronously so there is no processing indicator". The docs aren't clear on that, I'll update them,

Colin

olivierG

Thank you Colin,
(1) For the throttle I use jQuery.fn.dataTable.util.throttle(). As I said, on my computer, the input field freezes after the first letter is entered. Then the following letters are typed in a row.
(2) it is really not so fast on my computer (well the red cross disappears quickly, but the table takes long seconds to redraw). Maybe it is a matter of browser.
Regards,
Olivier

allan

Hi Olivier,

As a little test, could you try disabling the custom search functions you have on the page:

$.fn.dataTable.ext.search.push( function( settings, data, dataIndex ) {

It looks like there are up to 8 of them, and the DOM traversal they are doing is what I believe is taking so long (DOM interaction is always slow).

If that is indeed the issue, we can look at how to optimise it.

Allan

olivierG

Thank you Allan,
Yes when I disable the search fucntions, the table is displayed much faster.
Olivier
New url : https://cpts-france.fr/cpts-paris12/annuaire-test/

allan

I'm getting a login requirement from your page now. Could you show me one of search functions and we will take a look and see how it might be optimised?

Allan

olivierG

Please use the new url I mentioned : https://cpts-france.fr/cpts-paris12/annuaire-test/

Thank you

olivierG

This is an example of one search function on a checkbox filter, to select rows that contain a childnode with the class "vad" :

    $.fn.dataTable.ext.search.push( function( settings, data, dataIndex ) {
                var vadS = document.querySelector("#liste_pro_S #vad").checked;
                if (dataTableProS.cell(dataIndex,3).node()) {
                    var vadOption = (dataTableProS.cell(dataIndex,3).node().querySelector(".vad")) ? true : false;
                }
                if ( (vadS && vadOption ) || (!vadS)) {
                    return true;
                }
                return false;
            });

olivierG

I guess that a solution would be to create hidden columns with my search criteria.

allan

Very much so. If you can do your logic condition without touching the DOM it will be massively faster. For example it looks like the element with the vad class is dependent upon cpts_vad in the data? Can you just check that directly?

Allan

olivierG

Hi,
I have added hidden columns and the search is now performed on the values in these columns. It is indeed quite fast now !
I have another problem though : when I update data in the table, it is not taken into account in the search, although the update is correctly made and I redraw the table.
Olivier

colin

How are you updating the data? If you're just updating the data in the DOM then that would be the case, you would need to invalidate the cell or row after the update (using cell().invalidate() or row().invalidate()). To modify a record with DataTables updating it's caches automatically, use cell().data() or row().data(),

Colin

olivierG

It works with row().invalidate(). It is strange, though, because I am using row().data().
Thank you.

colin

That is odd, you shouldn't need to - see example here: http://live.datatables.net/lutixake/1/edit

If you can update that to demonstrate the issue, please, we can take a look - there might be something odd with your data perhaps,

Colin