Fetching Strategies & the N+1 Problem

#Introduction

The N+1 problem is the most common JPA performance bug — and the most common JPA interview question after entity states.

#LAZY vs EAGER

Defaults: @ManyToOne/@OneToOne EAGER, @OneToMany/@ManyToMany LAZY.

#The N+1 Problem

List<Employee> employees = employeeRepository.findAll();          // 1 query
for (Employee e : employees) {
    System.out.println(e.getDepartment().getName());              // +1 query per employee
}

select … from employees e1_0
select … from departments d1_0 where d1_0.id=?
select … from departments d1_0 where d1_0.id=?
select … from departments d1_0 where d1_0.id=?
… (one per distinct department)

1 query for the list + N queries for the associations. With 1,000 rows that's up to 1,001 round trips — fast in development with 5 rows, painful in production.

It often hides in JSON serialization: Jackson walks employee.department on every element (with OSIV on).

Detecting It

• Enable SQL logging in development: logging.level.org.hibernate.SQL=DEBUG (and org.hibernate.orm.jdbc.bind=TRACE for parameters).
• spring.jpa.properties.hibernate.generate_statistics=true logs query counts per session.
• Assert query counts in tests (e.g. with datasource-proxy or Hypersistence Utils).

#Fix 1 — JOIN FETCH

@Query("SELECT e FROM Employee e JOIN FETCH e.department")
List<Employee> findAllWithDepartment();

select e1_0.id, …, d1_0.id, d1_0.name
from employees e1_0
join departments d1_0 on d1_0.id = e1_0.department_id

One query. Use LEFT JOIN FETCH if the association can be null. Fetching a collection duplicates the parent per child row in SQL — Hibernate 6 de-duplicates the parent entities automatically (in Hibernate 5 you needed DISTINCT).

#Fix 2 — @EntityGraph

The same result without writing JPQL — works on derived queries and built-in methods:

@EntityGraph(attributePaths = {"department"})
List<Employee> findByActiveTrue();

@EntityGraph(attributePaths = {"department", "projects"})
Optional<Employee> findWithDetailsById(Long id);

@Override
@EntityGraph(attributePaths = "department")
List<Employee> findAll();

Or define it on the entity with @NamedEntityGraph and reference it by name. An entity graph is a fetch graph: listed attributes are fetched eagerly for that query, others use their mapped fetch type.

#Fix 3 — Batch Fetching

Instead of one query per association, Hibernate loads lazy associations in batches using IN (…):

spring.jpa.properties.hibernate.default_batch_fetch_size=50

or per association/class:

@OneToMany(mappedBy = "department")
@BatchSize(size = 50)
private List<Employee> employees;

select … from departments where id in (?, ?, ?, …)   -- up to 50 ids at a time

N+1 becomes 1 + N/50. It's a great global safety net — it needs no query changes and works with pagination.

#Fix 4 — DTO Projections

For read-only screens and APIs, select exactly what you need:

public record EmployeeRow(Long id, String name, String departmentName) { }

@Query("""
       SELECT new com.example.dto.EmployeeRow(e.id, e.name, d.name)
       FROM Employee e JOIN e.department d
       """)
List<EmployeeRow> rows();

One query, no entities, no lazy loading, no dirty checking — usually the fastest option.

#Comparison

#Collections and Pagination

@Query("SELECT d FROM Department d JOIN FETCH d.employees")
Page<Department> findAllWithEmployees(Pageable pageable);   // ⚠️

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

SQL LIMIT would cut the joined rows mid-collection, so Hibernate loads all rows and paginates in memory — fine with 10 departments, a memory bomb with 100,000. (Setting hibernate.query.fail_on_pagination_over_collection_fetch=true turns the warning into an error.)

Fix — two queries:

@Query("SELECT d.id FROM Department d")
Page<Long> findPageOfIds(Pageable pageable);

@Query("SELECT d FROM Department d JOIN FETCH d.employees WHERE d.id IN :ids")
List<Department> findWithEmployees(@Param("ids") List<Long> ids);

…or paginate the parents normally and let @BatchSize load their collections.

#MultipleBagFetchException

@Query("SELECT p FROM Post p JOIN FETCH p.comments JOIN FETCH p.tags")   // both are List

MultipleBagFetchException: cannot simultaneously fetch multiple bags

Fetching two List collections at once creates a Cartesian product (comments × tags) that Hibernate refuses to de-duplicate for bags. Fixes: fetch one collection per query (and the second in another query or via @BatchSize), or use Sets — which avoids the exception but still multiplies rows.

#Writing Efficiently — JDBC Batching

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

@Transactional
public void importAll(List<Employee> employees) {
    for (int i = 0; i < employees.size(); i++) {
        em.persist(employees.get(i));
        if (i % 50 == 49) { em.flush(); em.clear(); }   // keep the persistence context small
    }
}

Statements are sent in groups of 50 instead of one by one. Remember: IDENTITY ids disable insert batching — use SEQUENCE for bulk inserts.

#Key Takeaways

• LAZY everywhere; fetch per use case.
• N+1 = 1 list query + 1 query per row — watch the SQL log.
• Fix with JOIN FETCH, @EntityGraph, batch fetching or DTOs.
• Never paginate a collection fetch; fetch one bag per query.
• Batch writes with jdbc.batch_size and SEQUENCE ids.