N+1 query problem with JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to explain what the N+1 query problem is when using JPA and Hibernate and what’s the best way to fix it.
The N+1 query problem is not specific to JPA and Hibernate, as you can face this issue even if you are using other data access technologies.
What is the N+1 query problem
The N+1 query problem happens when the data access framework executes N additional SQL statements to fetch the same data that could have been retrieved when executing the primary SQL query.
The larger the value of N, the more queries will be executed and the larger the performance impact. And, unlike the slow query log that can help you find slow-running queries, the N+1 issue won’t be spotted because each individual additional query runs sufficiently fast to not trigger the slow query log.
The problem is executing a large number of additional queries that, overall, take sufficient time to slow down response time.
Let’s consider we have the following post
and post_comments
database tables which form a one-to-many table relationship:
We are going to create the following 4 post
rows:
INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 1', 1) INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 2', 2) INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 3', 3) INSERT INTO post (title, id) VALUES ('High-Performance Java Persistence - Part 4', 4)
And, we will also create 4 post_comment
child records:
INSERT INTO post_comment (post_id, review, id) VALUES (1, 'Excellent book to understand Java Persistence', 1) INSERT INTO post_comment (post_id, review, id) VALUES (2, 'Must-read for Java developers', 2) INSERT INTO post_comment (post_id, review, id) VALUES (3, 'Five Stars', 3) INSERT INTO post_comment (post_id, review, id) VALUES (4, 'A great reference book', 4)
N+1 query problem with plain SQL
As already explained, the N+1 query problem can be triggered using any data access technology, even with plain SQL.
If you select the post_comments
using this SQL query:
List<Tuple> comments = entityManager.createNativeQuery(""" SELECT pc.id AS id, pc.review AS review, pc.post_id AS postId FROM post_comment pc """, Tuple.class) .getResultList();
And, later, you decide to fetch the associated post
title for each post_comment
:
for (Tuple comment : comments) { String review = (String) comment.get("review"); Long postId = ((Number) comment.get("postId")).longValue(); String postTitle = (String) entityManager.createNativeQuery(""" SELECT p.title FROM post p WHERE p.id = :postId """) .setParameter("postId", postId) .getSingleResult(); LOGGER.info( "The Post '{}' got this review '{}'", postTitle, review ); }
You are going to trigger the N+1 query issue because, instead of one SQL query, you executed 5 (1 + 4):
SELECT pc.id AS id, pc.review AS review, pc.post_id AS postId FROM post_comment pc SELECT p.title FROM post p WHERE p.id = 1 -- The Post 'High-Performance Java Persistence - Part 1' got this review -- 'Excellent book to understand Java Persistence' SELECT p.title FROM post p WHERE p.id = 2 -- The Post 'High-Performance Java Persistence - Part 2' got this review -- 'Must-read for Java developers' SELECT p.title FROM post p WHERE p.id = 3 -- The Post 'High-Performance Java Persistence - Part 3' got this review -- 'Five Stars' SELECT p.title FROM post p WHERE p.id = 4 -- The Post 'High-Performance Java Persistence - Part 4' got this review -- 'A great reference book'
Fixing the N+1 query issue is very easy. All you need to do is extract all the data you need in the original SQL query like this:
List<Tuple> comments = entityManager.createNativeQuery(""" SELECT pc.id AS id, pc.review AS review, p.title AS postTitle FROM post_comment pc JOIN post p ON pc.post_id = p.id """, Tuple.class) .getResultList(); for (Tuple comment : comments) { String review = (String) comment.get("review"); String postTitle = (String) comment.get("postTitle"); LOGGER.info( "The Post '{}' got this review '{}'", postTitle, review ); }
This time, only one SQL query is executed to fetch all the data we are further interested in using.
N+1 query problem with JPA and Hibernate
When using JPA and Hibernate, there are several ways you can trigger the N+1 query issue, so it’s very important to know how you can avoid these situations.
For the next examples, consider we are mapping the post
and post_comments
tables to the following entities:
The JPA mappings look like this:
@Entity(name = "Post") @Table(name = "post") public class Post { @Id private Long id; private String title; //Getters and setters omitted for brevity } @Entity(name = "PostComment") @Table(name = "post_comment") public class PostComment { @Id private Long id; @ManyToOne private Post post; private String review; //Getters and setters omitted for brevity }
FetchType.EAGER
Using FetchType.EAGER
either implicitly or explicitly for your JPA associations is a bad idea because you are going to fetch way more data that you need. More, the FetchType.EAGER
strategy is also prone to N+1 query issues.
Unfortunately, the @ManyToOne
and @OneToOne
associations use FetchType.EAGER
by default, so if your mappings look like this:
@ManyToOne private Post post;
You are using the FetchType.EAGER
strategy, and every time you forget to use JOIN FETCH
when loading some PostComment
entities with a JPQL or Criteria API query:
List<PostComment> comments = entityManager .createQuery(""" select pc from PostComment pc """, PostComment.class) .getResultList();
You are going to trigger the N+1 query issue:
SELECT pc.id AS id1_1_, pc.post_id AS post_id3_1_, pc.review AS review2_1_ FROM post_comment pc SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1 SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2 SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3 SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4
Notice the additional SELECT statements that are executed because the post
association has to be fetched prior to returning the List
of PostComment
entities.
Unlike the default fetch plan, which you are using when calling the find
method of the EntityManager
, a JPQL or Criteria API query defines an explicit plan that Hibernate cannot change by injecting a JOIN FETCH automatically. So, you need to do it manually.
If you didn’t need the post
association at all, you are out of luck when using FetchType.EAGER
because there is no way to avoid fetching it. That’s why it’s better to use FetchType.LAZY
by default.
But, if you want to use post
association, then you can use JOIN FETCH
to avoid the N+1 query problem:
List<PostComment> comments = entityManager.createQuery(""" select pc from PostComment pc join fetch pc.post p """, PostComment.class) .getResultList(); for(PostComment comment : comments) { LOGGER.info( "The Post '{}' got this review '{}'", comment.getPost().getTitle(), comment.getReview() ); }
This time, Hibernate will execute a single SQL statement:
SELECT pc.id as id1_1_0_, pc.post_id as post_id3_1_0_, pc.review as review2_1_0_, p.id as id1_0_1_, p.title as title2_0_1_ FROM post_comment pc INNER JOIN post p ON pc.post_id = p.id -- The Post 'High-Performance Java Persistence - Part 1' got this review -- 'Excellent book to understand Java Persistence' -- The Post 'High-Performance Java Persistence - Part 2' got this review -- 'Must-read for Java developers' -- The Post 'High-Performance Java Persistence - Part 3' got this review -- 'Five Stars' -- The Post 'High-Performance Java Persistence - Part 4' got this review -- 'A great reference book'
For more details about why you should avoid the
FetchType.EAGER
fetching strategy, check out this article as well.
FetchType.LAZY
Even if you switch to using FetchType.LAZY
explicitly for all associations, you can still bump into the N+1 issue.
This time, the post
association is mapped like this:
@ManyToOne(fetch = FetchType.LAZY) private Post post;
Now, when you fetch the PostComment
entities:
List<PostComment> comments = entityManager .createQuery(""" select pc from PostComment pc """, PostComment.class) .getResultList();
Hibernate will execute a single SQL statement:
SELECT pc.id AS id1_1_, pc.post_id AS post_id3_1_, pc.review AS review2_1_ FROM post_comment pc
But, if afterward, you are going to reference the lazy-loaded post
association:
for(PostComment comment : comments) { LOGGER.info( "The Post '{}' got this review '{}'", comment.getPost().getTitle(), comment.getReview() ); }
You will get the N+1 query issue:
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 1 -- The Post 'High-Performance Java Persistence - Part 1' got this review -- 'Excellent book to understand Java Persistence' SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 2 -- The Post 'High-Performance Java Persistence - Part 2' got this review -- 'Must-read for Java developers' SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 3 -- The Post 'High-Performance Java Persistence - Part 3' got this review -- 'Five Stars' SELECT p.id AS id1_0_0_, p.title AS title2_0_0_ FROM post p WHERE p.id = 4 -- The Post 'High-Performance Java Persistence - Part 4' got this review -- 'A great reference book'
Because the post
association is fetched lazily, a secondary SQL statement will be executed when accessing the lazy association in order to build the log message.
Again, the fix consists in adding a JOIN FETCH
clause to the JPQL query:
List<PostComment> comments = entityManager.createQuery(""" select pc from PostComment pc join fetch pc.post p """, PostComment.class) .getResultList(); for(PostComment comment : comments) { LOGGER.info( "The Post '{}' got this review '{}'", comment.getPost().getTitle(), comment.getReview() ); }
And, just like in the FetchType.EAGER
example, this JPQL query will generate a single SQL statement.
Even if you are using
FetchType.LAZY
and don’t reference the child association of a bidirectional@OneToOne
JPA relationship, you can still trigger the N+1 query issue.For more details about how you can overcome the N+1 query issue generated by
@OneToOne
associations, check out this article.
Second-level cache
The N+1 query issue can also be triggered when using the second-level cache for fetching collections or query results.
For instance, if you execute the following JPQL query that uses the Query Cache:
List<PostComment> comments = entityManager.createQuery(""" select pc from PostComment pc order by pc.post.id desc """, PostComment.class) .setMaxResults(10) .setHint(QueryHints.HINT_CACHEABLE, true) .getResultList();
If the PostComment
is not stored in the second-level cache, N queries will be executed to fetch each individual PostComment
association:
-- Checking cached query results in region: org.hibernate.cache.internal.StandardQueryCache -- Checking query spaces are up-to-date: [post_comment] -- [post_comment] last update timestamp: 6244574473195524, result set timestamp: 6244574473207808 -- Returning cached query results SELECT pc.id AS id1_1_0_, pc.post_id AS post_id3_1_0_, pc.review AS review2_1_0_ FROM post_comment pc WHERE pc.id = 3 SELECT pc.id AS id1_1_0_, pc.post_id AS post_id3_1_0_, pc.review AS review2_1_0_ FROM post_comment pc WHERE pc.id = 2 SELECT pc.id AS id1_1_0_, pc.post_id AS post_id3_1_0_, pc.review AS review2_1_0_ FROM post_comment pc WHERE pc.id = 1
The Query Cache only stored the entity identifiers of the matching PostComment
entities. So, if the PostComment
entities are not cached as well, they will be fetched from the database. Hence, you will get N additional SQL statements.
For more details about this topic, check out this article.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Knowing what the N+1 query issue is very important when using any data access framework, not just JPA or Hibernate.
While for entity queries, like JPQL or Criteria API, the JOIN FETCH
clause is the best way to avoid the N+1 query issue, for the query cache, you will need to make sure the underlying entities are stored in the cache.
If you want to automatically detect an N+1 query issue in your data access layer, this article explains how you can do that using the Hypersistence Utils open-source project.
