How to reduce load time Retrieving large database table with 12,000+ rows in laravel

Question

Hi there,

I have a 'subscribers' table in my DB, having more than 12,000 rows and the number is increasing gradually. When I open the subscriber view in the browser, it take a minute or more to load all the data and then arrange pagination in the datatable.

Here is my code of SubscribersController

public function index()
    {
        $subscribers = DB::table('subscribers')->get();
        return view('subscribers')->with('subscribers',$subscribers);
    }

Is there anyway which can increase the performance of the page loading.
Thanks in Advance!

Answer 1

I have a similar problem and I resolved it byy using composer require yajra/laravel-datatables-oracle:"~9.0". With datatables you can use server-side loading which will take about 5 seconds to load 15000 records.

First, install the package following the guide: install yajra/datatables.

Second, set a route to the function that will retrieve the data in the controller, like:

Route::get('/subscribers/render-subscribers-datatable', 'SubscribersController@render_subscribers_datatable')->name('render_subscribers_datatable')->middleware('auth');

Third, configure the datatable in the view to get and process the data, like:

$('#subscribers').DataTable({
    responsive: true,
    processing: true,
    deferRender: true,
    serverSide: true,
    bLengthChange: false,
    searchDelay: 500,
    pageLength: 10,
    ajax: '{{ route('render_subscribers_datatable') }}',
    columns: [
        { data: 'active', orderable: false, searchable: false, className: "text-center"},
        { data: 'client_name' },
        { data: 'date_of_birth' },
        { data: 'personal_id_number' },
        { data: 'partner_number' },
        { data: 'phone' },
        { data: 'delivery_address' },
        { data: 'email' },
        { data: 'action', orderable: false, searchable: false, className: "text-center"},
    ],
});

Configure the columns properly to your project...

Forth, work out the controller, like:

public function render_subscribers_datatable(Request $request)
{
    // Check if Request Came From Ajax
    if($request->ajax())
    {
        // Get data
        $dump = DB::table('tbl_subscribers')->select()->get();

        // Return datatable
        return Datatables::of($dump)->make(true);
    }

    // Else, redirect
    // redirect with message
    return redirect()->route('home')->with('error', 'cant process the request.');
}

Hope it helped.