DataTables with large number of rows

sgmartin18

I've been using DataTables for quite some time now on small tables with <1000 rows, but now I have a table with 16K rows and it takes about 45 - 60 seconds to load the page. I'm looking at using server-side processing, hoping that would make it load faster but I'm having dfficulty making the examples work for me.
[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/cmdb/server_processing.php"
} );
[/code]

It doesn't even appear to be caling the script. I put some debug type lines to see if it was calling the script. I used the PHP script that I found on the DataTable site for server-side processing and made the necessary changes. It doesn't appear to be bringing back the data.

[code]
<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
include('constants.php');
include('common.inc.php');
include('db.conf.php');
include('ez_sql_core.php');
include('ez_sql_mysql.php');
PrintLog("Called server_processing.php");

/* 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)
*/
//$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );
$aColumns = array('hostname',' primary_cap', 'serial_no', 'status', 'env_spec', 'cust_code', 'lid_code', 'cust_region');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "hostname";

/* DB table to use */
$sTable = "consolidated_ci_hw";

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/


/* Database connection information */
$gaSql['user'] = $cfg['cap_cmdb2']['username'];
$gaSql['password'] = $cfg['cap_cmdb2']['password'];
$gaSql['db'] = $cfg['cap_cmdb2']['database'];
$gaSql['server'] = $cfg['cap_cmdb2']['hostspec'];

$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if (isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_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]
In the main script, does JQuery take care of loading the rows or how do I present each row/column of data that would come back?

[code]



Server Name
Asset Type
Serial #
Asset Status
Environment
Cust Code
LID Code
Cust Region




Loading data from server




Server Name
Asset Type
Serial #
Asset Status
Environment
Cust Code
LID Code
Cust Region




[/code]
All I see is "Loading data from server" and no data.

allan

> "bServerSide": true

Its almost certainly the server-side process taking a very long time. Take a look at the timeline in Chrome and you'll be able to confirm if this is the case. If so, you need to optimise your database /script as its not a DataTables issue.

Allan