php - laravel datatable take long time to load data

299

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', '[email protected]')->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!

38

Answer

Solution:

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 thetonic_data. There are callback functions as per this answer.

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

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

325

Answer

Solution:

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

Because of theGROUP BY, theLIMIT 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 provideSHOW CREATE TABLE tonic_data; I may have further tips.

91

Answer

Solution:

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

People are also looking for solutions to the problem: Server side authentication for Google Analytics API in PHP

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.