Finding and Killing N+1 Queries in Spring Data JPA

An endpoint that returns 20 products with their variants runs 21 queries. Nobody notices in development, where the database is on localhost and the dataset is small. In production, with a network hop per query, it's the difference between 40ms and 900ms.

N+1 is the most common performance bug in JPA applications, and it's entirely invisible unless you go looking. Here's how I go looking, and what I do about it.

Step one: see the queries

You can't fix what you can't count. show-sql dumps SQL to the console with no aggregation, which is useless once there are hundreds of statements. Hibernate's statistics are better:

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
logging:
  level:
    org.hibernate.stat: DEBUG

That prints a summary per session, including the query count. Hit an endpoint, look at the number, compare it to what you expected. If loading a page of 20 products costs 21 queries, you've found one.

The version I actually rely on is an assertion in tests, because a fix that isn't enforced regresses within a month:

@Test
void productListingRunsOneQuery() {
    var statistics = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
    statistics.clear();

    productService.listProducts(PageRequest.of(0, 20));

    assertThat(statistics.getPrepareStatementCount()).isEqualTo(2); // content + count
}

This test failing is how a colleague finds out they added a lazy association walk — instead of a user finding out.

Why it happens

@Entity
public class Product {

    @OneToMany(mappedBy = "product")
    private List<ProductVariant> variants = new ArrayList<>();
}

@OneToMany is lazy by default, which is correct. But then:

products.stream()
    .map(p -> new ProductDto(p.getName(), p.getVariants().size()))
    .toList();

Each getVariants() is a proxy touch, and each proxy touch is a SELECT. One query for the products, N for the variants. The code reads like plain object access. It isn't.

The version people miss is the @ManyToOne side. Those default to eager, so every Product load silently drags in its Category, its Supplier, and whatever those pull in — even for an endpoint that needs none of it. Mark them lazy explicitly:

@ManyToOne(fetch = FetchType.LAZY)
private Category category;

spring.jpa.open-in-view is worth turning off at the same time. It's on by default and keeps the persistence context open through view rendering, which means lazy loads silently succeed during serialization instead of throwing. Convenient — and exactly why N+1s go unnoticed. With it off, a missing fetch fails loudly in development.

Fix 1: fetch the association up front

@EntityGraph(attributePaths = {"variants"})
List<Product> findByCategoryId(UUID categoryId);

One query, joined. This is the right fix for a bounded result set — and the trap is what happens when you add a Pageable.

The pagination trap

@EntityGraph(attributePaths = {"variants"})
Page<Product> findAll(Pageable pageable); // don't

LIMIT operates on SQL rows, not entities. A product with 5 variants produces 5 rows, so LIMIT 20 would cut you off mid-product and hand back a partially loaded entity. Hibernate refuses to do that, and instead loads every matching row into memory and paginates there. Older versions log HHH000104 and carry on; newer ones are stricter. Either way the pagination is a lie, and on a large table it's an OOM waiting to happen. It works fine with 50 products in dev, which is the cruel part.

Two fixes.

Two queries. Page the IDs first — no join, so LIMIT is honest — then fetch the full entities for those IDs:

@Query("select p.id from Product p")
Page<UUID> findProductIds(Pageable pageable);

@Query("select distinct p from Product p left join fetch p.variants where p.id in :ids")
List<Product> findWithVariants(@Param("ids") List<UUID> ids);

Two round trips, correct pagination, no in-memory paging. It's the approach I use for every paginated list in Raaqib.

Or @BatchSize. Leave the association lazy and tell Hibernate to load the collections in batches:

@BatchSize(size = 50)
@OneToMany(mappedBy = "product")
private List<ProductVariant> variants;

21 queries becomes 2: one for the page of products, one WHERE product_id IN (...) for the variants. Pagination stays correct because the root query never joins. Set it globally with hibernate.default_batch_fetch_size and a whole category of N+1s quietly turns into +1s. It's the highest-leverage line of configuration in a JPA app.

MultipleBagFetchException

Join-fetch two collections at once and Hibernate throws MultipleBagFetchException: cannot simultaneously fetch multiple bags. It isn't being difficult — fetching two List collections in one query produces a cartesian product, and Hibernate can't tell duplicate rows apart in an unordered List.

Switching both to Set makes the exception go away, and that's the advice you'll find first. It does not make the cartesian product go away: 10 variants and 10 stock entries is 100 rows, deduplicated in memory afterwards. Fetch one collection and batch the other.

Fix 2: don't load entities at all

Half the N+1s I've fixed disappeared because the endpoint never needed entities. A listing that shows a name, a price and a variant count doesn't need managed objects with dirty checking and lazy proxies — it needs a row of data:

public interface ProductSummary {
    UUID getId();
    String getName();
    BigDecimal getPrice();
    long getVariantCount();
}

@Query("""
    select p.id as id, p.name as name, p.price as price, count(v.id) as variantCount
      from Product p
      left join p.variants v
     group by p.id, p.name, p.price
    """)
Page<ProductSummary> findSummaries(Pageable pageable);

One query, only the columns needed, nothing in the persistence context to dirty-check. For read-heavy list endpoints this is usually both the fastest fix and the simplest code — and the N+1 can't come back, because there's no association left to lazily walk.

The rule I follow: entities for writes and business logic, projections for reads that only render.

What I'd take away

Turn off open-in-view so lazy loading fails where you can see it. Make @ManyToOne explicitly lazy. Set a global batch fetch size. Use entity graphs for bounded sets and never with Pageable — page IDs first, or batch the collection. And when an endpoint is just rendering rows, skip entities entirely.

None of it is difficult. The hard part is noticing, which is why the query-count assertion is the piece I'd keep if I could only keep one.