A query with pagination takes much longer than using get()

Hello,

I was trying to see why a page takes over a minute to load. At first, I thought the query is too complex and unoptimized, so I directly put the raw query (given by ->toSql()) into the Microsoft SQL Server Management Studio.

But to my surprise the query executed instantly (00:00:00). But in Laravel it takes over 20 seconds

The difference is that in Laravel I use $query->paginate(), and I have 3 paginators in this page.

However if I change that to $query->get() it goes down to 2-3 seconds (The extra 2-3 seconds are probably because the DB is on a different server)

What could cause this issue? Can I fix that?

Thanks

warpig

2 years ago

Level 12

have you checked if your console and network tabs is throwing any errors or something unusual? and if you have debugbar, have you checked if there are any duplicate queries? would be nice if you could share the code where you are returning the records.

1 like

Ligonsker

2 years ago

Level 8

@warpig thank you. I am not using debugbar, but there aren't duplicate queries or errors. Because I narrowed it down to the controller. What I do is, I wrap only the query part with microtime(true) to measure the time then I dd() the time it took inside the controller itself. So that time is purely from this piece of code in the controller:

public function my_controller(Request $request)
{
    ... some code

    $start_time = microtime(true);
    $query = DB::table('table')->(some long query)->paginate();
    $end_time = microtime(true);
    dd($end_time - $start_time);  // 20+ seconds
}

And if I do the same with get() it's much faster:

public function my_controller(Request $request)
{
    ... some code

    $start_time = microtime(true);
    $query = DB::table('table')->(some long query)->get();
    $end_time = microtime(true);
    dd($end_time - $start_time); // 2-3 seconds
}

jlrdw

2 years ago

Level 75

@Ligonsker show the query, are there joins, grouping, etc.

1 like

Please or to participate in this conversation.