Server Side Loading

techupin

Hello,

I have a database Table with 2 million records. I am using Server-Side loading with Pagination.

My problem is:

When I first load the page, the loading of DataTables is not immediate, there is a gap of about 5 seconds even for the first loading of 10 records. Is it possible to reduce this time?

Say if, in the same table, I want to pre-fetch/cache load first 50 records within minimal loading time, and then load the rest of the records, based on search and as per pagination request, is there a easy way to do it?

i.e., Load first 50 records immediately as the page loads, and the rest can be as per request.

I tried deferLoading, but my problem is that my Database keeps regularly updated, and I cannot make a static table of first N records in HTML.

Thanks and Regards,

TU

Terradon

I have a database table with 4 million records, loading in less then 0,5 second.
Your problem will certainly lay in the way how you select your records.
Please post your tables and used sql.

techupin

I am using standard v1.10 simple(...) function in server_processing.php

The SQL executed is below. All the values of $where, $order, $limit, are default.

      $data = self::sql_exec( $db, $bindings,
                        "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
                         FROM `$table`
                         $where
                         $order
                         $limit"
                );

I am not even filtering the table with a search... its the first default loading of a paginated table that is taking time. The desc of the mysql table is

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| tid     | int(11)      | NO   | PRI | NULL    | auto_increment |
| ttype   | varchar(1)   | YES  |     | NULL    |                |
| dest    | varchar(80)  | YES  |     | NULL    |                |
| tdate   | date         | YES  |     | NULL    |                |
| src     | varchar(80)  | YES  |     | NULL    |                |
| desc | varchar(500) | YES  |     | NULL    |                |
| code     | int(11)      | YES  |     | NULL    |                |
| qty         | double       | YES  |     | NULL    |                |
| unittype    | varchar(20)  | YES  |     | NULL    |                |
| value       | double       | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Terradon

I am NOT using the example class I wrote my own serverside code. Especially because i would have better understandings about what happens then (my lack of knowledge to understand the class well enough).

Jsut one of the the first hits in google about SQL_CALC_FOUND_ROWS:
http://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

This article tells you basicly that people often only counts the amont of queries instead of looking to the overall query process.

I started with the basic query. Later on i have build up the query depending on all sort and search options.

Hereby my own basic query, rewrite the values for your own situation and you will probably notice the quick response. After that you could try to add the other demands. If needed i can provide you with my code.

Using a tool to look at the ajax calls en -responses will greatly improve debugging tasks. I myself use Firebug, but their are other alternatives enough.

    // count total rows in log for this user
    $sql = "SELECT COUNT(*) as totalrows FROM logboek WHERE speler = :playerID ";
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(':playerID', $_SESSION['playerID'],PDO::PARAM_INT);
    $stmt->execute();
    $aData = $stmt->fetchAll();
    $totalRows = $aData[0]['totalrows'];

    $sql = "SELECT datum, actie, gameID, opID, opNick, beschrijving 
            FROM logboek
            WHERE speler = :playerID
            ".$orderby."
            LIMIT :offset,:limit";
            
    try {
        $stmt = $dbh->prepare($sql);

        $stmt->bindValue(':playerID', $_SESSION['playerID'],PDO::PARAM_INT);
        $stmt->bindValue(':offset', (int) $_GET['start'], PDO::PARAM_INT); // offset
        $stmt->bindValue(':limit', (int) $_GET['length'], PDO::PARAM_INT); // LIMIT
        
        $stmt->execute();
        $aData = $stmt->fetchAll(); 
    } 
    catch (PDOException $e) { 
        echo 'failed: ' . $e->getMessage();
        // your error handling (log?) here
        exit; 
    }

    $aReturn = array();
    $aReturn['draw'] = (int)$_GET['draw'];
    $aReturn['recordsTotal'] = $totalRows;
    $aReturn['recordsFiltered'] = $totalRows; // alleen anders bij search!
    $aReturn['data'] = array();


    foreach($aData as $key => $aRows)
    {
        $aArray = array();
        
        $aArray[] = $aRows['datum'];
        $aArray[] = $aRows['actie'];
        $aArray[] = $aRows['gameID'];
        $aArray[] = $aRows['opNick'];
        $aArray[] = $aRows['beschrijving'];
        $aReturn['data'][] = $aArray;
    }

    echo json_encode($aReturn);

techupin

Thanks!! I will try out writing raw sql queries, and sql optimizations and share the results.

TU

techupin

Terradon, thx!!

