laravel-datatables: Data loading is very slow for big number of rows.

Summary of problem or feature request

I tried to implement Datatable with Laravel but I have problem when my table has big number of rows because Datatable get ALL in one request and then pagination is on JS side. Can I use Laravel pagination or can I return paginated rows with paginate() method from Eloquent on ajax method ? Because this is big problem for my project. Data loading is very slow. When I search some word processing is also very slow and after some operation like sort, search etc. Page freeze and nothing happen. I hope that someone has same problem like this.

Code snippet of problem

                serverSide: true,
                processing: true,
                render: true,
                columns: [
                    {data: 'code'},
                    {data: 'name', "defaultContent": "<i>Not set</i>"},
                    {data: 'native_name',  "defaultContent": "<i>Not set</i>"},
                    {data: 'action'}
                ],
                ajax: '{!! route('dt-languages') !!}'

System details

Operating System Linux
PHP Version 7.0.4
Laravel Version 5.4
Laravel-Datatables Version 7.2

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 15 (4 by maintainers)

Most upvoted comments

You need to remove get to use the query builder.

return $this->languages->select('languages.*');

+10

yajra on Mar 9, 2017

I am suffering from the problem. Slow rendering. My server side query.

 $products = DB::connection("mysql2")->table('products')->
        join('products_description', 'products.products_id', '=', 'products_description.products_id')
            ->select(['products.products_id', 'products_description.products_name', 'products.products_isbn13', 'products.products_distributor', 'products.products_price', 'products.products_status']);
 return Datatables::of($products)->make(true);

help me. Should I write my own script to paginate? what should I do? I have records of more than 4,000,000 products. @yajra very appreciating if you help me.

hsali on Aug 8, 2017

I got this one too, may be the datatable request not chaining the pagination param to the query?

pnghai on Oct 5, 2017

Glad I was able to help! Thanks!

yajra on Mar 9, 2017

My testing results:

First query: select count(*) as aggregate from (select '1' as row_count from languages where languages.deleted_at is null and languages.deleted_at is null) count_row_table -> 3.64ms

Second query: select languages.* from languages where languages.deleted_at is null order by code asc limit 10 offset 0 -> 1.05ms

Thanks @yajra once again for help.

hilioski on Mar 9, 2017