MiddlePerformanceVery commonNot answered yet

What causes the N+1 select problem in Hibernate, and how do you remove it?

One query loads N parents; touching each parent's lazy association then fires one extra SELECT per parent, so a page of 100 orders issues 101 queries. Remove it by loading the association in the same query — JOIN FETCH in JPQL or an @EntityGraph on the repository method — or by batching (@BatchSize), which collapses the N selects into a few IN (...) queries. EAGER is not a fix: it issues the same N selects earlier.

✗Reaching for EAGER fetching as the fix, which only moves the same N selects earlier
✗Blaming a missing index when the query count, not the query cost, is the problem
✗Not noticing the problem at all because it only shows up under production-sized N

→Why does @BatchSize reduce the query count without joining the tables?
→What does JOIN FETCH do to pagination with setMaxResults?

Contents

Where the 1 + N queries come from

@Entity
class Order {
    @Id Long id;
    @OneToMany(mappedBy = "order")          // LAZY by default
    List<OrderItem> items = new ArrayList<>();
}

// 1 query: SELECT * FROM orders LIMIT 100
List<Order> orders = em.createQuery("select o from Order o", Order.class)
                       .setMaxResults(100)
                       .getResultList();

for (Order o : orders) {
    // ⚠️ every iteration issues one more SELECT for this order's items
    total += o.getItems().size();           // 100 extra queries
}

The Hibernate log gives it away immediately:

select o.id from orders o limit 100;
select i.* from order_items i where i.order_id = 1;
select i.* from order_items i where i.order_id = 2;
-- ... 98 more of the same

Three working fixes

// 1 — JOIN FETCH: the association is loaded by the same query
List<Order> orders = em.createQuery(
        "select distinct o from Order o join fetch o.items", Order.class)
    .getResultList();                       // exactly 1 query

// 2 — @EntityGraph on a Spring Data repository method
public interface OrderRepo extends JpaRepository<Order, Long> {
    @EntityGraph(attributePaths = "items")
    List<Order> findAll();
}

// 3 — @BatchSize: N selects collapse into N/size queries using IN (...)
@OneToMany(mappedBy = "order")
@BatchSize(size = 50)
List<OrderItem> items;                      // 100 orders → 2 queries instead of 100

❌ fetch = FetchType.EAGER is not a fix: Hibernate still issues the same N selects, only earlier — and now on every query that so much as mentions the entity.

⚠️ A collection join fetch does not combine with setMaxResults: Hibernate has to pull every row and paginate in memory (the HHH000104 warning). For a paged collection use @BatchSize, or two queries — first the page's ids, then where id in (...) with a join fetch.