Datatables loading speed

spacemancw

I have a number of datatables tables ranging in size from a few hundred rows to a few thousand rows.
Once I get over 2000 it can take time for the table to load.
I have one table with 12 columns, the last one is hidden using

<th style=display:none;></th>

There's 3900 rows and it takes 45 seconds to load fully. That means pagination is done, the "show number of entries" drop down appears and I can actually start using the search box.

The tables are all flat html files created nightly by scripts in cron.
The web server is apache using server side includes.
A web page for a table is typically a .shtml file.

For example, table.shtml will look like this:

    <!--#include virtual=/includes/commonheader.shtml -->

    <table id="example">

    <!--#include file=tablehead.html -->

    <!--#include file=tablerows.html -->

    </table>

    <!--#include virtual=/includes/commonfooter.shtml -->

So in the case of the 3900 rows, tablerows.html is a 3462153k file (3mb).
I am not pulling the data from a live database, so there is no delay in retrieving data.
Is there any way to speed up loading the data.

I am also using these options ;

 "order": [[ 2, 'desc' ]],

"columnDefs": [{
    // Hide columns 3,4,7,8
    "visible": false,
    "targets": [3,4,7,8]
  }],

  dom: 'Blfrtip',
  buttons: [

    'colvis',

So I show the hidden columns with the "Column Visibility" drop down.

allan

45 seconds for only 3900 rows is exceptionally slow. I would note that requiring the download of a 3MB file before the table can be rendered though might not be the best move in terms of speed optimisation. That said, 45 seconds is still a very long time.

If you can link to the page, I could profile it and see a little bit more about where it is running slow.

Have you taken a look at the speed FAQ? One important point is that with JSON loaded data, DataTables doesn't need to read the data from the DOM. That is always very slow. You would notice a significant difference if JSON loading I suspect (unless of course it is the bandwidth of the server that is the issue).

Allan

spacemancw

@allan the server is internal. I could ask to allow public access, but I would have to lock it down to your source IP.

are you saying I can load data into the table from a JSON file?
I don't know enough about JS to do this. A quick look at that FAQ link .. Is there a how-to link somewhere?

I'll google around a bit.

allan

I've sent my IP via a PM.

are you saying I can load data into the table from a JSON file?

Sure can. Examples here and the manual here.

The first thing to do is create the JSON file. Then if you are stuck, show me a sample of the JSON data.

Allan

spacemancw

OK @allan .. thanks for the help ....
I got it working ... kinda.
So I made my script write the output to a file in JSON format .. and I was able to load 3862 rows in 14 seconds. Much faster. But I am having other issues. I have 3 columns that I was previously hiding, and using for searches and drawing charts, as follows

       <th style=display:none;></th>
       <th style=display:none;></th>
       <th style=display:none;></th>

I dont want to hide them using the datatables'visible' command with 'targets' becaue they can be seen with the 'Column Visibility' button that I use.

Now with the JSON, those three columns show in the table (with a blank header).

Also I have an exclude function that is no longer working.
here is an old discussion that shows an example of my hidden column and my exclude function. You can see that I have hidden data in the last column that I use to draw a chart. I want to keep this ability.

https://live.datatables.net/bituyelo/2/edit

In this table, this is what a row looks like ;

[
    "VCL1", 
    "<div style=display:none;>1</div><i class='fa fa-desktop' aria-hidden='true' style=color:#80ba02;</i>", 
    "<div class=tooltip>TMTSERVER02-TEST<span class=tooltiptext>VMNAME : TMTSERVER02-TEST <br>Powerstate : PoweredOn <br>Guest OS ; Windows 10 (64-bit)</span></div>", 
    "James",
    "10-31-2024",
    "Windows 10 (64-bit)", 
    "Pre-Sales Testing",
    "500", 
    "576", 
    "<div class=tooltip><i class='fa fa-circle' aria-hidden='true' style=color:#80ba02;></i><span class=tooltiptext>ACTIVE</span></div>", 
    "<div class=tooltip><i class='fa fa-circle' aria-hidden='true' style=color:#80ba02;></i><span class=tooltiptext>BK_EXEMPT<br>DR_Exempt</span></div>", 
    "BK_EXEMPT DR_Exempt TES", 
    "PoweredOn", 
    "ACTIVE"
]

The last 3 should be hidden.

It does allow me to continue using fa awesome fonts, which is cool.

thanks

spacemancw

Just noticesd .. my highcharts are not working either.
After googling a bit, I found this, Highcharts - Ajax Loaded Data Chart

my charts worked after I added ;

<script src = "https://code.highcharts.com/modules/data.js"></script>

allan

Good to hear the charts are working.

I dont want to hide them using the datatables 'visible' command with 'targets' becaue they can be seen with the 'Column Visibility' button that I use.

You can tell ColVis only to see specific columns - see this example.

I'd suggest doing that and using something like:

columnDefs: [ {
  targets: [7, 8, 9],
  className: 'noVis',
  visible: false
} ]

DataTables will not process the display: none of cells in the table when initialising.

Allan