Datatable Very Slow Loading
Datatable Very Slow Loading
binexline
Posts: 27Questions: 10Answers: 0
Hello,
I have a datatable, that fetches maximum 10,000 rows of data from a remote SQL server.
It takes about 14sec for the table to load 950 entries.. where TTFB alone took 13sec.
Can anyone... please guide me how I could reduce this time...?
Below is my table:
$('#mblList').DataTable({
"dom": 'Blfrtip',
ajax: "data.php",
columns: [{
data: 'ROWNUM',
className: 'not-editable'
},
{
data: function(row, type, val, meta) {
if (row.BLOCK == 'HF') return 'Y';
return '';
},
"name": "Block",
className: 'text-danger not-editable'
},
{
data: 'REF_NO',
className: 'not-editable'
},
{
data: 'MBL_NO',
className: 'not-editable'
},
{
data: null,
render: function(data, type, row) {
if (data.BLOCK == 'HF') return '';
return '<button type="button" class="btn btn-dark btn-sm">HC</button>';
},
className: 'HC not-editable'
},
{
data: null,
render: function(data, type, row) {
return '<button type="button" class="btn btn-dark btn-sm">View</button>';
},
className: 'View not-editable'
},
{
data: 'SHP_MOD',
className: 'not-editable'
},
{
data: 'OFC',
className: 'not-editable'
},
{
data: 'CARR_BKG_NO',
className: 'not-editable'
},
{
data: 'HBL_CNT',
className: 'not-editable'
},
{
data: 'ETD',
className: 'not-editable'
},
{
data: 'ETA',
className: 'not-editable'
},
{
data: 'DOC_CUT_OFF_DT',
className: 'not-editable'
},
{
data: 'VGM_CUT_OFF_DT',
className: 'not-editable'
},
{
data: function(row, type, val, meta) {
if (row.CNTR_CNT > 0) return row.CNTR_NO + ' + ' + row.CNTR_CNT;
return row.CNTR_NO;
},
"name": "Container",
className: 'not-editable'
},
{
data: 'CNTR_SUMMARY',
className: 'not-editable'
},
{
data: 'FORWARDING_AGENT',
className: 'not-editable'
},
{
data: 'DEST_AGENT',
className: 'not-editable'
},
{
data: 'TRIANGLE_AGENT',
className: 'not-editable'
},
{
data: 'SHIPPER',
className: 'not-editable'
},
{
data: 'CNEE',
className: 'not-editable'
},
{
data: 'BILLING_CARRIER',
className: 'not-editable'
},
{
data: 'VSL_NM',
className: 'not-editable'
},
{
data: 'VOY',
className: 'not-editable'
},
{
data: 'CARRIER',
className: 'not-editable'
},
{
data: 'POR',
className: 'not-editable'
},
{
data: 'POL',
className: 'not-editable'
},
{
data: 'POD',
className: 'not-editable'
},
{
data: 'DEL',
className: 'not-editable'
},
{
data: 'PIKUP_DT',
className: 'not-editable'
},
{
data: 'RLSD_FLG',
render: function(data, type, row) {
if (type === 'display') {
if (data == "Y") return '<i class="fas fa-check-square"></i> <i class="fa fa-pencil edit"/>';
else return '<i class="far fa-square"></i> <i class="fa fa-pencil edit"/>';
}
return data;
}
},
{
data: 'RLSD_DT',
render: editIcon
},
{
data: 'FRT',
className: 'not-editable'
},
{
data: 'AR',
className: 'not-editable'
},
{
data: 'AP',
className: 'not-editable'
},
{
data: 'DC',
className: 'not-editable'
},
{
data: 'CUST_REF_NO',
className: 'not-editable'
},
{
data: 'VERIFY',
className: 'not-editable'
},
{
data: 'PAY',
className: 'not-editable'
},
{
data: 'ISSUED_BY',
className: 'not-editable'
},
{
data: 'SALES_PIC',
className: 'not-editable'
},
{
data: 'POST_DT',
className: 'not-editable'
},
{
data: 'AGENT_REF_NO',
className: 'not-editable'
},
{
data: 'A_EDI',
className: 'not-editable'
},
{
data: 'RGST_TMS',
className: 'not-editable'
},
{
data: 'MODI_USR_NM',
className: 'not-editable'
},
{
data: 'MODI_TMS',
className: 'not-editable'
}
],
"deferRender": true,
"scrollX": true,
"scrollY": '65vh',
"scrollCollapse": true,
select: {
style: 'api'
},
processing: true,
"pageLength": 200,
"lengthMenu": [
[200, 300, 500, 1000, -1],
[200, 300, 500, 1000, "All"]
],
buttons: [{
extend: "excel",
text: 'Excel All'
}, {
extend: "remove",
editor: editor
}],
keys: {
columns: ':not(.HC, .View)',
blurable: false
},
initComplete: function() {
// Apply the search
this.api().columns().every(function() {
var that = this;
$('input', this.footer()).on('keyup change clear', function() {
if (that.search() !== this.value) {
that
.search(this.value)
.draw();
}
});
});
}
});
And below is my data.php
This question has accepted answers - jump to:
This discussion has been closed.
Answers
And below is my data.php
When I execute below query from for example SSMS, it only takes 4sec to get the results
Also please ignore the " from below code, I kinda edited the Query.php file for " to work as '..:) (I forgot why I did that)
This section of the FAQ should help, it discusses various techniques to improve performance,
Cheers,
Colin
Hi @colin ,
Thanks for the reference. But paging is set true, draw performance is fine, and deferRender is set true also. (my table is ajax sourced)
Are there any other methods..?
Regards,
Leanne
Hi Leanne,
Did you try enabling the
serverSideoption that is mentioned in the FAQ Colin linked to? It should help a bit with so many join conditions. To enable, change:to be:
(since it needs to be a POST request for how the PHP is setup there).
Failing that, add
->debug(true)to your initial Editor (PHP) chain which will result in the SQL that is being generated being returned to the client, so you can see what the SELECT statement is.The next step would be to use
EXPLAIN ...on that SELECT statement, so your SQL server can tell you why the query is running slowly.My guess would be that one or more of your left join conditions are being done on non-indexed columns.
Allan
Hi Allan,
WOW adding ->debug(true) and testing with the query generated from here pointed me to the right direction!! This query takes 13sec to process...!
Strange, because I have this another query that does the exact same thing but the query is structured differently and takes only about 4sec to process.
THANK YOU
OK, Allan, you're right,
Below section of the query was the problem.
This section wasn't included in my another query which was why it only took 4sec.
Thank you again for your help!
@allan ,
The table still loads very slowly even after I fixed my query and the query itself runs fast..
The 'Waiting (TTFB)' still takes 13sec when loading the datatable.
Do you know any other methods to reduce this time...?
Regards,
Leanne
Hi Leanne,
With the debug mode still enabled, what does the query look like now? Can you show me the JSON response when you load the table?
Allan
Hi Allan,
Yes, it looks like below:
It doesn't look like server-side processing is enabled as I suggested before. Did you try enabling it? Does it make a difference?
Allan
@allan ,
Yes I tried server-side processing before but it didn't make any difference.
THANK YOU