Your suggestion of avoiding SQL_CALC_FOUND_ROWS reduced my loading time by half!! Still it is taking more time than I would like. I am also trying to optimize my SQL front with indexing and caching and partial in-memory loading.

In case, somebody has any suggestions/tricks on Pre-Fetching'' andConcurrent / Predictive Look-Ahead'' loading of say, first few pages of the DataTable, please let me know.

Thanks!!

TU

Terradon

I dont like caching sql results. The only entity knowing what sql results to cache, is the database itself, which already does it automatically for you. Focus on efficient queries, not a caching system application-side! it is the wrong approach!

My given eample was my first example to start for the first view of the table. I added all kind of search and order possibilties en ended up with building up 3 queries for everything.

ALWAYS use prepared statements, with PDO it's sooooooooooo easy to to. Quit mysqli, use PDO for easier coding. Some people will tell you it cost more time, but that is hardly to measure. Your queries themselves will have much more influence on needed time. I maintain a heavy ajaxed site with an avarage of 100-150 vistors simultaniously online without any database issues.

Hereby my final backend:

## Error reporting 
ini_set ('display_errors', 0); // not displaying php errors to visitor
ini_set ('log_errors', 1);
ini_set ('error_log', '/home/xxxxxxxxxxx.nl/public_html/Maintenance/phperrorlog.txt');
error_reporting(E_ALL); // show all errors and warnings 


 * Easy set variables
 */
 
 session_start();
// check inlog
if(!isset($_SESSION['playerID']) OR $_GET['playerID'] != $_SESSION['playerID'])
    die('Geen toegang'); // hoe foutmelding geven??

require'../includes/header.php';

if($_GET['length'] == '-1')
    $_GET['length'] = 1; // still printing problem with datatools !!!!!!



// Count total rows in log for this user, filtered and showing are different figures:)
$sql = "SELECT COUNT(*) as totalrows FROM logboek WHERE speler = :playerID ";  

$stmt = $dbh->prepare($sql);
$stmt->bindValue(':playerID', $_SESSION['playerID'],PDO::PARAM_INT);
$stmt->execute();
$aData = $stmt->fetchAll();

$totalRows = $aData[0]['totalrows'];
$recordsFiltered = $totalRows;


/**
    Order by
*/
$orderby = ''; // switch!!!!
if($_GET['order'][0]['column'] == 0)
{
    // order by datum/tijd: praktisch gezien, altijd aanwezig
    if($_GET['order'][0]['dir'] == 'asc')
        $orderby = ' ORDER BY datum ASC';
    else
        $orderby = ' ORDER BY datum DESC';
}
elseif($_GET['order'][0]['column'] == 1)
{
    if($_GET['order'][0]['dir'] == 'asc')
        $orderby = ' ORDER BY actie ASC';
    else
        $orderby = ' ORDER BY actie DESC';
}
elseif($_GET['order'][0]['column'] == 2)
{
    if($_GET['order'][0]['dir'] == 'asc')
        $orderby = ' ORDER BY gameID ASC';
    else
        $orderby = ' ORDER BY gameID DESC';
}
elseif($_GET['order'][0]['column'] == 3)
{
    if($_GET['order'][0]['dir'] == 'asc')
        $orderby = ' ORDER BY opNick ASC';
    else
        $orderby = ' ORDER BY opNick DESC';
}
elseif($_GET['order'][0]['column'] == 4)
{
    if($_GET['order'][0]['dir'] == 'asc')
        $orderby = ' ORDER BY beschrijving ASC';
    else
        $orderby = ' ORDER BY beschrijving DESC';
}




/**
    SQL opbouwen
    volorde velden: datum, actie, speler, gameID, opID, opNick, beschrijving
*/


## where parts
$where = ' WHERE ';
$firstwhere = true; 

// datum
if($_GET[columns][0][search][value] != '')
{
    $datums = explode("-tot-",$_GET[columns][0][search][value]);
    $datumvan = $datums[0]; 
    $datumtot =  $datums[1].' 23:59:59'; // anders wordt 00-00-00 als standaard genomen!
    
    $where .= ' datum BETWEEN :datumvan AND :datumtot ';
    $firstwhere = false;
}

// actie

if($_GET[columns][1][search][value] != '')
{
    if($firstwhere)
    {
        $where .= ' actie = :actie ';
        $firstwhere = false;
    }
    else    
        $where .= ' AND actie = :actie';
}

// speler
if($firstwhere)
{
    $where .= ' speler = :playerID';
    $firstwhere = false;
}
else
    $where .= ' AND speler = :playerID'; 

