What is the N+1 problem, and how do you detect it?

The Answer

Say this in the room. 45 seconds.

• One query returns N parents. Touching a lazy association on each one fires one more query per parent — hence 1 + N.
• You cannot see it by reading the code. order.getItems() is a getter. The loop that costs one query and the loop that costs a thousand look identical.
• You detect it by counting queries, and the only reliable way is to assert on the count in a test. Logging SQL finds it once; a test stops it coming back.
• Four fixes, and they are not interchangeable: join fetch (1 query), @EntityGraph (the declarative form of the same thing), batch size (1 + n/batch), subselect (2).
• Making the mapping EAGER is not a fix. It moves the queries, fires them on every query that returns that entity, and usually makes the total worse.
• A fetch join and pagination do not combine. Hibernate cannot apply the limit in SQL, so it reads the whole result and paginates in memory — one query, and the entire table.

Understand It

Everything below runs against a model of an ORM's lazy loading, not Hibernate. It exists to do the one thing that finds this bug — count the queries. What is not modelled: SQL itself, the persistence context's identity map, or caching. Where the model would mislead, the prose says so.

The loop that looks free

// The loop every codebase has. Twenty orders, three items each.
var log = new QueryLog();
var orm = new Orm(log, Strategy.LAZY, 0).seed(20, 3);

var orderIds = orm.findOrders(20);
int lines = 0;
for (long id : orderIds) {
    lines += orm.items(id, orderIds).size();       // looks free. is not.
}

System.out.println("  orders   : " + orderIds.size());
System.out.println("  lines    : " + lines);
System.out.println("  queries  : " + log.count());
System.out.println();
System.out.println("  first four statements:");
log.statements().stream().limit(4).forEach(s -> System.out.println("    " + s));

  orders   : 20
  lines    : 60
  queries  : 21

  first four statements:
    select o from Order o limit 20
    select i from Item i where i.orderId = 1
    select i from Item i where i.orderId = 2
    select i from Item i where i.orderId = 3

Twenty-one queries to build one page. The important thing is not the number — it is that nothing in the Java hints at it. In real code that line is order.getItems().size(), a getter call on an object you already have, and a getter that issues a database query looks exactly like one that does not.

That is why this bug is not found by review. Two engineers can read the loop, agree it is correct — it is correct — and ship a page that does a thousand round trips. The only thing that distinguishes the fast version from the slow one is a number neither of them was looking at.

It is also why this scales the way it does. Twenty orders in a test fixture is twenty-one queries, fast enough that nobody notices. A thousand orders in production is a thousand and one, each with its own network round trip, and the endpoint times out.

Four fixes, and what each one actually costs

// Same loop, same data, four loading strategies. Only the mapping changes.
System.out.printf("  %-28s %-9s %s%n", "strategy", "queries", "what it costs");
for (var s : List.of(Strategy.LAZY, Strategy.BATCH, Strategy.SUBSELECT, Strategy.JOIN_FETCH)) {
    int queries = countQueriesForLoop(s, 20, 3, 5);
    String cost = switch (s) {
        case LAZY -> "nothing, until you count";
        case BATCH -> "one IN query per 5 parents";
        case SUBSELECT -> "re-runs the parent query as a subquery";
        case JOIN_FETCH -> "duplicate parent rows on the wire";
    };
    System.out.printf("  %-28s %-9d %s%n", s, queries, cost);
}

// And it scales the way you would fear.
System.out.println();
for (int n : new int[] { 10, 100, 1000 })
    System.out.printf("  lazy with %4d orders -> %5d queries%n", n, countQueriesForLoop(Strategy.LAZY, n, 3, 5));

  strategy                     queries   what it costs
  LAZY                         21        nothing, until you count
  BATCH                        5         one IN query per 5 parents
  SUBSELECT                    2         re-runs the parent query as a subquery
  JOIN_FETCH                   1         duplicate parent rows on the wire

  lazy with   10 orders ->    11 queries
  lazy with  100 orders ->   101 queries
  lazy with 1000 orders ->  1001 queries

