The N+1 problem

The N+1 problem is the most common performance trap in Hibernate. You load a list of N posts with one query, then touch the author of each one — and every lazy author is loaded by its own query. One query for the list plus N for the details. With 3 posts that is barely visible; with 500 posts it is 501 queries per page.

Reproducing it

The test data is the Seed class from the previous lesson: three posts by Anna, Oleg and Maria.

// src/main/java/com/example/blog/Main.java
Seed.run(emf);
emf.runInTransaction(em -> {
    List<Post> posts = em.createQuery("select p from Post p order by p.id", Post.class)
            .getResultList();
    for (Post post : posts) {
        IO.println(post.getTitle() + " by " + post.getAuthor().getName());
    }
});

Hibernate: select p1_0.id,p1_0.author_id,p1_0.title,p1_0.views from Post p1_0 order by p1_0.id
Hibernate: select a1_0.id,a1_0.name from Author a1_0 where a1_0.id=?
Hello, Hibernate by Anna
Hibernate: select a1_0.id,a1_0.name from Author a1_0 where a1_0.id=?
Records in Java 25 by Oleg
Hibernate: select a1_0.id,a1_0.name from Author a1_0 where a1_0.id=?
Lazy loading by Maria

Four queries for three posts. The code looks innocent — the problem is visible only in the SQL log.

Fix 1: join fetch

join fetch in the query tells Hibernate to load the relationship together with the main entity:

// src/main/java/com/example/blog/Main.java
List<Post> posts = em.createQuery(
                "select p from Post p join fetch p.author order by p.id", Post.class)
        .getResultList();

Hibernate: select p1_0.id,a1_0.id,a1_0.name,p1_0.title,p1_0.views from Post p1_0 join Author a1_0 on a1_0.id=p1_0.author_id order by p1_0.id
Hello, Hibernate by Anna
Records in Java 25 by Oleg
Lazy loading by Maria

One query. Note that join fetch is an inner join: posts without an author would disappear. For optional relationships write left join fetch.

Fix 2: an entity graph

An entity graph lists the relationships to load, separately from the query text. The same query can be reused with different graphs:

// src/main/java/com/example/blog/Main.java
EntityGraph<Post> graph = em.createEntityGraph(Post.class);
graph.addAttributeNode("author");
graph.addAttributeNode("comments");
 
List<Post> posts = em.createQuery("select p from Post p order by p.id", Post.class)
        .setHint("jakarta.persistence.fetchgraph", graph)
        .getResultList();

Hibernate: select p1_0.id,a1_0.id,a1_0.name,c1_0.post_id,c1_0.id,c1_0.text,p1_0.title,p1_0.views from Post p1_0 left join Author a1_0 on a1_0.id=p1_0.author_id left join Comment c1_0 on p1_0.id=c1_0.post_id order by p1_0.id
Hello, Hibernate by Anna, comments: 2
Records in Java 25 by Oleg, comments: 1
Lazy loading by Maria, comments: 0

Authors and comments came in one query. The first post has two comments, so the database returned it twice, but Hibernate removed the duplicates itself — the list has three posts.

Fix 3: batch fetching

If you cannot change the queries, add a property to persistence.xml:

<!-- src/main/resources/META-INF/persistence.xml -->
<property name="hibernate.default_batch_fetch_size" value="20"/>

Lazy relationships are now loaded in groups:

Hibernate: select p1_0.id,p1_0.author_id,p1_0.title,p1_0.views from Post p1_0 order by p1_0.id
Hibernate: select a1_0.id,a1_0.name from Author a1_0 where a1_0.id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Two queries instead of N+1, without touching the code. In Spring Boot the same property is written as spring.jpa.properties.hibernate.default_batch_fetch_size=20.

Common mistake

Switching the relationship to FetchType.EAGER does not fix N+1 in queries. With @ManyToOne(fetch = FetchType.EAGER) the same JPQL query still loads every author separately — even if the code never touches them:

Hibernate: select p1_0.id,p1_0.author_id,p1_0.title,p1_0.views from Post p1_0 order by p1_0.id
Hibernate: select a1_0.id,a1_0.name from Author a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.name from Author a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.name from Author a1_0 where a1_0.id=?

Keep relationships lazy and say what to load in each query.

Thanks for reading!