The N+1 Problem: Detection, Root Cause, and All Solutions

Part 26 of 28

May 04, 2026 Abhay 7 min read

The N+1 Problem: Detection, Root Cause, and All Solutions

What Is the N+1 Problem?

The N+1 problem occurs when loading a list of N entities triggers N additional queries to load their associations — one query per entity.

Example: load 50 orders, then access each order’s customer:

SELECT * FROM orders;                         -- 1 query
SELECT * FROM customers WHERE id = 1;        -- query for order 1's customer
SELECT * FROM customers WHERE id = 2;        -- query for order 2's customer
SELECT * FROM customers WHERE id = 3;        -- query for order 3's customer
...                                           -- 50 more queries
-- Total: 1 + 50 = 51 queries (N+1)

In development with 5 orders it’s invisible. In production with 500 orders it’s 501 queries — a page load that could execute in 10ms takes 500ms or more.

Root Cause

Lazy loading. JPA associations are lazy by default for collections (@OneToMany, @ManyToMany) and by default eager for single references in some configurations. When the application accesses a lazy association outside of the original query, Hibernate fires a new SELECT:

@Entity
public class Order {
    @ManyToOne(fetch = FetchType.LAZY) // lazy
    private Customer customer;

    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY) // lazy
    private List<OrderItem> items;
}

@Transactional(readOnly = true)
public List<OrderDto> getAllOrders() {
    List<Order> orders = orderRepository.findAll(); // 1 query

    return orders.stream()
        .map(order -> new OrderDto(
            order.getId(),
            order.getCustomer().getName(), // N queries — one per order
            order.getItems().size()        // N more queries — one per order
        ))
        .toList();
    // Total queries: 1 + N + N = 2N+1
}

Detecting N+1 Problems

1. Enable SQL Logging

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE

Watch the console. If you see repetitive SELECT patterns with different ID values, you have N+1.

2. Hibernate Statistics

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
logging:
  level:
    org.hibernate.stat: DEBUG

After a request, the log shows:

Session Metrics {
  32897 nanoseconds spent acquiring 1 JDBC connections;
  ...
  25347897 nanoseconds spent executing 51 JDBC statements;
  ...
  Queries executed to database: 51
}

51 queries for a 50-item list — clear N+1.

3. datasource-proxy / p6spy

Libraries like datasource-proxy or p6spy intercept every JDBC call and log it with timing. They can be configured to throw an exception if query count exceeds a threshold — useful in integration tests:

// With datasource-proxy in tests
@Test
void findAllOrders_shouldNotTriggerNPlusOne() {
    // Runs the query
    List<Order> orders = orderRepository.findAll();
    orders.forEach(o -> o.getCustomer().getName()); // access lazy

    // Verify query count
    assertThat(queryCounter.getCount()).isLessThanOrEqualTo(2);
}

4. Hibernate’s Statistics in Tests

@DataJpaTest
class OrderRepositoryTest {

    @Autowired
    private EntityManagerFactory emf;

    @Test
    void noNPlusOne_when_fetchingOrdersWithCustomers() {
        Statistics stats = emf.unwrap(SessionFactory.class).getStatistics();
        stats.setStatisticsEnabled(true);
        stats.clear();

        List<Order> orders = orderRepository.findAllWithCustomers();
        orders.forEach(o -> o.getCustomer().getName());

        assertThat(stats.getPrepareStatementCount()).isEqualTo(1);
    }
}

Solution 1: JOIN FETCH

Add JOIN FETCH to your JPQL query. Hibernate uses an SQL JOIN to load the association in the same query:

@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findByStatusWithCustomer(@Param("status") OrderStatus status);

-- Generated SQL: one query
SELECT o.*, c.*
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = ?

1 query instead of N+1.

JOIN FETCH with collections:

@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.id IN :ids")
List<Order> findByIdsWithItems(@Param("ids") List<Long> ids);

Use DISTINCT — without it, each order row is duplicated for each item in the result set.

Limitation: cannot JOIN FETCH two collections in one query:

// This causes MultipleBagFetchException
@Query("SELECT o FROM Order o JOIN FETCH o.items JOIN FETCH o.tags")
List<Order> findWithItemsAndTags(); // ERROR

Hibernate cannot join two bags (unordered collections) in one query without producing a Cartesian product. Solutions:

Use sets instead of lists for one collection
Use separate queries (one per association)
Use @BatchSize (see below)
Use @EntityGraph (see Article 27)

Solution 2: @BatchSize

@BatchSize tells Hibernate to load lazy associations in batches instead of one-by-one:

@Entity
public class Order {
    @ManyToOne(fetch = FetchType.LAZY)
    @BatchSize(size = 25)
    private Customer customer;

    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    @BatchSize(size = 25)
    private List<OrderItem> items;
}

When loading 50 orders and accessing customers, instead of 50 queries Hibernate fires:

SELECT * FROM orders;                                     -- 1 query
SELECT * FROM customers WHERE id IN (?, ?, ..., ?)        -- ids 1-25
SELECT * FROM customers WHERE id IN (?, ?, ..., ?)        -- ids 26-50
-- Total: 3 queries instead of 51

