N+1 Query: The Silent Performance Killer in Spring Boot JPA

One day your API feels smooth — response times look great, CPU and RAM are fine. Then data grows to tens of thousands of records, and suddenly everything slows to a crawl.

You check the usual suspects. Infrastructure looks healthy. The culprit is hiding somewhere else entirely.

You're likely hitting the N+1 Query Problem.

What Is the N+1 Query Problem?

When you fetch a list of N records and then fire one additional query per record to load an associated entity, you end up with N+1 total queries instead of 1.

The root cause in Spring Boot JPA is almost always FetchType.LAZY. Hibernate defers loading associations until the moment they're accessed — meaning every call to a lazy getter inside a loop triggers a brand-new SQL roundtrip.

A simple example

@Entity
public class Post {
    @ManyToOne(fetch = FetchType.LAZY)
    private User author;
}

List<Post> posts = postRepository.findAll();

posts.forEach(post -> {
    System.out.println(post.getAuthor().getName()); // triggers a query each time
});

What Hibernate actually executes:

SELECT * FROM post;                       -- 1 query

SELECT * FROM user WHERE id = 1;          -- then...
SELECT * FROM user WHERE id = 2;          -- one per post
SELECT * FROM user WHERE id = 3;
-- ...N more

With 100 posts, that's 101 queries. With 10,000 posts — 10,001 queries.

Why it's dangerous

The problem is nearly invisible in development:

Dev:         10 records  →  11 queries    — you don't notice
Production:  10,000 records  →  10,001 queries — database collapses

Each extra query costs: opening a connection, sending SQL over the network, reading results, mapping them to Java objects. Multiply that by thousands per HTTP request and you have a slow API, an overloaded database, and rising infrastructure costs.

How to Spot N+1 Before Production

In development, add these lines to application.properties:

spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG

Call an endpoint and count the SQL statements in your console. If the number is far larger than you'd expect, you have an N+1.

In tests, use the QuickPerf library to enforce query counts at CI time:

@Test
@ExpectSelect(1)
void should_load_posts_in_one_query() {
    postRepository.findAllWithAuthor();
}
// This test FAILS the moment a hidden N+1 appears

The Three Fixes — and When to Use Each

Before diving into real scenarios, here's the toolkit:

Real-World Scenario 1 — E-commerce: Order History with Line Items

The setup

A customer service dashboard loads all orders for a user — each order showing its line items and the product name for each item.

@Entity
public class Order {
    @OneToMany(fetch = FetchType.LAZY)
    private List<OrderItem> items;
}

@Entity
public class OrderItem {
    @ManyToOne(fetch = FetchType.LAZY)
    private Product product;
}

The controller looks completely innocent:

List<Order> orders = orderRepository.findByUserId(userId);

orders.forEach(order -> {
    order.getItems().forEach(item -> {
        log.info(item.getProduct().getName());
    });
});

What's actually happening

1   query  → fetch orders
50  queries → fetch items per order         (1 per order)
250 queries → fetch product per item        (1 per item)

Total: 301 queries per page load

This is a two-level N+1 — each level multiplies the previous one. With 200 orders and 5 items each, you're at 1,201 queries inside a single HTTP request.

The fix — nested JOIN FETCH

@Query("""
    SELECT DISTINCT o
    FROM Order o
    JOIN FETCH o.items i
    JOIN FETCH i.product
    WHERE o.user.id = :userId
""")
List<Order> findByUserIdWithDetails(@Param("userId") Long userId);

Result: 1 query. All orders, items, and products in a single SQL join.

⚠️ DISTINCT is required here. Without it, Hibernate returns duplicate Order rows — one row per OrderItem — because of how the JOIN inflates the result set.

Real-World Scenario 2 — SaaS: Workspace Members with Roles

The setup

A SaaS admin panel lists all workspaces — each showing member names and their assigned roles.

@Entity
public class Workspace {
    @OneToMany(mappedBy = "workspace", fetch = FetchType.LAZY)
    private List<WorkspaceMember> members;
}

@Entity
public class WorkspaceMember {
    @ManyToOne(fetch = FetchType.LAZY)
    private User user;

    @ManyToOne(fetch = FetchType.LAZY)
    private Role role;
}

A page with 30 workspaces, 20 members each:

The fix — DTO Projection, grouped in Java

Instead of loading full entities, select only the columns the UI actually needs:

public record WorkspaceMemberDto(
    Long workspaceId,
    String workspaceName,
    String userName,
    String roleName
) {}

