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

If I look into blackfire I see that the most time is being handled in Eloquent itself. Where it's basically comforting the results to Eloquent models. This takes a lot of memory time.

Can you show the specific code that generates this query?

@bobbybouwmann - sure.

// Query

$this->stockMovements()
    ->selectRaw('-SUM(picked) as packed, product_id')
    ->with('product')
    ->groupBy('product_id')
    ->get();

// Relationship

public function stockMovements() : HasMany
{
    return $this->hasMany(StockMovement::class, 'location_id');
}

I have tried removing the `with('product') also, but this does not help any.

Can I ask where about's you can tell the time is taken in Eloquent? I can see here that all the time is in a PDOStatement - execute. Does this specifically relate to Eloquent opposed to the database?

Mmh that shouldn;t be it then! It could be the -SUM(picked) part. What happens if you replace that with something else?

Also did you add the foreign key constraint to the product_id column? This can also really improve the speed!

Just testing that locally, removing the sum improves the result from 5.7 seconds to about 5.1 seconds... so it helps a little but not that much. The thing that baffles me is that running this query in Laravel on its own runs in 10ms.

I have an index on the product_id column but not a foreign key constraint. However, if I remove the with('product') part, the total runtime is not improved either.

I have also tried increasing the sql memory buffer size.

Just to compare, I have profiled the single request running 30 times (looking up against 30 different ID's). There are a few additional queries being run through middleware etc. but the query which takes 9seconds to complete 29 times in the above profile, now completes just 11.5ms.

Blackfire profile

To give some further context, I am seeing this occur within an API resource, being called via the following code.

// Shipment package Resource - return array

'items' => ShipmentPackageItemResource::collection($this->PackageContents)

// Shipment package Item Resource - return array

return [
    'product_id' => $this->product_id,
    'sku' => $this->product->sku,
    'qty_packed' => $this->packed
];

// Package Contents Attribute

public function getPackageContentsAttribute() : Collection
{
    return $this->stockMovements()
        ->selectRaw('-SUM(picked) as packed, product_id')
        ->with('product')
        ->groupBy('product_id')
        ->get();
}

Removing the 'items' => ShipmentPackageItemResource::collection($this->PackageContents) from the resource fixes the issue, so I know this is where it is occurring... but it makes no sense!

Yeah, so like I said. Collections and building up models is a really resource intensive task. What are you exactly doing in your ShipmentPackageItemResource?

@bobbybouwmann I am just surprised that this one query in particular can almost 3x the response time. The total response has about 100 queries and these 30 are accounting for that much. Especially when the raw query is so fast, it doesn't feel like it makes sense.

The usage case is for a REST API. I guess one way I could get around this is to not provide the lower level detail on the collection and only return it when requesting a single resource. I would really like to try and work this out though!

It seems that you're returning a lot of data in one endpoint, however REST should be focused on an endpoint per type of resource. Than it's up to the client to make multiple requests to get the correct data.

However there is nothing wrong with returning more data in one endpoint, but that hits performance at this point!

Also caching your queries will make this a lot faster as well ;)