Slow loading when data more than 1000

[manelbenmiled]

The issue is that my laravel application speed's is too slow when the result has over 7k rows

It keep loading for at 23 seconds or more to show the data in a table (i'm using bootstrap model of datatable)

System details

• Operating System windows 10
• PHP Version 8.0.2
• Laravel Version 9.11

[marcelhoogantink]

Cause could be a complex query, or a sorting that issues the load of all the records in background.
If there is a sorting, are the columns indexed?
Also in the last version (10.x) the totals for the tables are calcutated twice (for total records AND for filtered records) even if there's no filter. When having a complex query this can slow down the speed dramaticly

[manelbenmiled]

my controller :

public function list(){
$file = File::orderBy('date', 'DESC')->get();
return view('welcome' , compact('file'));
}

Blade

@foreach($file as $key=>$f)
      //call to attributes
@endforeach

and i have more than 6000 row to show in the table.

[marcelhoogantink]

Is the 'date' column indexed in the database?
In the migration file should be a line like:

 $table->timestamp('date')->index();

or

 $table->datetime('date')->index();

[manelbenmiled]

yes i modified it to indexed but i'm facing the same problem yet.

[marcelhoogantink]

Is it a view of a single table or of mutiple joined tables.

[manelbenmiled]

just for a single table

[marcelhoogantink]

Do you have Debugbar active in Laravel and can you see see the duration of the query?

[yajra]

public function list(){
$file = File::orderBy('date', 'DESC')->get();
return view('welcome' , compact('file'));
}

I think you are not using the package, only DataTables client-side implementation. See this documentation for reference. Note that it's an old approach but the concept should be the same.

[manelbenmiled]

i found a solution with paginate function

public function list(){
$file = File::orderBy('date', 'DESC')->paginate(10);
return view('welcome' , compact('file'));
}

in welcome.blade.php i added
{{ $file->links() }}

it works with a load time 700 ms, but the search function of datatable works only on the current page not for the whole table what should i do please the deadline is today.

NB : i'm beginner with laravel and i didn't know how to manupilate a table with ajax, thanks @yajra for your advise but i didn't succeed to work with your solution.

[yajra]

If you want to use serverSide, you can use something like the below:

public function list(){
  if (request()->ajax()) {
    $file = File::query();

    return datatables()->eloquent($file)->toJson();
  }

  return view('welcome');
}

// view
<table id="files-table">
<thead>
  <tr>
    <th>ID</th>
    <th>Name</th>
  </tr>
</thead>
</table>

// js
$('#files-table').DataTable({
  processing: true,
  serverSide: true,
  ajax: '{{ route('files.list') }}',
  columns: [
    {data: 'id'},
    {data: 'name'},
  ]
});

[manelbenmiled]

thanks a lot it works great

[yassineahlaou]

It's better to use pagination like you did, great!! or with ajax wich you use if you want to use pagination without redirecting to another view !