Eliminate Hibernate N+1 Queries

A Huge Performance Improvement

Hibernate is a famous ORM for Java applications. In this article, I show you how to improve performance eliminating the Hibernate N+1 Queries.

After some months on my first complex project with Spring and Hibernate, I needed to improve the performance to meet my users’ needs. That’s when I discovered the N+1 queries problem and its huge impact on the performance of my requests.

For example, because of the Hibernate N+1 queries, a request to get the 20 last messages was triggering 218 queries to the database. After having solved them, the number of queries went down to 7 and the processing time went down from 3 seconds to 400ms!

To help you achieve similar results, I will first explain what is the Hibernate N+1 queries problem. Then I will show how to detect it easily using spring-hibernate-query-utils. And finally, I will give solutions to fix the N+1 queries.

Understanding the Hibernate N+1 Queries

The N+1 queries problem is a performance anti-pattern where an application spams the database with N+1 small queries instead of 1 query fetching all the data needed. We could think that it is faster but doing a lot of connections to the database server will take much more time.

Let’s see an example with two classes User and Message, a message has an author and a user is the author of several messages:

In Spring, the Message domain would have an author field that is configured to be lazily fetched to avoid fetching it when not needed:

If we want to list all the messages with their author name, we could write the following code:

This service would have the expected behavior but would trigger 8 queries to log the 7 last messages, 1 query to fetch the messages and 7 queries to get each message author:

INFO: select message0_.id as id1_0_, message0_.author_id as author_i3_0_, message0_.text as text2_0_ from messages message0_
INFO: select user0_.id as id1_1_0_, user0_.name as name2_1_0_ from users user0_ where user0_.id=?
INFO: select user0_.id as id1_1_0_, user0_.name as name2_1_0_ from users user0_ where user0_.id=?
INFO: select user0_.id as id1_1_0_, user0_.name as name2_1_0_ from users user0_ where user0_.id=?
INFO: select user0_.id as id1_1_0_, user0_.name as name2_1_0_ from users user0_ where user0_.id=?
INFO: select user0_.id as id1_1_0_, user0_.name as name2_1_0_ from users user0_ where user0_.id=?
INFO: select user0_.id as id1_1_0_, user0_.name as name2_1_0_ from users user0_ where user0_.id=?
INFO: select user0_.id as id1_1_0_, user0_.name as name2_1_0_ from users user0_ where user0_.id=?

You could say we should only remove the Lazy configuration and it would work. True but this would force you to fetch the author each time you fetch a message and would impact the performance too.

We will dig more into the solutions after seeing how to detect those N+1 queries.

Detect N+1 queries

After having understood the problem, the second step to eliminate it is to assure that any developer knows if he introduces new N+1 queries and that it will break the tests.

After some research online, I discovered that ruby on rails has a great tool, named Bullet, for detecting the N+1 queries but I did not find anything similar for Spring.

So I decided to create a library to first count the queries and then detect the N+1 queries. The library spring-hibernate-query-utils is now available and provides automatic detection of Hibernate N+1 queries.

The setup is really easy, simply add it to your dependencies and it will trigger error logs each time an N+1 query is detected:

• Add the dependency to your project inside your pom.xml file:

• See the N+1 queries error logs triggered:

When we launch the test, we can see an ERROR log for each N+1 query generated:

ERROR 49239 --- [main] c.y.i.HibernateQueryInterceptor: 
N+1 queries detected on a getter of the entity entity.User
    at NPlusOneQueriesLoggingTest.lambda$nPlusOneQueriesDetection_isLoggingWhenDetectingNPlusOneQueries$0(NPlusOneQueriesLoggingTest.java:16)
    Hint: Missing Eager fetching configuration on the query that fetches the object of type com.yannbriancon.entity.User

Logging is fine but to eliminate N+1 queries, we need an exception to break our tests.

For this purpose, I added a configuration property hibernate.query.interceptor.error-level that can be set to EXCEPTION to throw an exception each time an N+1 query is detected.

To eliminate the N+1 queries, I strongly advise setting the error level to EXCEPTION in the application properties of the test profile. It will allow you to detect all the N+1 queries in your tests and be able to tag them.

Once you have done that, you may need to put the error level back to ERROR not to break some existing tests. For that, I suggest to change it only on the tests you cannot fix right now using @SpringBootTest:

Your test will not fail anymore and you can plan to fix it later.

That’s it! Every developer that adds an N+1 query will break a test and will have to improve his code.

But how can we change the code to avoid N+1 queries?

Fix N+1 Queries

Eager Fetching

The solution to fix the N+1 queries is to configure Hibernate to eagerly fetch the data needed in each query.

As I explained before, the best practice is to configure every entity’s relationship (ManyToOne…) to be lazily fetched by default. Each query should then override the configuration if necessary to avoid fetching useless data in every query.
For more details, take a look at the great article of Vlad Mihalcea: EAGER fetching is a code smell.

With Spring and Hibernate, you have several ways of doing a query and the same number of ways to configure the fetching. Here are the different solutions for each type of query:

• JPA query, use an entity graph:

• JPQL query, use the keyword JOIN FETCH:

• Criteria query, use the fetch method:

Perfect, now you have a strategy to fix the N+1 queries!

However, there is an edge case, check the next section for more details.

Eager Fetching With Result Limiting

What if you need to get only the last 5 users with their messages?

With the strategy explained above, the query will lead to the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!.

This warning prevents very bad performance when all the rows are fetched and the limiting is done after in memory. The best to understand why the limiting cannot be done in SQL is to look at the query generated when fetching the users:

select 
  user0_.id, 
  messages1_.id, 
  user0_.name, 
  messages1_.author_id, 
  messages1_.text,
  messages1_.author_id, 
  messages1_.id
from users user0_ 
left outer join messages messages1_ 
  on user0_.id=messages1_.author_id

The query is fetching one line per user message. If the limit was applied directly it would get only 5 user messages instead of 5 users with their messages.

In that case, the solution is to do two queries. The first one to get the ids of the items to fetch and the second one to eagerly fetch all the data for these items.

Let’s see an example:

For more detail on this issue, check Vlad Mihalcea’s article.

Conclusion

After reading this article, you can eliminate all Hibernate N+1 queries spoiling your Spring application performance.

Here is a summary of the process to eliminate the N+1 queries:

• Add the library spring-hibernate-query-utils to your dependencies
• Set hibernate.query.interceptor.error-level to EXCEPTION in the application properties for the test profile.
• Launch the application tests
• Tag each failing test and to avoid blocking those tests add @SpringBootTest("hibernate.query.interceptor.error-level=ERROR")
• Fix each test tagged following the guidelines explained above

I hope your Spring application performance is now outstanding!
Feel free to comment and participate in the library. 😎