Hibernate N+1 query problem: how to fix it — a deep dive

The Silent Killer of JPA Performance

You deploy a new Spring Boot service that loads orders from the database. The endpoint feels sluggish, database CPU spikes, and logs fill with hundreds of nearly identical SELECT statements. Welcome to the Hibernate N+1 query problem: how to fix it — a deep dive. This is the most common performance bug we encounter in production JPA applications, and it's deceptively easy to introduce without noticing.

The problem is simple: you execute one query to fetch a list of parent entities, then Hibernate fires off one additional query for each child relationship on each parent—resulting in 1 + N queries instead of 1. With 1,000 orders, that's 1,001 database round trips. Understanding how to spot and fix N+1 is essential for any developer working with Spring Data JPA and Hibernate.

Understanding the N+1 Query Problem

Let's define the problem precisely. Imagine an Order entity with a one-to-many relationship to OrderItem:

@Entity
public class Order {
    @Id
    private Long id;
    
    private LocalDateTime createdAt;
    
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderItem> items;
}

@Entity
public class OrderItem {
    @Id
    private Long id;
    
    @ManyToOne
    private Order order;
    
    private String productName;
    private BigDecimal price;
}

By default, the items collection is lazy-loaded. When you write a simple query like this:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByStatus(Status status);
}

And call it in a service:

List<Order> orders = orderRepository.findByStatus(Status.PENDING);
for (Order order : orders) {
    System.out.println(order.getItems().size());  // Triggers one query per order
}

Hibernate executes one query to fetch all orders, then a separate query for each order's items collection. With 100 pending orders, you've just made 101 database calls instead of 1.

Spotting N+1 in Your Code

The first step to fixing a problem is seeing it happen. Most teams never notice N+1 until production traffic reveals the symptom: high database latency and CPU spikes.

Enable SQL Logging

Add this to your application.yml or application.properties:

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE

Now every SQL statement is logged. The signature of N+1 is unmistakable: one SELECT statement, followed by dozens of identical SELECT statements with different parameter values.

Use Hibernate Statistics

For a more systematic approach, enable Hibernate statistics:

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        use_sql_comments: true

logging:
  level:
    org.hibernate.stat: DEBUG

At the end of each request, you'll see a summary of query counts. A report showing 101 queries when you expected 1 is a red flag. You can also programmatically check statistics in tests:

SessionFactory sessionFactory = entityManagerFactory.unwrap(SessionFactory.class);
Statistics stats = sessionFactory.getStatistics();
System.out.println("Query count: " + stats.getPrepareStatementCount());

Fix #1: Fetch Joins

The most straightforward fix is to explicitly join-fetch the lazy collection in your query. JPQL's JOIN FETCH tells Hibernate to load the relationship in the same query:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("SELECT DISTINCT o FROM Order o " +
           "LEFT JOIN FETCH o.items " +
           "WHERE o.status = :status")
    List<Order> findWithItemsByStatus(@Param("status") Status status);
}

Now Hibernate generates a single SQL query with a LEFT OUTER JOIN. The result set contains one row per item, with order data repeated across rows. Hibernate deduplicates the results in memory and populates the collection correctly.

Note the DISTINCT: The DISTINCT keyword is necessary because the SQL result set has duplicate order rows (one per item). Without it, you'll have duplicate Order objects in the list. The DISTINCT operates in Java, not SQL, so it doesn't optimize the query—it just prevents memory duplication.

Usage remains the same:

List<Order> orders = orderRepository.findWithItemsByStatus(Status.PENDING);
for (Order order : orders) {
    System.out.println(order.getItems().size());  // No additional queries
}

This approach is excellent when you know exactly which relationships you need. The downside: you must create a separate query method for each fetch plan, which leads to repository bloat if you have many combinations.

Fix #2: Entity Graphs

Entity graphs provide a more declarative way to specify fetch plans. Define an entity graph on your entity:

@Entity
@NamedEntityGraph(
    name = "Order.withItems",
    attributeNodes = @NamedAttributeNode("items")
)
public class Order {
    @Id
    private Long id;
    
    private LocalDateTime createdAt;
    
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderItem> items;
}

Then reference it in your repository:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @EntityGraph("Order.withItems")
    List<Order> findByStatus(Status status);
}

Or use the inline @EntityGraph annotation with attribute paths:

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