@Query("""
    SELECT new com.example.WorkspaceMemberDto(
        w.id, w.name, u.name, r.name
    )
    FROM Workspace w
    JOIN w.members m
    JOIN m.user u
    JOIN m.role r
""")
List<WorkspaceMemberDto> findAllMembersFlat();

Then reconstruct the nested structure in the service layer — far cheaper than lazy loading:

Map<Long, List<WorkspaceMemberDto>> byWorkspace = results.stream()
    .collect(Collectors.groupingBy(WorkspaceMemberDto::workspaceId));
// One SQL query. Grouping done in Java. Zero extra DB roundtrips.

No Persistence Context. No dirty checking. No unnecessary columns fetched.

Real-World Scenario 3 — Finance: Batch Invoice PDF Generation

The setup

A background job generates PDF invoices in bulk — resolving nested relationships for each: customer details, line items, tax rate, and payment records. Nobody profiled it until job runtime crept past 40 minutes.

List<Invoice> invoices = invoiceRepository.findByStatus("PENDING");

for (Invoice inv : invoices) {
    Customer      c   = inv.getCustomer();     // query per invoice
    List<LineItem> li = inv.getLineItems();    // query per invoice
    TaxRate       tax = inv.getTaxRate();      // query per invoice
    List<Payment> pay = inv.getPayments();     // query per invoice

    pdfService.generate(inv, c, li, tax, pay);
}

500 invoices × 4 lazy associations = 2,001 queries

Before fix:  ~38 minutes
After fix:   ~90 seconds

The fix — @EntityGraph with multiple attribute paths

@EntityGraph(attributePaths = {
    "customer",
    "lineItems",
    "taxRate",
    "payments"
})
@Query("SELECT i FROM Invoice i WHERE i.status = :status")
List<Invoice> findByStatusEager(@Param("status") String status);

Hibernate produces one SQL join that loads all four associations upfront. The loop then reads already-loaded data — zero lazy queries.

⚠️ Watch out for MultipleBagFetchException. Fetching two List collections (lineItems + payments) in the same query can throw this. The fix: change collection fields to Set instead of List, or split into two queries and merge the results in memory.

Real-World Scenario 4 — Job Board: Search Results with Aggregated Stats

The setup

A job search page shows each listing with company name, location, number of applicants, and average salary. A common mistake is computing these numbers in Java:

jobs.forEach(job -> {
    // Loads ALL Application rows just to count them
    int count = job.getApplications().size();

    // Loads ALL SalaryRange rows just to average them
    double avg = job.getSalaryRanges().stream()
        .mapToDouble(SalaryRange::getAmount)
        .average()
        .orElse(0);
});

Even with JOIN FETCH, this loads thousands of child rows into memory to compute two numbers that the database could calculate in milliseconds.

The fix — push aggregation into SQL

public record JobSearchDto(
    Long id,
    String title,
    String companyName,
    String locationName,
    Long applicantCount,
    Double avgSalary
) {}

@Query("""
    SELECT new com.example.JobSearchDto(
        j.id,
        j.title,
        c.name,
        l.city,
        COUNT(a.id),
        AVG(s.amount)
    )
    FROM Job j
    JOIN j.company c
    JOIN j.location l
    LEFT JOIN j.applications a
    LEFT JOIN j.salaryRanges s
    WHERE j.status = 'ACTIVE'
    GROUP BY j.id, j.title, c.name, l.city
""")
List<JobSearchDto> searchActive();

COUNT and AVG are computed by the database engine in a single pass — not by loading thousands of records into JVM heap.

Rule of thumb: aggregation belongs in SQL, never in a Java loop.

Decision Guide

Need to save/update after loading?
    → JOIN FETCH + Entity

Simple CRUD, want less code?
    → @EntityGraph

Read-only API, dashboard, or report?
    → DTO Projection

Need COUNT, SUM, or AVG?
    → DTO Projection + SQL aggregation

The pattern most production systems use

Write (Create / Update)
    Entity + JOIN FETCH    → Hibernate manages lifecycle, dirty checking works

Read (GET API / Dashboard / Report)
    DTO Projection         → No Persistence Context, no dirty checking, fastest path

Key Takeaways

• N+1 is nearly invisible in development and explosive in production.
• FetchType.LAZY is safe and correct — the problem is accessing lazy associations inside loops without a proper fetch strategy.
• Use spring.jpa.show-sql=true to count queries in development. Use QuickPerf to enforce limits in CI.
• JOIN FETCH and @EntityGraph solve loading problems. DTO Projection solves over-fetching — don't use full entities when the API only needs three columns.
• Always push COUNT, AVG, and SUM to SQL. Never compute aggregations in Java by iterating a loaded collection.

Don't just watch the record count. Watch the number of queries Hibernate is actually firing.