The N + 1 selection problem in object-relational mapping (ORM)

The n + 1 query problem ( select n + 1 ) occurs when the system executes N additional SQL statements against the data to retrieve the same data that could have been retrieved by executing the main SQL query.

The larger the value of N, the more queries will be executed, the greater the impact on performance. And, unlike the slow query log, which can help you find slow queries, the N+1 problem won't be obvious because each additional query is executed quickly enough to not trigger the slow query log.

The problem is that a large number of additional requests are performed, which together take enough time to slow down the response time.

Let's consider that we have the following database tables post and post_comments, which form a one-to-many table relationship:

We are going to create the following 4 postrows:

And we will also create 4 post_commentchild records:

N+1 Query Problem with Plain SQL

If you choose post_commentsto use this SQL query:

And later you decide to get related post titlefor each post_comment:

You are about to cause an N+1 query problem because instead of executing one SQL query you executed 5 (1+4):

Fixing the N+1 query issue is very simple. All you need to do is extract all the data you need in the original SQL query, like this:

This time, only one SQL query is executed to retrieve all the data we want to use later.

N+1 Query Problem with JPA and Hibernate

When using JPA and Hibernate, there are several ways to cause the N+1 query problem, so it is important to know how to avoid such situations.

In the following examples we will look at MAPPING postand post_commentstables for the following individuals:

JPA mappings look like this:

Potemkin Monkey on the fruit stairs

FetchType.EAGER

Using FetchType.EAGERimplicitly or explicitly for your JPA associations is a bad idea because you are going to get much more data than you need. Moreover, this FetchType.EAGERstrategy is also prone to N+1 query problems.

Unfortunately, associations are @ManyToOnealso used by default, so if your mappings look like this:@OneToOneFetchType.EAGER

@ManyToOne
private Post post;

You use FetchType.EAGERa strategy, and every time you forget to use it JOIN FETCHwhen loading some PostCommententities using a JPQL or Criteria API query:

You are about to cause an N+1 query problem:

Note the extra SELECT statements that are executed because postthe association must be extracted before returning Listfrom PostCommentthe subjects.

Unlike the default fetch plan you use when calling finda method EnrityManager, a JPQL or Criteria API query defines an explicit plan that Hibernate cannot change by automatically injecting a JOIN FETCH. This is what the website https://intellect.icu says. So you have to do it manually.

If you don't need postthe association at all, you're out of luck using it FetchType.EAGER because there's no way to avoid getting it. So it's best to use FetchType.LAZYthe default.

But if you want to use postassociation, you can use JOIN FETCHto avoid N+1 query problem:

This time, Hibernate will execute a single SQL statement:

FetchType.LAZY

Even if you switch to using FetchType.LAZYexplicit for all associations, you may still run into the N+1 problem.

This time postthe association is displayed as follows:

@ManyToOne(fetch = FetchType.LAZY) private Post post;

Now when you receive PostCommentobjects:

Hibernate will execute one SQL statement:

But if you then want to reference posta lazy loading association:

You will get the N+1 query problem:

Because postthe association is fetched lazily, when a lazy association is accessed, a secondary SQL statement will be executed to generate a log message.

Again, the fix is ​​to add JOIN FETCHa clause to the JPQL query:

And, just like in FetchType.EAGERthe example, this JPQL query will generate a single SQL statement.

Even if you use FetchType.LAZYand do not reference the child association @OneToOneof a JPA bidirectional relationship, you can still cause the N+1 query problem.

How to Automatically Detect N+1 Query Issue

If you want to automatically detect the N+1 query problem at the data access layer, you can use the db-utilopen source project.

First, you need to add the following Maven dependency:

After that, you just need to use SQLStatementCountValidatorthe utility to validate the generated basic SQL statements:

If you use FetchType.EAGERand run the above test case, you will get the following test case failure:

Solving the N+1 Request Problem Without Increasing Memory Consumption in Laravel

