Sorting and loading is slow for server side

anjaruss

Dear All

I have a very peculiar problem,as when i am loading data into datatables from server-side in php.
The sorting on columns like "name" is very slow and takes around 19 to 20 secs minimum.
I have a huge database of around 202,346 records.
Is it possible to fasten it up..Please help me out.

Thanks
Khan Anjaruss

fbas

In the database, adding indexes to your columns should speed things up.

in mysql: http://dev.mysql.com/doc/refman/5.0/en/create-index.html
CREATE INDEX indexname USING BTREE ON tablename colname

anjaruss

Thanks for replying so early, i am glad to hear that

I have applied index on the "name" column but still can't see the performance niether in firefox,chrome,or safari

fbas

How many rows are you viewing at once on the client side? Where is the performance bottleneck?

I can't think of anything else right now other than pre-querying things with temp tables or some other caching mechanism on the server side in case someone wants to see the first page of your table sorted by this column or another one. This will take up more storage and processing on the server side.

anjaruss

I am viewing 10 rows at the moment, when i click on the column "name" on the datatable it takes around 15 to 20 secs to return the result.

Although the index(as you said) i have applied, the other columns sorting are working fine like "id" and some other,basically all the columns with integer values are sorted like a charm,but bottleneck is when i click on "name" column with alphabets,the sorting seems to be very slow.

fbas

What kind of database are you using?

Are you able to provide a link to the project?

anjaruss

I am using a php-mysql database and as i speak i am uploading my contents for you to have a look in around 15 mins

anjaruss

I am really sorry to day,that i am not able to load the link today due to bandwidth problem,but will do it first thing in the morning.
Thanks for listening.

anjaruss

Hi fbas

I have uploaded the contents,please check it on http://bay20.com/new/examples/data_sources/server_side.html
The searching,and sorting seems to be slow,please help me out to recover.

allan

Chrome shows that your XHR is taking almost a second to load - which is very slow. You might want to look at optimising your database and SQL a bit. Also you might want to use plug-ins such as this to reduce the impact on your server: http://datatables.net/plug-ins/api#fnSetFilteringDelay

Allan

anjaruss

I have used the fnSetFilteringDelay(250) and also set indexing on columns,but it still is the same.

My html file is as

[code]


$(document).ready(function() {

$('#example').dataTable( {

"bProcessing": true,

"bServerSide": true,

"sAjaxSource": "../server_side/scripts/server_processing.php"





} ).fnSetFilteringDelay(250);

} );



jQuery.fn.dataTableExt.oApi.fnSetFilteringDelay = function ( oSettings, iDelay ) {
/*
* Inputs: object:oSettings - dataTables settings object - automatically given
* integer:iDelay - delay in milliseconds
* Usage: $('#example').dataTable().fnSetFilteringDelay(250);
* Author: Zygimantas Berziunas (www.zygimantas.com) and Allan Jardine
* License: GPL v2 or BSD 3 point style
* Contact: zygimantas.berziunas /AT\ hotmail.com
*/
var
_that = this,
iDelay = (typeof iDelay == 'undefined') ? 250 : iDelay;

this.each( function ( i ) {
$.fn.dataTableExt.iApiIndex = i;
var
$this = this,
oTimerId = null,
sPreviousSearch = null,
anControl = $( 'input', _that.fnSettings().aanFeatures.f );

anControl.unbind( 'keyup' ).bind( 'keyup', function() {
var $$this = $this;

if (sPreviousSearch === null || sPreviousSearch != anControl.val()) {
window.clearTimeout(oTimerId);
sPreviousSearch = anControl.val();
oTimerId = window.setTimeout(function() {
$.fn.dataTableExt.iApiIndex = i;
_that.fnFilter( anControl.val() );
}, iDelay);
}
});

return this;
} );
return this;
}
[/code]

and my php code is:-

[code]
<?php

/*

* Script: DataTables server-side script for PHP and MySQL

* Copyright: 2010 - Allan Jardine

* License: GPL v2 or BSD (3-point)

*/



/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* Easy set variables

*/



/* Array of database columns which should be read and sent back to DataTables. Use a space where

* you want to insert a non-database field (for example a counter or static image)

*///hhmid,hhid,name,relationshipdate_age

$aColumns = array( 'hhmid', 'hhid','uniqueid','memberid','name' );

//$aColumns = array( 'hhmid', 'name');

/* Indexed column (used for fast and accurate table cardinality) */

$sIndexColumn = "uniqueid";


