Datatables slow [closed]

Question

Datatables slow [closed]

Navigation

#1 by (8 votes)

4

I'm using datatables, but it takes a long time to load with many records. Is it possible to improve this loading of 5,985 records?

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>

cadastroCarrega Registrations.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;
?>

jquery jquery-datatables

asked by anonymous 28.11.2014 / 16:30

1 answer

Keep line in SVG always connecting two objects How to add jQuery data in a table?

score 8 · Accepted Answer

James, the way you're doing what happens is as follows;

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 the DataTables to all those 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 the DataTables is probably 90% of this time.

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

The fact is, very rarely will the user consume 5k records every time they open your 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 you will apply the DataTables
2 - As soon as the browser receives the HTML and the page is ready (in $(function() {}); for example) you call the DataTables passing the parameters to use the native pagination of them, that is to say:

$('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 you now need to implement filtering logic in the backend. For you to find out what parameters the 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 deploying but it's worth it and the app will load much faster.

Ps: The datatables doc teaches you all of this, spend some 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 put 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 src of image properly.

I think you'll need to define an object of this for each column, I do not know if you can issue the rest. do the test with what I suggested and answer me there in chat