my data table takes too long to load the data shown in laravel

emmanuel921218

How can I make this process speed up?, I share the code

//Data Table

var table =  $('#genealogy_tab').DataTable({
            "order": [[ 1, "asc" ]],
            "pageLength": 30,
            "stateSave": true,
            "processing": true,
            "serverSide": true,
            "ajax": "genealogy/{{ $ids }}",
            columns:[
              {data: 'Line'},
              {data: 'Level'},
              {data: 'associateid'},
              {data: 'associatename'},
              {data: 'Distributor_status'},
              {data: 'email'},
              {data: 'mobile_number'},
              {data: 'country'},
              {data: 'PinRank'},
              {data: 'PayRank'},
              {data: 'InactiveMonths'},
              {data: 'RenewalDate'},
            ],
            "language": {
                          "sProcessing":     "Processing...",
                          "sLengthMenu":     " _MENU_ ",
                          "sZeroRecords":    "No results found",
                          "sEmptyTable":     "Ningún dato disponible en esta tabla",
                          "sInfo":           "Showing records  _START_ of _END_ from a total of _TOTAL_ records",
                          "sInfoEmpty":      "Showing records 0 of 0 from a total of 0 records",
                          "sInfoFiltered":   "(filtrado de un total de _MAX_ registros)",
                          "sInfoPostFix":    "",
                          "sSearch":         "Search:",
                          "sUrl":            "",
                          "sInfoThousands":  ",",
                          "sLoadingRecords": "Loading Records...",
                          "oPaginate": {
                              "sFirst":    "First",
                              "sLast":     "Last",
                              "sNext":     "Next",
                              "sPrevious": "Previous"
                          },
                          "oAria": {
                              "sSortAscending":  ": Activar para ordenar la columna de manera ascendente",
                              "sSortDescending": ": Activar para ordenar la columna de manera descendente"
                          }
                      },

                        dom: 'Bfrtip',

                        buttons: [
                          {
                              extend:    'excelHtml5',
                              text:      'Excel',
                              titleAttr: 'Excel',
                              className: 'btn '
                          },
                          {
                              extend:    'csvHtml5',
                              text:      'CSV',
                              titleAttr: 'CSV',
                              className: 'btn '
                          },
                          {
                              extend:    'pdfHtml5',
                              orientation: 'landscape',
                              pageSize: 'LEGAL',
                              text:      'PDF',
                              titleAttr: 'PDF',
                              className: 'btn '
                          },
                          {
                              extend:    'print',
                              text:      'Print',
                              titleAttr: 'Print',
                              className: 'btn '
                          },
                           {
                                extend: 'colvis',
                                postfixButtons: [ 'colvisRestore' ]
                            }
                        ]
          });

//controller

public function genealogy(Request $request,$abi){
        $id = $abi;
        try {

            ini_set('memory_limit', '-1');
            $period='201909';

            $conection = \DB::connection('sqlsrvgenealogy');

            $data= $conection->select('EXEC Sp_TreePerId ?,?', array($id, $period));


            \DB::disconnect('sqlsrvgenealogy');

            return DataTables::of($data)->make(true);
            
        } catch (Exception $e) {

            echo($e->getMessage());
            
        }


//route

Route::get('/genealogy/{abi}', 'Controller@genealogy'); 

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

kthorngren

You have:

"pageLength": 30,
"stateSave": true,

EDIT:

            "processing": true,
            "serverSide": true,

The server should be returning only 30 rows of data for each page request. Is this the case? You can check it with the browser's developer tools by looking at the response in the Network tab.

Also you can see in the developer tools if the response time is long. Basically you need to take each step of the data request to the server, the server's time in fetching the request, the response time and the time it takes Datatables to render the table.

With 30 rows and 12 columns I would expect a short render time with Datatables. If you need further help then we would need a link to your page to help debug the problem.

Kevin

colin

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

Cheers,

Colin

emmanuel921218

I share the link http://mynikkentest.nikkenlatam.com/ you must enter with the id 7087000 the data table is in the genealogy option.

I hope you can help me.

kthorngren

The problem seems to be with slow server response. I had sporadic issues with not receiving a response. The few times I did it took at 2 minutes. Here is a screenshot:

The first line is the Datatables request for the page. Once the data is returned there are only 30 rows as expected and it doesn't take long for Datatables to populate the table.

You will need to look at your server to determine why it takes 2 minutes or longer for a response. This is not a Datatables client rendering issue.

Kevin

emmanuel921218

Cómo se pronuncia
If it were the server, it would delay the same when painting the table with the page of Laravel and changed when I paint it with Laravel it takes from 12 to 13 seconds

kthorngren

You are using ajax to fetch the data. The default mode of Ajax is asynchronous, meaning the page load will not be blocked while waiting for the ajax response. You can read more about this in the jQuery Ajax docs. Look for the async option.

You can see this for your self by opening the browser's developer tools. When you load the page you will see the ajax request to the server, the top line. You will see the response (Status column) is pending while waiting for the response. This technote provides steps for for this.

And if you happen to catch it when the response is received you will see that Datatables takes no time to load.

Kevin