Sorting and loading is slow for server side
Sorting and loading is slow for server side
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
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
This discussion has been closed.
Replies
in mysql: http://dev.mysql.com/doc/refman/5.0/en/create-index.html
CREATE INDEX indexname USING BTREE ON tablename colname
I have applied index on the "name" column but still can't see the performance niether in firefox,chrome,or safari
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.
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.
Are you able to provide a link to the project?
Thanks for listening.
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
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]
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?
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.
Does "sDom": 'T<"clear">lfrtip' will do the job for me?
http://www.datatables.net/extras/tabletools/initialisation
Please see the link:
http://bay20.com/new/examples/data_sources/server_side.html
[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?
is this a bug in TableTools? because all the other operations only operate on what's in the current view's iDisplayLength
About the Bug,I have no idea. but worth watching it.
Please suggest.
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]
[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.
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
$(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