Fewer queries is not automatically better, and the last column is why.

Join fetch gets to one query by returning one row per child, so a parent with fifty items arrives fifty times. For small collections that is the right trade. For a parent with several collections it is a cartesian product, and the row count multiplies rather than adds — which is the one case where two queries genuinely beat one.

Batch size is the setting most people should reach for and most people have never set. It is a mapping-level default (@BatchSize, or hibernate.default_batch_fetch_size), it needs no change at the call site, and it turns 1 + N into 1 + N/batch everywhere at once. It does not eliminate round trips; it makes them proportional rather than linear.

Subselect is two queries regardless of N, which is the best asymptotic answer here, and it re-runs the original parent query as a subquery — so if that query was expensive, you pay for it twice.

Lazy is not on this list as a mistake. It is the right default: a page that never touches items should not pay for them. The bug is not laziness, it is laziness plus a loop.

Why EAGER makes it worse

// The "fix" people try first: change the mapping to EAGER so the association
// is always there. Here items stay lazy and the child's customer is eager.
var log = new QueryLog();
var orm = new Orm(log, Strategy.LAZY, 0).seed(20, 3);
var ids = orm.findOrders(20);
for (long id : ids) {
    orm.items(id, ids);
    orm.customerOf(id);          // an EAGER many-to-one fires here, every time
}
System.out.println("  lazy items only            : " + countQueriesForLoop(Strategy.LAZY, 20, 3, 0) + " queries");
System.out.println("  plus one EAGER association : " + log.count() + " queries");

  lazy items only            : 21 queries
  plus one EAGER association : 41 queries

Twenty-one becomes forty-one. EAGER did not eliminate a round trip; it added one per row for an association this page never asked for.

The deeper problem is that EAGER is a property of the mapping, not of the query. Once it is on, it fires for every query anywhere in the application that returns an Order — the list page, the search results, the export, the health check that counts rows. A fetch join is a property of one query and affects only that query, which is why the correct instinct is lazy mappings plus explicit fetching where it is needed.

This is also the answer to "why not just make everything eager": because eager is a decision made once, in a mapping file, on behalf of every query that will ever be written against that entity, by someone who cannot know what those queries need.

Where the obvious fix stops working

// The same fetch join, now asking for a page of 10 out of 1000 orders.
for (var strategy : List.of(Strategy.LAZY, Strategy.JOIN_FETCH)) {
    var log = new QueryLog();
    var orm = new Orm(log, strategy, 0).seed(1000, 5);
    var page = orm.findOrders(10);
    for (long id : page) orm.items(id, page);

    System.out.printf("  %-11s page of %2d -> %2d queries, %d rows read%s%n",
        strategy, page.size(), log.count(), orm.rowsFetched,
        orm.paginatedInMemory ? ", then paginated in memory" : "");
}

  LAZY        page of 10 -> 11 queries, 10 rows read
  JOIN_FETCH  page of 10 ->  1 queries, 5000 rows read, then paginated in memory

One query looks like a win until you read the second number. Five thousand rows to return ten.

A fetch join produces one row per child, so a SQL LIMIT 10 would return ten item rows — three orders, one of them with its collection cut in half. Rather than return wrong data, Hibernate declines to apply the limit in SQL, reads the entire result set, and paginates in memory. It tells you, in a log line almost nobody reads:

HHH000104: firstResult/maxResults specified with collection fetch; applying in memory

That warning is one of the highest-value strings to grep your logs for. It is the difference between an endpoint that reads ten rows and one that reads the table, and it appears the moment someone adds Pageable to a repository method that already had a fetch join — a change that looks entirely safe.

The fix is two queries on purpose: page the ids first, then fetch the collections for that page.

Reference

The correct implementation, the configuration, and the migration path. Copy from here.

Detecting it, in order of usefulness

# 1. See the queries at all. Development only — this is very noisy.
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG

# 2. Get the count without the noise. datasource-proxy or p6spy will do this,
#    and Hibernate 6 exposes statistics directly.
spring.jpa.properties.hibernate.generate_statistics=true

