Datatables very slow

Rappi

Hi Allan.

I've already searched the forum for solutions, but unfortunately could not find anything suitable.

For a table, the display takes to be about 10 seconds to display all data records. There are 769 records in the database.

I can not deliver a debug, because the browser 4 times a timeout signals and the data is not stored on your server (loop).
Unfortunately I can not allow access because it is real data in a password-protected system.

Why is the query so slow?
Where can I optimize access?

Rappi

Rappi

Here my JS Part 1

    (function($){

    $(document).ready(function() {
    
    $.fn.dataTable.moment( 'DD.MM.YYYY' );
    
    var table=$('#tm_tiere').DataTable( {
    serverSide: false,
    
    dom: '<"clear"><"bRight"B><"clear"><"bLeft"l>frtip',
    colReorder: true,
    stateSave: true,
    buttons: [

            {
                extend: 'colvis',
                collectionLayout: 'fixed three-column',
                postfixButtons: [ 'colvisRestore' ],
                columns: ':not(:first-child)'
            }
            
            
        ],

        language: {
            buttons: {
                colvis: "Anzuzeigende Spalten",
                colvisRestore: "Zurücksetzen"
            },
            paginate: {
                first:      "Erste",
                last:       "Letzte",
                next:       "Weiter",
                previous:   "Zurück"
            },
            search: "Suchen:",
            info: "Zeige _START_ bis _END_ von _TOTAL_ Einträgen",
            infoEmpty:      "Zeige 0 bis 0 von 0 Einträgen",
            decimal:        ",",
            thousands:      ".",
            lengthMenu:     "Zeige _MENU_ Einträge",
            loadingRecords: "Loading...",
            processing:     "Bitte warten...",
            zeroRecords:    "Keine Einträge gefunden"
        },
        
        "info": true,
        "searching": true,
        "responsive": false,
        
        "bAutoWidth": true,
        
        "sPaginationType": "full_numbers",
    "scrollX": true,
    

    
        columnDefs: [
            {
                targets: [ 0 ],
                visible: true,
                searchable: false
            },
            {
                targets: [ 1,2,3,4,5,6,8,9,14,49 ],
                visible: true,
                searchable: true
            },
            {
                targets: [ 7,10,11,12,13,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,50,51,52,53,54,55,56 ,57],
                visible: false,
                
                searchable: true
            }
        ],

    

        ajax: "tiere_processing.php",
        
        columns: [
            {
                data: "tm_tiere.id"
            },
            {
                data: "tm_tiere.lfdnr"
            },
            {
                data: "tm_tiere.Name"
            },
            {
                data: "tm_tiere.Aufnahmedatum",
                render: function ( data, type, row ) {
                    var rowvalueallday = row.tm_tiere.Aufnahmedatum;
                    
                      if (rowvalueallday == '0000-00-00') {
                        var gdat = '0000-01-01';
                        return (moment(gdat).format("DD.MM.YYYY"));
                      } else {
                        return (moment(data).format("DD.MM.YYYY"));
                      }       
                       
                }
                
            },
            {
                data: "tm_tiere.Aufnahmegrund"
            },
            {
                data: "tm_tiere.Woher"
            },
            {
                data: "tm_tierart.Art"
            },
            {
                data: "tm_tiere.Rasse"
            },
            {
                data: "tm_tiere.Geburtsdatum",
                render: function ( data, type, row ) {
                    var rowvalueallday = row.tm_tiere.Geburtsdatum;
                    
                      if (rowvalueallday == '0000-00-00') {
                        var gdat = '0000-01-01';
                        return (moment(gdat).format("DD.MM.YYYY"));
                      } else {
                        return (moment(data).format("DD.MM.YYYY"));
                      }       
                       
                }
            },
            {
                data: "tm_tiere.Age"
            },
            {
                data: "tm_tiere.Geschlecht"
            },
            {
                data: "tm_tiere.Kastriert"
            },
            {
                data: "tm_tiere.Laeufig",
                render: function ( data, type, row ) {
                    var rowvalueallday = row.tm_tiere.Laeufig;
                    
                      if (rowvalueallday == '0000-00-00') {
                        var gdat = '0000-01-01';
                        return (moment(gdat).format("DD.MM.YYYY"));
                      } else {
                        return (moment(data).format("DD.MM.YYYY"));
                      }       
                       
                }
            },
            {
                data: "tm_tiere.Farbe"
            },
            {
                data: "tm_aufenthaltsort.Ort"
            },
            {
                data: "tm_tiere.Intra"
            },
            {
                data: "tm_tiere.Tasso"
            },
            {
                data: "tm_tiere.DHR"
            },
            {
                data: "tm_tiere.Chip1"
            },
            {
                data: "tm_tiere.Chiport1"
            },
            {
                data: "tm_tiere.Chip1wann",
                render: function ( data, type, row ) {
                    var rowvalueallday = row.tm_tiere.Chip1wann;
                    
                      if (rowvalueallday == '0000-00-00') {
                        var gdat = '0000-01-01';
                        return (moment(gdat).format("DD.MM.YYYY"));
                      } else {
                        return (moment(data).format("DD.MM.YYYY"));
                      }       
                       
                }
            },
            {
                data: "tm_tiere.Chip2"
            },
            {
                data: "tm_tiere.Chiport2"
            },
            {
                data: "tm_tiere.Ringnummer"
            },

Rappi

Part 3

            {
                data: "tm_tiere.Krankheiten"
            },
            {
                data: "tm_tiere.Besonderheiten"
            },
            {
                data: "tm_pflegestellen.Name"
            },
            {
                data: "tm_adoptanten.Name"
            },
            {
                data: "tm_tiere.Vermitteltdatum",
                render: function ( data, type, row ) {
                    var rowvalueallday = row.tm_tiere.Vermitteltdatum;
                    
                      if (rowvalueallday == '0000-00-00') {
                        var gdat = '0000-01-01';
                        return (moment(gdat).format("DD.MM.YYYY"));
                      } else {
                        return (moment(data).format("DD.MM.YYYY"));
                      }       
                       
                }
            },
            {
                data: "tm_paten.Name"
            },
            {
                data: "tm_tiere.Image"
            },
            {
                data: "tm_tiere.Verstorben",
                render: function ( data, type, row ) {
                    var rowvalueallday = row.tm_tiere.Verstorben;
                    
                      if (rowvalueallday == '0000-00-00') {
                        var gdat = '0000-01-01';
                        return (moment(gdat).format("DD.MM.YYYY"));
                      } else {
                        return (moment(data).format("DD.MM.YYYY"));
                      }       
                       
                }
            },
            {
                data: "tm_tiere.Gestorben",
                render: function ( data, type, row ) {
                    if ( type === 'display' ) {
                        return '<input type="checkbox" class="editor-gestorben">';
                    }
                    return data;
                },
                classname: "dt-body-center"
            },
            {
                data: "tm_tiere.Quarantaene",
                render: function ( data, type, row ) {
                    if ( type === 'display' ) {
                        return '<input type="checkbox" class="editor-quarantaene">';
                    }
                    return data;
                },
                classname: "dt-body-center"
            },
            {
                data: "tm_tiere.Quarantaene_Datum",
                render: function ( data, type, row ) {
                    var rowvalueallday = row.tm_tiere.Quarantaene_Datum;
                    
                      if (rowvalueallday == '0000-00-00') {
                        var gdat = '0000-01-01';
                        return (moment(gdat).format("DD.MM.YYYY"));
                      } else {
                        return (moment(data).format("DD.MM.YYYY"));
                      }       
                       
                }
            },
            {
                data: "tm_tiere.Schutzgebuehr",
                render: function ( data, type, row ) {
                    if ( type === 'display' ) {
                        return '<input type="checkbox" class="editor-schutzgebuehr">';
                    }
                    return data;
                },
                classname: "dt-body-center"
            },
            {
                data: "tm_tiere.Vertrag",
                render: function ( data, type, row ) {
                    if ( type === 'display' ) {
                        return '<input type="checkbox" class="editor-vertrag">';
                    }
                    return data;
                },
                classname: "dt-body-center"
            },
            {
                data: "tm_tiere.Ansprechpartner"
            },
            {
                data: "tm_tiere.Telefon"
            },
            {
                data: "tm_tiere.Handy"
            },
            {
                data: "tm_tiere.Email"
            },
            {
                data: "tm_tiere.Uhrzeit"
            },
            {
                data: "tm_tiere.Beschreibung"
            },
            {
                data: "tm_tiere.Homepage",
                render: function ( data, type, row ) {
                    if ( type === 'display' ) {
                        return '<input type="checkbox" class="editor-homepage">';
                    }
                    return data;
                },
                classname: "dt-body-center"
            }
        ],


         rowCallback: function ( row, data ) {
            if ( data.tm_tiere.Gestorben == 1 )  {
                    //$(row).addClass( 'hell' );
                    $('td', row).css('color', 'blue');
            }
            if ( data.tm_tiere.Verstorben != '0000-00-00' )  {
                    //$(row).addClass( 'hell' );
                    $('td', row).css('color', 'blue');
            }
            if ( data.tm_tiere.Quarantaene == 1 )  {
                    //$(row).addClass( 'hell' );
                    $('td', row).css('color', 'red');
            }
            // Set the checked state of the checkbox in the table
            $('input.editor-stubenrein', row).prop( 'checked', data.tm_tiere.Stubenrein == 1 );
            $('input.editor-verhaltensgestoert', row).prop( 'checked', data.tm_tiere.Verhaltensgestoert == 1 );
            $('input.editor-kinderfreundlich', row).prop( 'checked', data.tm_tiere.Kinderfreundlich == 1 );
            $('input.editor-haeuslich', row).prop( 'checked', data.tm_tiere.Haeuslich == 1 );
            $('input.editor-angstbeisser', row).prop( 'checked', data.tm_tiere.Angstbeisser == 1 );
            $('input.editor-vertraeglich', row).prop( 'checked', data.tm_tiere.Vertraeglich == 1 );
            $('input.editor-fiv', row).prop( 'checked', data.tm_tiere.FiV == 1 );
            $('input.editor-fivp', row).prop( 'checked', data.tm_tiere.FiVp == 1 );
            $('input.editor-fip', row).prop( 'checked', data.tm_tiere.FiP == 1 );
            $('input.editor-fipp', row).prop( 'checked', data.tm_tiere.FiPp == 1 );
            $('input.editor-felv', row).prop( 'checked', data.tm_tiere.FelV == 1 );
            $('input.editor-felvp', row).prop( 'checked', data.tm_tiere.FelVp == 1 );
            $('input.editor-mmt', row).prop( 'checked', data.tm_tiere.MMT == 1 );
            $('input.editor-mmtp', row).prop( 'checked', data.tm_tiere.MMTp == 1 );
            $('input.editor-tollwut', row).prop( 'checked', data.tm_tiere.Tollwut == 1 );
            $('input.editor-tollwutp', row).prop( 'checked', data.tm_tiere.Tollwutp == 1 );
            $('input.editor-vertrag', row).prop( 'checked', data.tm_tiere.Vertrag == 1 );
            $('input.editor-schutzgebuehr', row).prop( 'checked', data.tm_tiere.Schutzgebuehr == 1 );
            $('input.editor-homepage', row).prop( 'checked', data.tm_tiere.Homepage == 1 );
            $('input.editor-gestorben', row).prop( 'checked', data.tm_tiere.Gestorben == 1 );
            $('input.editor-quarantaene', row).prop( 'checked', data.tm_tiere.Quarantaene == 1 );
        }
    } );
    $('#reset').click( function (e) {
        e.preventDefault();
         
        table.colReorder.reset();
    } );
        
   $('#tm_tiere tbody').on('click', 'tr', function () {
     
       var id = $('td', this).eq(0).text();
        window.location = "tiere_show.php?id="+id+"&action=show";
    } );    
   } );

    var original_init = $.fn.dataTable.ext.buttons.columnVisibility.init;
     $.fn.dataTable.ext.buttons.columnVisibility.init =
    function (dt, button, conf) {
      var that = this;
      original_init.call(this, dt, button, conf);
      dt.off('column-reorder.dt' + conf.namespace);
      dt.on('column-reorder.dt' + conf.namespace,
        function (e, settings, details) {
          var col = dt.column(conf.columns);
          var btn = button.children() ? button.children() : button;
          btn.text(conf._columnText(dt, conf.columns));
          that.active(col.visible());
        });
    };  
     }(jQuery));

