Slow datatables

Question

I have a database of 5,985 records. I am using dataTables, but it takes a long time to load.

It is possible to improve this load?

See my code:

<div class="row">
  <div class="col-xs-12">
    <div class="table-responsive">
      <table id="tabela-agenda" class="table table-striped table-bordered table-hover">
        <thead>
          <tr>
            <th>Foto</th>
            <th>Badge</th>
            <th>Nome</th>
            <th>E-mail</th>
            <th class="hidden-480">Telefone</th>
            <th class="hidden-480">Status</th>
            <th></th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td>
              <div class="visible-md visible-lg hidden-sm hidden-xs action-buttons">

                <?php if ($row_rs['badge'] != ""){?>
                <a class="purple" href="carteirinha.php?badge=<?php echo $row_rs['badge']; ?>" onclick="return hs.htmlExpand(this, {objectType: 'iframe', width: 800, height:400 } )" title="Carteirinha"> <i class="icon-print bigger-130"></i> </a>
                <?php }?>

                <a class="blue" href="cadastroPerfil.php?id=<?php echo $row_rs['ID']; ?>&i=2a" title="Perfil"> <i class="icon-zoom-in bigger-130"></i> </a>
                <a class="green" href="cadastroEditar.php?id=<?php echo $row_rs['ID']; ?>&i=2a" title="Editar" onclick="return hs.htmlExpand(this, {objectType: 'iframe', width: 800, height: 600 } )"> <i class="icon-pencil bigger-130"></i> </a>
                <a class="red delete-event" href="cadastroApagar.php?id=<?php echo $row_rs['ID']; ?>&foto=<?php echo $row_rs['foto']; ?>&i=2a" title="Apagar" data-title="Apagar" data-content="Deseja apagar esse membro?" onClick="return false;"> <i class="icon-trash bigger-130"></i> </a> </div>
              <div class="visible-xs visible-sm hidden-md hidden-lg">
                <div class="inline position-relative">
                  <button class="btn btn-minier btn-yellow dropdown-toggle" data-toggle="dropdown"> <i class="icon-caret-down icon-only bigger-120"></i> </button>
                  <ul class="dropdown-menu dropdown-only-icon dropdown-yellow pull-right dropdown-caret dropdown-close">
                    <li> <a href="#" class="tooltip-info" data-rel="tooltip" title="Ver"> <span class="blue"> <i class="icon-zoom-in bigger-120"></i> </span> </a> </li>
                    <li> <a href="#" class="tooltip-success" data-rel="tooltip" title="Editar"> <span class="green"> <i class="icon-edit bigger-120"></i> </span> </a> </li>
                    <li> <a href="#" class="tooltip-error" data-rel="tooltip" title="Apagar"> <span class="red"> <i class="icon-trash bigger-120"></i> </span> </a> </li>
                  </ul>
                </div>
              </div>
            </td>
          </tr>
        </tbody>
      </table>
    </div>
  </div>
</div>

Javascript that loads:

<script src="assets/js/jquery.dataTables.js"></script>
<script src="assets/js/jquery.dataTables.bootstrap.js"></script>
<script type="text/javascript">
  var oTable1 = $('#tabela-agenda').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "cadastroCarregaRegistros.php",
    columns: [{
        data: "foto", //Nome do campo no JSON, que contem a url da foto
        render: function(value) {
          return '<img src="/images/fotos/' + value + '" height="50" alt=""/>';
        }
      },
      null,
      null,
      null,
      null,
      {
        data: "status", //Nome do campo no JSON, retorna o status do cadastro
        render: function(value) {
          switch (value) {
            case "N":
              return '<span class="label label-warning">Aguardando aprovação</span>';
              break;
            case "A":
              return '<span class="label label-success">Ativo</span>';
              break;
            case "V":
              return '<span class="label label-danger">Vencido</span>';
              break;
            case "B":
              return '<span class="label label-inverse">Banido</span>';
              break;
            case "FP":
              return '<span class="label label-purple">Falta Pagar</span>';
              break;
          }
        }
      },
      null
    ]
  });

</script>

cadastre RegistrationRegisters.php

