Spring JPA N-Plus-One Query Diagnosis

“What is the N+1 query problem and how would you solve it in Spring Data JPA?” is asked in nearly every backend interview that touches Hibernate. It is popular because it is the most common JPA performance bug: code that looks like a single repository call quietly issues hundreds of SQL statements. Interviewers want a precise definition, a way to detect it, more than one fix with trade-offs, and ideally a way to stop it coming back.

Before you start

You should be comfortable with JPA entity relationships (@OneToMany, @ManyToOne), lazy versus eager fetching, and Spring Data repository methods. Reading JPA lazy loading and persistence context boundaries first helps. Examples target Spring Boot 3.x with Hibernate 6.

The short answer

N+1 happens when you load N parent entities with one query, then touch an association on each parent, causing Hibernate to run one more query per parent. It shows up with lazy associations accessed in a loop or during JSON serialization, and with EAGER @ManyToOne associations loaded by JPQL queries. Detect it by logging SQL or counting statements. Fix it per use case: JOIN FETCH or @EntityGraph to load the association in the same query, a DTO projection that selects only needed columns, or default_batch_fetch_size as a safety net that turns N queries into a few IN queries.

How it works

Take authors and books:

@Entity
public class Author {
    @Id @GeneratedValue private Long id;
    private String name;

    @OneToMany(mappedBy = "author")
    private List<Book> books = new ArrayList<>();
}

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

    @ManyToOne(fetch = FetchType.LAZY)
    private Author author;
}

And a service that builds a report:

@Transactional(readOnly = true)
public List<String> report() {
    return authorRepository.findAll().stream()                       // 1 query
            .map(a -> a.getName() + ": " + a.getBooks().size())      // 1 query per author
            .toList();
}

With 100 authors the SQL log shows:

select a1_0.id, a1_0.name from author a1_0
select b1_0.author_id, b1_0.id, b1_0.title from book b1_0 where b1_0.author_id=?
select b1_0.author_id, b1_0.id, b1_0.title from book b1_0 where b1_0.author_id=?
... 98 more

Each statement may take a millisecond locally. In production, with network round trips of 1 to 2 ms and connection contention, 101 queries become hundreds of milliseconds, and 10,000 parents become seconds.

EAGER does not fix this. findAll() is translated to JPQL select a from Author a, which does not join anything; Hibernate then loads each EAGER association with a secondary select per row. EAGER only changes when the N queries run.

Step-by-step walkthrough

Step 1: Make it visible

Turn on SQL logging in development and statistics in tests:

logging:
  level:
    org.hibernate.SQL: debug
spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true

Statistics log a summary per session, including “N JDBC statements executed”. A repeating where ...author_id=? statement is the signature.

Step 2: Fetch the association in the same query

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("select distinct a from Author a left join fetch a.books")
    List<Author> findAllWithBooks();
}

One statement with a join. In Hibernate 6, duplicate parent rows from the join are de-duplicated in the result automatically, so distinct is no longer needed for that purpose, though it is harmless.

Step 3: Use an entity graph when you want derived methods

@EntityGraph(attributePaths = "books")
List<Author> findByNameStartingWith(String prefix);

Same SQL shape, declared next to the method. Both approaches have one catch: fetch joins and pagination don’t mix. With Pageable, Hibernate cannot apply LIMIT to a joined collection, so it loads everything and pages in memory, logging HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory. For pages, fetch the parent IDs with a paged query first, then fetch-join for those IDs.

Step 4: Project to DTOs for read-only views

A report needs counts, not entities:

public record AuthorBookCount(String name, long books) { }

@Query("""
       select new com.example.library.AuthorBookCount(a.name, count(b))
       from Author a left join a.books b
       group by a.id, a.name
       """)
List<AuthorBookCount> bookCounts();

One query, no entities in the persistence context, no dirty checking, and the database does the counting.

Step 5: Add batch fetching as a safety net

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 50

Now touching books on the first uninitialized author loads books for up to 50 authors with where author_id in (?, ?, ...). 100 authors take 3 queries instead of 101. It is a broad mitigation for code paths you have not optimized, not a reason to skip explicit fetching.

