Performance Improvements

gkm

I have a table with 12 columns and 3000 rows which is experiencing performance issues. It takes a full minute and a few seconds for DataTables to render the table (This is after the data is already returned to the screen, so I know the query and network are not issues). Due to business requirements I cannot use paging, therefore, DataTables is having to render all 3000 records. My JSP creates the HTML DOM table and then it is initialized like so:

$("#OrderHistoryTable2").dataTable({
            "paging":       false,
            "ordering":     true,
            "info":         true,
            "filter":       true,
            "length":       true,
            "processing":   true,
            "dom":          't<"bottom"if><"clear">',        
            "scrollY":      "200px",
            "scrollX":      true,
            "columnDefs":   [
                { "orderData": [],    "targets": 0 }, //TURN OFF DEFAULT SORTING OF TABLE
                { "orderable": false, "targets": 0 }  //TURN OFF SORTABLILITY FOR COLUMN 1
            ],
            "language":     {
                "search": "filter:",
                "info": " _TOTAL_ items ",
                "infoEmpty": "0 items "
            }
});

I read that it would be faster if I used ajax instead of making DataTables convert the DOM. As a result, I have changed the JSP to only generate the <thead> section of the table. My initialization code now looks like this:

 $("#OrderHistoryTable2").dataTable({
            "paging":       false,
            "ordering":     true,
            "info":         true,
            "filter":       true,
            "length":       true,
            "processing":   true,
            "dom":          't<"bottom"if><"clear">',        
            "scrollY":      "200px",
            "scrollX":      true,
            "columnDefs":   [
                { "orderData": [],    "targets": 0 }, //TURN OFF DEFAULT SORTING OF TABLE
                { "orderable": false, "targets": 0 }  //TURN OFF SORTABLILITY FOR COLUMN 1
            ],
            "language":     {
                "search": "filter:",
                "info": " _TOTAL_ items ",
                "infoEmpty": "0 items "
            },
            "ajax":         {
                "url":      "/app/getOrderHistoryData",
                "dataSrc":  "list",
                "type":     "GET"
            },
            "columns":      [
                {"data": "Column1"},
                {"data": "Column2"},
                {"data": "Column3"},
                {"data": "Column4"},
                {"data": "Column5"},
                {"data": "Column6"},
                {"data": "Column7"},
                {"data": "Column8"},
                {"data": "Column9"},
                {"data": "Column10"},
                {"data": "Column11"},
                {"data": "Column12"}
            ],
            "deferRender": true
        });

With these changes DataTables is now rendering the table in 16 seconds. This is much better than it was, but 16 seconds is still a long time to wait for a screen to render.

Is this the best I can hope for since I am not able to use paging or can I do more to improve the performance?

allan

16 seconds still seems like a very long time. In fairness, deferRender will make no difference if you have paging disabled. You could possibly try using Scroller which makes use of paging but visually the end user won't see that.

But beyond that, I'd need a link to the page so I can profile it and see what it is running so slowly.

Allan

gkm

Thank you so much for your response. I was thinking the same thing about deferRender. For now i will remove that. I will keep in mind the Scroller option, but I would really like to figure out what I am doing wrong first so that I can use DataTables as efficiently as possible.

Unfortunately, my application is on an internal network with sensitive data, so I can't give a link to the page. However, I will try to see if I can recreate just this page with some mock data out on the web somewhere. Do you have a recommendation on a good place to do that?

allan

JSFiddle, CodePen, JSBin or http://live.datatables.net .

Allan

gkm

Thanks for the suggestions. I am working on the example but have run into a problem I don't know how to get past. I have been trying to figure out how to pass a JSON variable into DataTables using JsFiddle. I am trying to mimic the ajax call by just supplying a JSON object. How do I do that, given my previous initialization example?

gkm

Here is my example html

<head>
    <!-- JQUERY -->
    <script src="https://code.jquery.com/jquery-1.11.1.js"></script>
    
    <!-- BOOTSTRAP -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css">
      <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js"></script>
    
      <!-- JQUERY UI -->
      <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css">
      <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
      
      <!-- JQUERY DATATABLES -->
      <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
      <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
    
      <!-- MOMENTS -->
      <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.js"></script>
        
      <!-- DATATABLES MOMENTS -->
      <script src="https://cdn.datatables.net/plug-ins/1.10.12/sorting/datetime-moment.js"></script>
    
      <!-- FONTAWESOME -->
      <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" integrity="sha384-wvfXpqpZZVQGK6TAh5PVlGOfQNHSoD2xbE+QkPxCAFlNEevoEH3Sl0sibVcOQVnN" crossorigin="anonymous">
    
      <!-- DATATABLES - FONTAWESOME -->
      <link rel="stylesheet" href="https://cdn.datatables.net/plug-ins/1.10.12/integration/font-awesome/dataTables.fontAwesome.css">

