Course topics

By WebNest Studio Engineering Team

Spring Boot Tutorial

Solving the N+1 Query Problem

The N+1 problem is the most common performance bug in JPA applications. You load a list of 50 orders with one query, then your code (or Jackson, while serialising JSON) touches order.getCustomer() on each one, and Hibernate quietly runs 50 more queries. The page works fine in development with five rows and falls over in production with thousands.

This lesson shows how to detect N+1 queries, and the four main fixes: fetch joins, entity graphs, batch fetching, and DTO projections — along with the pagination pitfall of fetching collections.

Why It Happens

Lazy associations are loaded on first access. Loading N parent rows takes one query; accessing a lazy association on each parent takes one query per parent: N+1 in total. Eager fetching does not fix it — for JPQL queries Hibernate still loads EAGER associations with separate queries, and now it happens even when you did not need the data.

Detecting N+1 Queries

Turn on SQL logging in development (logging.level.org.hibernate.SQL=debug) and watch for repeated identical queries. Hibernate statistics (hibernate.generate_statistics=true) report how many statements ran per session. In tests, you can assert the number of queries so regressions are caught automatically.

Fix 1: Fetch Join

join fetch in JPQL loads the association in the same SQL query. It is explicit and efficient for to-one associations. Fetch-joining a collection multiplies rows, so combine it with distinct semantics (automatic in Hibernate 6+) and never with pagination — Hibernate would load everything and paginate in memory (it warns "firstResult/maxResults specified with collection fetch").

Fix 2: @EntityGraph

@EntityGraph(attributePaths = {"customer", "items"}) on a repository method tells Spring Data which associations to fetch, without writing JPQL. It works with derived query methods and is easy to vary per use case.

Fix 3: Batch Fetching

Setting hibernate.default_batch_fetch_size (for example 50) makes Hibernate load lazy associations for many parents at once using WHERE id IN (...). N+1 becomes 1 + N/50 queries with no code changes. It is a great global safety net, and the best fix for paginated lists that need collections.

Fix 4: DTO Projections

For read-only screens, select exactly the columns you need into a record with a JPQL constructor expression or a native query. No entities, no lazy loading, no persistence context overhead — usually the fastest option of all.

Examples

The problem: 1 + N queries

Java
# application.yml (development only)
logging:
  level:
    org.hibernate.SQL: debug

@GetMapping("/api/orders")
public List<OrderSummary> list() {
    return orderRepository.findAll().stream()
        .map(o -> new OrderSummary(o.getId(), o.getCustomer().getEmail(), o.getItems().size()))
        .toList();
}
Output
select o1_0.id, o1_0.customer_id, o1_0.status from orders o1_0
select c1_0.id, c1_0.email from customers c1_0 where c1_0.id=?
select i1_0.order_id, i1_0.id, ... from order_item i1_0 where i1_0.order_id=?
select c1_0.id, c1_0.email from customers c1_0 where c1_0.id=?
select i1_0.order_id, i1_0.id, ... from order_item i1_0 where i1_0.order_id=?
... (201 queries for 100 orders)

Fix with a fetch join and with an entity graph

Java
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("select o from Order o join fetch o.customer join fetch o.items where o.status = :status")
    List<Order> findWithCustomerAndItems(OrderStatus status);

    @EntityGraph(attributePaths = {"customer", "items"})
    List<Order> findByCreatedAtAfter(Instant since);
}
Output
select o1_0.id, c1_0.id, c1_0.email, i1_0.order_id, i1_0.id, i1_0.product, ...
from orders o1_0
join customers c1_0 on c1_0.id=o1_0.customer_id
join order_item i1_0 on o1_0.id=i1_0.order_id
where o1_0.status=?
(1 query for 100 orders)

Batch fetching for paginated lists

Java
# application.yml
spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 50

