Skip to content

N+1 Query Detection

When a read test exceeds its query budget, inspect the repeated SQL and exercise the associations the application reads. @EnableQueryInspector keeps findings advisory while you review the captured statements and available Hibernate events. Use a budget or a reviewed N+1 finding policy to keep the corrected access pattern in a test. See the workflow guide.
Issue code n-plus-one
Severity ERROR
Default threshold 3 repetitions

What Is the N+1 Problem?

The N+1 problem occurs when your application executes 1 query to fetch a list of entities, then N additional queries -- one per entity -- to load a related association.

1 query:  SELECT * FROM orders                          -- fetch all orders
N queries: SELECT * FROM members WHERE id = ?            -- one per order (lazy load)
           SELECT * FROM members WHERE id = ?
           SELECT * FROM members WHERE id = ?
           ...

With 100 orders this becomes 101 queries. With 10,000 orders it becomes 10,001. The problem scales linearly and is one of the most common causes of slow application performance.

             Application                          Database
                 |                                    |
                 |--- SELECT * FROM orders ---------> |
                 |<-- 100 rows ---------------------- |
                 |                                    |
                 |--- SELECT * FROM members (id=1) -> |  \
                 |<-- 1 row ------------------------- |   |
                 |--- SELECT * FROM members (id=2) -> |   |  N times
                 |<-- 1 row ------------------------- |   |  (one per order)
                 |--- SELECT * FROM members (id=3) -> |   |
                 |<-- 1 row ------------------------- |  /
                 |           ...                      |

How QueryAudit Detects It

Since 0.7.0 the confirmed n-plus-one finding comes from JDBC evidence, and it is the only built-in rule in the default recommended profile. Neither EXPLAIN nor Hibernate is required.

  1. Normalize SQL -- Replace literal values with ? placeholders

    SELECT * FROM members WHERE id = 42   --> SELECT * FROM members WHERE id = ?
SELECT * FROM members WHERE id = 77   --> SELECT * FROM members WHERE id = ?

  2. Group by call site -- Statements are grouped by normalized SQL and the full application call stack. Proxy, reflection, CGLIB, and framework frames are ignored, so the same repository method called from two different places forms two groups.

  3. Count -- A group with >= threshold SELECT statements (default: 3) that bind different values produces an ERROR n-plus-one finding. Three cases do not:

    • A statement with a multi-placeholder IN (?, ?, ...) list is a batched fetch, so @BatchSize and batch fetching do not fail a test.
    • A statement that pages with OFFSET (LIMIT ? OFFSET ?, OFFSET ? ROWS, LIMIT ?, ?) reads the next page, not the next row's association.
    • A group whose executions all bind the same values repeats one lookup, such as a test that sends the same request in a loop or a page loop's count query. It is reported as an INFO n-plus-one finding, which does not fail the test, so a lookup that production code repeats inside one operation stays visible. QueryAudit compares a hash of the bound values and does not keep the values.

Keyset pagination (WHERE id > ? ORDER BY id LIMIT ?) binds a new value on each page and is reported. Suppress n-plus-one for that test when the loop is intended.

Hibernate lazy-load events are recorded when the integration is active. They produce INFO n-plus-one findings that name the collection or proxy and suggest the fetch to add. They explain a confirmed finding but never confirm one on their own. The older SQL-only n-plus-one-suspect rule is still available through strict or enabled-rules.

@QueryAudit fails the test on a confirmed finding and @EnableQueryInspector only reports it. @QueryAudit(failOn = IssueType.N_PLUS_ONE) fails on N+1 alone when other rules are enabled.

How to Diagnose

When QueryAudit reports an N+1 issue, follow these steps to find and fix the root cause:

Step 1: Identify the Repeated Query

The console report shows the repeated SELECT, the line that issued it, and the table it reads (excerpt):

[ERROR] N+1 Query detected
  Query:  select m1_0.id,m1_0.name from members m1_0 where m1_0.id=?
  Source: com.example.OrderService.lambda$findOrders$0:42
  Target: members
  Detail: The same SELECT ran 100 times from one call site