// 3. The one that stops it coming back: assert on the count in a test.
@Test
void listingOrdersTakesOneQuery() {
    var statistics = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
    statistics.clear();

    orderService.listRecent(PageRequest.of(0, 20));

    assertThat(statistics.getPrepareStatementCount())
        .as("N+1 check — see jpa-n-plus-one")
        .isEqualTo(1);
}

The test is the point. Logging finds an N+1 once; a test that fails when the count changes stops the next one, including the one someone reintroduces by adding an innocent-looking getItems() call two years from now. Assert an exact number rather than a bound — a test that allows "up to 5" will happily pass at 5 when it used to be 1.

The four fixes, as code

// Join fetch — one query, for one specific use case.
@Query("select distinct o from Order o join fetch o.items where o.status = :status")
List<Order> findWithItems(@Param("status") Status status);

// @EntityGraph — the same thing, declaratively, and it composes with derived queries.
@EntityGraph(attributePaths = { "items", "customer" })
List<Order> findByStatus(Status status);

// Batch size — a default that fixes every loop at once, including the ones
// nobody has written yet. Set it globally and stop thinking about it.
// application.properties:
//   spring.jpa.properties.hibernate.default_batch_fetch_size=50
@BatchSize(size = 50)
@OneToMany(mappedBy = "order")
private List<Item> items;

// Subselect — two queries whatever N is.
@Fetch(FetchMode.SUBSELECT)
@OneToMany(mappedBy = "order")
private List<Item> items;

If you change one thing after reading this, set default_batch_fetch_size. It costs nothing, needs no code change, and converts every unfixed N+1 in the application from linear to a fraction of linear.

Paginating a collection fetch

// Wrong: triggers HHH000104 and reads everything.
@Query("select o from Order o join fetch o.items")
Page<Order> findAllWithItems(Pageable pageable);

// Right: page the ids in SQL, then fetch the collections for just those ids.
@Query("select o.id from Order o where o.status = :status")
Page<Long> findIdPage(@Param("status") Status status, Pageable pageable);

@Query("select distinct o from Order o join fetch o.items where o.id in :ids")
List<Order> findWithItems(@Param("ids") List<Long> ids);

Two queries, and the first one is a real paged query the database can optimise. This is the standard shape and it is worth recognising on sight, because a repository returning Page<T> with a join fetch in it is always a bug.

Not fetching at all

// The best fix for a read-only screen is often to stop loading entities.
public interface OrderSummary {          // a projection interface
    Long getId();
    String getReference();
    int getItemCount();
}

@Query("""
    select o.id as id, o.reference as reference, count(i) as itemCount
    from Order o left join o.items i
    where o.status = :status
    group by o.id, o.reference
    """)
List<OrderSummary> summarise(@Param("status") Status status);

A list screen usually needs a handful of columns and a count, not object graphs with dirty checking and lazy proxies attached. A projection sidesteps the entire problem: there is no association to load lazily, so there is no N+1 to have.

What each fix does to the query count

Scenarios

Real situations, with the decision and the argument.

1. An endpoint is fast in staging and times out in production.

Check the row count before anything else. Staging has fifty orders and production has fifty thousand, and an N+1 is invisible at fifty — twenty milliseconds of overhead nobody would investigate — while being fatal at fifty thousand.

The tell is that latency scales with the number of rows returned rather than with load. Turn on statistics for one request, count the queries, and compare against the number of rows. If they match, you have found it, and no profiler was needed.

2. Someone fixes an N+1 by changing the mapping to EAGER.

It will look fixed on the page they were testing and will have made the system slower overall. EAGER is a property of the mapping, so it now fires on every query returning that entity anywhere in the application — including screens that never touch the association.

Push back with the specific alternative rather than the principle: keep the mapping lazy and add @EntityGraph to the one repository method that needs it. That fixes the same page, changes nothing else, and is visible at the call site where the next reader will look.

3. A repository returns Page<Order> with a join fetch and the logs are full of HHH000104.

