Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 39 ยท Caching and Performance

N+1 Queries and Fetch Strategies

Find and fix Hibernate's N+1 select problem with lazy loading, fetch joins, batch fetching, entity graphs and DTO projections, and count the queries in a runnable demo.

The most common Hibernate performance bug has a name: N+1 selects. You load a list of N parents with one query, then touch a lazy association on each parent, and Hibernate quietly runs one more query per parent. A page that should need one query runs 101. Nobody notices in development with five rows, and production notices with five thousand.

This lesson shows how it happens, how to detect it, and the five standard cures. Hibernate itself is not available in the sandbox, so its code is read-only; the runnable programs count the SQL statements of equivalent JDBC access patterns so the difference is visible in numbers.

How N+1 happens

Take Author with @OneToMany(mappedBy = "author") List<Book> books. Collections are lazy by default: the books list is a proxy that loads on first access.

Java
List<Author> authors = session.createSelectionQuery("from Author", Author.class).getResultList(); // 1 query

for (Author a : authors) {
    System.out.println(a.getName() + ": " + a.getBooks().size());   // 1 query PER author
}

With 100 authors that is 1 + 100 = 101 queries. The same happens with @ManyToOne loaded lazily and read inside a loop, and with EAGER mappings queried through HQL (HQL ignores the mapping's eager hint and issues follow-up selects, which is why switching everything to EAGER is not a fix).

Count the queries

Java 1 + N queries versus one join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The lazy loop needs 6 statements for 5 authors; the join needs 1; batching needs 2 (plus the initial author query). Scale 5 to 5,000 and the gap becomes a production incident.

How to detect it

  • Turn on SQL logging in tests and development: hibernate.show_sql (simple) or the org.hibernate.SQL logger at DEBUG (with bind values at TRACE for org.hibernate.orm.jdbc.bind).
  • Use Hibernate statistics (hibernate.generate_statistics=true): statistics.getPrepareStatementCount() is the number to watch. Statistics are covered later in this chapter.
  • Assert query counts in tests. A test that loads 20 rows and asserts "at most 2 statements" catches regressions forever. Libraries such as datasource-proxy or Hypersistence Utils offer assertion helpers.
  • Look at a profiler or APM trace: a staircase of identical short queries is the signature.

Cure 1: fetch joins

Java
List<Author> authors = session.createSelectionQuery(
        "select a from Author a left join fetch a.books", Author.class).getResultList();

One query returns authors and books together, and the collection is initialized. Limits to know:

  • Fetch one collection per query. Two bag collections (List without an order column) at once throw MultipleBagFetchException, and even with Set the result is a cartesian product. Fetch one collection with a join and the rest with batch fetching or a second query.
  • Do not combine a collection fetch with setMaxResults: Hibernate warns "firstResult/maxResults specified with collection fetch; applying in memory" and loads everything. Page the parent ids first, then fetch the books for that page with where a.id in :ids.
  • Single-valued associations (@ManyToOne, @OneToOne) are safe and cheap to fetch-join.

Cure 2: batch fetching

Keep the lazy mapping but let Hibernate load several collections or proxies at once:

Java
@OneToMany(mappedBy = "author")
@BatchSize(size = 32)
private List<Book> books;

or globally with hibernate.default_batch_fetch_size=32. When the first a.getBooks() is touched, Hibernate loads books for up to 32 authors in one where author_id in (?, ?, ...) query. N+1 becomes 1 + N/32. This is the safest cure to apply broadly, because it never changes the result shape and works with paging. Hibernate 6.x can also use an array parameter (= any(?)) on databases that support it.

Cure 3: entity graphs

An entity graph states what to fetch, separately from the query text:

Java
EntityGraph<Author> graph = session.createEntityGraph(Author.class);
graph.addAttributeNodes("books");

List<Author> authors = session.createSelectionQuery("from Author", Author.class)
    .setHint("jakarta.persistence.fetchgraph", graph)
    .getResultList();

Named graphs (@NamedEntityGraph) work for repeated cases, and Spring Data JPA offers @EntityGraph(attributePaths = "books") on repository methods. The generated SQL is a join like join fetch, with the same limits.

Cure 4: subselect and fetch modes

@Fetch(FetchMode.SUBSELECT) loads the collections of all authors from the original query in a second query that reuses it as a subselect. That makes two queries total, regardless of N, but it re-runs the original query logic in the database and cannot be combined with later changes of the first query's parameters. @Fetch(FetchMode.JOIN) and SELECT select the default strategy when you use find by id.

Cure 5: stop loading entities

If a screen only displays names and counts, entities are the wrong tool. A DTO query does the whole job in one statement and loads nothing into the persistence context:

Java
"select new com.example.AuthorStat(a.name, count(b)) from Author a left join a.books b group by a.id, a.name"

The next demo compares the memory work of entity-style loading with one aggregate query:

Java An aggregate query instead of loading rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The mapping side: lazy by default

Defaults matter: @ManyToOne and @OneToOne are eager by default in JPA, @OneToMany and @ManyToMany are lazy. The standard advice is to declare every association fetch = FetchType.LAZY and ask for what a use case needs per query. Eager mappings hide N+1 inside find and cannot be made lazy per query. Lazy @OneToOne on the non-owning side may still load eagerly unless you use a shared primary key (@MapsId) or bytecode enhancement.

Avoid Open Session In View as a way to make lazy loading "just work" in web views: it keeps the session open during rendering, which hides N+1 in templates and holds a database connection longer. Fetch what the view needs inside the service layer and return DTOs.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the N+1 select problem?

    Touching a lazy association inside a loop triggers one query per element, on top of the initial query.
  2. 2. Which cure keeps the lazy mapping and works with pagination?

    Batch fetching loads several collections with one IN query and does not change the main query, so paging stays correct.
  3. 3. Why is join fetch of a collection combined with setMaxResults a problem?

    The joined result has several rows per parent, so SQL limits would cut parents' collections; Hibernate loads everything and pages in memory.
  4. 4. For a screen that shows author names and book counts, what is usually best?

    A single grouped query returns just the numbers and avoids loading and tracking entities.

Summary

  • N+1 happens when a lazy association is read inside a loop; it is invisible until data grows.
  • Detect it with SQL logging, Hibernate statistics and query-count assertions in tests.
  • Fix it with fetch joins (one collection), batch fetching, entity graphs, subselect fetching or DTO projections.
  • Map associations lazily and decide per use case what to fetch.

Next: Batch Processing and Hibernate Statistics