N + 1 Query Problem With JPA And Hibernate

In this article, I will explain what the SQL N+1 query problem is, in which situations this problem occurs, and provide details along with solutions.

What is the N+1 Query Problem?

The N+1 query problem arises when an entity has a relationship with another entity. This issue occurs when Hibernate generates unnecessary additional queries to populate the associated entities. In other words, a result set that could be fetched with a single query is instead retrieved using 1 + (number of child entities) queries. This situation is particularly common in scenarios where related data is loaded using lazy loading.

N + 1 Query Problem When FetchType is Eager

Let me demonstrate the N+1 query problem with an example using FetchType.EAGER. For this example, our entity classes will be as follows:

Post Entity

@Entity
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
   
    private String content;

    //Getter and Setter methods...
}

PostComment Entity

@Entity
public class PostComment {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
   
    //ManyToOne's FetchType is Eager as default
    @ManyToOne
    private Post post;
   
    private String comment;

    //Getter and Setter methods...
}

There is a one-to-many relationship between the Post and PostComment entities described above. Therefore, when performing a sequence of operations as shown in the code example below, additional SELECT queries will be executed.

// CREATE OPERATIONS
Post post_1 = new Post();
post_1.setContent("This is the first post content.");

Post post_2 = new Post();
post_2.setContent("This is the second post content.");

Post post_3 = new Post();
post_3.setContent("This is the third post content.");

List<Post> persistedPostList = postRepository.saveAll(List.of(post_1, post_2, post_3));

PostComment postComment_1 = new PostComment();
postComment_1.setComment("The content of first post is awesome.");
postComment_1.setPost(persistedPostList.get(0));

PostComment postComment_2 = new PostComment();
postComment_2.setComment("The content of second post is awesome.");
postComment_2.setPost(persistedPostList.get(1));

PostComment postComment_3 = new PostComment();
postComment_3.setComment("The content of third post is awesome.");
postComment_3.setPost(persistedPostList.get(2));

postCommentRepository.saveAll(List.of(postComment_1, postComment_2, postComment_3));
h
// FETCH OPERATION
postCommentRepository.findAll();

Hibernate query execution log

Hibernate: select pc1_0.id, pc1_0.comment, pc1_0.post_id from post_comment pc1_0
Hibernate: select p1_0.id, p1_0.content from post p1_0 where p1_0.id=?
Hibernate: select p1_0.id, p1_0.content from post p1_0 where p1_0.id=?
Hibernate: select p1_0.id, p1_0.content from post p1_0 where p1_0.id=?

When we focus on the logs of the SELECT queries generated by Hibernate, we can see that a total of 4 queries were executed. Since we have 3 PostComment entities, 3 additional queries were generated to populate the Post field for each individual PostComment entity.

How Can We Reduce the Number of Queries to a Single Query?

To minimize the number of queries generated by Hibernate and avoid the N+1 query problem, we can explicitly instruct Hibernate to fetch the associated entities in a single query. This can be achieved by using JOIN FETCH in JPQL or Criteria API queries.

PostComment Repository

public interface PostCommentRepository extends JpaRepository<PostComment, Long> {
    @Query("SELECT pc FROM PostComment pc JOIN FETCH pc.post")
    List<PostComment> getAllPostComments();
}

Hibernate query execution log

Hibernate: select pc1_0.id,pc1_0.comment,p1_0.id,p1_0.content from post_comment pc1_0 join post p1_0 on p1_0.id=pc1_0.post_id

In the Hibernate log, it is visible that only a single query was generated for all comments. This approach significantly improves performance when working with large amounts of data, as it eliminates the need for multiple additional queries.

N + 1 Query Problem When FetchType is Lazy

Even when the relationship type between entities is set to FetchType.LAZY, we can still encounter the N+1 query problem. In an example where we list PostComment entities, Hibernate initially generates a single query. However, if a method call triggers lazy loading on the retrieved PostComment entities, it will execute a query for each PostComment entity.

As shown in the example below, calling the getPost() method within a loop will lead to the N+1 query problem:

Get content of Post entity for each individual PostComment entity

List<PostComment> postCommentList = postCommentRepository.findAll();
postCommentList.forEach(postComment -> System.out.println(postComment.getPost().getContent()));

Hibernate query execution log

Hibernate: select p1_0.id, p1_0.content from post p1_0 where p1_0.id=?
Hibernate: select p1_0.id, p1_0.content from post p1_0 where p1_0.id=?
Hibernate: select p1_0.id, p1_0.content from post p1_0 where p1_0.id=?

To solve the N+1 query problem with lazy fetching, we should apply JOIN FETCH, just as we do with eager fetching.

Conclusion

The N+1 query problem can significantly impact the performance of your application, whether you’re using lazy or eager fetching. By understanding the underlying behavior of Hibernate you can effectively optimize your data access layer. Choosing the appropriate strategy depends on your application’s requirements, but proactive optimization can help avoid unnecessary database queries and improve overall efficiency. Always monitor your Hibernate logs and test with real-world data to ensure your solution scales effectively.