5 months ago

Determine source of slow page load - fast query on its own but slow when run as part of more complex operation. Blackfire Profiling included.

Posted 5 months ago by lambooni

I have a call to an endpoint that returns a paginated set of results (15 at a time). To get all the information needed, I am running between 60 - 100 queries. I realise this is a lot and I am optimizing this seperately, but I really want to get to the source of my issue!

Using laravel devbug bar, I can see all queries are fast apart from a single sum where. As part of the page load this take 200ms and runs 29 times. If I take the exact SQL query and run this directly in MySQL or as just a single query within Laravel, the run time is no more than 10ms.

I have profiled the request within BlackFire on a production server with plenty of free memory and CPU.

Blackfire Public Link

Run time: 9s Peak Memory: 1.68mb Queries: 98

The slow query is the same as the one as shown in debugbar and takes a total of 9.07s over 29 queries.

What could be causing this query that takes only 10ms to run on its own, to now take over 9 seconds to run 29 times?

For reference, the "slow" query is:

select -SUM(picked) as packed, `product_id` from `stock_movements` where `stock_movements`.`location_id` = '700020241' and `stock_movements`.`location_id` is not null and `stock_movements`.`company_id` = '8b11050c-612c-4922-8b34-d04d579e02a9' group by `product_id`

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