@BatchSize is easy to add, doesn’t change the repository method signature, and works for any number of associations. It’s the lowest-friction solution for collection associations.

Global batch size (applies to all entities):

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 25

Setting a global batch size is a safe default that reduces N+1 across the entire application with one config line.

Solution 3: Subselect Fetching

Hibernate’s subselect fetching loads all instances of an association for all entities in the original query’s result set:

@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
@Fetch(FetchMode.SUBSELECT)
private List<OrderItem> items;

SELECT * FROM orders WHERE status = ?;                          -- 1 query

-- When any order's items are accessed:
SELECT * FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE status = ?                      -- subquery matches original
);                                                              -- 1 more query, all items

Total: 2 queries, regardless of N. Best when you always need the collection for all results.

Solution 4: DTO Projection (fetch only what you need)

If you don’t need full entities, project to a DTO with all the data in one query:

public record OrderSummary(Long orderId, String customerName, int itemCount, BigDecimal total) {}

@Query("""
    SELECT new com.example.dto.OrderSummary(
        o.id, o.customer.name, SIZE(o.items), o.total
    )
    FROM Order o
    WHERE o.status = :status
    """)
List<OrderSummary> findSummaries(@Param("status") OrderStatus status);

One query, no entity graph, no N+1 possible. DTOs are the cleanest solution when you don’t need managed entities.

Solution 5: Separate Queries + In-Memory Join

For two collections that can’t be JOIN FETCHed together:

@Transactional(readOnly = true)
public List<OrderDto> findOrdersWithDetails(List<Long> orderIds) {
    // Query 1: orders with their items
    List<Order> orders = orderRepository.findByIdsWithItems(orderIds);

    // Query 2: all tags for all orders in one query
    Map<Long, List<Tag>> tagsByOrderId = tagRepository
        .findTagsForOrders(orderIds)
        .stream()
        .collect(groupingBy(TagRow::getOrderId, mapping(TagRow::getTag, toList())));

    // Join in memory
    return orders.stream()
        .map(o -> new OrderDto(o, tagsByOrderId.getOrDefault(o.getId(), List.of())))
        .toList();
}

2 queries total, no Cartesian product, clean code.

When Each Solution Applies

Scenario	Best solution
Single association (ManyToOne)	JOIN FETCH in query
One collection	JOIN FETCH with DISTINCT
Two+ collections	`@BatchSize` or separate queries
Always need all associations	`@Fetch(SUBSELECT)`
Read-only data for API	DTO projection
Mixed, no query change wanted	Global `default_batch_fetch_size`

Making EAGER the Wrong Solution

A common but wrong response to N+1 is switching the association to EAGER:

@ManyToOne(fetch = FetchType.EAGER) // DON'T do this
private Customer customer;

EAGER doesn’t eliminate N+1 — it just moves it to every query, even ones that don’t need the association. A findById() that only needs the order ID now always joins the customer table. The association is loaded even when the caller never accesses it.

EAGER is the wrong fix. Always keep associations lazy and fetch what you need explicitly.

Complete Example: Eliminating N+1 in an Order API

Before (N+1):

@Transactional(readOnly = true)
public List<OrderDto> getOrders() {
    return orderRepository.findAll()   // 1 query
        .stream()
        .map(o -> new OrderDto(
            o.getId(),
            o.getCustomer().getEmail(),   // N queries
            o.getItems().size()           // N queries
        ))
        .toList();
    // N = 100 orders → 201 queries
}

After (fixed with JOIN FETCH + BatchSize):

// Repository
@Query("""
    SELECT DISTINCT o FROM Order o
    JOIN FETCH o.customer
    WHERE o.status = :status
    """)
@QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "false"))
List<Order> findByStatusWithCustomer(@Param("status") OrderStatus status);

// Entity
@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
@BatchSize(size = 25)
private List<OrderItem> items;

// Service
@Transactional(readOnly = true)
public List<OrderDto> getOrders(OrderStatus status) {
    return orderRepository.findByStatusWithCustomer(status)  // 1 query: orders + customers
        .stream()
        .map(o -> new OrderDto(
            o.getId(),
            o.getCustomer().getEmail(),  // from JOIN FETCH — no extra query
            o.getItems().size()          // @BatchSize — loaded in batches
        ))
        .toList();
    // 100 orders → 1 + ceil(100/25) = 5 queries total
}

From 201 queries to 5 — a 40× reduction.

Summary

N+1 happens when lazy associations are accessed in a loop after the initial query.
Detect it by enabling SQL logging, Hibernate statistics, or query-count assertions in tests.
JOIN FETCH is the cleanest solution for single associations and single collections. Use DISTINCT for collections.
@BatchSize (or default_batch_fetch_size) reduces N+1 to batch/N queries with one annotation or config line — the best default for collections.
Subselect fetching loads all collection data in one additional query — best when you always need the full association.
DTO projections eliminate N+1 entirely for read-only use cases.
Never change FetchType.LAZY to EAGER to “fix” N+1 — it makes the problem worse.

Next: Article 27 covers @EntityGraph and batch loading — fine-grained control over what gets fetched per query without changing the entity mapping.