Datatable is too long for a hundred rows

JujuPomme

Hey all,

As the title say, I load a hundred rows in my Datatable, and it takes about 20 seconds to display it, it's a bit long...

How can I improve it?

Here is my dataTable configuration :

$('a#historique').click(function() {
    var table_2 = $('#historique_datatable').DataTable( {
        "language": {
            "lengthMenu": "Voir _MENU_ enregistrements par page",
            "sInfo": "Résultat des _START_ sur _END_ enregistrement sur un total de _TOTAL_",
            "sSearch": "Rechercher",
            "oPaginate": {
                "sFirst": "Premier",
                "sPrevious": "Précédent",
                "sNext": "Suivant",
                "sLast": "Dernier"
            },
            "sEmptyTable": "Aucune donnée disponible à afficher"
        },
        "ajax": "_send_historic_in_json.php",
        "deferRender": true,
        "paging": true,
        "autoWidth": false,
        "columnDefs": [{
            targets: 0,
            width: '1%'
        },
        {
            targets: 1,
            width: '20%'
        },
        {
            targets: 2,
            width: '15%'
        },
        {
            targets: 3,
            width: '10%'
        },
        {
            targets: 4,
            width: '15%'
        },
        {
            targets: 5,
            width: '34%',
            "visible": false
        }],
        "responsive": {
            "details": {
                "display": $.fn.dataTable.Responsive.display.modal(),
                "renderer": function (api, rowIdx, columns) {
                    var data = $.map(columns, function(col, i) {
                        return ('<tr>' +
                                '<td>'+col.title+'</td> ' +
                                '<td>'+col.data+'</td>'+
                                '</tr>');
                    } ).join('');
                    return $('<table/>').append(data);
                }
            }
        },
        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            { "data": "NOM" },
            { "data": "TYPE" },
            { "data": "DESTINATAIRE" },
            { "data": "DATE_REALISE" },
            { "data": "DETAIL" }
        ],
        "order": [[4, 'desc']]
    } );
    $('a#historique').off('click');
    $('a#historique').on( 'click', function () {
        table_2.ajax.reload();
    } );

I really need a help for this one, I don't understand why.

Thanks a lot,

allan

Thanks for your question - however, per the forum rules can you link to a test case showing the issue please. This will allow the issue to be debugged.

Information on how to create a test page, if you can't provide a link to your own page can be found here.

Thanks,
Allan

JujuPomme

Allan,

I'd like to provide you a test case, but I can't insert my php files and it's a part of a dashboard running localy. The most important here is my ajax request who take too much time. How can I give you that informations?

I would like to implement the server side datatable's function, but i'm sorry, i'm french and I don't understand everything here. Do you have a simply exampe similar to my datatable initialization?

Thanks a lot for all your answers, you're a great man.

Rgds,

JujuPomme

I've one more question. What is the difference between "sAjaxSource" and "Ajax"? "bServerSide" and "serverSide"? And so on...

allan

Do you have a simply exampe similar to my datatable initialization?

This example uses deferred rendering.

The key question is how long does your Ajax file take to load. You can us your browser's developer tools to find that out. You can also use them to profile the Javascript.

Allan

JujuPomme

Thanks for your answer.
My ajax file take about 25 seconds to load 1000 rows (I've limited to 1000 in my sql request because it was too long for 3xxx, I had a timeout...)

I follow this example, but nothin change. You have my datatable configuration in my first post.
+ with the serverSide option and the processing option, I lost the pagination. Why?

JujuPomme

Maybe that could help you :

When I run my page with the serverSide and processing options, I had this message :

"Un script sur cette page est peut-être occupé ou ne répond plus. Vous pouvez arrêter le script maintenant, l'ouvrir dans le débogueur ou le laisser continuer.
Script: https://cdn.datatables.net/t/dt/dt-1.10.11,kt-2.1.1,r-2.0.2,rr-1.1.1/datatables.min.js:114"

And Firefox ask me if I want to continue or to debug the script...

allan

My ajax file take about 25 seconds to load 1000 rows

That's an amazing about of time to simply get 1000 rows. I would very strongly suggest that you look at improving whatever server-side script you are using there. I would expect it to be less than 0.5 seconds to just get 1000 rows.

with the serverSide option and the processing option, I lost the pagination. Why?

Its impossible to say without a link to a test page I'm afraid.

Allan

JujuPomme

Thanks for your reply,

I'll try to implement the server-side option.

Here's my .php file, call by the "ajax" option in my datatable :

<?php
    // include
    require '_inc_config.php';
    

    $contact_id = $_SESSION['contact_id'];
    // Récupère l'historique du contact
    $data3 = $phpclass->getHistory($contact_id);
    
    
    $toJson = '{"data":';
    $toJson .= $data3;
    $toJson .= '}';
    
    echo $toJson;

<?php
>
?>

It's an easy file so...
Do you have any idea about the error in the script? It could be because I've a bad implementation about the server-side script I think?...

Thanks a lot for your answers,

Rgds

allan

$phpclass->getHistory($contact_id);

I don't know what that code triggers, so I'm afraid I can't comment there. It would be worth trying to profile your PHP to see what is happening. I would also suggest you use json_encode() in PHP to create a JSON string rather than trying to create your own.

Allan

JujuPomme

It my sql request, here :

public function getHistory($contact_id) {
        $ret = array();
        $_HISTORY_CHAMPS = 'somedatas';
        $sql = "select first(1000) ".$_HISTORY_CHAMPS." from historique h where h.data like '".$contact_id."' and h.date > CAST('NOW' as DATE) - 1500 order by h.date desc";
        
        $_array_bind = array();
        $results = DB::getInstance()->fetchAll($sql, $_array_bind );
        if (isset($results) && !empty($results)) {
            foreach ($results as $key => $val) :
            
            $ret[] = array('NOM'=>trim(utf8_encode($val['data'])), 'TYPE'=>trim(utf8_encode($val['data'])), 'DEST'=>trim(utf8_encode($val['data'])), 'DATE'=>trim(utf8_encode($val['data'])),'DETAIL'=>@rtf2text(trim(utf8_encode($val['data']))));
            
            endforeach;
        }
        return json_encode($ret);
    }

You imagined that I change the important part of the request so don't be afraid.

I add the { "data: .... } to respect the json the datatable want. (I don't know if it's really english ...)

Thanks again and again for your answers, I know I could be boring... and I apologize.

Rgds,

allan

I don't see anything particularly obvious in the above code, but if it is taking 25 seconds to run then there is an issue. You would need to profile it to be able to identify where the issue is. I'm afraid there is little other help I can offer since I'm really just guessing without being able to see the issue.

Allan

JujuPomme

I have an idea, maybe stupid but... in my server side implementation.

I guess that when datatable want to load my datas, it load it like that :

Line 1

Line 1-2

Line 1-2-3

Line 1-2-3-4 ...

and so whereas I would like :

Line 1
Line 2
Line 3
Line 4...

One more question, the implementation of the ssp.class.php is mandatory?

JujuPomme

One more thing Allan,

I tried in _send_historic.php file (I post it before) to return an array I have taken in your example, and datatable load it imediatly.

Maybe because the json that I return is missing parameters?

(Sorry for my english's mistakes...)

Thanks again.

allan

the implementation of the ssp.class.php is mandatory?

No. You can write your own. The parameters are documented here.

Allan

JujuPomme

I'm a bit lost, I apologize.

I tried something like that :

```
<?php
require '_inc_config.php';

$table = 'ACTIONS';

$primaryKey = 'AC_CODE';

$columns = array (
    array( 'db' => 'AC_NOM_ACTION', 'dt' => 0),
    array( 'db' => 'AC_TYPEACTION', 'dt' => 1),
    array( 'db' => 'AC_UTILISATEUR_DEST', 'dt' => 2),
    array( 'db' => 'AC_DATE_PREVU', 'dt' => 3),
    array( 'db' => 'AC_DETAIL', 'dt' => 4),
);

$sql_details = array(
    'user' => 'user',
    'pass' => 'pass',
    'db' => 'C:\db.fdb',
    'host' => '127.0.0.1'
);


require ('ssp.class.php');

echo json_encode(
SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns)
);

<?php > ``` ?>

But it doesn't work, :

An error occurred while connecting to the database. The error reported by the server was: SQLSTATE[HY000] [1045] Access denied for user 'user'@'localhost' (using password: YES)

Whereas my connection to the database is correct. I'm using Firebird.

Have I to send all the parameters?

Thanks

allan

I'm using Firebird.

You would need to update my demo script to work with Firebird. It is designed for use with MySQL.

Allan

JujuPomme

Which one?

Is there an example somewhere?

Julien,

allan

The SSP class that you are using. I don't have any demos for Firebird database connections.

JujuPomme

But now, with PDO on Firebird, the implementation is really different?

allan

I honestly couldn't say - I'm not familiar with Firebird. This is more of a general PHP issue, so you might be better asking on StackOverflow or similar.

JujuPomme

Thanks Allan for your answers, very sympathic, as always !

JujuPomme

Here is where am I actually :

My datatable display 1000 hundred rows (in my select, I delimited to 1000), whereas I have "Résultat des 1 sur 10 enregistrement sur un total de 50 (filtered from 25 total entries)" that is absolutely dumb.
So I lost the pagination, the filtering and sorting options. Do you have any idea?

allan

It sounds like the required parameters for server-side processing are not being returned correctly from the client.

However, once again, without a link to a test page showing the issue I'm guessing. I'm afraid I won't be able to reply to this thread again without a link to a test case.

Allan

JujuPomme

'cause i can't provide you that. It's not that I don't want... If I can help you to answer me, of course I will do it.

I think that everything is working fine now, 3800 rows for 0.535s is better?

allan

I would say around 0.5s for 3800 is around about what I would expect. Its certainly a lot better than your original 20 seconds - well done :-)