Performance Tuning — Solving the N+1 Query Problem for Good

The N+1 query problem is responsible for more JPA performance incidents than any other single cause. It’s easy to introduce accidentally, invisible until you look at what’s actually being sent to the database, and gets worse as data grows. I’ve seen it in every project that uses JPA without a deliberate approach to fetch strategy.

The fix is straightforward once you understand the pattern. The harder part is building habits that prevent it from appearing in the first place.

What N+1 Actually Is

Load a list of 100 Order entities. Each Order has a @ManyToOne relationship to a Customer. If the customer association is fetched lazily (the default), accessing order.getCustomer() triggers a separate SQL query per order. The result: 1 query to load the orders, 100 queries to load the customers — 101 queries total for what should be 1 or 2.

// This looks innocent
List<Order> orders = orderRepository.findAll();
for (Order order : orders) {
    System.out.println(order.getCustomer().getName()); // triggers N queries
}

In development with 10 rows, this is imperceptible. In production with 10,000 orders, it’s a catastrophe.

Detecting It

Hibernate statistics — add to application.yml:

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

This logs query counts per session. If you see “100 queries executed” for a page load, something is wrong.

p6spy — a JDBC proxy that logs every SQL statement with the actual bound parameter values. Add com.github.gavlyukovskiy:p6spy-spring-boot-starter and you’ll see every individual query in the log, making N+1 patterns immediately obvious.

The Wrong Fix: EAGER Fetching

The instinctive response is to change the relationship to FetchType.EAGER:

@ManyToOne(fetch = FetchType.EAGER)  // don't do this
private Customer customer;

Eager fetching joins the customer into every query that touches Order, including queries where you don’t need the customer. It solves one N+1 while making everything else worse. Always leave associations as LAZY and explicitly fetch what you need per query.

JOIN FETCH

The standard solution: use JPQL JOIN FETCH to load the association in a single query:

@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findByStatusWithCustomer(@Param("status") OrderStatus status);

This generates a SQL JOIN that retrieves orders and customers in one round-trip. The fetched customer is already in the persistence context, so accessing order.getCustomer() hits the first-level cache rather than the database.

For collections (@OneToMany), be careful with JOIN FETCH — it can produce a Cartesian product with multiple collection fetches. Use DISTINCT and fetch one collection at a time, or use separate queries.

Entity Graphs

Entity graphs give you fetch strategy control without putting it in a query string:

@NamedEntityGraph(
    name = "Order.withCustomerAndItems",
    attributeNodes = {
        @NamedAttributeNode("customer"),
        @NamedAttributeNode(value = "items", subgraph = "items")
    },
    subgraphs = @NamedSubgraph(name = "items", attributeNodes = @NamedAttributeNode("product"))
)
@Entity
public class Order { ... }

Reference it in the repository:

@EntityGraph("Order.withCustomerAndItems")
List<Order> findByStatus(OrderStatus status);

Or define it ad-hoc without the @NamedEntityGraph annotation:

@EntityGraph(attributePaths = {"customer", "items.product"})
List<Order> findByStatus(OrderStatus status);

Entity graphs are useful when the same entity is fetched with different associated data in different contexts — you can define multiple named graphs and apply the right one per repository method.

Batch Fetching

For cases where JOIN FETCH isn’t practical, @BatchSize tells Hibernate to load lazy associations in batches rather than one at a time:

@OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
@BatchSize(size = 50)
private List<OrderItem> items;

Instead of N individual queries, Hibernate issues WHERE id IN (?, ?, ?, ...) queries in batches of 50. For 1,000 orders, that’s 20 queries instead of 1,000. Not as good as JOIN FETCH, but viable when the association is only sometimes needed.

Projections: Don’t Load Entities You Don’t Need

If you’re rendering a summary list — order ID, customer name, total value — loading full Order entities with all their associations is wasteful. Use a DTO projection:

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

@Query("SELECT new com.example.OrderSummary(o.id, c.name, o.total) " +
       "FROM Order o JOIN o.customer c WHERE o.status = :status")
List<OrderSummary> findSummaryByStatus(@Param("status") OrderStatus status);

The SQL fetches only the columns you need. No entity is constructed, no first-level cache entries are created, no lazy proxies exist to accidentally trigger later. For read-heavy endpoints, this is the most impactful optimisation available.

The rule I apply on every project: entities for write operations, projections for read operations. A list page has no business loading full entity graphs.

The Pattern That Works

Define all associations as LAZY. For each repository method, decide what you need and fetch it explicitly — via JOIN FETCH, entity graph, or projection. Measure query counts before and after any significant data access change. The N+1 problem doesn’t reappear in codebases where these habits are established.

If you’re dealing with JPA performance issues or want to establish better data access patterns across a Spring Boot application, get in touch.