Datatable loading too slowly
Datatable loading too slowly
Khalid Teli
Posts: 251Questions: 71Answers: 0
Hi
I have a table with 23 columns and 4000 rows
1) On average it takes between 17 seconds to load table
2) I have paging enabled and on average it takes 12 secondsto go from one page to another
3) similar time is used if using search box to search something from table
4) In addition to this when I enable the searchPanes, the performance degrades further.
5) I have incluided the code without searchPanes. I want to improve the performance without using serchPanes and later include it in .
My client side code looks like this:
<table id="products" class="display table-bordered nowrap cell-border " cellspacing="0" style="width:100%">
<thead>
<tr>
<th style="background-color:lightgreen;text-align:center;"colspan="23">Product Details</th>
</tr>
<tr>
<th>Product Id</th>
<th>CRG Code </th>
<th></span>Product Code</div></th>
goes to 23 colums in total
</tr>
</table>
</body>
</html>
<script type="text/javascript" language="javascript">
var editor;
function selectColumns ( editor, csv, header ) {
var selectEditor = new $.fn.dataTable.Editor();
var fields = editor.order();
for ( var i=0 ; i<fields.length ; i++ ) {
var field = editor.field( fields[i] );
selectEditor.add( {
label: field.label(),
name: field.name(),
type: 'select',
options: header,
def: header[i]
} );
}
selectEditor.create({
title: 'Map CSV fields',
buttons: 'Import '+csv.length+' records',
message: 'Select the CSV column you want to use the data from for each field.'
});
selectEditor.on('submitComplete', function (e, json, data, action) {
// Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
editor.create( csv.length, {
title: 'Confirm import',
buttons: 'Submit',
message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
} );
for ( var i=0 ; i<fields.length ; i++ ) {
var field = editor.field( fields[i] );
var mapped = data[ field.name() ];
for ( var j=0 ; j<csv.length ; j++ ) {
field.multiSet( j, csv[j][mapped] );
}
}
} );
}
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajax": "/Editor/Editor-1.9.5/controllers/products/products_fetch.php",
"table": "#products",
"fields": [
data here
]
} );
// Upload Editor - triggered from the import button. Used only for uploading a file to the browser
var uploadEditor = new $.fn.dataTable.Editor( {
fields: [ {
label: 'CSV file:',
name: 'csv',
type: 'upload',
ajax: function ( files ) {
// Ajax override of the upload so we can handle the file locally. Here we use Papa
// to parse the CSV.
Papa.parse(files[0], {
header: true,
skipEmptyLines: true,
complete: function (results) {
if ( results.errors.length ) {
console.log( results );
uploadEditor.field('csv').error( 'CSV parsing error: '+ results.errors[0].message );
}
else {
uploadEditor.close();
selectColumns( editor, results.data, results.meta.fields );
}
}
});
}
} ]
} );
$('#products').on( 'click', 'tbody td:not(:first-child)', function (e) {
editor.inline( this );
} );
var table = $('#products').DataTable( {
"processing": true,
"serverSide": true,
"paging": true,
"responsive": true,
"stateSave": true,
"info": true,
"deferRender": true,
"dom": 'Blfrtip',
"ajax":
{
url:"/Editor/Editor-1.9.5/controllers/products/products_fetch.php",
type:'POST'
},
"columns": [
{ data: "product_id" },
{ data: "unique_id" },
{ data: "product_code" },
{ data: "product_name" },
{ data: "pack_size" },
{ data: "supplier_name" },
{ data: "product_brand" },
{ data: "product_storage" },
{data: "product_id",
"render": function (data, type, row) {
return '<button class="btn btn-primary" data-toggle="modal" data-id="' + row.product_id +
'" data-title="' + data.product_id + '" data-fieldname="' + row.product_id + '" data-target="#terms-modal">Terms</button>'
}
},
{data: "product_id",
"render": function (data, type, row) {
return '<button class="btn btn-primary" data-toggle="modal" data-id="' + row.product_id +
'" data-title="' + data.product_id + '" data-fieldname="' + row.product_id + '" data-target="#logs-modal">Logistics</button>'
}
},
{ data: "current_price" },
{ data: "effective_date" },
{ data: "barcode_outer" },
{ data: "barcode_inner" }
//additional columns here , in total 23 columns
],
"columnDefs": [
{
"targets": [ 10,11,12,13,14,15,16,17,18,19,20,21,22],
"visible": false
},
{
targets: [5],
"render": function (data, type, row, meta) {
return '<a href="http://crgdirectory.co.uk/index.php/supplier-details/?supp_name='+row.supplier_name+'">' + data + '</a>';
}
},
{
}
],
select: {
style: 'os',
selector: 'td:first-child'
},
buttons: [
{ extend: "create", editor: editor},
{ extend: "edit", editor: editor},
{ extend: "remove", editor: editor },
{
text: 'Import CSV',
action: function () {
uploadEditor.create( {
title: 'CSV file import'
} );
}
},
],
} );
$('#products tbody').on('click', 'tr td:nth-child(9)', function () {
var productName = table.row( this ).data().product_name;
$("#modalTitle").text(productCode + '-'+productName + '-'+supplierName);
// $("#modalTitlefooter").text(supplier);
$("#termsModal").html("<h4> "+ "Current Price: " + + currentPrice + '<br>'+ "</h4>");
});
$('#products tbody').on('click', 'tr td:nth-child(10)', function () {
// Get the rows id value
var productName = table.row( this ).data().product_name;
// alert( 'Clicked row id '+id );
$("#modalTitleLogs").text(productCode + '-'+productName + '-'+supplierName);
$("#termsLogs").html("<h4> "+ "Pack Size: " + packSize + '<br>'+
'<br>' +"Outer Barcode: "+outerBarcode + '<br>'+ "</h4>");
});
});
</script>
And serverside code looks like this 
<?php
include( "../../lib/DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions,
DataTables\Editor\SearchPaneOptions;
Editor::inst( $db, 'products', 'product_id' )
->field(
Field::inst( 'products.product_id', 'product_id'),
Field::inst( 'products.product_code', 'product_code'),
Field::inst( 'products.product_name', 'product_name')
->searchPaneOptions( SearchPaneOptions::inst() ),
Field::inst( 'products.product_brand' ,'product_brand')
->searchPaneOptions( SearchPaneOptions::inst() ),
//additional columns below ..
)
->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )
->debug( true )
->tryCatch( false )
->transaction( false )
->process($_POST)
->json();
Please help, I am expected to load around 50k rows and I hope the performance increases
Thank you
This discussion has been closed.
Answers
Hi Just to add on that, it might help
Once the datatable is loaded
1) on loading of datatable request/response duration are as follows:
2) and on search draw, these parameters are as follows:
Request sent is 96 us waiting (TTFB) is on avg 15 and and contend download 1.37ssecondsThe place to start is to narrow down where the delay is coming from, for example:
Let us know what you find.
Kevin
Hi @kthorngren
Thank you, I will have a look and update you.
Meanwhile, please see my previous comment for request/response duration time
Thank you
@kthorngren
Thank you very much.
1) I guess SQL is taking too long
2) Even though the
pagination is True, server side responds with all rows on every draw, which makes it slow3) on loading of datatable request/response duration are as follows:
[Request sent is 81 us] [waiting (TTFB) is on avg 13]
and [contend download 1.94s]
4)Isn't datatable supposed to return only now of rows defined in pagination option? or do we need to write a code on server side?
5) Also, with the column rendering, I just need them in row data and I removed their definition. It is still slow , of course of the above mentioned reasons.
Thank you
The server side protocol is described here:
https://datatables.net/manual/server-side
It requires a server script to respond to the protocol. Click on the Server-side script tab of this example to see the script. It is including this ssp-class script. I don't use them but my understanding is the Editor comes with server side scripts.
Kevin
Hi @kthorngren
Thank you.
1) Looking at the Server side Protocol, I am doing exactly same what it is asking in terms of setting page length.
The length
set is 15, so server should only return 15 rows in a single draw?But on each draw it is returning all 4k rows
2) I am using the Editor, so I guess ssp is irrelevant in this case? or is it needed? I am not sure
Thank you
serverSideis still applicable with Editor. It would be worth looking at this blog post, as it discusses using Editor's scripts for SSP.Cheers,
Colin
@colin
Thanks
I can't find any blog post attached. Can you please link it again Thank you
@colin
I have been looking around SSP class and I understand how the limit function is defined.
I am sorry but I cant figure it out how it works with Editor libraries. My basic understanding says when the
serverside:true, the server side should respond with the no. of rows as defined in page length.Like the one in example below:
https://editor.datatables.net/examples/simple/server-side-processing.html
Using the developer tool and checking the response , I can see on each page click it only fetched 10 rows of data . And that is what it is expected to do.
I am using the exactly similar process
Please help
Hi @colin
Found the issue, searchPanes was causing the issue
->searchPaneOptions( SearchPaneOptions::inst() ),Excellent, glad all sorted. This is the blog post I meant: https://datatables.net/blog/2020-05-12 . Apologies for not pasting it before,
Colin