// gameID
if($_GET[columns][2][search][value] != '')
{
    if($firstwhere)
        $where .= ' gameID LIKE :gameID ';
    else
        $where .= ' AND gameID LIKE :gameID ';

    $firstwhere = false;
}


// opID

// OpNick
if($_GET[columns][3][search][value] != '')
{
    if($firstwhere)
        $where .= ' opNick LIKE :opNick ';
    else
        $where .= ' AND opNick LIKE :opNick ';

    $firstwhere = false;
}


// beschrijving
if($_GET[columns][4][search][value] != '')
{
    if($firstwhere)
        $where .= ' beschrijving LIKE :beschrijving ';
    else
        $where .= ' AND beschrijving LIKE :beschrijving ';

    $firstwhere = false;
}



// sql: count filtered
    $sql_count = 'SELECT COUNT(*) as totalrows FROM logboek '.$where;

    $stmt = $dbh->prepare($sql_count);
        
    ## bind parts
    
    // daterange
        if($_GET[columns][0][search][value] != '')
        {
            $stmt->bindValue(':datumvan', $datumvan);
            $stmt->bindValue(':datumtot', $datumtot);
        }
        
    // actie
        if($_GET[columns][1][search][value] != '')
        {
            $stmt->bindValue(':actie', $_GET[columns][1][search][value]);
        } 
    
    // speler
        $stmt->bindValue(':playerID', $_SESSION['playerID'],PDO::PARAM_INT);
        
    // gameID
        if($_GET[columns][2][search][value] != '')
        {
            $stmt->bindValue(':gameID', '%'.$_GET[columns][2][search][value].'%',PDO::PARAM_INT); 
        }
    // OpNick
        if($_GET[columns][3][search][value] != '')
        {
            $stmt->bindValue(':opNick', '%'.$_GET[columns][3][search][value].'%');
        }
    // beschrijving
        if($_GET[columns][4][search][value] != '')
        {
            $stmt->bindValue(':beschrijving', '%'.$_GET[columns][4][search][value].'%');
        }
    
    
    $stmt->execute();
    $aData = $stmt->fetchAll();
    $recordsFiltered = $aData[0]['totalrows'];

    
//sql: data results
    $sql_search = 'SELECT datum, actie, gameID, opID, opNick, beschrijving FROM logboek '.$where.' '.$orderby.' LIMIT :offset, :limit';

    $stmt = $dbh->prepare($sql_search);
        
    ## bind parts
    
    // daterange
        if($_GET[columns][0][search][value] != '')
        {
            $stmt->bindValue(':datumvan', $datumvan);
            $stmt->bindValue(':datumtot', $datumtot);
        }
        
    // actie
        if($_GET[columns][1][search][value] != '')
        {
            $stmt->bindValue(':actie', $_GET[columns][1][search][value]);
        }

    // speler
        $stmt->bindValue(':playerID', $_SESSION['playerID'],PDO::PARAM_INT);
    
    // gameID
        if($_GET[columns][2][search][value] != '')
        {
            $stmt->bindValue(':gameID', '%'.$_GET[columns][2][search][value].'%',PDO::PARAM_INT); 
        }
    // OpNick
        if($_GET[columns][3][search][value] != '')
        {
            $stmt->bindValue(':opNick', '%'.$_GET[columns][3][search][value].'%'); 
        }
    // beschrijving
        if($_GET[columns][4][search][value] != '')
        {
            $stmt->bindValue(':beschrijving', '%'.$_GET[columns][4][search][value].'%');
        }
    //limit offset
    $stmt->bindValue(':offset', (int) $_GET['start'], PDO::PARAM_INT); // offset
    $stmt->bindValue(':limit', (int) $_GET['length'], PDO::PARAM_INT); // LIMIT
    
    $stmt->execute();
    $aData = $stmt->fetchAll(); 
    
    $aReturn = array();
    $aReturn['draw'] = (int)$_GET['draw'];
    $aReturn['recordsTotal'] = $totalRows;
    $aReturn['recordsFiltered'] = $recordsFiltered; // alleen anders bij search!
    $aReturn['data'] = array();
    
    foreach($aData as $key => $aRows)
    {
        $aArray = array();
        
        $aArray[] = $aRows['datum'];
        $aArray[] = $aRows['actie'];
        $aArray[] = $aRows['gameID'];
        $aArray[] = $aRows['opNick'];
        $aArray[] = $aRows['beschrijving'];
        $aReturn['data'][] = $aArray;
    }
    
echo json_encode($aReturn); 

Hope this will help you to get the idea.
This script is still under development for improving, but it already works fast.
If you have any questions, let me know.