laravel datatable take long time to load data

Question

I am trying to display database table to laravel datatable using below code but laravel datatable taking long time to load data. I have approx 700000 records in database. How to decrease time of load data?

Code

web.php

Route::get('home', 'HomeController@index')->name('home');

HomeController.php

public function index()
{
    $campaigns = TonicData::select('campaign')->distinct()->get();

    if (request()->ajax()) {
        $data = \DB::table('tonic_data')
            ->whereNotNull('subid4')
            ->where('subid4', '!=', '')
            ->select('subid4')
            ->groupBy('subid4')
            ->selectRaw('sum(view) as sum_of_views, sum(term_view) as sum_of_term_views,
                sum(add_click) as sum_of_add_click, sum(revenue_usd) as sum_of_revenue,
                (sum(revenue_usd)/sum(view)*1000) as rpm')
            ->when((request()->has('selectedCampaign') && request()->get('selectedCampaign') != ''), function ($query) {
                $query->whereIn('campaign', request()->get('selectedCampaign'));
            })
            ->when((request()->has('selectedDateRange') && request()->get('selectedDateRange') != ''), function ($query) {
                $query->whereBetween('day_date', [request()->get('selectedDateRange')['fromDate'], request()->get('selectedDateRange')['toDate']]);
            });

        return DataTables::of($data)
            ->addIndexColumn()
            ->make();
    }

    return view('dashboard', compact('campaigns'));
}

dashboard.blade.php

<script type="text/javascript">
    $(document).ready(function() {
        $('#datatable').dataTable({
            responsive: true,
            processing: true,
            serverSide: true,
            ajax: '{{ route('home') }}',
            columns: [
                {data: 'DT_RowIndex', orderable: false, searchable: false},
                {data: 'subid4', name: 'subid4'},
                {data: 'sum_of_views', name: 'sum_of_views', searchable: false},
                {data: 'sum_of_term_views', name: 'sum_of_term_views', searchable: false},
                {data: 'sum_of_add_click', name: 'sum_of_add_click', searchable: false},
                {data: 'sum_of_revenue', name: 'sum_of_revenue', searchable: false},
                {data: 'rpm', name: 'rpm', searchable: false}
            ]
        });
   });
</script>

sql code

query 1:
select  count(*) as aggregate
    from ( SELECT  `subid4`,
                   sum(view) as sum_of_views,
                   sum(term_view) as sum_of_term_views,
                   sum(add_click) as sum_of_add_click,
                   sum(revenue_usd) as sum_of_revenue,
                   (sum(revenue_usd)/sum(view)*1000) as rpm
            from  `tonic_data`
            where  `subid4` is not null
              and  `subid4` != ?
            group by  `subid4`
         ) count_row_table

query 2: 
select  `subid4`, sum(view) as sum_of_views, sum(term_view) as sum_of_term_views,
        sum(add_click) as sum_of_add_click, sum(revenue_usd) as sum_of_revenue,
            (sum(revenue_usd)/sum(view)*1000) as rpm
    from  `tonic_data`
    where  `subid4` is not null
      and  `subid4` != ?
    group by  `subid4`
    limit  10 offset 0

Thanks in advance!

Answer 1

To summarize the discussion, as your query try to get all 700k records at once, no wonder it takes a long time.

Al though you are showing only a limited number of records while rendering the page, technically query gets all records on page load itself.

Action Required

1. Minimize the below section:

->selectRaw('sum(view) as sum_of_views, sum(term_view) as sum_of_term_views, sum(add_click) as sum_of_add_click, sum(revenue_usd) as sum_of_revenue, (sum(revenue_usd)/sum(view)*1000) as rpm')

Instead of sum up all these values on the fly, maintain a separate table and store all these values when any record gets altered in the tonic_data. There are callback functions as per this answer.

2. Instead of datatables use laravel pagination and sorting which will perform on server side that controls loading the huge data on page load.

3. try to implement date based or other possible filters.

Answer 2

Looks like Laravel is running the same query twice -- once to get a row count, once to get the first 10 rows.

Because of the GROUP BY, the LIMIT 10 has little effect on the speed. This is because it must gather lots of rows, then do the grouping, and only finally deliver 10 rows.

The first query (to get the count) could be simplified and greatly sped up by changing to

    select  count(DISTINCT subid4) as aggregate
        from  `tonic_data`
        where  `subid4` is not null
          and  `subid4` != ?

and having

    INDEX(subid4)

Or forego of the count (if Laravel lets you do that).

Please provide SHOW CREATE TABLE tonic_data; I may have further tips.

Answer 3

First of all I suggest looking into your query performance and try to optimize your query.

Secondly jquery datatables plugin has a feature (named as pipeline) to cache number of pages to reduce ajax calls. So this way let's say on first request if you have cached 10 pages, then it won't make another request to server until the 11th page is accessed and this goes on.

So on client side your ajax will be updated to

<script type="text/javascript">
    $(document).ready(function() {
        $('#datatable').dataTable({
            responsive: true,
            processing: true,
            serverSide: true,
            ajax: $.fn.dataTable.pipeline({
                url: '{{ route('home') }}',
                pages: 20 // number of pages
            })
        });
   });
</script>

For more help on pipeline, please refer https://datatables.net/examples/server_side/pipeline.html