/*

* Paging

*/

$sLimit = "";

if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )

{

$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".

mysql_real_escape_string( $_GET['iDisplayLength'] );

}





/*

* Ordering

*/

$sOrder = "";

if ( isset( $_GET['iSortCol_0'] ) )

{

$sOrder = "ORDER BY ";

for ( $i=0 ; $i $iFilteredTotal,

"aaData" => array()

);



while ( $aRow = mysql_fetch_array( $rResult ) )

{

$row = array();

for ( $i=0 ; $i


[/code]

fbas

Anjarus, I took a look at your link and on my machine it's running smoothly for Name column as well as the others. Were you able to fix it? Perhaps it's the client-side that is slow on certain machines, but my computer was speedy.

anjaruss

I did nothing more than just adding the fnSetFilteringDelay suggested by Allan and indexing by you.
Thanks to you guys.

Allan says XHR is taking almost a second..how can I reduce it?

Yes you are right,it runs fine on a server rather than my localhost but can you suggest any other performance boost to it, as the record size may gets doubled on its actual size.

Can i use the Tabletool functinality like export as csv and excel etc for my serverside script?

fbas

TableTool works on the client side, based on what is in the view (if you show 10 rows at a time, it only gets those 10 rows). It works the same for server-side as for any other datatable.

I can't think of any other (simple) changes you can make on the server/database to speed up the XHR AJAX call. In my case it was 1/4 of a second, so maybe Allan's lag is due to network traffic and distance from the server.

I'm in Texas, USA, I believe he is in England.

anjaruss

Could you please suggest,how would I use Table Tool in server side?

Does "sDom": 'T<"clear">lfrtip' will do the job for me?

fbas

You need to install and include the for TableTools, and specify the location of the Adobe flash component that does the work of TableTools (install this on your server) in "sSwfPath"



http://www.datatables.net/extras/tabletools/initialisation

anjaruss

Thanks it worked like a charm ,but whenever I want to print it it throws an "Json formatting error".
Please see the link:

http://bay20.com/new/examples/data_sources/server_side.html

fbas

Look in the debugger for the server_processing.php server call. your php script runs out of memory:

[code]



Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 35 bytes) in /home/content/11/7515011/html/new/examples/server_side/scripts/server_processing.php on line 313

[/code]

does print try to print the entire database, rather than a filtered portion?

anjaruss

No, The print just prints the filtered portion as the value in the dropdown

fbas

it's sending iDisplayLength:-1, doesn't that mean get all records? with 200,000 records it's not too surprising to run out of memory.

is this a bug in TableTools? because all the other operations only operate on what's in the current view's iDisplayLength

anjaruss

Yes I agree that it tries to print all the records instead of the pagination defined.

About the Bug,I have no idea. but worth watching it.

Please suggest.

fbas

set print button options bShowAll to false: http://www.datatables.net/extras/tabletools/button_options

anjaruss

Worked have done it but all other buttons disppeared.

Please check this.

[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../server_side/scripts/server_processing.php",
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"sSwfPath": "../../media/swf/copy_cvs_xls_pdf.swf",
"aButtons": [
{
"sExtends": "print",
"bShowAll": false
}
]
}


} ).fnSetFilteringDelay(250);
} );

[/code]

anjaruss

Oops my mistake, I have changed the above code to :-

[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../server_side/scripts/server_processing.php",
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"sSwfPath": "../../media/swf/copy_cvs_xls_pdf.swf",
"aButtons": ["copy","csv", "pdf","xls",
{
"sExtends": "print",
"bShowAll": false
}
]
}



} ).fnSetFilteringDelay(250);
} );
[/code]



and worked like a charm.

I appreciate the effort Fbas, Thank you very much.

Thedi6

Please. Help meee!!

I have a few questions about this functionality. I tried to deploy a Lotus Notes database. But I can not have favorable results.

I have a few days researching and testing. First, because Lotus gives me the tables as HTML and not XHTML. This use JQuery some instructions to convert it to XHTML.

Second. The data loading time is a little slower the more records I have. I've been reading a bit about this way and I've implemented, but does not work and send me error messages

You must use a PHP?. Since I do I have to implement to Lotus Notes (databases documentaries). And in my case still does not work all these instructions on my board


regards

Thedi6

This is the code:

$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/invest/sara/pruebas.nsf",
"sDom": 'T<"clear">lfrtip'
} ).fnSetFilteringDelay(250);
} );


But beneath the table, embed a code to convert HTML to XHTML. I do not know if this will damage