Step 2: Find the Triggering Code

Look for code that:

  • Iterates over a collection of entities
  • Accesses a lazy-loaded association inside the loop
  • Calls a repository method inside a loop

Stack trace

QueryAudit captures a stack trace at query execution time. If the console's selected source frame points to a proxy, inspect the query's retained stackTrace in the JSON report and find your application's package. Hibernate event findings may lack a source location; correlate them with the captured statements from the same test.

Step 3: Check the Entity Mapping

@Entity
public class Order {
    @ManyToOne(fetch = FetchType.LAZY)  // <-- lazy loading = N+1 risk
    private Member member;
}

Step 4: Check for Common Code Patterns

N+1 issues often hide in non-obvious places. Look for these patterns:

// The classic: accessing a lazy relation in a loop
for (Order order : orderRepository.findAll()) {
    order.getMember().getName();  // <-- triggers lazy load
}

// Same problem but harder to spot in functional style
List<String> names = orders.stream()
    .map(o -> o.getMember().getName())  // <-- lazy load per element
    .collect(Collectors.toList());

// Not an ORM issue -- explicit queries in a loop
for (Long memberId : memberIds) {
    Member m = memberRepository.findById(memberId).orElseThrow();
    results.add(m);
}

<!-- Thymeleaf / JSP: lazy load triggered during rendering -->
<tr th:each="order : ${orders}">
    <td th:text="${order.member.name}"/>  <!-- N+1 here -->
</tr>

Step 5: Choose a Fix Strategy

See the How to Fix section below. The right strategy depends on your use case.

Real-World Examples

Example 1: Basic JPA Lazy Loading

The most common N+1 scenario -- iterating over entities and accessing a lazy association.

// 1 query: SELECT * FROM orders
List<Order> orders = orderRepository.findAll();

for (Order order : orders) {
    // N queries: SELECT * FROM members WHERE id = ? (lazy loading)
    String memberName = order.getMember().getName();
    log.info("Order {} by {}", order.getId(), memberName);
}

-- 1st query
SELECT o.id, o.status, o.member_id, o.total FROM orders o;

-- N lazy-load queries (one per order)
SELECT m.id, m.name, m.email FROM members m WHERE m.id = 1;
SELECT m.id, m.name, m.email FROM members m WHERE m.id = 2;
SELECT m.id, m.name, m.email FROM members m WHERE m.id = 3;
-- ... repeated for every order

Example 2: Nested N+1 (Order -> Member -> Address)

A particularly severe case where N+1 is nested, causing N x M queries.

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

for (Order order : orders) {
    Member member = order.getMember();           // N queries
    Address address = member.getAddress();       // N more queries (nested!)
    log.info("{} lives at {}", member.getName(), address.getCity());
}

With 100 orders:
  1  (orders)
+ 100 (members)
+ 100 (addresses)
= 201 queries total

@EntityGraph(attributePaths = {"member", "member.address"})
@Query("SELECT o FROM Order o")
List<Order> findAllWithMemberAndAddress();

Reduces 201 queries to 1 query.

Example 3: Collection Mapping (@OneToMany)

@Entity
public class Department {
    @OneToMany(mappedBy = "department", fetch = FetchType.LAZY)
    private List<Employee> employees;
}

List<Department> departments = departmentRepository.findAll();

for (Department dept : departments) {
    // Each call triggers: SELECT * FROM employees WHERE department_id = ?
    int headcount = dept.getEmployees().size();
}

@EntityGraph(attributePaths = {"employees"})
@Query("SELECT d FROM Department d")
List<Department> findAllWithEmployees();

@Query("SELECT DISTINCT d FROM Department d JOIN FETCH d.employees")
List<Department> findAllWithEmployees();

Use DISTINCT with JOIN FETCH on collections

Without DISTINCT, a JOIN FETCH on a @OneToMany produces duplicate parent entities (one per child row). Hibernate 6+ handles this automatically, but Hibernate 5 requires explicit DISTINCT.

