Data loading is very slow for big number of rows.

[hilioski]

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

[yajra]

You might be using collection instead of query? Can you paste your server-side code? Thanks!

[hilioski]

I use repository pattern and in my LanguageRepository.php I have:
return $this->languages->select('languages.*')->get();

[yajra]

You need to remove get to use the query builder.

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

[hilioski]

Okey. Now looks better. I will test it again and will write my results.
But in the meantime one more problem is there. When I try to Search some word, I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'languages.action' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `languages` where (LOWER(`languages`.`name`) LIKE %m% count_row_table)

[yajra]

Set action as not searchable/orderable since it's added column.

[hilioski]

Thank you very much! @yajra
When I finish with testing I will send results to you

[hilioski]

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.

[yajra]

Glad I was able to help! Thanks!

[hsali]

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.

[pnghai]

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

[hsali]

@pnghai how I can check it?

[nguyenhaiphan]

@hsali I'm sorry. False alarm. Turned out that my logic involved a remote image fetching, that links to a dead-end server and force each record fetch wrong 404 connection, that take up number of record * 60 (seconds before timeout). I suspended that logic and things work very well now.

Thank you very much
(p/s: the pnghai nick is another github account of myself)

[kennedy-osaze]

@pnghai how I can check it?

Were you able to resolve this? If yes, how?

[M4jx]

For anyone who gets slow loading, you might have some sort of loop in your controller function that loops the fetched records in some way.

[royallife88]

Hi, @yajra. I am facing a problem with loading speed. count_row_table taking a big time in the query section. Please check the below results. It's about 7000 data rows and it's taking more than 7s only the count query.

And I code like this.
$query = Transaction::leftjoin('transaction_payments', 'transactions.id', 'transaction_payments.transaction_id') ->leftjoin('stores', 'transactions.store_id', 'stores.id') ->leftjoin('customers', 'transactions.customer_id', 'customers.id') ->leftjoin('customer_types', 'customers.customer_type_id', 'customer_types.id') ->leftjoin('transaction_sell_lines', 'transactions.id', 'transaction_sell_lines.transaction_id') ->leftjoin('products', 'transaction_sell_lines.product_id', 'products.id') ->leftjoin('users', 'transactions.created_by', 'users.id') ->where('transactions.type', 'sell')->whereIn('status', ['final', 'canceled']);

$sales = $query->select( 'transactions.final_total', 'transactions.payment_status', 'transactions.status', 'transactions.id', 'transactions.transaction_date', 'transactions.service_fee_value', 'transactions.invoice_no', 'transaction_payments.paid_on', 'stores.name as store_name', 'users.name as created_by_name', 'customers.name as customer_name', 'customers.mobile_number' )->with([ 'return_parent', 'customer', 'transaction_payments', 'deliveryman', 'canceled_by_user', 'sell_products', 'sell_variations', ]) ->groupBy('transactions.id');