Datatables takes more time to load after adding SearchPanes

Shivani Vyas

Hello,

After adding SearchPanes Button on my Page , page taking too much time to load. It is very confusing for users who can easily assume from delay that page isn't working. But its actually working fine. I have around 30k rows data using ajax call , server side.

Please check my page here - https://sandpit.assettrack.cx/asset/displayAssets_ss.php

Please advice how I can reduce the delay or add some loading message or icon to let user know that page is loading and working perfectly fine.

My Code

     var table = $('#assyntCx_Table').DataTable( {

        dom: 'lBfrtip',


        processing: true,
        ajax: {
          url: "../ajax/at/assets_ss.php",
          type: "POST",
          deferRender: true,
        },

        //TABLE WINDOW
        // "65vh",
        scrollY:        '600px', 
        scrollX:        true,
        scrollCollapse: true,
        paging:         true,
        fixedColumns:   {
          left: 2
        },


        columns: [
                  // All Columns
            ],

        columnDefs: [
                 { "visible": false,  "targets": [ 0 ] },

                {
                    searchPanes: {

                      show: true,
                    },
                    targets: [3,4,5,6,7,8,10,12,13,14,15]

                    //[1,2,9,11,16,17,18,19,20,21,22,23]
                }

        ],

        serverSide: true,

        //SHOW SELECTION & SAVE PREVIOUS STATE
        select: true,
        //stateSave: false,

        //DISABLE AUTOWIDTH
        "autoWidth" : false,

        //BUTTONS
        buttons: [

            {
            extend: 'searchPanes',
            config: {
                cascadePanes: true
            },
          }
        ],

        //PAGINATION OPTIONS
        pageLength: 250,
        lengthMenu: [[50, 100, 250, 500, -1], [50, 100, 250, 500, "All"]],


        // Search Select Input Dropdwon

        initComplete: function() {

            this.api().columns().every(function () {
                        var column = this;

                        var select = $('<select><option value=""></option></select>')
                            .appendTo($(column.footer()).empty())
                            .on('change', function () {
                                var val = $.fn.dataTable.util.escapeRegex($(this).val());

                               // column.search(val ? '^' + val + '$' : '', true, false).draw();
                                column.search($(this).val()).draw();
                            });

                        column
                            .data()
                            .unique()
                            .sort()
                            .each(function (d, j) {
                                //console.log(d);
                                select.append('<option value="' + d + '">' + d + '</option>');
                            });


                    });

                    this.api().columns.adjust().draw();

        }



      } );

Controller file:

    <?php


    include("../lib/DataTables.php");

    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Join,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions,
        DataTables\Editor\SearchPaneOptions;

    // Log function
    require("logchange.php");

    Editor::inst( $db, 'asset A', 'A.id' )
    ->field(
        //Static Fields - No Validation
        Field::inst( 'A.id', 'id' ),

        Field::inst( 'S.assetStatus', 'assetStatus' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'ST.assetTag', 'specTag' ),

        Field::inst( 'T.assetType', 'assetType' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'D.discipline', 'discipline' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'E.entityTLA', 'entity' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'L1.levelTLA', 'level' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'L.LocationName', 'loc' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'TR.statusTLA', 'PR' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'TR1.statusTLA', 'SR' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'SY.systemName', 'subsystem' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'A.Room', 'room' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

        Field::inst( 'B.buildingTLA', 'building' )
        ->searchPaneOptions(SearchPaneOptions::inst()),

    )

    ->debug(true)
    ->process( $_POST )
    ->json();
    ?>

colin

Where is the slowness? Is that on the server-side with the queries, or the rendering of the table on the client? If would be worth profiling it and seeing. Your test case doesn't load so we're unable to profile that.

Also, this section of the FAQ should help, it discusses various techniques to improve performance,

Cheers,

Colin

Shivani Vyas

@colin As per the FAQ I have added everything - paging, scrollx, scrolly, deferRender but still the same delay. Also slowness is not from server side or rendering of the table.. as before adding SearchPane .. the page was loading faster.. Here is the picture of devtool if its helpful

Shivani Vyas

@colin After trying few options available in FAQ I am still not able to solve this loading issue.

Here is the Link of my Page with SearchPane - which takes too much to load around 50000 ms.

https://sandpit.assettrack.cx/asset/displayAssets_ss.php

Here is the Link of my page without adding SearchPane - whih loads faster. So clearly there is no issue on the server-side with the queries, or the rendering of the table on the client.

https://test.assettrack.cx/asset/displayAssets_ss.php

Please wait 1-2 minutes when you check Page code with SearchPane. Its working but taking more time to load.

I am not sure whats wrong or what should I do as I need SearchPane on this page as per our new requirements. Please suggest.