One of the main problems of developers when they create an application with ORM is N+1 query in their applications. N+1 query problem is an inefficient way of accessing the database when the application generates a query for each object call. This problem usually occurs when we get a list of data from the database without using lazy or eager load. Fortunately, Laravel with its ORM Eloquent provides tools for convenient work, but they have some drawbacks.
In this article, we will consider the N+1 problem, ways to solve it and optimize memory consumption.

Let's look at a simple example of how to use eager loading in Laravel. Let's say we have a simple web application that shows a list of the top article titles of the application's users. Then the relationship between our models could be something like this:

and then a simple action of getting data from the database and passing it to the template might look like this:

A simple test.blade.php template to display a list of users with the corresponding titles of their first article:

And when we open our test page in the browser, we will see something like this:

I am using debugbar (https://github.com/barryvdh/laravel-debugbar) to show how our test page is executed. To display this page, 11 queries are called to the database. One query to get all the information about the users and 10 queries to show the title of their first article. You can see that 10 users are making 10 database queries to the articles table. This is called the N+1 query problem.

Solving the N+1 Query Problem with Greedy Loading

You may think that this is not a performance issue for your application as a whole. But what if we want to display more than 10 items? And often, we also have to deal with more complex logic consisting of more than one N+1 request per page. This condition can lead to more than 11 requests or even an exponentially growing number of requests.

So how do we solve this? There is one general answer to this:

Eager load

Eager loading is a process where a query for one type of object also loads related objects in a single database query. In Laravel, we can load related models' data using the with() method. In our example, we would change the code as follows:

And finally, reduce the number of our queries to two:

We can also create a hasOne relationship, with a corresponding query to get the user's first article:

Now we can upload it along with the users:

The result now looks like this:

So, we can reduce the number of our queries and solve the N+1 query problem. But did we improve our performance that much? The answer might be "no"! It's true that we reduced the number of queries and solved the N+1 query problems, but in fact we added a new nasty problem. As you can see, we reduced the number of queries from 11 to 2, but we also increased the number of models loaded from 20 to 10010. This means that to show 10 users and 10 article titles we load 10010 Eloquent objects into memory. If you have unlimited memory, this is not a problem. Otherwise, you can crash your application.

Greedy loading of dynamic relations

There should be 2 goals when developing an application:

1. We must keep the number of queries to the database to a minimum.
2. We must keep memory consumption to a minimum.

In our example, we failed to minimize memory consumption while we reduced our queries to a minimum. In many cases, developers also achieve the first goal well, but fail the second. In this case, we can use the eager loading approach of dynamic relations via a subquery to achieve both goals.

To implement dynamic relationship, we will directly use primary key instead of its foreign key. We also need to use subquery in related table to get corresponding id. Subquery will be placed in select based on filtered data of related table.

Example of getting users and their first article id via a subquery:

We can get this query by adding select to a subquery in our query builder. Using Eloquent, this can be written as follows:

This code generates the same sql query as in the example above. After that, we can use the "first_article_id" relationship to get the first articles of the user. To make our code cleaner, we can use Eloquent's query scope to package our code and perform eager loading to get the first article. So, we should add the following code to the User model class:

Finally, let's change our controller and template. We need to use scope in our controller to eagerly load the first article. And we can directly access the first_article variable in our template:

Below is the page performance result after making these changes:

Now our page contains only 2 queries and loads 20 models. We have achieved both the goals of optimizing the number of database queries and minimizing memory consumption.

Lazy loading of dynamic relationships

Our dynamic relationship won't work well automatically. We first have to load a subquery to create this relationship. What if we want to lazy load a user's first articles in one place and eager load articles in another place?

To do this, we need to add a small hint to our move by adding an accessor to the first article's property:

In fact, we didn't implement lazy loading for the dynamic link. We simply assigned the result of executing a query to get the user's first article. This should work equally well when accessing the first_article property for both eager and lazy loading.

Dynamic Relationships in Laravel 5.X

Unfortunately, our solution only applies to Laravel 6 and above. Laravel 6 and previous versions use different implementation of addSelect. To use older versions of the framework, we need to change our code. We need to use selectSub to perform a subquery: