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

Question

I understand that the N+1 problem is where one query is executed to fetch N records and N queries to fetch some relational records.

But how can it be avoided in Hibernate?

Answer 1

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.

Answer 2

Suppose we have a class Manufacturer with a many-to-one relationship with Contact.

We solve this problem by making sure that the initial query fetches all the data needed to load the objects we need in their appropriately initialized state. One way of doing this is using an HQL fetch join. We use the HQL

"from Manufacturer manufacturer join fetch manufacturer.contact contact" 

with the fetch statement. This results in an inner join:

select MANUFACTURER.id from manufacturer and contact ... from  MANUFACTURER inner join CONTACT on MANUFACTURER.CONTACT_ID=CONTACT.id 

Using a Criteria query we can get the same result from

Criteria criteria = session.createCriteria(Manufacturer.class); criteria.setFetchMode("contact", FetchMode.EAGER); 

which creates the SQL :

select MANUFACTURER.id from MANUFACTURER left outer join CONTACT on  MANUFACTURER.CONTACT_ID=CONTACT.id where 1=1 

in both cases, our query returns a list of Manufacturer objects with the contact initialized. Only one query needs to be run to return all the contact and manufacturer information required

for further information here is a link to the problem and the solution.