While working on the upcoming Oh Dear redesign, I noticed that list that displays all sites of a team was very slow.

To display that list, a lot of queries were used. With a couple of minor adjustments, I could reduce the number of queries needed to just a single one, solving the performance problem.

In this small blog post, I'd like to share one of the techniques I used.

Discovering an N+1 problem

Let's look at a simplified version of how we fetch the data to build the site list for the current team.

$sites = Team::current() // returns an instance of the team model
	->sites()
	->get()
	->filter(fn (Site $site) => currentUser()->can('administer', $site));

In the code above, you can see that we filter out the sites that the current user isn't allowed to see. We use a policy check for that, and not a simple where clause, as there is some business logic needed to decide if someone is allowed to see a site.

Now let's look at the administer method of the SitePolicy.

class SitePolicy
{
    use HandlesAuthorization;

    public function administer(User $user, Site $site): bool
    {
        if ($user->ownsTeam($site->team)) {
            return true;
        }

        // other rules...
    }
}

So the first rule is that if the user owns the team to which the site belongs, he/she can view the site. You can see that we call $site->team to get the team.

Getting the team there is an n+1 problem: this query is executed for each site we loop over. When displaying a site list for teams with 200 sites, 200 extra queries are performed, and this is what makes the site list slow.

From eager loading the relationship...

To solve the n+1 problem, we can eager load the relationship by using with in our query.

Here's the modified code:

$sites = Team::current() // returns an instance of the team model
	->sites()
	->with('team')
	->get()
	->filter(fn (Site $site) => currentUser()->can('administer', $site));

This successfully solves the N+1 problem. We now fetch all of the sites and teams in one query, and we don't need to execute multiple queries to fetch sites.

... to setting the relation manually

Most of you, and past me, would call it a day. We've solved the n+1 problem; now we can go home (or work on other stuff if it's not the end of the workday).

The solution above has a couple of problems. But before listing them, I want to point you to the fact that we've asked Eloquent to get all the sites for a team. So it is no surprise that all sites have the same team.

But what is logical for us as humans isn't logical for MySQL/Laravel. Here are a couple of things that are not optimal now

• even though we know that each site has the same team, in MySQL's query result to Laravel, each row will have information about the team. The query result is larger than it needs to be
• Laravel will convert the query result to Site objects, and Team objects. We as humans know that all teams are the same, but Laravel will still create a new Team object in memory for each team. This conversion, or with a better word "hydration", will take a little bit of time
• All of the Team objects that Laravel creates are separate instances, meaning that they all will take some memory.

The problems above are not that big if you only have a few results. But if you have hundreds or thousands of results, you might notice a small performance hit.

Isn't it a shame that all this time and memory is needed to process the same team over and over again?

Luckily, we can tackle our original N+1 problem in another way that avoids all the little problems listed above. Let's take a look at our original query.

$sites = Team::current() // returns an instance of the team model
	->sites()
	->get()
	->filter(fn (Site $site) => currentUser()->can('administer', $site));

Notice that in the code above, we already of the instance of the team model available. Let's rewrite the code, so we store the team in a separate variable.

$team = Team::current();

$team
	->sites()
	->get()
	->filter(fn (Site $site) => currentUser()->can('administer', $site));

Now, instead of eager loading the team for each site, we will manually set the team relation of a model. This can be done using the setRelation method.

$team = Team::current();

$team
	->sites()
	->get()
	->map(fn (Site $site) => $site->setRelation('team', $team))
	->filter(fn (Site $site) => currentUser()->can('administer', $site));

By using setRelation, Eloquent will not reach out to the database anymore to get the team relation, it will simply return the Team instance we passed.

Because we didn't rely on eager loading:

• MySQL doesn't have to return any team results when fetching sites
• Eloquent doesn't need to hydrate all those Team objects anymore
• memory usage is lower, as we only have a single Team object in memory, and not one per site.
• we avoided the N+1 problem

In conclusion

I hope that you liked this little Eloquent performance tip. I originally heard of this method by reading Jonathan Reinink's blog post on circular relationships, and I highly recommend your reading if you want to see another example of setRelation.

Like mentioned in the intro, I'm using this technique in Oh Dear, the all-in-one monitoring tool for your entire website. Oh Dear monitors uptime, SSL certificates, broken links, scheduled tasks, application health, DNS, domain expiry and more. We send notifications when something's wrong. All that paired with a developer-friendly API and kick-ass documentation. Register to start your 10 day free trial.