Killing the N+1 Query in Spring Data JPA

The catalog endpoint took 90 milliseconds in staging and timed out in production. Same code, same query, same indexes. The only difference was that staging had 20 products and production had 2,400.

That shape — fine on small data, catastrophic on real data, no single slow query in the logs — is almost always N+1: one query to fetch the list, then one more per row to fetch something related. Twenty rows is 21 queries and nobody notices. Two thousand four hundred rows is 2,401 round trips.

Seeing it

You cannot fix what you cannot see, and Hibernate is silent about this by default. Turn on statistics in your development profile — only there, they are not free:

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        format_sql: true
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE   # the actual bound parameters

Now every request logs a statistics line. If 2401 statements appears next to an endpoint returning one page of results, you have found it.

Better still, fail the build. Assert query counts in your integration tests, so an N+1 is caught by CI instead of by production:

@Test
void listingProductsIssuesOneQuery() {
  Statistics stats = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
  stats.clear();

  catalogService.page(PageRequest.of(0, 50));

  // one for the rows, one for the count
  assertThat(stats.getPrepareStatementCount()).isEqualTo(2);
}

Where it comes from

Here is the code that caused it. Nothing about it looks wrong, which is the problem — @ManyToOne is EAGER by default, and every eager association is an extra query per row unless Hibernate can join it.

@Entity
public class Product {

  @Id
  private Long id;
  private String name;

  @ManyToOne              // defaults to FetchType.EAGER
  private Category category;

  @OneToMany(mappedBy = "product")
  private List<PriceTier> tiers;   // LAZY, but touched in the mapper
}

public Page<ProductView> page(Pageable pageable) {
  return repo.findAll(pageable)
             .map(ProductView::from);   // <- reads p.getCategory() and p.getTiers()
}

findAll issues one query. Then ProductView::from touches category and tiers on each row, and each touch is another SELECT. The mapper looks pure; it is issuing database traffic.

Fix 1 — a fetch join

The direct answer: tell the query what to bring back with it.

@Query("""
    SELECT DISTINCT p FROM Product p
    LEFT JOIN FETCH p.category
    WHERE p.active = true
    """)
List<Product> findActiveWithCategory();

One query, categories included. Good for a single to-one association and a bounded result set — and it has a sharp edge worth knowing about before you reach for it on a paged endpoint.

Fix 2 — an entity graph

Entity graphs say what to fetch without writing the join, so one repository method can serve different fetch plans:

@EntityGraph(attributePaths = {"category", "brand"})
Page<Product> findByActiveTrue(Pageable pageable);

Same limitation applies: safe for to-one associations, dangerous the moment a collection is in the path alongside pagination.

Fix 3 — batch fetching, the one I reach for

This is the highest value-per-line change in the whole post, and most projects never enable it. Instead of eliminating the extra queries, Hibernate batches them: rather than 50 single-row lookups, one IN query for 50 IDs.

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 50   # global, applies to every lazy association

N+1 becomes N/50 + 1. A 2,401-query request drops to about 49. No code change, no query rewrite, and critically it composes with pagination — the page query stays a real SQL LIMIT, and the associations are filled in afterwards in batches.

For collections, pair it with @BatchSize where you want a different size than the default:

@Entity
public class Product {

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "category_id")
  private Category category;

  @OneToMany(mappedBy = "product", fetch = FetchType.LAZY)
  @BatchSize(size = 30)
  private List<PriceTier> tiers;
}

Fix 4 — stop loading entities you are not mutating

The best fix for a read-only endpoint is not to hydrate entities at all. A projection selects exactly the columns the response needs, skips the persistence context, and never triggers a lazy load because there is nothing lazy to trigger.

public interface ProductRow {
  Long getId();
  String getName();
  BigDecimal getPrice();
  String getCategoryName();   // resolved via the join below
}

@Query("""
    SELECT p.id AS id, p.name AS name, p.price AS price, c.name AS categoryName
    FROM Product p JOIN p.category c
    WHERE p.active = true
    """)
Page<ProductRow> findActiveRows(Pageable pageable);

This is usually several times faster than the entity version, and the gap widens with row count — less data over the wire, no dirty-checking, no first-level cache full of objects nobody will modify. For any list or search endpoint, reach for this first.

Choosing between them

The other one: unbounded pagination

While you are in there — OFFSET pagination degrades linearly. Page 1 with LIMIT 20 OFFSET 0 is instant; page 5,000 with OFFSET 100000 makes the database read and discard 100,000 rows first.

For deep or infinite-scroll pagination, use a keyset (seek) instead. It stays constant-time because it is an index range scan:

@Query("""
    SELECT p FROM Product p
    WHERE p.active = true
      AND (p.createdAt, p.id) < (:lastCreatedAt, :lastId)
    ORDER BY p.createdAt DESC, p.id DESC
    """)
List<Product> nextPage(Instant lastCreatedAt, Long lastId, Limit limit);

The id tiebreaker matters: without it, rows sharing a timestamp can be skipped or repeated across pages.