May 5th, 2020
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.
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.
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:
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?
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:
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.
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.
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:
I hope your Spring application performance is now outstanding!
Feel free to comment and participate in the library. 😎