Fetching and the N+1 Problem

Recognise the N+1 query pattern, fix it with fetch joins, entity graphs or batch fetching, and catch it before production does.

Syntax@EntityGraph(attributePaths = {"customer", "lines"}) List<PurchaseOrder> findByStatus(OrderStatus status); @Query("select o from PurchaseOrder o join fetch o.customer where o.status = :s") spring.jpa.properties.hibernate.default_batch_fetch_size=50

Load 50 orders, then read each order's customer name, and Hibernate runs one query for the orders and 50 more for the customers: N+1. Each query is fast, so nothing looks wrong in development with five rows. In production, a page of 100 rows turns into 101 round trips, and the endpoint's latency grows with the data.

The cause is lazy loading, which is the right default. A lazy association is a proxy that queries on first access. The problem is accessing it in a loop.

Seeing it

Turn on SQL logging in development with logging.level.org.hibernate.SQL=debug and watch for the same select ... where id=? repeated. Hibernate's statistics (spring.jpa.properties.hibernate.generate_statistics=true) log the statement count per session. In tests, assert on it so a regression fails the build.

Four fixes

  • Fetch join: select o from PurchaseOrder o join fetch o.customer loads both in one statement. Precise, but it lives in a query string.
  • Entity graph: @EntityGraph(attributePaths = {"customer"}) on a repository method, including derived and inherited ones, turns the listed associations into joins for that call only.
  • Batch fetching: hibernate.default_batch_fetch_size makes Hibernate load lazy associations for many parents at once with where id in (...). N+1 becomes 1+1 or 1+2 with no code changes. A good global safety net.
  • Projection: select just the columns you need, including the customer name, into a record. No entities, no lazy loading.

Collections are harder

Fetch-joining a @OneToMany multiplies rows: an order with ten lines comes back ten times. Hibernate 7 de-duplicates the parents, but two collection fetches in one query produce a cartesian product, and two List collections fail with MultipleBagFetchException.

Paging a query that fetch-joins a collection is worse. The database cannot limit parents when rows are per child, so Hibernate fetches every row and pages in memory, logging warning HHH90003004. On a big table this is an outage. Set spring.jpa.properties.hibernate.query.fail_on_pagination_over_collection_fetch=true so such a query throws instead of running. Page the parent ids first, then load those parents with their collections, or rely on batch fetching.

LazyInitializationException

With spring.jpa.open-in-view=false, touching a lazy association after the service method returns throws LazyInitializationException. That is the exception doing its job: the service did not load what the caller needed. Fix the query or return a DTO built inside the transaction. Do not switch to EAGER mappings; eager loading applies to every query for that entity, forever.

Example

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;
import java.math.BigDecimal;
import java.util.List;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Entity
class Customer {
    @Id
    private Long id;
    private String name;

    protected Customer() { }

    String getName() { return name; }
}

@Entity
class PurchaseOrder {
    @Id
    private Long id;
    private String status;
    private BigDecimal total;
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;

    protected PurchaseOrder() { }

    Long getId() { return id; }
    BigDecimal getTotal() { return total; }
    Customer getCustomer() { return customer; }
}

interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, Long> {

    // Without the graph: 1 query for orders + 1 per distinct customer.
    @EntityGraph(attributePaths = "customer")
    List<PurchaseOrder> findByStatus(String status);
}

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

@Service
public class OrderReport {
    private final PurchaseOrderRepository orders;

    public OrderReport(PurchaseOrderRepository orders) {
        this.orders = orders;
    }

    @Transactional(readOnly = true)
    public List<OrderRow> paidOrders() {
        return orders.findByStatus("PAID").stream()
                .map(o -> new OrderRow(o.getId(), o.getCustomer().getName(), o.getTotal()))
                .toList();
    }
}

When to use it

  • Fix an order list endpoint whose latency grows with the number of rows on the page.
  • Turn open-in-view off and repair the endpoints that start throwing LazyInitializationException.
  • Add a regression test that fails when a report starts issuing a query per row.

More examples

A global batch size

With a batch size of 50, touching the first lazy customer loads up to 50 pending customers in one IN query. Statistics and the stat logger print the number of statements per session, which makes an N+1 obvious in the log. Turn statistics off in production; they cost a little on every operation.

Example · properties
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.default_batch_fetch_size=50
spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.stat=debug

Batch fetching one collection

Hibernate's @BatchSize applies the same idea to one mapping. When a page of 20 articles is rendered and the first article's tags are read, Hibernate loads tags for all 20 in one query. This keeps paging on the articles query correct, which a fetch join on the collection would not.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.annotations.BatchSize;

@Entity
class Tag {
    @Id
    private Long id;
    private String label;
    @ManyToOne(fetch = FetchType.LAZY)
    private Article article;

    protected Tag() { }
}

@Entity
public class Article {
    @Id
    private Long id;
    private String title;

    @OneToMany(mappedBy = "article")
    @BatchSize(size = 100)
    private List<Tag> tags = new ArrayList<>();

    protected Article() { }

    public List<Tag> getTags() { return tags; }
}

Failing a test on extra queries

The statistics count every prepared statement, so removing the entity graph or adding a lazy access in the mapping code makes this test fail with a number instead of a vague slowdown weeks later. Seed enough rows with more than one distinct customer, or an N+1 cannot show up.

Example · java
import static org.assertj.core.api.Assertions.assertThat;

import jakarta.persistence.EntityManagerFactory;
import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;

@SpringBootTest
@TestPropertySource(properties = "spring.jpa.properties.hibernate.generate_statistics=true")
class OrderReportQueryCountTest {

    @Autowired
    EntityManagerFactory emf;

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

        // call the service under test here, for example orderReport.paidOrders()

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

Related

sb relationshipssb query and jpqlsb projectionssb jpa and hibernate in spring bootsb prop spring jpa open in view

Discussion

  • Be the first to comment on this lesson.