Laravel Eager Loading saved us 1.2 million queries per day

Valerio Barbera
Laravel Eager loading inspector monitoring

In this article I discussed how we saved 1.2 million queries per day making a better use of Laravel Eloquent Eager Loading.

Since various elements of the Inspector backend rely on Laravel, I worked a lot with the ORM component myself, and its Eager Loading features.

The tradeoff in using an ORM always remains tremendously positive for developers. Laravel Eloquent (the Laravel’s ORM) has meant for me a huge increase in productivity and flexibility in building Inspector.

But it’s a technical tool. As our application grows or is subject to ever higher load, we need to improve the use we make of our technology stack.

As I always say to my collaborators “it’s a good thing”. It’s because the business is growing.

I’m Valerio, software engineer and CTO at Inspector. In this article I’ll show you how I saved 1.2 million queries per day using Eager Loading correctly.

Let’s first clarify what eager loading in Laravel means before continuing.

Eager Loading in Laravel

Working with databases is incredibly easy thanks to object relational mapping (ORM). Although querying related model data is made simple by object-oriented definitions of database relationships, developers could overlook the underlying database calls

Eloquent is part of Laravel and makes working with your database fun.

How is the ORM expected to understand your intentions, after all? 

Eager Loading means you get all of the required data at the same time. In contrast, Lazy Loading only retrieves related things when they are actually needed and only gives you one item at a time. 

Let me show you a real life example. Consider a database with two tables: posts and comments.

A post naturally contains numerous comments since all comments have a post_id field on them that links them to the corresponding posts (1 to N relation or hasMany). 

Below there are the Post and Comment Eloquent models.

namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Post extend Model
{
    /**
     * The comments associated to the post.
     */
    public function comments(): HasMany
    {
        return $this->hasMany(Comment::class);
    }
}
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Comment extend Model
{
    /**
     * The Post that own the comment.
     */
    public function comments(): BelongsTo
    {
        return $this->belongsTo(Post::class);
    }
}

Let’s say that the posts table contains 10 items. To access all posts, we just need to:

$posts = Post::all();

Then, to get every comment connected to a post, we might do something like this:

foreach ($posts as $post) {
    echo $post->comments->count();
}

The initial query will run once to retrieve all the posts, followed by further 10 requests to retrieve the corresponding comments. The total number of queries is now 11. 

Putting posts in a foreach loop N stands for the number of rows retrieved from the posts table, which in this case is 10, plus one related to the comments relation, so the formula utilized is N + 1.

That is nothing more than lazy loading. However, with eager loading, we only need to run two queries to retrieve the 10 posts and their comments. 

$posts = Post::with('comments')->get();
foreach ($posts as $post){
    echo $post->comment->count();
}

We have concurrently loaded all 10 posts and their comments using the “with” method. Eloquent will hydrate the internal comment property of the post model, so when you use it in your code it won’t run a new query but can rely on previously fetched data. This will avoid the additional (+1) query on each post’s iteration.

Since various elements of the Inspector backend system rely on it, I worked a lot with this framework’s component myself. Later I will explain how I saved more than 1 million queries per day using this technique.

Eager Loading Multiple Relationships

Let’s imagine that our Post model has another relationship, such as Category:

namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Post extend Model
{
    /**
     * The category that own the post.
     */
    public function category(): HasMany
    {
        return $this->belongsTo(Category::class);
    }
	
    /**
     * The comments associated to the post.
     */
    public function comments(): HasMany
    {
        return $this->hasMany(Comment::class);
    }
}

We can simply retrieve the relationships without our code needing to hit the database repeatedly:

$posts = Post::with('comments', 'category')->get();
foreach ($posts as $post) {
    echo "Category name is {$post->category->name}";
    foreach ($post->comments as $comment){
        echo "Comment is {$comment->message}";
    }
}

Useful if you plan to loop for more relationships during the rest of the execution.

There are many other options you can use to take advantage from this feature, so I strongly recommend that you consult the official documentation for all possible configurations: 

https://laravel.com/docs/master/eloquent-relationships#eager-loading

How I saved 1.2 million queries per day with Laravel Eager Loading

Recently we decided to rely on a Cache layer in order to offload the SQL database from some queries that are executed millions of times every day.

The cache layer is structured following the Repository Pattern. You can read more about our implementation in the article below:

Following the same schema of the example above with posts and comments, our users can have multiple subscription plans.

In the cache layer we cache the result of the query below:

public function get($id): User
{
    return User::with('plans')->findOrFail($id);
}

But later we used the “plans” relation to retrieve the most recent subscription as below:

if ($this->hasSubscription()) {
    return $this->plans()->first();
}

Here was the bug.

In order to use the eager loaded plans we have to use the $this->plans property not the method. 

Invoking $this->plans() Eloquent will run the query again. 

It was enough to remove the parentheses from the statement to tell Eloquent to use preloaded records and avoid the execution of 1.2 million queries per day.

if ($this->hasSubscription()) {
    return $this->plans->first();
}

In the image below you can see the magnitude of reduction in the number of queries per second.

Laravel Eager loading inspector monitoring

Autofix your Laravel application for free

Inspector is a Code Execution Monitoring tool specifically designed for software developers. You don’t need to install anything on the infrastructure, just install the Laravel package and you are ready to go.

If you are looking for effective automation, and the ability to automatically receive code change proposals to fix application errors try Inspector for free. Register your account.

Or learn more on the website: https://inspector.dev

Related Posts

Laravel Http Client Overview and Monitoring

Laravel HTTP client was introduced starting from version 10 of the framework, and then also made available in all previous versions. It stands out as a powerful tool for making HTTP requests and handling responses from external services. This article will delve into the technical foundations of the Laravel HTTP client, its motivations, and how

Laravel Form Request and Data Validation Tutorial

In this article I will talk about Laravel Form Request to send data from your application frontend to the backend. In web applications, data is usually sent via HTML forms: the data entered by the user into the browser is sent to the server and stored in the database eventually. Laravel makes it extremely simple

Upload File in Laravel

You can upload file in Laravel using its beautiful unified API to interact with many different types of storage systems, from local disk to remote object storage like S3. As many other Laravel components you can interact with the application filesystem through the Storage Facade: Illuminate/Support/Facades/Storage This class allows you to access storage drivers called