Rappi

and the PHP script

require_once("models/config.php"); 

/*
 * Editor server script for DB table tiere
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "classes/DataTables.php" );

$db->sql("SET character_set_client=utf8");
$db->sql("SET character_set_connection=utf8");
$db->sql("SET character_set_results=utf8");


// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;


// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'tm_tiere', 'id' )
    ->fields(
        Field::inst( 'tm_tiere.id' ),
        Field::inst( 'tm_tiere.lfdnr' ),
        Field::inst( 'tm_tiere.Geloescht' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Name' )
            ->validator( 'Validate::notEmpty' ),
        Field::inst( 'tm_tiere.Aufnahmedatum' ),
        Field::inst( 'tm_tiere.Aufnahmegrund' ),
        Field::inst( 'tm_tiere.Woher' ),
        Field::inst( 'tm_tiere.Tierart' )
            ->options( 'tm_tierart', 'id', 'Art' ),
        Field::inst( 'tm_tierart.Art' ),
        Field::inst( 'tm_tiere.Rasse' ),
        Field::inst( 'tm_tiere.Geburtsdatum' ),
        Field::inst( 'tm_tiere.Age' ),
        Field::inst( 'tm_tiere.Geschlecht' ),
        Field::inst( 'tm_tiere.Kastriert' ),
        Field::inst( 'tm_tiere.Laeufig' ),
        Field::inst( 'tm_tiere.Farbe' ),
        Field::inst( 'tm_tiere.Aufenthaltsort' )
            ->options( 'tm_aufenthaltsort', 'id', 'Ort' ),
        Field::inst( 'tm_aufenthaltsort.Ort' ),
        Field::inst( 'tm_tiere.Intra' ),
        Field::inst( 'tm_tiere.Tasso' ),
        Field::inst( 'tm_tiere.DHR' ),
        Field::inst( 'tm_tiere.Chip1' ),
        Field::inst( 'tm_tiere.Chiport1' ),
        Field::inst( 'tm_tiere.Chip1wann' ),
        Field::inst( 'tm_tiere.Chip2' ),
        Field::inst( 'tm_tiere.Chiport2' ),
        Field::inst( 'tm_tiere.Ringnummer' ),
        Field::inst( 'tm_tiere.Stubenrein' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Verhaltensgestoert' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Kinderfreundlich' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Haeuslich' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Angstbeisser' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Vertraeglich' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Letzte_Impfung' ),
        Field::inst( 'tm_tiere.Naechste_Impfung' ),
        Field::inst( 'tm_tiere.Ausweis' ),
        Field::inst( 'tm_tiere.Ausweisnr' ),
        Field::inst( 'tm_tiere.Tierarzt' )
            ->options( 'tm_arzt', 'id', 'Name' ),
        Field::inst( 'tm_arzt.Name' ),
        Field::inst( 'tm_tiere.FiV' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.FiVp' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.FiP' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.FiPp' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.FelV' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.FelVp' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.MMT' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.MMTp' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Tollwut' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Tollwutp' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Tollwutdatum' ),
        Field::inst( 'tm_tiere.Tgueltigdatum' ),
        Field::inst( 'tm_tiere.Naechste_Entwurmung' ),
        Field::inst( 'tm_tiere.Entwurmungdatum' ),
        Field::inst( 'tm_tiere.Krankheiten' ),
        Field::inst( 'tm_tiere.Besonderheiten' ),
        Field::inst( 'tm_tiere.Pflegestelle' )
            ->options( 'tm_pflegestellen', 'id', 'Name' ),
        Field::inst( 'tm_pflegestellen.Name' ),
        Field::inst( 'tm_tiere.Adoptant' )
            ->options( 'tm_adoptanten', 'id', 'Name' ),
        Field::inst( 'tm_adoptanten.Name' ),
        Field::inst( 'tm_tiere.Vermitteltdatum' ),
        Field::inst( 'tm_tiere.Pate' )
            ->options( 'tm_paten', 'id', 'Name' ),
        Field::inst( 'tm_paten.Name' ),
        Field::inst( 'tm_tiere.Image' ),
        Field::inst( 'tm_tiere.Verstorben' ),
        Field::inst( 'tm_tiere.Gestorben' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Quarantaene' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Quarantaene_Datum' ),
        Field::inst( 'tm_tiere.Schutzgebuehr' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Vertrag' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'tm_tiere.Ansprechpartner' ),
        Field::inst( 'tm_tiere.Telefon' ),
        Field::inst( 'tm_tiere.Handy' ),    
        Field::inst( 'tm_tiere.Email' ),
        Field::inst( 'tm_tiere.Uhrzeit' ),
        Field::inst( 'tm_tiere.Beschreibung' ),
        Field::inst( 'tm_tiere.Homepage' )
        ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } )
    )
    
    ->where( 'tm_tiere.Geloescht', 0, '=')
    
     ->leftJoin( 'tm_tierart', 'tm_tierart.id', '=' , 'tm_tiere.Tierart' )
     ->leftJoin( 'tm_aufenthaltsort', 'tm_aufenthaltsort.id', '=' , 'tm_tiere.Aufenthaltsort' )
     ->leftJoin( 'tm_pflegestellen', 'tm_pflegestellen.id', '=' , 'tm_tiere.Pflegestelle' )
     ->leftJoin( 'tm_adoptanten', 'tm_adoptanten.id', '=' , 'tm_tiere.Adoptant' )
      ->leftJoin( 'tm_paten', 'tm_paten.id', '=' , 'tm_tiere.Pate' )
      ->leftJoin( 'tm_arzt', 'tm_arzt.id', '=' , 'tm_tiere.Tierarzt' )
    ->process( $_POST )
    ->json();

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.

My guess is that there are columns in your database that should be indexed.

Thanks,
Allan

Rappi

Hi Allan.

Thanks for your fast answer.

The trouble only occurs with more than 700 records. And I can not publish because they are subject to data protection.
A debug, as already written, is not possible because the data can not be sent to your server. The browser crashes when I make a debug!
Helps a debug of an installation with less data from another installation?

Rappi

allan

I'm primarily interested to profile the page to know where the slow down is coming from. Is the majority of the time the Ajax loading for the data for example? If so, then it is the database / server that needs to changed to improve performance (as I say, possibly by adding an index).

Allan