Solving N+1 Queries

The N+1 query problem is the single most common performance killer in Spring Data JPA applications. It happens when you load N parent entities with one query, then Hibernate fires one extra query per parent to resolve a lazy association, producing 1 + N round-trips to the database instead of one.

This page shows how to reproduce it, how to see it in the logs, and three idiomatic fixes — each with the actual SQL Hibernate generates.

What the Problem Looks Like

Consider a classic one-to-many relationship: an Author owns many Book rows.

@Entity
public class Author {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
    private List<Book> books = new ArrayList<>();

    // getters and setters
}

@Entity
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;

    // getters and setters
}

Now a service that lists every author and counts their books:

List<Author> authors = authorRepository.findAll();
for (Author author : authors) {
    // touching the lazy collection triggers a query PER author
    System.out.println(author.getName() + ": " + author.getBooks().size());
}

With 10 authors, this runs 1 query for the authors plus 10 queries for the books — 11 statements total. At 1,000 authors it is 1,001 queries.

Detecting N+1 in the Logs

You cannot fix what you cannot see. Turn on SQL logging in application.properties:

# Pretty-print the generated SQL
spring.jpa.properties.hibernate.format_sql=true

# Log every SQL statement Hibernate issues
logging.level.org.hibernate.SQL=DEBUG

# Log the bound parameter values (Hibernate 6+)
logging.level.org.hibernate.orm.jdbc.bind=TRACE

Note: In Hibernate 6 the parameter-binding logger moved to org.hibernate.orm.jdbc.bind. The older org.hibernate.type.descriptor.sql category no longer prints values.

Running the loop above produces a tell-tale pattern: one parent query followed by a repeated child query.

-- 1 query for all authors
select a1_0.id, a1_0.name from author a1_0

-- then ONE query per author (the "N")
select b1_0.author_id, b1_0.id, b1_0.title from book b1_0 where b1_0.author_id=?
-- binding parameter [1] as [BIGINT] - [1]
select b1_0.author_id, b1_0.id, b1_0.title from book b1_0 where b1_0.author_id=?
-- binding parameter [1] as [BIGINT] - [2]
select b1_0.author_id, b1_0.id, b1_0.title from book b1_0 where b1_0.author_id=?
-- binding parameter [1] as [BIGINT] - [3]
-- ... and so on, one per author

Whenever you see the same select repeated with only the parameter changing, you have an N+1.

Fix 1: JOIN FETCH in JPQL

The most direct fix is to fetch the association eagerly in a single statement using join fetch.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("select distinct a from Author a join fetch a.books")
    List<Author> findAllWithBooks();
}

select distinct a1_0.id, a1_0.name, b1_0.author_id, b1_0.id, b1_0.title
from author a1_0
join book b1_0 on a1_0.id = b1_0.author_id

One query, no N+1. The distinct keyword de-duplicates the parent rows that the join multiplies.

Warning: JOIN FETCH on a collection breaks database-level pagination. If you add Pageable, Hibernate logs HHH000104: firstResult/maxResults specified with collection fetch; applying in memory and pulls the entire result set into the JVM before paging — a memory risk on large tables. Use fix 2 or 3 when you need paging.

Fix 2: @EntityGraph

@EntityGraph declares which associations to fetch without writing JPQL, so it composes cleanly with derived queries and works better with pagination.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @EntityGraph(attributePaths = {"books"})
    List<Author> findByNameStartingWith(String prefix);
}

select a1_0.id, a1_0.name, b1_0.author_id, b1_0.id, b1_0.title
from author a1_0
left join book b1_0 on a1_0.id = b1_0.author_id
where a1_0.name like ? escape '\'

The graph produces a single left join fetch. It is declarative — you annotate the method instead of hand-writing the join — and it layers on top of Spring Data’s derived queries.

Fix 3: Batch Fetching

Sometimes a join is undesirable (multiple collections, large rows). Batch fetching keeps the lazy loading but groups the child queries into IN (...) lookups, turning N selects into ceil(N / size) selects.

Annotate the collection:

@OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
@BatchSize(size = 5)
private List<Book> books = new ArrayList<>();

Or set it globally in application.properties:

spring.jpa.properties.hibernate.default_batch_fetch_size=10

-- author query as before
select a1_0.id, a1_0.name from author a1_0

-- children fetched in batches via IN, not one-by-one
select b1_0.author_id, b1_0.id, b1_0.title
from book b1_0
where b1_0.author_id in (?, ?, ?, ?, ?)

With 10 authors and size = 5, that is 1 + 2 = 3 queries instead of 11. Crucially, batch fetching never multiplies parent rows, so it stays fully pagination-friendly.

Comparing the Three Fixes

Aspect	JOIN FETCH	@EntityGraph	Batch Size
Single query?	Yes	Yes	No (1 + ceil(N/size))
Pagination-friendly?	No (in-memory paging, HHH000104)	Yes (with `JOINED` graph; some caveats)	Yes
Produces duplicate rows?	Yes (use `distinct`)	Yes (auto de-duplicated)	No
Declarative?	No (JPQL)	Yes (annotation)	Yes (annotation/property)
Best for	One collection, no paging	Reusable graphs, paging	Multiple collections, large datasets

Tip: Reach for @EntityGraph as the default, and switch to batch fetching whenever you need to fetch more than one collection at once or you are paginating a deep result set.

The Multiple-Collection Trap

You cannot JOIN FETCH two List collections in the same query. Hibernate throws MultipleBagFetchException: cannot simultaneously fetch multiple bags because a List (a “bag”) loses its row identity. Even when it does work, fetching two collections in one query creates a cartesian product — rows = books x reviews — which explodes result size.

Warning: To fetch multiple collections together, change the field type from List to Set, or keep List and fetch the second collection with @BatchSize instead. Mixing two eager List fetches will fail at startup or balloon the result set. See fetch types for the lazy-vs-eager trade-offs.