What is the solution for the N+1 issue in JPA and Hibernate?

by

The problem

The N+1 query issue happens when you forget to fetch an association and then you need to access it.

For instance, let’s assume we have the following JPA query:

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    where pc.review = :review
    """, PostComment.class)
.setParameter("review", review)
.getResultList();

Now, if we iterate the PostComment entities and traverse the post association:

for(PostComment comment : comments) {
    LOGGER.info("The post title is '{}'", comment.getPost().getTitle());
}

Hibernate will generate the following SQL statements:

SELECT pc.id AS id1_1_, pc.post_id AS post_id3_1_, pc.review AS review2_1_
FROM   post_comment pc
WHERE  pc.review = 'Excellent!'

INFO - Loaded 3 comments

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 1

INFO - The post title is 'Post nr. 1'

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 2

INFO - The post title is 'Post nr. 2'

SELECT pc.id AS id1_0_0_, pc.title AS title2_0_0_
FROM   post pc
WHERE  pc.id = 3

INFO - The post title is 'Post nr. 3'

That’s how the N+1 query issue is generated.

Because the post association is not initialized when fetching the PostComment entities, Hibernate must fetch the Post entity with a secondary query, and for N PostComment entities, N more queries are going to be executed (hence the N+1 query problem).

The fix

The first thing you need to do to tackle this issue is to add [proper SQL logging and monitoring][1]. Without logging, you won’t notice the N+1 query issue while developing a certain feature.

Second, to fix it, you can just JOIN FETCH the relationship causing this issue:

List<PostComment> comments = entityManager.createQuery("""
    select pc
    from PostComment pc
    join fetch pc.post p
    where pc.review = :review
    """, PostComment.class)
.setParameter("review", review)
.getResultList();

If you need to fetch multiple child associations, it’s better to fetch one collection in the initial query and the second one with a secondary SQL query.

How to automatically detect the N+1 query issue

This issue is better to be caught by integration tests.

You can use an automatic JUnit assert to validate the expected count of generated SQL statements. The db-util project already provides this functionality, and it’s open-source and the dependency is available on Maven Central.

More Related Contents:

  1. JPA JoinColumn vs mappedBy
  2. Difference between FetchType LAZY and EAGER in Java Persistence API?
  3. How to map a composite key with JPA and Hibernate?
  4. Hibernate Annotations – Which is better, field or property access?
  5. How to map an entity field whose name is a reserved word in JPA
  6. Do I need elements in persistence.xml?
  7. What’s the difference between JPA and Hibernate? [closed]
  8. What is the “owning side” in an ORM mapping?
  9. JPA CascadeType.ALL does not delete orphans
  10. What is the difference between persist() and merge() in JPA and Hibernate?
  11. When and why JPA entities should implement the Serializable interface?
  12. JPQL Create new Object In Select Statement – avoid or embrace?
  13. What is the difference between Unidirectional and Bidirectional JPA and Hibernate associations?
  14. How to remove entity with ManyToMany relationship in JPA (and corresponding join table rows)?
  15. JPA/Hibernate Native Queries do not recognize Parameters
  16. How to solve the LazyInitializationException when using JPA and Hibernate
  17. I found JPA, or alike, don’t encourage DAO pattern
  18. Getting Database connection in pure JPA setup
  19. Hibernate/JPA – annotating bean methods vs fields [duplicate]
  20. How do I properly cascade save a one-to-one, bidirectional relationship on primary key in Hibernate 3.6
  21. JPA: Implementing Model Hierarchy – @MappedSuperclass vs. @Inheritance
  22. Is it possible to dynamically define column names in Hibernate / JPA?
  23. Doing an “IN” query with Hibernate
  24. Specifying an Index (Non-Unique Key) Using JPA
  25. The onFlushDirty Hibernate Interceptor method is never called
  26. Difference between @MapKey, @MapKeyColumn and @MapKeyJoinColumn in JPA and Hibernate
  27. Hibernate throws strange error: Class is not mapped
  28. Batch inserts with JPA/EJB3
  29. Hibernate vs JPA vs JDO – pros and cons of each? [closed]
  30. Clarifying terminology – What does “hydrating” a JPA or Hibernate entity mean when fetching the entity from the DB

Leave a Comment