This generates the same fetch-join behavior but keeps your repository methods cleaner and reusable. You can also specify multiple attributes:

@EntityGraph(attributePaths = {"items", "customer", "shippingAddress"})
List<Order> findByStatus(Status status);

Entity graphs work well when you have a standard fetch plan for common queries. They're less flexible than fetch joins for complex filtering, but they're easier to maintain.

Fix #3: Batch Fetching

Sometimes you can't fetch everything in one query due to query complexity or Cartesian product concerns. Batch fetching provides a middle ground: lazy collections are loaded in batches instead of one by one.

Configure this in application.yml:

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 10

Now when you access a lazy collection, Hibernate loads up to 10 collections in a single query using an IN clause. With 100 orders and a batch size of 10, you make 11 queries instead of 101.

You can also configure batch fetching per collection:

@Entity
public class Order {
    @Id
    private Long id;
    
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    @BatchSize(size = 20)
    private List<OrderItem> items;
}

Batch fetching is useful when:

• You have many relationships and can't fetch them all at once
• You're not sure ahead of time which relationships will be accessed
• You want a "safe" default that handles most cases without explicit query tuning

The trade-off: you still make multiple queries, so network latency accumulates. Batch size is a tuning knob—too small and you make many queries; too large and individual queries become expensive.

Comparing the Three Approaches

A Practical Example: Putting It Together

Let's build a realistic scenario. Your e-commerce platform needs to display pending orders with their items and customer details for an admin dashboard:

@Entity
@NamedEntityGraphs({
    @NamedEntityGraph(
        name = "Order.forAdmin",
        attributeNodes = {
            @NamedAttributeNode("items"),
            @NamedAttributeNode("customer")
        }
    )
})
public class Order {
    @Id
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer;
    
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderItem> items;
    
    private Status status;
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    // Option 1: Fetch Join
    @Query("SELECT DISTINCT o FROM Order o " +
           "LEFT JOIN FETCH o.items " +
           "LEFT JOIN FETCH o.customer " +
           "WHERE o.status = :status")
    List<Order> findAdminViewWithFetch(@Param("status") Status status);
    
    // Option 2: Entity Graph
    @EntityGraph("Order.forAdmin")
    List<Order> findByStatus(Status status);
}

Both achieve the same result: one query that loads orders, items, and customer data together. Choose fetch joins when you have complex WHERE clauses on related entities; choose entity graphs for simpler, reusable queries.

Common Mistakes

Mistake 1: Using DISTINCT carelessly. Some developers add DISTINCT to every fetch-join query thinking it optimizes SQL. It doesn't—the SQL query still runs with duplicate rows. Use DISTINCT only in Java to deduplicate objects in memory, and only when necessary.

Mistake 2: Fetching too much. A fetch join that loads orders, items, customers, and payment histories together might be faster than multiple queries, but it transfers massive result sets. Monitor query performance. Sometimes several targeted queries outperform one large joined query.

Mistake 3: Ignoring lazy loading in loops. Even with batch fetching enabled, accessing lazy collections inside loops can feel slow because of network latency. Always ensure data is loaded before entering hot loops.

Mistake 4: Setting batch size too high. A batch size of 1,000 on every collection means a single access might load 1,000 collections at once—expensive memory and bandwidth. Start conservative (10–20) and tune based on actual usage patterns.

Mistake 5: Not testing in production-like scenarios. N+1 is invisible with 10 test records and obvious with 10,000. Use realistic data volumes in integration tests or staging before deploying.

Key Takeaways

• Spot it first: Enable SQL logging or Hibernate statistics to see the symptom—many identical SELECT queries firing in sequence.
• Fetch joins solve it with one query: JOIN FETCH in JPQL explicitly loads relationships; use DISTINCT in Java, not SQL, to deduplicate results.
• Entity graphs provide a cleaner, reusable alternative: Define fetch plans on entities or query methods without writing explicit join syntax.
• Batch fetching is the safety net: When you can't guarantee fetch requirements upfront, hibernate.default_batch_fetch_size ensures N+1 doesn't degrade to catastrophic performance.
• Choose based on your access pattern: Fetch joins for targeted, complex queries; entity graphs for standard fetch plans; batch fetching for variable or unknown access patterns.
• Test with realistic volumes: N+1 bugs hide in small datasets. Always benchmark with production-scale record counts before deploying.