Optimising database queries in queued jobs
I recently reviewed some code where a request from an admin dashboard was timing out while sending out emails to users. This was working initially but became slower as the user base started growing and began timing out at around 10k users.
The Initial Implementation
Here's a simplified version of the code snippet that attempts to send emails to users showcasing certain properties (the actual queries were more complex with nested relationships):
// Get a list of properties
$properties = Property::whereIn('id', [31697, 2253, 27443, 11919, 2535])->get();
// Users with their properties eager-loaded so that
// they don't get sent emails about their own properties
$users = User::with('properties')->get();
// Loop over users and queue each mailable
// Note: error handling removed for this article for simplicity
$users->each(function ($user) use ($properties) {
Mail::to($user)->queue(new PropertyShowcaseEmail($user, $properties));
});
As a result, three queries are generated when the queued job runs for each user:
SELECT * FROM `properties` WHERE `properties`.`id` IN (2253, 2535, 11919, 27443, 31697);
SELECT * FROM `properties` WHERE `properties`.`user_id` IN (18765) AND ...;
SELECT * FROM `users` WHERE `users`.`id` = 18765 LIMIT 1;
That's 300 queries per 100 users, or 30,000 queries for 10k users.
There are a couple of things we can improve immediately:
- We can stop looping over each user individually to avoid timeouts
- We can reduce the number of queries
Refactor
To better manage the process, we can create a queued job, SendPropertyShowcaseEmails. We'll handle sending the mailable here.
Note that we no longer queue the PropertyShowcaseEmail mailable.
class SendPropertyShowcaseEmails implements ShouldQueue
{
use Queueable;
public function __construct(public Collection $users, public Collection $properties)
{}
public function handle(): void
{
$this->users->each(fn ($user) =>
Mail::to($user)
->send(new PropertyShowcaseEmail($user, $this->properties))
);
}
}
To avoid timeouts, we process users in chunks of 100 users each and pass them to the newly created queued job:
$properties = Property::whereIn('id', [31697, 2253, 27443, 11919, 2535])->get();
// $users = User::with('properties')->get();
// $users->each(function ($user) use ($properties) {
// Mail::to($user)->queue(new PropertyShowcaseEmail($user, $properties));
// });
User::with('properties')->chunkById(100, fn ($users) =>
SendPropertyShowcaseEmails::dispatch($users, $properties)
);
The queries look like this:
// 100 individual queries for user.properties
select * from `properties` where `properties`.`user_id` = 1106 and...
select * from `properties` where `properties`.`user_id` = 1105 and...
select * from `properties` where `properties`.`user_id` = 1104 and...
select * from `properties` where `properties`.`user_id` = 1102 and...
select * from `properties` where `properties`.`id` in (2253, 2535, 11919, 27443, 31697)
select * from `users` where `users`.`id` in (1102, 1104, 1105, 1106, 1108, 1109, 1110...
// 100 individual queries for user.properties
select * from `properties` where `properties`.`user_id` = 18 and `properties`.`user_id`...
select * from `properties` where `properties`.`user_id` = 16 and `properties`.`user_id`...
select * from `properties` where `properties`.`user_id` = 1 and `properties`.`user_id`...
select * from `properties` where `properties`.`id` in (2253, 2535, 11919, 27443, 31697)
select * from `users` where `users`.`id` in (1, 16, 18, 19, 21, 22, 23, 25, 26, 29, 33,...
Now we have 102 queries per 100 users. Or 10,200 queries for 10k users. It's an improvement from earlier, but we have an N+1 query problem with the properties relationship.
We eager-loaded the properties relationship in the queued jobs. What's happening here?
From the Laravel documentation:
"If a job receives a collection or array of Eloquent models instead of a single model, the models within that collection will not have their relationships restored when the job is deserialized and executed. This is to prevent excessive resource usage on jobs that deal with large numbers of models."
This means that eager-loading properties in the initial query is ineffective because the queued job does not restore relationships, leading to N+1 queries when accessing them in the job.
To fix this, we explicitly load the relationships within the job before looping over $users:
public function handle(): void
{
$this->users->load('properties'); // include this
$this->users->each(fn ($user) =>
Mail::to($user)->send(new PropertyShowcaseEmail($user, $this->properties))
);
}
The queries:
select * from `properties` where `properties`.`user_id` in (1102, 1104, 1105, 1106...
select * from `properties` where `properties`.`id` in (2253, 2535, 11919, 27443, 31697)
select * from `users` where `users`.`id` in (1102, 1104, 1105, 1106, 1108, 1109, 1110...
select * from `properties` where `properties`.`user_id` in (1, 16, 18, 19, 21, 22, 23...
select * from `properties` where `properties`.`id` in (2253, 2535, 11919, 27443, 31697)
select * from `users` where `users`.`id` in (1, 16, 18, 19, 21, 22, 23, 25, 26, 29, 33,...
Now we have 3 queries per 100 users. Or 300 queries for 10k users.
But we can still do better. The showcased properties don't change for the duration of the email send. They don't have to be queried for each job. We can just cache them.
When handling the request, we won't retrieve the properties. Instead we will pass the property IDs to the job:
// $properties = Property::whereIn('id', [31697, 2253, 27443, 11919, 2535])->get();
$properties = [31697, 2253, 27443, 11919, 2535];
User::chunkById(100, fn ($users) => SendPropertyShowcaseEmails::dispatch($users, $properties));
We modify the SendPropertyShowcaseEmailsjob to accept an array of property IDs, retrieve the models and cache them:
class SendPropertyShowcaseEmails implements ShouldQueue
{
use Queueable;
public function __construct(public Collection $users, public array $properties)
{
}
public function handle(): void
{
$this->users->load('properties');
$this->users->each(fn ($user) =>
Mail::to($user)
// ->send(new PropertyShowcaseEmail($user, $this->properties)));
->send(new PropertyShowcaseEmail($user, $this->getProperties()))
);
}
private function getProperties(): EloquentCollection
{
return Cache::remember('SendPropertyShowcaseEmails', 3600, fn () =>
Property::whereIn('id', $this->properties)->get()
);
}
}
The queries:
select * from `properties` where `properties`.`user_id` in (1102, 1104, 1105, 1106...
select * from `users` where `users`.`id` in (1102, 1104, 1105, 1106, 1108, 1109, 1110...
select * from `properties` where `properties`.`user_id` in (1, 16, 18, 19, 21, 22, 23...
select * from `users` where `users`.`id` in (1, 16, 18, 19, 21, 22, 23, 25, 26, 29, 33,...
select * from `properties` where `id` in (31697, 2253, 27443, 11919, 2535)...
The showcased properties query will only run once and will be retrieved from the cache during subsequent job runs.
Now we have a total of 2 queries per 100 users, plus that one query that runs before it's cached. That's only 201 queries for 10k users.
The changes above have made sending emails more efficient and reduced the load on the database. If the number of users grow significantly in the future, we can move the chunking process to a queued job as well.
Liked this article? Share it on X