# The N+1 Select Problem — Hibernate / JPA

Source: https://www.skillbyai.com/en/hibernate-jpa/f-nplus1

> Detect and fix N+1 queries with join fetch, entity graphs and batch fetching.

## One query became a hundred

The **N+1 select problem** is the most common ORM performance bug. You load N orders with one query, then loop over them and access a lazy association such as `order.getCustomer().getName()`; Hibernate issues **one additional query per order**, so 1 + N queries instead of one or two. With 1,000 orders, that is 1,001 round trips. It hides easily: each query is fast, but together they dominate response time. **Detect** it by logging SQL in development, enabling Hibernate statistics, or using tools that count queries per request (some teams assert query counts in tests). **Fix** it with: **`join fetch`** in JPQL (`select o from Order o join fetch o.customer`), which loads associations in the same query; **entity graphs** (`@EntityGraph` in Spring Data, or `jakarta.persistence.fetchgraph` hints) to choose associations per use case; **batch fetching** (`@BatchSize` or `hibernate.default_batch_fetch_size`), which loads lazy associations for many owners at once with `IN (...)` queries; or **DTO projections** that select exactly the columns needed. Beware of `join fetch` on **collections** combined with pagination, which Hibernate cannot apply in SQL and may do in memory.

## From N+1 to two queries

Join fetch for single-valued associations; batch fetching for collections.

```java
// N+1: 1 query for orders, then 1 per order for its customer
List<PurchaseOrder> orders = em.createQuery("select o from PurchaseOrder o", PurchaseOrder.class)
                               .getResultList();
for (PurchaseOrder o : orders) {
    log(o.getCustomer().getName());          // triggers a SELECT each time
}

// fix 1: join fetch the to-one association
List<PurchaseOrder> fixed = em.createQuery(
        "select o from PurchaseOrder o join fetch o.customer where o.placedAt >= :since",
        PurchaseOrder.class)
    .setParameter("since", since)
    .getResultList();

// fix 2: Spring Data entity graph per repository method
public interface OrderRepository extends JpaRepository<PurchaseOrder, Long> {
    @EntityGraph(attributePaths = {"customer"})
    List<PurchaseOrder> findByPlacedAtAfter(Instant since);
}

// fix 3: batch fetching for lazy collections (application.yml)
// spring.jpa.properties.hibernate.default_batch_fetch_size: 50
// -> lines of 50 orders loaded with one SELECT ... WHERE order_id IN (?, ?, ...)
```

## Fetching groceries one item per trip

N+1 is driving to the shop once for the list, then making a separate trip for every item on it. Join fetching or batch fetching is buying everything on one or two trips.

**Quiz:** You load 200 orders and print each order's customer name, and the SQL log shows 201 queries. What is the most direct fix?

- [ ] Switch every association to EAGER
- [ ] Increase the connection pool size
- [ ] Disable SQL logging
- [x] Use join fetch (or an entity graph) for the customer association in that query

*Answer:* Use join fetch (or an entity graph) for the customer association in that query. Fetching the association in the same query removes the per-row queries.