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?

Solution 1

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.

Solution 2

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.

Solution 3

Native solution for 1 + N in Hibernate, is called:

20.1.5. Using batch fetching

Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways we can configure batch fetching: on the 1) class level and the 2) collection level...

Check these Q & A:

• @BatchSize but many round trip in @ManyToOne case
• Avoiding n+1 eager fetching of child collection element association

With annotations we can do it like this:

A class level:

@Entity
@BatchSize(size=25)
@Table(...
public class MyEntity implements java.io.Serializable {...

A collection level:

@OneToMany(fetch = FetchType.LAZY...)
@BatchSize(size=25)
public Set<MyEntity> getMyColl() 

Lazy loading and batch fetching together represent optimization, which:

• does not require any explicit fetching in our queries
• will be applied on any amount of references which are (lazily) touched after the root entity is loaded (while explicit fetching effects only these named in query)
• will solve issue 1 + N with collections (because only one collection could be fetched with root query) without need to farther processing To get DISTINCT root values (check: Criteria.DISTINCT_ROOT_ENTITY vs Projections.distinct)

Solution 4

You can even get it working without having to add the @BatchSize annotation everywhere, just set the property hibernate.default_batch_fetch_size to the desired value to enable batch fetching globally. See the Hibernate docs for details.

While you are at it, you will probably also want to change the BatchFetchStyle, because the default (LEGACY) is most likely not what you want. So a complete configuration for globally enabling batch fetching would look like this:

hibernate.batch_fetch_style=PADDED
hibernate.default_batch_fetch_size=25

Also, I'm suprised that one of the proposed solutions involves join-fetching. Join-fetching is rarely desirable because it causes more data to be transferred with every result row, even if the dependent entity has already been loaded into the L1 or L2 cache. Thus I would recommend to disable it completey by setting

hibernate.max_fetch_depth=0

Solution 5

This is a frequently asked question so I created the article Eliminate Spring Hibernate N+1 Queries to detail the solutions

To help you detect all the N+1 queries in your application and avoid adding more queries, I created the library spring-hibernate-query-utils that auto-detects the Hibernate N+1 queries.

Here is some code to explain how to add it to your application:

• Add the library to your dependencies

<dependency>
    <groupId>com.yannbriancon</groupId>
    <artifactId>spring-hibernate-query-utils</artifactId>
    <version>1.0.0</version>
</dependency>

• Configure it in your application properties to return exceptions, default is error logs

hibernate.query.interceptor.error-level=EXCEPTION

View more solutions

Comments

[Tunaki]

Use lazy-loading: stackoverflow.com/q/2192242

[BetaRide]

@Tunaki: That's wrong. Whether you have eager or lazy loading does say when the select is executed. It does not at all avoid the N+1 issue.

[DavidS]

@BetaRide, maybe I'm misunderstanding your comment, but despite your seven upvotes it sounds completely wrong. Eager fetching does absolutely avoid the N+1 issue. See point 3 of this blog post by a JPA expert.

[Vipul Agarwal]

I think batch fetching or lazy loading is just delaying the queries and not actually avoiding the multiple queries. This doesn't solve the problem as such. It just is one way to have less impact of it.

[Radim Köhler]

The fact is, that I use this setting on every Class and every collection. Every. Whenever I load list of some entity, and touch its reference or collection ... only ONE (well depends on batch size) SELECT for all of them (1 + 1) is executed. That's the real, built in solution for 1 + N. Even more, we do not have to change our queries (to use Fetch.mode on some references or collection)... we can query just root entity... with very few SELECT later (lazily) we get all loaded... hope it is clear a bit more ;)

[Dragan Bozanovic]

+1. Lazy loading with batch fetching and, only when really needed, join fetch in queries is the most straightforward and standard way to deal with n+1 selects problem.

[faizan]

What can I do if I am updating a set of objects of same entity in a single transaction, the orm layer issues multiple updates but I want one update statement to work. Is that doable? How?

[loicmathieu]

Batch fetching doesn't avoid the n+1 query but divide the number of n queries by the batch size. So the appropriate question si the join fetch. Moreover, batch fetching is defined at entity level, so if multiple queries use the same entity they will use the same batch size and it can be problematic (imagine the same entity used in a batch and in a GUI)

[aksh1618]

Welcome to Stack Overflow! While self-promotion isn't an issue if it helps, please consider adding some code/ explanation that relates to solving the actual problem for it to be a valuable answer, or consider using comments once you have answered enough questions :)

[Ybri]

Thanks for your advice @aksh1618 I added some code to show how it relates to the problem.