performance datatables more than 1000 item and 10 column

johnw75

Hi all,
i have the the scenario as follow:

datatables has been etablished in php.

<script type="text/javascript" language="javascript">
            $(document).ready(function() {
                var dataTable = $('#mitarbeiterliste-grid').DataTable( {
                    "columns": [
                                   { "width": "5%" , "orderable" : true },                                 
                                   { "width": "5%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "15%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true },
                                   { "width": "10%" , "orderable" : true }
                                 ],
                     "columnDefs": [
                                    {
                                        "targets": [8,10,11,12,13,14],
                                        "render": $.fn.dataTable.render.number('.', ',', 2, '')
                                    },                                  
                            
                                    { 
                                        "targets" : [0,1,5,6,7,8,9,10,11,12,13,14],
                                        "className" : "dt-center"  
                                        }
                                   ],
    
                     "paging" :         false,
                     "processing": true, 
                     "serverSide": true,
                     "Sort": false,
                     "SortClasses": false,
                     "DeferRender": true,
                     "language": {
                        "processing": "Waiting for response..." 
                      },
                     "searching": false,
                     "scrollY":         "600px",
                     "scrollCollapse" : true,
                     "scroller" : {
                         "loadingIndicator": true
                     },
                     "info" :           true,
                      "ajax": {
                    url:  "../listen/mitarbeiter_summen-grid-data.php",
                      "error": function() {
                            $(".mitarbeiterliste-grid-error").html("");
                            $("#mitarbeiterliste-grid").append('<tbody class="mitarbeiterliste-grid-error"><tr><th colspan="3">Error in processing</th></tr></tbody>');
                            $("#mitarbeiterliste-grid-processing").css("display","none");
                        }
                 }
            } );
        } );
</script>

mitarbeiter_summen-grid-data.php
---------------------------------------------
    
    $columns = array( 
    // datatable column index  => database column name
        0 => 'CompanyCode',
        ...
        14 => 'Total'           
    );

....
        
        
    $sql .= " ORDER BY ". $columns[$requestData['order'][0]['column']] ." ". $requestData['order'][0]['dir'];
    $data = $myMitarbeiterController->getMitarbeiterListeAsJSON($sql);

    $totalData = $myMitarbeiterController->getCount();
    $totalFiltered = $totalData;
     
    $json_data = array(
                "draw"            => intval( $requestData['draw'] ),   
                "recordsTotal"    => intval( $totalData ), 
                "recordsFiltered" => intval( $totalFiltered ), 
                "data"            => $data   
                );

    echo json_encode($json_data);  
-------------------

$data delivers more than 1000 items. it takes then at least 50-100 s. The query is quick, but the mapping takes a lot time.

snippet from query

------------------------
while ($row = sqlsrv_fetch_array( $result)) {
                    $nestedData=array();                    
additional 13 more items..
                    $nestedData[] = trim($row['Total']);
                    $data[] = $nestedData;
                    
                }
            }

Can any help me to solve that issue.

Thanks

rf1234

Take a look at this please: https://datatables.net/reference/option/deferRender

If you use Editor as well you could also use https://datatables.net/reference/option/serverSide

I am using server side processing for tables with more than 30,000 rows. Works great also for searching.

allan

You have server-side processing enabled, but paging disabled. That means that every request is still going to request every single row. And since server-side processing is enabled, it will make a request for every draw action.

Either disable server-side processing or enable paging.

50-100 seconds is very slow. You'd need to profile the PHP script to know why it is running so slowly. Only 1000 records really shouldn't take anything like as long as that.

Allan

johnw75

Hello Allan,T
hanks for the answer. In the meantime, I found the potential performance issue.
Without the renderer inserting 0 or 1 symbols, this is much faster. How it it possible to get a performant renderer?

Thanks for the reply rf1234. The deferRenderer has not brought any performance improvement.
Meanwhile, I have also incorporated a time measurement.
Once I have taken more than 1000 lines, the data is within 1 second of the database, but the data will not be displayed quickly. Even the paging does not bring any performance increase. (IE is till a problem).

Any ideas?

                "columnDefs": [                                     
                                { 
                                    "targets": [1],
                                    "render": function( data, type, row ) {
                                        return '<a href="page.php?method=ausreq&pers=' + data + '">' + data + '<a/>';
                                    }
                                },
                                {
                                    "targets": [8,10,12,14,16],
                                    "render": $.fn.dataTable.render.number('.', ',', 2, '')
                                },
                                { 
                                    "targets": [7,8,9,10,11,12,13,14,15,16,17,18,19],
                                    "className": "dt-center" 
                                },
                                { 
                                    "targets": [],
                                     "render": $.fn.dataTable.render.ellipsis(10)
                                },
                                {
                                    "targets": [5],
                                    "className" : "dt-left",
                                    "render": function( data, type, row ) {
                                                    return linkUrl(data);
                                    }
                                }

function linkUrl(data, type, row) {
$('.envelope_edit, .actionLinkImage, .actionLinkText, .envelope_edit_2').css( 'cursor', 'pointer' );

    if (data == 'Requested'){
        var e_URL = data + '<span style="margin-left:10px;"></span>' + '<?php
            if ($_SESSION ['util']->checkSecurity ( $change, $rollen )) {
                echo '<i class="fa fa-pencil-square envelope_edit" aria-hidden="true"></i>';
            }
            ?>';
        var d_URL = '<?php
            if ($_SESSION ['util']->checkSecurity ( $delete, $rollen )) {
                echo '<i class="fa fa-trash envelope_edit_2" aria-hidden="true"></i>';
            }
            ?>';        
    }

....

I understood the renderer will be called by each line.

Michael

rf1234

If rendering the data at the front end causes the issue there is a simple solution to this: Do the rendering on the server using PHP. I have never tried client side numbers and date rendering. I always do it on the server. Never had any performance issues even in a table with over 300,000 rows. (For that I use server side processing of course.)

If you are using Editor you can use get and set formatters for this.
https://editor.datatables.net/manual/php/formatters

If you don't like the standard formatters it is easy to build your own.