// Paginated query stays simple; associations are loaded in batches
Page<Order> page = orderRepository.findAll(PageRequest.of(0, 50, Sort.by("createdAt").descending()));
page.forEach(o -> System.out.println(o.getCustomer().getEmail() + " " + o.getItems().size()));
Output
select ... from orders o1_0 order by o1_0.created_at desc offset ? rows fetch first ? rows only
select ... from customers c1_0 where c1_0.id in (?,?,?, ... ?)
select ... from order_item i1_0 where i1_0.order_id in (?,?,?, ... ?)
(3 queries for a page of 50 orders instead of 101)

The fastest read path: a record projection

Java
public record OrderSummary(Long id, String customerEmail, long itemCount) {}

@Query("""
    select new com.webnest.shop.order.OrderSummary(o.id, c.email, count(i))
    from Order o join o.customer c left join o.items i
    group by o.id, c.email
    order by o.id desc
    """)
List<OrderSummary> summaries();
Output
select o1_0.id, c1_0.email, count(i1_0.id) from orders o1_0 join customers c1_0 on ... left join order_item i1_0 on ...
group by o1_0.id, c1_0.email order by o1_0.id desc
(1 query, only 3 columns, no entities in memory)

Common Mistakes

  • Switching associations to FetchType.EAGER to "fix" lazy loading, which causes N+1 everywhere instead.
  • Combining a collection fetch join with Pageable, forcing in-memory pagination of the whole table.
  • Serialising entities directly to JSON, so Jackson triggers lazy loading for every association.
  • Only testing with a handful of rows, where N+1 is invisible.
  • Relying on open-in-view to avoid LazyInitializationException, which hides N+1 queries in the view layer.

Exercise

Using the lesson's The problem: 1 + N queries example, create a small implementation or design sketch that demonstrates N+1 = one query for parents plus one per parent for a lazy association.. Add a test case or scenario that would catch Switching associations to FetchType.EAGER to "fix" lazy loading, which causes N+1 everywhere instead., and describe the expected result.

Show hints
  • Start with Why It Happens and compare your approach with The problem: 1 + N queries. Identify the decision or behavior the lesson recommends.
  • Walk through your scenario step by step. Confirm that it follows N+1 = one query for parents plus one per parent for a lazy association. and that it would catch the failure described by Switching associations to FetchType.EAGER to "fix" lazy loading, which causes N+1 everywhere instead..

Quick quiz

1. Which choice is a mistake to avoid in Solving the N+1 Query Problem?

  • A. Combining a collection fetch join with Pageable, forcing in-memory pagination of the whole table.
  • B. N+1 = one query for parents plus one per parent for a lazy association.
  • C. Switching associations to FetchType.EAGER to "fix" lazy loading, which causes N+1 everywhere instead.
Show answer

C. Avoid this approach: Switching associations to FetchType.EAGER to "fix" lazy loading, which causes N+1 everywhere instead. The lesson recommends: N+1 = one query for parents plus one per parent for a lazy association.

Interview questions

A teammate is about to make this mistake: Switching associations to FetchType.EAGER to "fix" lazy loading, which causes N+1 everywhere instead. How would you diagnose and correct it using the lesson's guidance?

Show answer

N+1 = one query for parents plus one per parent for a lazy association. Apply this in Why It Happens; the example The problem: 1 + N queries shows one way to do it. Avoid Switching associations to FetchType.EAGER to "fix" lazy loading, which causes N+1 everywhere instead. and explain how your implementation or design prevents that failure.

Key Points to Remember

  • N+1 = one query for parents plus one per parent for a lazy association.
  • Detect it with SQL logging or Hibernate statistics, and guard against it in tests.
  • Use join fetch or @EntityGraph to load needed associations in one query.
  • default_batch_fetch_size turns N+1 into a few IN queries and works with pagination.
  • DTO projections are the most efficient solution for read-only views.

Practice in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs plain Java 17. Frameworks such as Spring, and databases, are not available here. Your code for each topic is kept in this browser.

Run the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

These examples need a JDK project with their libraries on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.

WebNest Studio

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering