Very high TTFB when fetching 1000 records with relations

Are you fetching the query using the get() method? try switching to using cursor() instead, this might speed up everything a lot for you.

https://laravel.com/docs/7.x/eloquent#chunking-results

But the query isn't slow, it takes 74ms to finish for all the rows and related data.

What is slow is server returning the data, hence Wait Time (TTFB).

Even if the query finishes quick i thought that it might take time for you to get all the data from the database into your models, but you are right, i think i didn't read that part properly in your initial post.

1. How big is the API response you are returning?

2. What are you doing with the models after you fetched them from the database? It seems the slow part is in that section, it is hard to say what you can optimize without seeing the code.

1. 2.1MB, it downloads in 31ms.
2. I display them in a table, paginated - only 10 rows per page.

@jjk There’s a lot more involved in fetching 1,000 records from a database than the query itself. As @ishra says, once the query’s finished the framework still has to do other work, such as putting that raw data into Eloquent models, and then constructing a response to be returned to the user. That’s also going to have quite considerable memory requirements.

I display them in a table, paginated - only 10 rows per page.

If you only show 10 records at a time, then why are fetching 1,000 records on page load?! Fetch the 10 you need. You’ll find this is much faster. You can then use eager loading where you fetch the next page in the background so that if the user does click the next link in your pagination, the data has already started to be fetched.

So yeah, you have a large TTFB because you haven’t built your application and interface in an efficient way.

@martinbean I download 1000 because I need them for clientside excel file generator. I only show 10 to prevent lags on the clients side.

It's of utmost importance that I fetch 1000 rows and I don't see how TTFB would be so long for 2MB of data.

@jjk There are a multitude of reasons. But you’re asking a server process to perform an SQL query, hydrate those 1,000 records into Eloquent models, convert it to a response, and then send that response to the browser. That’s going to take memory and time.

Okay, that makes sense.

Thanks for satisfying my curiosity, now I'm about to move excel generation to backend lol.

Without sending a response it would still create a 1000 Models right? Why not do 2 or 3 queries and map them yourself as pure Objects? By using PDO fetchUnique and fetchGroup for many-to-many's all you do is map the primary keys with an array lookup. Very very fast. I can send 400k records hydrated with relations to the browser. (sometimes handy to have a single call and do processing via js in the browser - no more server requests...)