Entity Relationships & Associations

The N+1 Select Problem

18 min Lesson 7 of 13

The N+1 Select Problem

The N+1 select problem is one of the most common and damaging performance issues in any ORM-based application. It is subtle enough to slip past code review, yet severe enough to bring a production service to its knees under load. This lesson explains exactly how it arises in Hibernate, how to detect it, and why it matters before you learn the solutions in the next lesson.

What the Problem Looks Like

Imagine a simple domain: a Customer has a collection of Order entities. You need to display a summary page listing every customer with their order count.

@Entity
public class Customer {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY)
    private List<Order> orders = new ArrayList<>();

    // getters omitted for brevity
}

@Entity
@Table(name = "orders")
public class Order {

    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private BigDecimal total;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private Customer customer;
}

Now consider this service method that loads all customers and iterates their orders:

@Service
@Transactional(readOnly = true)
public class CustomerReportService {

    @Autowired
    private CustomerRepository customerRepository;

    public List<String> buildSummary() {
        List<Customer> customers = customerRepository.findAll();   // Query 1

        List<String> lines = new ArrayList<>();
        for (Customer c : customers) {
            // Accessing c.getOrders() triggers a NEW SELECT per customer
            int count = c.getOrders().size();                      // Query 2..N+1
            lines.add(c.getName() + " — " + count + " orders");
        }
        return lines;
    }
}

If the database holds 200 customers, this innocent-looking loop fires 201 SQL statements: 1 to load all customers, then 1 per customer to load their orders collection. That is the N+1 pattern: 1 root query + N collection queries.

Why LAZY Fetch Causes It

The default fetch type for @OneToMany and @ManyToMany associations is LAZY, which means Hibernate does not load the related collection until you actually access it. This is a sensible default — you do not want to pull in every order for every customer just because you loaded the customer list. But when you do access every collection in a loop, you get one database round-trip per row.

Lazy loading is not the root cause — unplanned access patterns are. LAZY fetch is the right default. The problem arises when you access a lazy collection inside a loop without telling Hibernate to pre-load the data in a single join query.

Counting the Queries: A Concrete Example

Enable Hibernate's SQL logging to see it in action. Add this to application.properties:

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

With 5 customers in the database you will see output like this:

-- Query 1: load all customers
select c1_0.id, c1_0.name from customer c1_0

-- Query 2: orders for customer id=1
select o1_0.customer_id, o1_0.id, o1_0.total
from orders o1_0 where o1_0.customer_id=1

-- Query 3: orders for customer id=2
select o1_0.customer_id, o1_0.id, o1_0.total
from orders o1_0 where o1_0.customer_id=2

-- ... 3 more queries ...

-- Query 6: orders for customer id=5
select o1_0.customer_id, o1_0.id, o1_0.total
from orders o1_0 where o1_0.customer_id=5

Six queries for five customers. Scale that to 1,000 customers and you have 1,001 database round-trips for a single page render.

Performance Impact at Scale

Each database round-trip has a fixed cost: network latency (even on localhost this is typically 0.1–1 ms), connection acquisition from the pool, query parsing, and I/O. With a conservative 1 ms per query:

100 customers: ~101 ms just in database time
1,000 customers: ~1,001 ms — over 1 second
10,000 customers: ~10 seconds — a timeout waiting to happen

Worse, every concurrent HTTP request spawns the same flood of queries, so connection pool exhaustion and database CPU saturation hit simultaneously under load.

N+1 is invisible at development time. Your local database has 10 rows; the query count looks trivial. In production with 50,000 rows, the same code becomes a cascading failure. Always measure with production-scale data before you ship.

The Problem Is Not Limited to Collections

N+1 also strikes with @ManyToOne associations when the owning side is loaded lazily. Suppose you query all orders and then access each order's customer:

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

for (Order o : orders) {
    // Each call may trigger a SELECT on the customer table
    System.out.println(o.getCustomer().getName());  // N queries
}

Although @ManyToOne defaults to EAGER fetch in JPA, Hibernate can still produce N+1 in certain Spring Data JPQL projections or when the association has been explicitly set to LAZY. The pattern is the same.

Detecting N+1 in a Spring Boot Project

There are several reliable ways to catch this in development:

SQL logging — Enable spring.jpa.show-sql=true and count the repeated statements. Tedious but always available.
Hibernate Statistics — Enable spring.jpa.properties.hibernate.generate_statistics=true; Hibernate logs a summary at session close including collection fetch count. A count equal to the number of root entities is a red flag.
datasource-proxy / p6spy — Proxy the DataSource to log every query with a stack trace; ideal for integration tests. Libraries like datasource-micrometer integrate this into Micrometer metrics.
Hypersistence Optimizer — A commercial static-analysis tool that flags N+1 patterns at test time without running a query.

Add a query count assertion to your integration tests. Use a DataSource proxy library to fail the test if more than a threshold number of SQL statements are executed for a given service call. This prevents N+1 regressions from sneaking into production unnoticed.

Why Switching to EAGER Fetch Is the Wrong Fix

A common — and incorrect — instinct is to change the fetch type to EAGER:

@OneToMany(mappedBy = "customer", fetch = FetchType.EAGER)  // Do NOT do this
private List<Order> orders;

This does not solve N+1; it just moves the problem. Hibernate still issues a separate SELECT per customer to load orders unless you also write a JOIN FETCH query. Additionally, EAGER forces the collection to load in every query context, even when you do not need orders — wasting memory and bandwidth on every single customer lookup across your entire application. You have replaced a selective performance problem with a universal one.

Summary

The N+1 problem arises when Hibernate issues one SQL statement to load N root entities and then fires an additional statement for each entity when you access a lazy association — giving you N+1 total queries instead of 1. It is caused by accessing lazy collections inside loops without pre-loading the data. It is invisible on small datasets but catastrophic at scale. Switching to EAGER fetch is not the solution — it trades a selective problem for a permanent overhead. The correct solutions — JOIN FETCH, entity graphs, and batch fetching — are the subject of the next lesson.