The N+1 Problem

What N+1 Means

You run 1 query to load a list of N orders, then your code touches a lazy association of each order, and Hibernate runs N more queries, one per order. 20 orders = 21 queries; 500 orders = 501 queries. Each query is fast alone, but the network round trips add up and the page becomes slow.

@Transactional(readOnly = true)
public List<OrderSummaryDto> myOrders(Long userId) {
    List<Order> orders = orderRepository.findByUserId(userId);     // 1 query
    return orders.stream()
            .map(o -> new OrderSummaryDto(o.getId(), o.getTotal(),
                                          o.getItems().size()))     // +1 query per order!
            .toList();
}

The Repeated SELECTs in the Log

application.yml and log

Turn on SQL and parameter logging, then call the endpoint for a user with 5 orders:

application.yml

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE

Example output

DEBUG org.hibernate.SQL : select o1_0.id,o1_0.status,o1_0.total,o1_0.user_id from orders o1_0 where o1_0.user_id=?
TRACE org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [42]
DEBUG org.hibernate.SQL : select i1_0.order_id,i1_0.id,i1_0.price,i1_0.product_id,i1_0.quantity from order_items i1_0 where i1_0.order_id=?
TRACE org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [501]
DEBUG org.hibernate.SQL : select i1_0.order_id,i1_0.id,i1_0.price,i1_0.product_id,i1_0.quantity from order_items i1_0 where i1_0.order_id=?
TRACE org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [502]
DEBUG org.hibernate.SQL : select i1_0.order_id,i1_0.id,i1_0.price,i1_0.product_id,i1_0.quantity from order_items i1_0 where i1_0.order_id=?
TRACE org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [503]
DEBUG org.hibernate.SQL : select i1_0.order_id,i1_0.id,i1_0.price,i1_0.product_id,i1_0.quantity from order_items i1_0 where i1_0.order_id=?
TRACE org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [504]
DEBUG org.hibernate.SQL : select i1_0.order_id,i1_0.id,i1_0.price,i1_0.product_id,i1_0.quantity from order_items i1_0 where i1_0.order_id=?
TRACE org.hibernate.orm.jdbc.bind : binding parameter (1:BIGINT) <- [505]

The same SELECT on order_items repeats with a different order_id. That pattern in a log is the signature of N+1.

Counting the Queries

Example

A fake database counts the round trips. The first loop behaves like lazy loading; the second behaves like one JOIN FETCH query.

import java.util.*;

public class Main {
    static int queries = 0;
    static final Map<Long, List<String>> ITEMS = new TreeMap<>();
    static {
        ITEMS.put(501L, List.of("Kurta", "Dupatta"));
        ITEMS.put(502L, List.of("Sneakers"));
        ITEMS.put(503L, List.of("Rice 5kg", "Dal 1kg", "Ghee"));
        ITEMS.put(504L, List.of("Phone"));
        ITEMS.put(505L, List.of("Novel", "Bookmark"));
    }

    static List<Long> selectOrders() {
        queries++;
        System.out.println("  select * from orders where user_id=42");
        return new ArrayList<>(ITEMS.keySet());
    }

    static List<String> selectItems(long orderId) {
        queries++;
        System.out.println("  select * from order_items where order_id=" + orderId);
        return ITEMS.get(orderId);
    }

    static Map<Long, List<String>> selectOrdersJoinFetchItems() {
        queries++;
        System.out.println("  select o.*, i.* from orders o join order_items i on o.id=i.order_id where o.user_id=42");
        return ITEMS;
    }

    public static void main(String[] args) {
        System.out.println("Lazy loading inside a loop:");
        int lines = 0;
        for (long id : selectOrders()) lines += selectItems(id).size();
        System.out.println("  -> " + lines + " items, " + queries + " queries (1 + N)");

        queries = 0;
        lines = 0;
        System.out.println("JOIN FETCH:");
        for (List<String> items : selectOrdersJoinFetchItems().values()) lines += items.size();
        System.out.println("  -> " + lines + " items, " + queries + " query");

        System.out.println("Round trips at 2 ms each:");
        for (int n : new int[] {20, 200, 2000}) {
            System.out.printf("  %4d orders: N+1 = %4d queries (%4d ms), fetch join = 1 query%n", n, n + 1, (n + 1) * 2);
        }
    }
}

Output

Lazy loading inside a loop:
  select * from orders where user_id=42
  select * from order_items where order_id=501
  select * from order_items where order_id=502
  select * from order_items where order_id=503
  select * from order_items where order_id=504
  select * from order_items where order_id=505
  -> 9 items, 6 queries (1 + N)
JOIN FETCH:
  select o.*, i.* from orders o join order_items i on o.id=i.order_id where o.user_id=42
  -> 9 items, 1 query
Round trips at 2 ms each:
    20 orders: N+1 =   21 queries (  42 ms), fetch join = 1 query
   200 orders: N+1 =  201 queries ( 402 ms), fetch join = 1 query
  2000 orders: N+1 = 2001 queries (4002 ms), fetch join = 1 query

Where N+1 Hides

Loops over entities that touch a lazy collection or a lazy @ManyToOne (product.getCategory().getName()).
Jackson serializing entities in a controller with open-in-view on: every getter is a possible query.
EAGER associations in JPQL queries and in findAll(): loaded with extra SELECTs after the main query.
Mappers (MapStruct, OrderDto.from(order)) that read nested fields.

Tip: Catch N+1 early: enable SQL logging in development, set spring.jpa.properties.hibernate.generate_statistics=true to see "N JDBC statements executed" per session, or assert query counts in tests with a library such as datasource-proxy.