</head>
<body>
        <div class="container-fluid center-text col-md-10 col-md-offset-1">

          <table id="OrderHistoryTable" class="table display">
            <thead>
              <tr>
                <th>Actions</th>
                <th>Requested Date</th>
                <th>Card Number</th>
                <th>Cardholder</th>
                <th>Ship Address 1</th>
                <th>Ship Address 2</th>
                <th>City</th>
                <th>State</th>
                <th>Zip</th>
                <th>Ship Method</th>
                <th>Created By</th>
                <th>Status</th>
              </tr>
            </thead>
          </table>
        </div>
      </body>

Here is my javascript

//MAKE REQUESTED DATE FORMAT RECOGNIZABLE FOR SORTING
$.fn.dataTable.moment('MM/DD/YYYY HH:mm:ss A');  

//var dataList = JSON.stringify(getData());
var dataList = getData();
alert(JSON.stringify(dataList));


//INITIALIZE ORDER HISTORY TABLE
$("#OrderHistoryTable").dataTable({
  "paging":     false,
  "ordering":   true,
  "info":       true,
  "filter":     true,
  "length":     true,
  "processing": true,
  "dom":          't<"bottom"if><"clear">',        
  "scrollY":    "200px",
  "scrollX":    true,
  "columnDefs":   [
        { "orderData": [],    "targets": 0 }, //TURN OFF DEFAULT SORTING OF TABLE
    { "orderable": false, "targets": 0 }  //TURN OFF SORTABLILITY FOR COLUMN 1
  ],
  "language":     {
    "search": "filter:",
      "info": " _TOTAL_ items ",
      "infoEmpty": "0 items ",
      "processing": "<div class='row spinner-icon'><i class='fa fa-spinner fa-5x fa-pulse'></i></div>"
  },
  "data":                   dataList,
  },
  "columns":            [
    {"data": "Actions"},
        {"data": "RequestedDate"},
    {"data": "CardNumber"},
    {"data": "CardHolder"},
    {"data": "ShipAddr1"},
    {"data": "ShipAddr2"},
    {"data": "City"},
    {"data": "State"},
    {"data": "Zip"},
    {"data": "ShipMethod"},
    {"data": "CreatedBy"},
    {"data": "Status"}
  ]
});
    


function getData()
{
    return [{
    "Actions":              "",
    "RequestedDate":    "10/20/2016",
    "CardNumber":           "123456789159483",
    "CardHolder":           "Jim Doe",
    "ShipAddr1":            "4532 Blue St",
    "ShipAddr2":            "",
    "City":                     "Big City",
    "State":                    "New York",
    "Zip":                      "10101",
    "ShipMethod":           "Regular Mail",
    "CreatedBy":            "Jane Doe",
    "Status":                   "COMPLETED"           
  }];
}

gkm

sorry, I really made a mess of that.

gkm

jsfiddle example that is not working, yet. https://jsfiddle.net/mkgarn/gr27xye5/

gkm

Here is an example with the json object, but it does not seem to take the 15 - 20 seconds that I am seeing. not sure what to look at now.

https://jsfiddle.net/mkgarn/p85eebff/1/

Apezdr

Is there a possibility to make a test page with ONLY the required files in it. That could help you in identifying if there are other scripts (etc.) causing the delay. The easiest way to do process of elimination here would be to use the network tab. Check out the attached image.

allan

To be honest, with performance issues, we'd really need to be able to access the page directly, since so many things would change with an example on JSFiddle. It is possible to use an echo feature in JSFiddle, but that wouldn't highlight any server performance issues.

In addition to the network tab Apezdr mentions there is a "Performance" tab in Chrome. That is always where I go to first for performance issues. Run a profile and take a look at the "chart" view to see where the time is being taken up.

How large the the JSON file you are loading? 12 * 3000 = 36000 which is a fair number of DOM elements to generate, but I wouldn't expect it to take 16 seconds unless you were using legacy IE browsers.

You could, as a test, try enabling paging in your table and see how that effects performance. If it suddenly renders the table in milliseconds, then there is a problem with the row generation. If not, then the problem is likely elsewhere.

Allan

gkm

Thank you very much Apezdr and Allan for the suggestions. I am using IE, but it is IE 11. I will try what you said Apezdr, but first I thought I would mention some interesting things I found by enabling paging, as you suggested Allan. I put some console log entries in the javascript and this is what I found:

WITHOUT PAGING (as my page has been designed)

