The N+1 Query Problem in Spring Data JPA

Your endpoint returns 50 orders. It works fine locally with three rows of test data. In production it takes four seconds and the database is on fire.

Turn on SQL logging and you find 51 queries where you expected one. This is the N+1 problem, and it is the single most common performance bug in Spring Data JPA.

How you get there

@Entity
public class Order {
    @Id private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;
}

List<Order> orders = orderRepository.findAll(); // 1 query

for (Order order : orders) {
    System.out.println(order.getCustomer().getName()); // 1 query each
}

The first call fetches 50 orders. Each getCustomer() then finds an uninitialised proxy and fires its own SELECT. One query for the list, N for the children, 51 in total.

See it before your users do

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

Better still, fail the build. Add datasource-proxy or Hypersistence Utils and assert a query count in your tests. That way the regression is caught in CI rather than by a customer.

Fix 1: A fetch join

Tell JPQL to bring the children back in the same query:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o JOIN FETCH o.customer")
    List<Order> findAllWithCustomer();
}

One query, one join. Note it must be JOIN FETCH, not JOIN. A plain join filters rows without initialising the association, and you will still get N+1.

Fix 2: An entity graph

The same result without hand-writing JPQL, which means you keep derived query methods and paging:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @EntityGraph(attributePaths = { "customer" })
    List<Order> findByStatus(OrderStatus status);
}

This is usually the tidiest option when the query itself is fine and only the fetching is wrong.

Fix 3: Batch fetching

When you cannot avoid lazy loading, whether that is several associations or a deep tree, tell Hibernate to load the proxies in batches instead of one at a time:

spring.jpa.properties.hibernate.default_batch_fetch_size=50

51 queries become 2: one for the orders, one WHERE customer_id IN (...) for the customers. This single line is the highest-value default in this article, and it costs you nothing.

Fix 4: Do not fetch entities at all

If the endpoint only needs three fields, loading full entities is wasted work. Project straight into a DTO:

public record OrderSummary(Long id, String customerName, BigDecimal total) { }

@Query("""
    SELECT new com.example.OrderSummary(o.id, c.name, o.total)
    FROM Order o JOIN o.customer c
    """)
List<OrderSummary> findSummaries();

One query, only the columns you need, and no managed entities to dirty-check. For read-only endpoints this is often both the fastest and the simplest option.

The trap: pagination with JOIN FETCH

Combine a fetch join on a collection with Pageable and Hibernate cannot paginate in SQL. The join multiplies rows, so it fetches everything and paginates in memory. On a large table that is how you get an OutOfMemoryError:

HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory

Treat that warning as an error. The fix is two queries: page the IDs, then fetch the collections for that page:

@Query("SELECT o.id FROM Order o WHERE o.status = :status")
Page<Long> findIds(OrderStatus status, Pageable pageable);

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

To-one associations (@ManyToOne, @OneToOne) do not multiply rows, so they paginate fine.

Which one to reach for

Situation	Fix
You always need the association	@EntityGraph
One specific query needs it	JOIN FETCH
Several associations, or a deep tree	default_batch_fetch_size
Read-only endpoint, few fields	DTO projection
Paging a collection	Two queries: IDs, then fetch

And set default_batch_fetch_size regardless. It turns the N+1s you have not found yet into something far less damaging.

One thing not to do

@ManyToOne(fetch = FetchType.EAGER) // don't

Switching to eager fetching makes this endpoint's problem disappear and gives it to every other query that touches Order, including the ones that never look at the customer. Keep associations lazy and fetch deliberately, per query.