Home/Learn/Hibernate & JPA/Fetch Joins & the N+1 Problem

Fetch Joins & the N+1 Problem

Intermediate

Performance

JOIN FETCH in JPQL or EntityGraph with FETCH eagerly initialises associations in a single query; beware the Cartesian product explosion when fetching multiple collections simultaneously.

Overview

The **N+1 problem** is the most common Hibernate performance issue: loading a list of N entities and then issuing one additional SELECT for each entity's lazy association — resulting in N+1 queries total. Example: `findAll()` returns 100 Orders, then accessing `order.getCustomer()` on each triggers 100 more SELECTs. **JOIN FETCH** in JPQL or `@EntityGraph(attributePaths = {...})` on the repository method solves this by loading the association in the same query as the parent. The trade-off: fetching multiple `@OneToMany` collections simultaneously with JOIN FETCH creates a Cartesian product — use `@BatchSize` or subselect fetching for multiple collections instead.

Detecting the N+1 Problem

Enable `spring.jpa.show-sql=true` or Hibernate's `hibernate.format_sql` and count the queries in the log. For production detection, enable `hibernate.generate_statistics=true` and alert on `Session.getStatistics().getPrepareStatementCount()`. The `datasource-proxy` library (or Hypersistence Utils) can assert maximum query counts in tests.

Hibernate — detect N+1 with show-sql and statistics

// N+1 example — 100 orders → 101 SELECTs
List<Order> orders = orderRepo.findAll();       // SELECT * FROM orders   (1 query)
for (Order o : orders) {
    System.out.println(o.getCustomer().getName()); // SELECT * FROM customers WHERE id=? × 100
}

// application.properties — detect in development
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.generate_statistics=true

// Test assertion with datasource-proxy (Maven: net.ttddyy:datasource-proxy)
@Test
void shouldLoadOrdersInOneQuery() {
    var counter = new QueryCountHolder();
    List<Order> orders = orderRepo.findAllWithCustomer();
    assertThat(QueryCountHolder.getGrandTotal().getSelect()).isEqualTo(1);
}

JOIN FETCH in JPQL

Add `JOIN FETCH o.customer` to the JPQL query — Hibernate generates a single SQL JOIN, initialising the `customer` association in one round trip. For Spring Data repositories, use `@Query` with JOIN FETCH or `@EntityGraph`. Note: JOIN FETCH on a `@OneToMany` collection **multiplies rows** — use `SELECT DISTINCT` or prefer `@BatchSize`.

Hibernate — JOIN FETCH and @EntityGraph

// JPQL JOIN FETCH — loads association in one query
@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findByStatusWithCustomer(@Param("status") String status);
// SQL: SELECT o.*, c.* FROM orders o INNER JOIN customers c ON c.id = o.customer_id

// EntityGraph alternative — no @Query needed
@EntityGraph(attributePaths = {"customer", "items"})
List<Order> findByStatus(String status);

// JOIN FETCH on @OneToMany — Cartesian product!
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
List<Order> findWithItems(@Param("status") String status);
// SQL returns customer × items rows — DISTINCT deduplicates at JPA level
// For large collections this explodes rows — prefer @BatchSize instead

@BatchSize and Subselect Fetching for Multiple Collections

When an entity has multiple `@OneToMany` collections, JOIN FETCH on both creates a Cartesian product explosion (orders × items × tags). **@BatchSize(size=N)** tells Hibernate to load lazy collections in batches: instead of 1 SELECT per order, it fetches N orders' collections in one `IN (?, ?, ...)` query — significantly reducing round trips without a Cartesian product.

Hibernate — @BatchSize to avoid Cartesian product

@Entity
public class Order {
    @Id Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    Customer customer;  // Use JOIN FETCH or @EntityGraph for this

    @OneToMany(mappedBy = "order")
    @BatchSize(size = 30)           // batches lazy loading: load 30 orders' items at once
    List<OrderItem> items;

    @OneToMany(mappedBy = "order")
    @BatchSize(size = 30)
    List<OrderNote> notes;
}

// With @BatchSize: findAll() returns 100 orders
// Accessing items → Hibernate: SELECT * FROM order_items WHERE order_id IN (?, ?, ...) × 4 queries
// Instead of 100 queries — massive improvement, no Cartesian product

// Alternative: @Fetch(FetchMode.SUBSELECT) — one query using a subselect
@OneToMany(mappedBy = "order")
@Fetch(FetchMode.SUBSELECT)
List<OrderItem> items;
// SQL: SELECT * FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE ...)

Key Points to Remember

1N+1 problem: N entities loaded → N additional SELECTs for each lazy association = N+1 total
2JOIN FETCH in JPQL or @EntityGraph loads the association in the same query as the parent
3JOIN FETCH on @OneToMany multiplies result rows — use DISTINCT or @BatchSize instead
4@BatchSize(size=N) loads lazy collections in batches using IN(?, ?, ...) — no Cartesian product
5Enable show-sql + generate_statistics to detect N+1 in development; assert in tests
6Multiple @OneToMany collections: use @BatchSize on each — one JOIN FETCH per collection creates explosions

Interview Questions

What is the N+1 problem in Hibernate and how does JOIN FETCH solve it?

EasyThoughtWorks

Why does JOIN FETCH on a @OneToMany collection create a Cartesian product?

HardOracle

When would you use @BatchSize instead of JOIN FETCH?

MediumAmazon

How can you detect the N+1 problem in a Spring Boot application in development?

EasyInfosys

What is the difference between @EntityGraph and @Query with JOIN FETCH?

MediumAtlassian

Ask Aria about Fetch Joins & the N+1 Problem

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

N+1 Problem

Entity Graphs