Eager load relations with concurrency #55316
-
|
It all started with this. We want to speed up the list response time that does not include DB query time. For this the active record Model was the starting point to see how we could do that because usually a model class has ~7000 rows and also Reflection is used for Attributes (PHP attributes and also Cast\Attribute). We figured that if we remove the reflection used for https://laravel.com/docs/12.x/eloquent-mutators#defining-a-mutator we could improve the time but for our case the model has ~425 methods and the \Illuminate\Database\Eloquent\Concerns\HasAttributes::getAttributeMarkedMutatorMethods method result is cached so it takes only 0.41 ms to cache that per request (we did not measure the PHP attributes check time cost). So this may be a dead end. We already did an improvement by doing async calls for count and fetch from our client lib but still, the fetch contains over 20 relations that are eager loaded in a sync way that increases the response time. But, starting from version 11 concurrency is available: https://laravel.com/docs/12.x/concurrency#running-concurrent-tasks So our idea is to load the relations in an async way in the collection. What are your opinions about this idea? |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 2 replies
-
|
A theoretical solution in a child of Eloquent builder: /**
* @inheritDoc
* @throws \Throwable
*/
public function eagerLoadRelations(array $models): array
{
if (!$this->model->eagerLoadRelationsConcurrently) { // new property needed in HasRelations trait
return parent::eagerLoadRelations($models);
}
$concurrentCallbacks = [];
$relations = [];
$i = 0;
foreach ($this->eagerLoad as $name => $constraints) {
// For nested eager loads we'll skip loading them here and, they will be set as an
// eager load on the query to retrieve the relation so that they will be eager
// loaded on that query, because that is where they get hydrated as models.
if (\str_contains($name, '.')) {
continue;
}
// First we will "back up" the existing where conditions on the query so we can
// add our eager constraints. Then we will merge the wheres that were on the
// query back to it in order that any where conditions might be specified.
$relation = $this->getRelation($name);
$relation->addEagerConstraints($models);
$constraints($relation);
$concurrentCallbacks[] = fn(): Collection => $relation->getEager(); // issue object identifier
$relations[] = [
'relation' => clone $relation,
'name' => $name,
];
$i++;
}
if ($concurrentCallbacks === []) {
return $models;
}
if ($i === 1) {
$relationArray = \reset($relations);
// Once we have the results, we just match those back up to their parent models
// using the relationship instance. Then we just return the finished arrays
// of models which have been eagerly hydrated and are readied for return.
return $relationArray['relation']->match(
$relationArray['relation']->initRelation($models, $relationArray['name']),
$relationArray['relation']->getEager(),
$relationArray['name']
);
}
return $this->eagerLoadConcurrently($concurrentCallbacks, $relations, $models);
}
/**
* @throws \Throwable
*/
protected function eagerLoadConcurrently(array $concurrentCallbacks, array $relations, array $models): array
{
$relationsEagerResults = Concurrency::run($concurrentCallbacks);
foreach ($relations as $key => $relationArray) {
// Once we have the results, we just match those back up to their parent models
// using the relationship instance. Then we just return the finished arrays
// of models which have been eagerly hydrated and are readied for return.
$models = $relationArray['relation']->match(
$relationArray['relation']->initRelation($models, $relationArray['name']),
$relationsEagerResults[$key],
$relationArray['name']
);
}
// or
// foreach ($relationsEagerResults as $key => $collection) {
// // Once we have the results, we just match those back up to their parent models
// // using the relationship instance. Then we just return the finished arrays
// // of models which have been eagerly hydrated and are readied for return.
// $models = $relations[$key]['relation']->match(
// $relations[$key]['relation']->initRelation($models, $relations[$key]['name']),
// $collection,
// $relations[$key]['name']
// );
// }
return $models;
}but still this has an issue with object identifier for relation somehow the callback would need a param to solve this. |
Beta Was this translation helpful? Give feedback.
-
|
If the callback can't receive a parameter because it is serialized, this is another UGLY solution for eager loading relations in sync batches of 5 (for example) async queries: /**
* @inheritDoc
* @throws \Throwable
*/
public function eagerLoadRelations(array $models): array
{
if (!$this->model->eagerLoadRelationsConcurrently) { // new property needed in HasRelations trait
return parent::eagerLoadRelations($models);
}
$eagerLoad = \array_filter(
$this->eagerLoad,
fn (mixed $constraints, string $name): bool => !\str_contains($name, '.'),
ARRAY_FILTER_USE_BOTH
);
if ($eagerLoad === []) {
return $models;
}
if (\count($eagerLoad) === 1) {
return parent::eagerLoadRelations($models);
}
foreach ($this->getBatchOf5EagerLoads($eagerLoad) as $eagerLoad) {
$relations = [];
$concurrentCallbacks = [];
$first = \reset($eagerLoad);
$second = \next($eagerLoad);
$third = \next($eagerLoad);
$fourth = \next($eagerLoad);
$fifth = \next($eagerLoad);
if (\is_array($first)) {
$constraints = $first[$name = \array_key_first($first)];
// First we will "back up" the existing where conditions on the query so we can
// add our eager constraints. Then we will merge the wheres that were on the
// query back to it in order that any where conditions might be specified.
$firstRelation = $this->getRelation($name);
$firstRelation->addEagerConstraints($models);
$constraints($firstRelation);
$concurrentCallbacks[] = fn(): Collection => $firstRelation->getEager();
$relations[] = [
'relation' => $firstRelation,
'name' => $name,
];
}
if (\is_array($second)) {
$constraints = $second[$name = \array_key_first($second)];
// second we will "back up" the existing where conditions on the query so we can
// add our eager constraints. Then we will merge the wheres that were on the
// query back to it in order that any where conditions might be specified.
$secondRelation = $this->getRelation($name);
$secondRelation->addEagerConstraints($models);
$constraints($secondRelation);
$concurrentCallbacks[] = fn(): Collection => $secondRelation->getEager();
$relations[] = [
'relation' => $secondRelation,
'name' => $name,
];
}
if (\is_array($third)) {
$constraints = $third[$name = \array_key_first($third)];
// third we will "back up" the existing where conditions on the query so we can
// add our eager constraints. Then we will merge the wheres that were on the
// query back to it in order that any where conditions might be specified.
$thirdRelation = $this->getRelation($name);
$thirdRelation->addEagerConstraints($models);
$constraints($thirdRelation);
$concurrentCallbacks[] = fn(): Collection => $thirdRelation->getEager();
$relations[] = [
'relation' => $thirdRelation,
'name' => $name,
];
}
if (\is_array($fourth)) {
$constraints = $fourth[$name = \array_key_first($fourth)];
// fourth we will "back up" the existing where conditions on the query so we can
// add our eager constraints. Then we will merge the wheres that were on the
// query back to it in order that any where conditions might be specified.
$fourthRelation = $this->getRelation($name);
$fourthRelation->addEagerConstraints($models);
$constraints($fourthRelation);
$concurrentCallbacks[] = fn(): Collection => $fourthRelation->getEager();
$relations[] = [
'relation' => $fourthRelation,
'name' => $name,
];
}
if (\is_array($fifth)) {
$constraints = $fifth[$name = \array_key_first($fifth)];
// fifth we will "back up" the existing where conditions on the query so we can
// add our eager constraints. Then we will merge the wheres that were on the
// query back to it in order that any where conditions might be specified.
$fifthRelation = $this->getRelation($name);
$fifthRelation->addEagerConstraints($models);
$constraints($fifthRelation);
$concurrentCallbacks[] = fn(): Collection => $fifthRelation->getEager();
$relations[] = [
'relation' => $fifthRelation,
'name' => $name,
];
}
$models = $this->eagerLoadConcurrently($concurrentCallbacks, $relations, $models);
}
return $models;
}
/**
* @throws \Throwable
*/
protected function eagerLoadConcurrently(array $concurrentCallbacks, array $relations, array $models): array
{
$relationsEagerResults = Concurrency::run($concurrentCallbacks);
foreach ($relations as $key => $relationArray) {
// Once we have the results, we just match those back up to their parent models
// using the relationship instance. Then we just return the finished arrays
// of models which have been eagerly hydrated and are readied for return.
$models = $relationArray['relation']->match(
$relationArray['relation']->initRelation($models, $relationArray['name']),
$relationsEagerResults[$key],
$relationArray['name']
);
}
// or
// foreach ($relationsEagerResults as $key => $collection) {
// // Once we have the results, we just match those back up to their parent models
// // using the relationship instance. Then we just return the finished arrays
// // of models which have been eagerly hydrated and are readied for return.
// $models = $relations[$key]['relation']->match(
// $relations[$key]['relation']->initRelation($models, $relations[$key]['name']),
// $collection,
// $relations[$key]['name']
// );
// }
return $models;
}
protected function getBatchOf5EagerLoads(array $eagerLoad): \Generator
{
$i = 1;
$batch = [];
foreach ($eagerLoad as $name => $constraints) {
$batch[] = [$name => $constraints];
if (0 === $i % 5) {
yield $batch;
$batch = [];
}
$i++;
}
if ($batch !== []) {
yield $batch;
}
} |
Beta Was this translation helpful? Give feedback.
-
|
We managed to implement this via guzzle (curl_multi_init) and the above is not 100% ok. We had to throw exception on StatementPrepared in order to get the full sql query for the eager load for any type of relation except MorphTo which we don't handle. For 100 rows and over 20 relations, with concurrency 20 on guzzle, we went from 23 to 16 seconds on an api call on local without opcache. |
Beta Was this translation helpful? Give feedback.
-
|
As a conclusion $relation->toSql() differs from the actual sql that is executed when that relation is eager loaded. |
Beta Was this translation helpful? Give feedback.
-
|
Conclusion for mysql:
If mysqli_poll will in the future support bindings, then that would be the fastest solution at the expence of more connections to mysql server. |
Beta Was this translation helpful? Give feedback.
Conclusion for mysql:
If mysqli_poll will in the future support bindings, then that would be the fastest solution at the expence of more connections to mysql server.