The limit is not being applied in SQL, so every call reads the whole result set and paginates in memory. It will have been introduced by adding Pageable to a method that already had the fetch join, which is a one-line change that looks entirely safe.

Split it: page the ids with a plain query, then fetch collections for that page of ids. Two queries, both properly limited. This is worth adding to a review checklist, because the combination is easy to write and the failure is invisible until the table is large.

4. Batch size is proposed globally and someone objects that it is a blunt instrument.

They are right that it is blunt and wrong that this is a reason not to do it. default_batch_fetch_size does not eliminate round trips, it makes them proportional — 1 + N/50 instead of 1 + N — and it applies to every loop in the codebase including the ones nobody has found.

The argument for it is coverage, not optimality: targeted fetch joins fix the loops you know about, and batch size limits the damage from the ones you do not. Do both. There is no scenario where the unfixed N+1 is better off without it.

5. A list screen is slow and every association is already fetched correctly.

Then the problem is probably that you are loading entities at all. A read-only list needs a few columns; an entity carries the full row, a persistence-context entry, dirty-check state and lazy proxies for everything you did not ask for.

A projection or DTO query removes all of it, and the conversation to have is about the boundary rather than the query: entities are for writes, and read screens are usually better served by a query shaped like the screen. It is a larger change than a fetch join, so it wants evidence — measure the two before arguing for it.

Interviewer's Next Move

The question after the question. This is what the round is actually testing.

1. "What is the N+1 problem?" One query returns N parents, and touching a lazy association on each fires one more query per parent. Twenty rows cost twenty-one queries; a thousand cost a thousand and one.

2. "How do you detect it?" By counting queries, not by reading code. Turn on Hibernate statistics or a query-counting proxy, then write a test that asserts the exact count — because the loop that costs one query and the loop that costs a thousand are identical to read.

3. "Why won't code review catch it?" Because the expensive line is a getter. order.getItems() looks like reading a field you already have, and nothing at the call site distinguishes an initialised collection from one that is about to hit the database.

4. "How do you fix it?" Join fetch or @EntityGraph for the specific query, batch size as a global default, subselect when two queries beat one big result set, or a projection when the screen does not need entities at all. They are not interchangeable.

5. "Why not just make the association EAGER?" Because eager is a property of the mapping, not the query — it fires on every query anywhere that returns that entity, including screens that never touch the association. It usually raises the total query count rather than lowering it.

6. "When is a join fetch the wrong choice?" With pagination, where Hibernate cannot apply the limit in SQL and reads the whole result set into memory — the HHH000104 warning. And with two collections on the same parent, where the join becomes a cartesian product and the row count multiplies.

7. "So how do you paginate a collection fetch?" Two queries. Page the ids with a plain query the database can limit, then fetch collections for that page of ids. A repository method returning Page<T> with a fetch join in it is always a bug.

8. "What single setting would you change on a legacy codebase?" hibernate.default_batch_fetch_size. It needs no code change and converts every N+1 in the application to 1 + N/batch, including the ones nobody has found yet.

9. "How do you stop it coming back?" A test that asserts an exact query count on the critical endpoints. Logging finds one instance; the assertion fails the build when someone reintroduces it, which they will, because the change that causes it looks harmless.

Code traps

Trap A — predict before you run:

@GetMapping("/orders")
public List<OrderDto> list() {
    return orderRepository.findAll().stream()
        .map(o -> new OrderDto(o.getId(), o.getItems().size()))
        .toList();
}

Trap B:

@Query("select o from Order o join fetch o.items")
Page<Order> findAllWithItems(Pageable pageable);

Trap C:

@OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
private List<Item> items;

@OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
private List<Payment> payments;

Common wrong answers

Check Yourself

Q1. Why is an N+1 essentially invisible in code review?

Q2. A colleague changes an association to FetchType.EAGER and the slow page gets faster. What is wrong with the fix?

Q3. A repository method has join fetch and returns Page<Order>. One query, correct results. What is the problem?

Practice