Slow response using serverSide and scroller for 200k rows

qqsmile

Hello,

I am currently using the newest DataTables with a huge mysql database (has around 5 millions of rows, 10+ columns).
I initialize my DT by

$(document).ready(function(){
    $('#detailTable').DataTable( {
        serverSide: true,
        ordering: false,
        searching: false,
        "ajax": {
            'type': 'POST',
            'url': 'getResult.php',
            'data': function(d){
                 d.cellline = getCellline();
              },
        },
        "columns": [
            { "data": "id" },
            { "data": "chrom" },
            { "data": "start" },
            { "data": "end" },
            { "data": "strand" },
            { "data": "size" },
            { "data": "method" },
            { "data": "cellline" },
            { "data": "tissue" },
            { "data": "specie" },
            { "data": "genome" }
        ],
        "processing": true,
        "language": {
            processing: '<i class="fa fa-spinner fa-spin fa-5x fa-fw" style="position:fixed; z-index: 10; top:600px; font-size: 6em; color: #39c0bc;"></i>'},
        scrollY: 300,
        scroller: {
            // loadingIndicator: false
        },
        stateSave: true
    } );
});

Whenever a user submits a form, I get the new constraint using the following function:

function getCellline(){
    return queryCellline;
}

and reload my DT by

$('#detailTable').DataTable().ajax.reload();

I load all the queried rows with the following server side script
```
<?php
ini_set('memory_limit','-1');

$cellline = $_POST['cellline'];
$table = 'my_table';
$primaryKey = 'id';

$columns = array(
    array( 'db' => 'id',         'dt' => 'id' ),
    array( 'db' => 'chrom',      'dt' => 'chrom' ),
    array( 'db' => 'start',      'dt' => 'start' ),
    array( 'db' => 'end',        'dt' => 'end' ),
    array( 'db' => 'strand',     'dt' => 'strand' ),
    array( 'db' => 'size',       'dt' => 'size' ),
    array( 'db' => 'method',     'dt' => 'method' ),
    array( 'db' => 'cellline',   'dt' => 'cellline' ),
    array( 'db' => 'tissue',     'dt' => 'tissue' ),
    array( 'db' => 'specie',     'dt' => 'specie' ),
    array( 'db' => 'genome',     'dt' => 'genome' )
);

$sql_details = array(
    'user' => '*',
    'pass' => '*',
    'db'   => '*',
    'host' => '*'
);
$whereResult = null;
$whereAll = "cellline='".$cellline."'";
require( 'ajax/dataTables/DataTables-1.10.21/examples/server_side/scripts/ssp.class.php' );

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, $whereResult, $whereAll)
);

<?php > ``` The problem is it requires about 8 minutes to load the primary data or scroll down for querying results of about 200k rows (30 seconds for 20k rows). I have played with my database and the above two queries cost only 7 and 0.87 seconds when directly running in mysql. ?>

I'm not sure if it's possible to load the json in batches if it's too time-consuming to load the complete data. Or is there any other possible option to speed up the reloading (Not limited to the Scroller mode)?

Has anyone work with this amount of data but still had a satisfactory speed? If so, please give me some hint on how to improve the performance.

Thanks in advance!

colin

It sounds like you're not complying with the serverSide protocol - you should only be receiving the records required for the page (or window of data as you're using Scroller). Even with millions of records, you should only be getting sent a handful of records on each request.

The protocol is discussed here. Also see examples here - if you look at the Ajax tab you can see what's being requested and received.

Cheers,

Colin

qqsmile

I have figured out this issue. Thank you so much Colin!