Example 4: MyBatis Nested Select

<!-- OrderMapper.xml -->
<resultMap id="orderWithMember" type="Order">
    <id property="id" column="id"/>
    <!-- This triggers a separate SELECT for each order -->
    <association property="member" column="member_id"
                 select="com.example.mapper.MemberMapper.selectById"/>
</resultMap>

<select id="selectAll" resultMap="orderWithMember">
    SELECT * FROM orders
</select>

<resultMap id="orderWithMember" type="Order">
    <id property="id" column="id"/>
    <association property="member" javaType="Member">
        <id property="id" column="member_id"/>
        <result property="name" column="member_name"/>
    </association>
</resultMap>

<select id="selectAllWithMember" resultMap="orderWithMember">
    SELECT o.*, m.name as member_name
    FROM orders o
    JOIN members m ON o.member_id = m.id
</select>

Example 5: Spring Data REST / JSON Serialization

A hidden N+1 that occurs during JSON serialization:

@RestController
public class OrderController {
    @GetMapping("/orders")
    public List<Order> getOrders() {
        // The N+1 happens during Jackson serialization, not here!
        return orderRepository.findAll();
    }
}

Jackson calls getMember() on each Order during serialization, triggering lazy loads.

This is hard to spot

The N+1 does not appear in your controller code. It happens inside the JSON serializer. Exercise serialization inside the audited test window, using the instrumented DataSource, so QueryAudit can capture those lazy-load queries too.

Fix: Use a DTO projection or @EntityGraph in the repository method:

@EntityGraph(attributePaths = {"member"})
List<Order> findAll();

Example 6: Spring Data JPA Derived Query

Spring Data derived queries do not support JOIN FETCH. This is a common trap:

// Derived query: generates SELECT * FROM orders WHERE status = ?
// No way to add JOIN FETCH via method naming
List<Order> orders = orderRepository.findByStatus("pending");

for (Order order : orders) {
    order.getMember().getName();  // N+1
}

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

@Query("SELECT o FROM Order o JOIN FETCH o.member WHERE o.status = :status")
List<Order> findByStatusWithMember(@Param("status") String status);

Example 7: Hibernate Second-Level Cache Miss

When using second-level cache, N+1 can resurface after a cache eviction or restart:

@Entity
@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class Member {
    // ...
}

// Works fine when cache is warm (no SQL at all for members)
// After cache eviction or restart: full N+1 occurs
for (Order order : orders) {
    order.getMember().getName();  // Cache miss = SQL query
}

Cache is not a fix for N+1

Cache can mask N+1 problems. The issue returns after cache eviction, restart, or when the data changes. Review the captured access pattern and choose a fetch strategy for the operation.

How to Fix

@Query("SELECT o FROM Order o JOIN FETCH o.member")
List<Order> findAllWithMember();

Produces a single query with a JOIN -- eliminates all N extra queries.

Pagination limitation

JOIN FETCH with @OneToMany collections and Pageable causes Hibernate to fetch all results in memory and paginate in the application (HHH000104 warning). Use @EntityGraph or @BatchSize instead for paginated queries.

@EntityGraph(attributePaths = {"member"})
@Query("SELECT o FROM Order o")
List<Order> findAllWithMember();

Tells JPA to eagerly fetch the member association in the same query.

For nested associations:

@EntityGraph(attributePaths = {"member", "member.address"})
@Query("SELECT o FROM Order o")
List<Order> findAllWithMemberAndAddress();

For named entity graphs defined on the entity:

@NamedEntityGraph(
    name = "Order.withMember",
    attributeNodes = @NamedAttributeNode("member")
)
@Entity
public class Order { ... }

// In repository:
@EntityGraph("Order.withMember")
List<Order> findAll();

@Entity
public class Order {
    @ManyToOne(fetch = FetchType.LAZY)
    @BatchSize(size = 100)
    private Member member;
}

Instead of N queries, Hibernate batches them into ceil(N / batchSize) queries using WHERE id IN (?, ?, ?, ...). Not a single query, but a dramatic reduction.

