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

# 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();
}

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)

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);
}

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)

# 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()));

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)

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();

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)