Open History screen
3702 records (static JSON array)
Before get data - Tue Nov 15 2016 09:19:57
After get data - Tue Nov 15 2016 09:19:57
Before Table Initialization - Tue Nov 15 2016 09:19:57
After Table Initialization - Tue Nov 15 2016 09:20:06
Total 9 sec.

Refresh with Destroy table and reinitialize table

console.log("Get table reference - "+new Date());
var dt = $("#OrderHistoryTable").dataTable().api();
console.log("Before Destroy - "+new Date());
dt.destroy();
console.log("After Destroy - "+new Date());
ccos.order.history.initializeOrderHistoryDataTable(tableData);

3702 records (static JSON array)
Before get data - Tue Nov 15 2016 09:21:35
After get data - Tue Nov 15 2016 09:21:35
Get table reference - Tue Nov 15 2016 09:21:35
Before Destroy - Tue Nov 15 2016 09:21:35
After Destroy - Tue Nov 15 2016 09:22:19
Before Table Initialization - Tue Nov 15 2016 09:22:19
After Table Initialization - Tue Nov 15 2016 09:24:35
Total 180 sec
NOTE: When I attempt to interact with the screen after doing this it displays this popup message "local host is not responding" with a button titled "Recover webpage"

Refresh with Clear, Add, and Draw table

console.log("Get table reference - "+new Date());
var dt = $("#OrderHistoryTable").dataTable().api();
console.log("Before Clear+Add+draw - "+new Date());
dt.clear();
dt.rows.add(tableData);
dt.draw();
console.log("After Clear+Add+draw - "+new Date());

3702 records (static JSON array)
Before get data - Tue Nov 15 2016 09:42:40
After get data - Tue Nov 15 2016 09:42:40
Get table reference - Tue Nov 15 2016 09:42:40
Before Clear+Add+draw - Tue Nov 15 2016 09:42:40
After Clear+Add+draw - Tue Nov 15 2016 09:43:21
Total 41 sec
NOTE: Screen is usable after refresh

WITH PAGING ENABLED
Only change to enable paging were these 3 lines

"paging":       true,
//            "dom":          't<"bottom"if><"clear">',        
//            "scrollY":    "200px",

Open History screen
3702 records (static JSON array)
Before get data - Tue Nov 15 2016 09:55:04
After get data - Tue Nov 15 2016 09:55:04
Before Table Initialization - Tue Nov 15 2016 09:55:04
After Table Initialization - Tue Nov 15 2016 09:55:08
Total 4 sec
Note: sorting of columns is much faster with paging enabled

Refresh with Destroy table and reinitialize table
3702 records (static JSON array)
Before get data - Tue Nov 15 2016 10:02:28
After get data - Tue Nov 15 2016 10:02:28
Get table reference - Tue Nov 15 2016 10:02:28
Before Destroy - Tue Nov 15 2016 10:02:28
After Destroy - Tue Nov 15 2016 10:02:29
Before Table Initialization - Tue Nov 15 2016 10:02:29
After Table Initialization - Tue Nov 15 2016 10:03:16
Total 48 sec
NOTE: page is usable

Refresh with Clear, Add, and Draw table
3702 records (static JSON array)
Before get data - Tue Nov 15 2016 09:56:06
After get data - Tue Nov 15 2016 09:56:06
Get table reference - Tue Nov 15 2016 09:56:06
Before Clear+Add+draw - Tue Nov 15 2016 09:56:06
After Clear+Add+draw - Tue Nov 15 2016 09:56:10
Total 4 sec

DataTables clearly performs better when using paging. This is very interesting. I have tried to convince the application users to allow us to page before. Perhaps I should try again.

Are there some further optimizations I need to make to the configuration in order to perform better without paging or is it just the fact that the draw code is having to draw all 3702 rows without paging and only 10 at a time with paging?

Thank you so much for your time and willingness to respond to my questions. It has really helped.

allan

The reason performance is much better with paging enabled (and the deferRender option enabled, plus Ajax or JS data loading) is that it doesn't need to create all DOM elements up front. So instead of 3702 rows (12 cells for each), you create 10 rows (12) - which is obviously going to be a heck of a lot faster.

If scrolling is enabled, that is a massive performance hit as well as DataTables needs to work out how to align the columns, which involves reading sizes from the DOM, which is slow in any browser.

The most performant you can get is Ajax loaded data, with paging, no scrolling and deferRender enabled.

That should really happen in <0.5S. Anything slower and I would consider it too slow.

Of course other things can impact performance such as plug-ins, row callbacks, draw callbacks, rendering functions, etc. Without being able to see the page and take a profile there isn't a huge amount of specific advice I can offer I'm afraid.

Allan

gkm

Thank you very much. I do very much appreciate your help.

allan

No problem. I need to get around to writing up a documentation page on performance really!