Worked scenario

An order history API returns 50 orders per page as JSON. Each Order has a lazy customer and a lazy lines collection, and each OrderLine has a lazy product. Response times are 40 ms in staging and 1.8 s in production.

Hibernate statistics on one production-like request show 1 query for orders, 50 for customers, 50 for lines and 312 for products: 413 statements. Jackson serializes entities, touching every association, and open-in-view keeps the session open to allow it. Staging had 3 orders per customer and almost no lines, so the problem never appeared there.

The fix has three parts:

@Query("select o.id from Order o where o.customer.id = :customerId order by o.createdAt desc")
Page<Long> pageIds(@Param("customerId") long customerId, Pageable pageable);

@Query("""
       select distinct o from Order o
       join fetch o.customer
       left join fetch o.lines l
       left join fetch l.product
       where o.id in :ids
       """)
List<Order> fetchDetails(@Param("ids") List<Long> ids);

The service pages IDs, fetches details for that page in one query, and maps to response DTOs before leaving the transaction. Open-in-view is disabled. The request drops to 2 queries and 35 ms in production. A test now asserts the statement count.

Common mistake

• “Make the association EAGER.” It changes when the N queries happen, and makes every other use of the entity slower.
• “N+1 only happens with lazy loading.” EAGER @ManyToOne associations loaded through JPQL cause it too.
• Fetch-joining a collection together with Pageable. Hibernate pages in memory and may load the entire table.
• Fetch-joining two List collections, which throws MultipleBagFetchException.
• Fixing it only in the endpoint you noticed. Without a query-count test, the next new field in the JSON response reintroduces it.

Verify the behavior

Assert the number of prepared statements:

@DataJpaTest(properties = "spring.jpa.properties.hibernate.generate_statistics=true")
class AuthorQueryCountTest {

    @Autowired AuthorRepository authors;
    @Autowired EntityManager em;

    @Test
    void reportUsesOneQuery() {
        for (int i = 0; i < 10; i++) persistAuthorWithBooks("author" + i, 3);
        em.flush();
        em.clear();

        Statistics stats = em.getEntityManagerFactory().unwrap(SessionFactory.class).getStatistics();
        stats.clear();

        authors.findAllWithBooks().forEach(a -> a.getBooks().size());

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

Swap findAllWithBooks() for findAll() and the count becomes 11, which proves the test detects the regression. Libraries such as datasource-proxy offer similar assertions at the JDBC level.

Follow-up questions

What is the difference between JOIN and JOIN FETCH in JPQL? join lets you filter or project on the association but does not initialize it on the returned entities; join fetch populates it.

When is @BatchSize better than a fetch join? When you need pagination on parents, or several collections, batch fetching keeps queries small and avoids Cartesian products.

What is a Cartesian product problem? Fetch-joining two collections multiplies rows: 10 books × 10 awards per author yields 100 rows per author. Fetch the second collection separately.

How do you find N+1 in production? Tracing spans per SQL statement, hibernate.session.events metrics, or APM tools showing repeated identical statements per request.

Interview exercise

A GET /departments endpoint returns each department with its employee count and manager name. It currently loads Department entities, calls getEmployees().size() and getManager().getName(), and takes 900 ms for 200 departments. Fix it.

Answer and reasoning

The current code runs 1 query for departments, up to 200 for employee collections and up to 200 for managers. Since the response is read-only and needs only three values, write one DTO query: select new DepartmentSummary(d.id, d.name, m.name, count(e)) from Department d left join d.manager m left join d.employees e group by d.id, d.name, m.name. It loads no entities, lets the database aggregate, and avoids initializing 200 collections only to count them. Add a statement-count test asserting 1 query. The reasoning: identify the per-row queries, then choose the fix by what the use case needs; a projection beats a fetch join when you need aggregates rather than objects.

Continue learning

• Spring Boot interview questions
• Spring Boot MCQs
• JPA lazy loading and persistence context boundaries
• Repository test slices
• Spring Data JPA reference: entity graphs