<?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)
     */
    $aColumns = array('ID', 'foto', 'badge', 'nome', 'email', 'tel_celu', 'status' );
    
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "ID";
    
    /* DB table to use */
    $sTable = "cadastro";
    
    /* Database connection information */
    $gaSql['user']       = "";
    $gaSql['password']   = "";
    $gaSql['db']         = "";
    $gaSql['server']     = "localhost";
    
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * 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
     */
    $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 ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
            mysql_real_escape_string( $_GET['iDisplayLength'] );
    }
    
    
    /*
     * Ordering
     */
    if ( isset( $_GET['iSortCol_0'] ) )
    {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
        {
            if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
            {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
            }
        }
        
        $sOrder = substr_replace( $sOrder, "", -2 );
        if ( $sOrder == "ORDER BY" )
        {
            $sOrder = "";
        }
    }
    
    
    /* 
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    $sWhere = "";
    if ( $_GET['sSearch'] != "" )
    {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }
    
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
        }
    }
    
    
    /*
     * SQL queries
     * Get data to display
     */
    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
        FROM   $sTable
        $sWhere
        $sOrder
        $sLimit
    ";
    $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    
    /* Data set length after filtering */
    $sQuery = "
        SELECT FOUND_ROWS()
    ";
    $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];
    
    /* Total data set length */
    $sQuery = "
        SELECT COUNT(".$sIndexColumn.")
        FROM   $sTable
    ";
    $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
    $aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];
    
    
    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );
    
    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $aColumns[$i] == "version" )
            {
                /* Special output formatting for 'version' column */
                $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
            }
            else if ( $aColumns[$i] != ' ' )
            {
                /* General output */
                $row[] = $aRow[ $aColumns[$i] ];
            }
        }
        $output['aaData'][] = $row;
    }
    
    $obj = json_encode( $output );
    echo $obj;
?>

Answer 1

James, the way you’re doing what happens is this;

1. You make a request on the server
2. You search all 5k records in the database
3. You render a gigantic HTML with all the thousands of records
4. The browser receives the data and spends a lot of time applying Datatables to all these records

First bottleneck: Item 3 - The traffic of so much data may be increasing the time until the page is ready

Second bottleneck: Item 4 - The time to apply Datatables is probably making 90% of this time.

PS.: Possible bottleneck in item 2 depending on how your application is deployed

The fact is, very hardly the user will consume 5k records every time they open their page, so the best approach in my opinion is;

1. You receive a request and return a basically empty page, containing only the empty HTML table structure, where Voce will apply Datatables
2. As soon as the browser receives the HTML and the page is ready (in $(function() {}); for example) you call Datatables by passing the parameters to use their native paging, i.e.:

$('table.dataTable').DataTable({
    displayLength: 15, //Começaremos com apenas 15 registros
    serverSide: true,  //Quem vai tratar a interação com a tabela é o servidor
    paginate: true,    //Queremos paginas
    filter: true,      //Queremos que o usuário possa procurar entre os 5k registros
    ajax: {
        url: 'filtragemDeRegistros.php' //O seu script que vai filtrar os dados do lado do servidor
    }
});

It turns out that now you need to implement filtering logic in the backend. To find out which parameters Datatables passes to the server you can search the documentation on the official DT page or simply print the request sent on the server console.

You’ll spend a little time implementing but it’s worth it and the app will load much faster.

Ps.: The datatables doc teaches in detail all this, spend time reading!

EDIT An example of server side processing taken directly from datatables documentation:

<?php
 
/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */
 
// DB table to use
$table = 'datatables_demo';
 
// Table's primary key
$primaryKey = 'id';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'first_name', 'dt' => 0 ),
    array( 'db' => 'last_name',  'dt' => 1 ),
    array( 'db' => 'position',   'dt' => 2 ),
    array( 'db' => 'office',     'dt' => 3 ),
    array(
        'db'        => 'start_date',
        'dt'        => 4,
        'formatter' => function( $d, $row ) {
            return date( 'jS M y', strtotime($d));
        }
    ),
    array(
        'db'        => 'salary',
        'dt'        => 5,
        'formatter' => function( $d, $row ) {
            return '$'.number_format($d);
        }
    )
);
 
// SQL server connection information
$sql_details = array(
    'user' => '',
    'pass' => '',
    'db'   => '',
    'host' => ''
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

EDIT:

To use render to generate an image you will need to place a "Columns field":

var oTable1 = $('#tabela-agenda').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "cadastroCarregaRegistros.php",
    columns: [
        {
            data: "foto", //Nome do campo no JSON, que contem a url da foto
            render: function(value) {
                return '<img src="http://seuendereco.com/imagens/' + value + '"/>';
            }
        }
    ]
});

OBS: Replace the src of the image properly.

I think Voce will need to define an object of this for each column, do not know if you can send the rest. takes the test that I suggested and answers me there in chat