Before: 100 queries  (SELECT ... WHERE id = ?)
After:  2 queries    (SELECT ... WHERE id IN (?, ?, ..., ?))  -- batch of 50

Global default batch size

Set a global default in application.yml to protect against all lazy-load N+1:

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 100

@Entity
public class Order {
    @ManyToOne(fetch = FetchType.LAZY)
    @Fetch(FetchMode.SUBSELECT)
    private Member member;
}

Hibernate loads all related entities in a single subselect query:

SELECT m.* FROM members m
WHERE m.id IN (SELECT o.member_id FROM orders o)

public record OrderSummary(Long orderId, String memberName, BigDecimal total) {}

@Query("""
    SELECT new com.example.dto.OrderSummary(o.id, m.name, o.total)
    FROM Order o JOIN o.member m
    """)
List<OrderSummary> findOrderSummaries();

The most efficient approach -- fetches only the columns you need in a single query, with no entity management overhead.

Fix Strategy Decision Guide

Do you need to modify the entities?
  |
  +-- YES --> Use JOIN FETCH or @EntityGraph
  |             |
  |             +-- Is it a @OneToMany? --> Use @EntityGraph (avoids pagination issues)
  |             +-- Is it a @ManyToOne? --> Either JOIN FETCH or @EntityGraph
  |
  +-- NO (read-only) --> Use DTO Projection (most efficient)

Need a safety net for all lazy loads?
  +-- Use @BatchSize or hibernate.default_batch_fetch_size

Before/After Comparison

+---------------------------------------------------------------------+
|  BEFORE (N+1)                    |  AFTER (JOIN FETCH)              |
|                                  |                                  |
|  SELECT * FROM orders;           |  SELECT o.*, m.*                 |
|  SELECT * FROM members           |  FROM orders o                   |
|    WHERE id = 1;                 |  JOIN members m                  |
|  SELECT * FROM members           |    ON o.member_id = m.id;        |
|    WHERE id = 2;                 |                                  |
|  SELECT * FROM members           |  -- 1 query total                |
|    WHERE id = 3;                 |  -- All data in one round-trip   |
|  ... (97 more)                   |                                  |
|                                  |                                  |
|  101 queries total               |                                  |
|  101 network round-trips         |                                  |
+---------------------------------------------------------------------+

QueryAudit Report Output

Each audited test prints its own report. Excerpt:

────────────────────────────────────────────────────────────────────────
  QUERYAUDIT REPORT
  Test: findOrders()
────────────────────────────────────────────────────────────────────────

--- CONFIRMED (sorted by priority) ---

  [ERROR] N+1 Query detected
    ID:     qa-finding-v1:7452…
    Query:  select m1_0.id,m1_0.name from members m1_0 where m1_0.id=?
    Source: com.example.OrderService.lambda$findOrders$0:42
    Target: members
    Detail: The same SELECT ran 100 times from one call site
    Fix:    Load the rows once before the loop: JOIN FETCH, @EntityGraph, or one query with an IN list.

When the test fails, the failure message repeats the finding with its application call stack.

Configuration

Threshold

The minimum number of repeated patterns to trigger an N+1 detection.

query-audit:
  n-plus-one:
    threshold: 3   # default

QueryAuditConfig config = QueryAuditConfig.builder()
    .nPlusOneThreshold(5)
    .build();

Choosing a threshold

The default of 3 catches most real N+1 scenarios while avoiding false positives from queries that legitimately run twice (e.g., cache warm-up). Increase to 5 if you have many intentionally repeated queries.

Suppressing

If a detected N+1 is intentional (e.g., a batch processor that deliberately issues per-row queries), suppress it:

@QueryAudit(suppress = {"n-plus-one"})
@Test
void batchProcessorTest() {
    // N+1 issues will not cause test failure
}

query-audit:
  suppress-patterns:
    - "n-plus-one"

query-audit:
  suppress-patterns:
    - "n-plus-one:members"