Query slow in datatable

Published 5 months ago by mgmk

Hi,

I have 35,000 over records in my table and I'm using datatables library to render all these records. After implemented with datatables, the query result took about 27 seconds. It is too long, right? Can you help me to check the way I implemented is correct or wrong?

Thanks in advance!

$products = $merchant->Product()->select(['id', 'name', 'price'])
                ->orderBy('created_at', 'desc')
                ->get();

return Datatables::of($products)->make();
bashy
bashy
5 months ago (1,002,600 XP)

Does it do pagination requests? If not, it's adding all 35k to the DOM which means the query is pulling all 35k items as well.

DariusIII

You need to either use some limit or do pagination, as you are pulling all the 35k records from database.

Also, it looks to me you are also pulling relations, that also pulls data from related tables, which ads to it too.

click
click
5 months ago (77,180 XP)

Indeed as mentioned earlier you are first retrieving all records before you pass it to the datatables helper. I suppose you use https://datatables.yajrabox.com/

You should change your code to:

$products = $merchant->Product()->select(['id', 'name', 'price'])
                ->orderBy('created_at', 'desc');

return Datatables::of($products)->make();

As a reference copied from the site above:

use Yajra\Datatables\Facades\Datatables;

// Using Eloquent
return Datatables::eloquent(User::query())->make(true);

// Using Query Builder
return Datatables::queryBuilder(DB::table('users'))->make(true);

// Using Collection
return Datatables::collection(User::all())->make(true);

// Using the Engine Factory
return Datatables::of(User::query())->make(true);
return Datatables::of(DB::table('users'))->make(true);
return Datatables::of(User::all())->make(true);
click
click
5 months ago (77,180 XP)

@jlrdw you are quite rude to be honest. This kind of things maybe makes a lot of sense to you but not everybody understands this from the beginning. Instead of leaving replies like the one above you could add a short explanation why it is wrong instead of saying it is wrong and say: 'use google' because that won't help anybody if you do not know what exactly is wrong.

jlrdw
jlrdw
5 months ago (251,870 XP)

@m-rk I meant Google as an example, could be https://laracasts.com/discuss or any site. I meant google has large results and google paginates results.

Any developer, by time they get to the point of using laravel, should fully realize large result sets need pagination.

If it makes you happy it's deleted.

But I still say any developer at laravel level should understand the importance of pagination in a large result set.

lostdreamer_nl

although it might come across as 'rude' to some people, I kinda agree with jlrdw on this one: If you have no knowledge (whatsoever) of handling large results, why would a person be building a system in a framework?

It's feels like: I have no idea about structures, materials or load bearing, but I really like the idea of building my own home instead of buying / renting one so I'll build myself a nice 2 floor apartment from any materials at hand without reading any books about the subject.

On subject: It's not your query that's taking ~30 seconds, but creating the page and sending it to the user. A query getting 35000 rows from a single table should not take more then a second, and even that would be slow.

Please sign in or create an account to participate in this conversation.