Mastering the N+1 Problem in Hibernate

Nov 12, 2024

Mastering the N+1 Problem in Hibernate

The N+1 query problem is a common performance issue in Hibernate or JPA applications.
It happens when your code triggers many small SQL queries instead of one efficient query.

Even experienced Java developers run into this when fetching entities with collections.

🧩 What is the N+1 Problem?

Imagine two entities: Client and Payment. A client can have many payments:

@Entity
public class Client {
    @Id
    private Long id;

    private String name;

    @OneToMany(mappedBy = "client", fetch = FetchType.LAZY)
    private List<Payment> payments;
}

Fetching all clients:

List<Client> clients = entityManager
    .createQuery("SELECT c FROM Client c", Client.class)
    .getResultList();

Hibernate runs one query:

SELECT * FROM clients;

But accessing each client's payments:

for (Client client : clients) {
    System.out.println(client.getPayments().size());
}

Triggers one extra query per client:

SELECT * FROM payments WHERE client_id = ?;

✅ 50 clients → 51 queries
✅ 1,000 clients → 1,001 queries

This is the N+1 problem.

⚙️ Why it happens

Hibernate defaults to lazy loading (FetchType.LAZY) to save memory.
The problem occurs when you actually need all the related entities. Each access fires a new SQL query.

Think of it as Hibernate being helpful but too eager to load just-in-time.

🧠 How to Spot It

  • Slow performance with many queries
  • Logs showing hundreds or thousands of SQL statements
  • High database CPU usage

Enable SQL logging:

spring.jpa.show-sql=true

Or use tools like P6Spy to see all queries.

🔧 How to Fix the N+1 Problem

There are four main strategies in Hibernate:

1️⃣ JOIN FETCH (Eager Loading in Query)

Use JOIN FETCH to load clients and payments in a single query:

@Query("SELECT c FROM Client c JOIN FETCH c.payments")
List<Client> findAllWithPayments();

Or:

String jpql = "SELECT c FROM Client c JOIN FETCH c.payments";
List<Client> clients = entityManager
    .createQuery(jpql, Client.class)
    .getResultList();

Pros: Simple, one query.
Cons: Can create huge result sets if there are many collections.

2️⃣ @EntityGraph (Reusable Fetch Plan)

@EntityGraph tells Hibernate which associations to fetch without changing the query itself.

Named EntityGraph Example:

@NamedEntityGraph(
    name = "Client.withPayments",
    attributeNodes = @NamedAttributeNode("payments")
)
@Entity
public class Client { ... }

Use it in a query:

EntityGraph<?> graph = entityManager.getEntityGraph("Client.withPayments");
List<Client> clients = entityManager
    .createQuery("SELECT c FROM Client c", Client.class)
    .setHint("javax.persistence.fetchgraph", graph)
    .getResultList();

Pros: Reusable, JPA standard, works well with clean architecture.
Cons: Slightly more setup than JOIN FETCH.

Tip: Use @EntityGraph when your domain model needs predictable fetch plans.

3️⃣ @BatchSize (Optimized Lazy Loading)

Keep lazy loading but reduce queries by fetching in batches:

@BatchSize(size = 10)
@OneToMany(mappedBy = "client", fetch = FetchType.LAZY)
private List<Payment> payments;

Or set globally:

spring.jpa.properties.hibernate.default_batch_fetch_size=16

Pros: Fewer queries while keeping lazy loading.
Cons: Still multiple queries; needs tuning.

4️⃣ @Fetch(FetchMode.SUBSELECT) (Bulk Lazy Loading)

Use a subquery to fetch all child entities at once:

@OneToMany(mappedBy = "client", fetch = FetchType.LAZY)
@Fetch(FetchMode.SUBSELECT)
private List<Payment> payments;

SQL example:

SELECT * FROM payments
WHERE client_id IN (SELECT id FROM clients);

Pros: Only 2 queries, avoids Cartesian joins.
Cons: Not good with pagination or huge datasets.

📊 Quick Comparison

| Method        | Queries | Best For                | Notes                           |
|---------------|---------|-------------------------|---------------------------------|
| JOIN FETCH    |    1    | Small datasets          | Easy, but beware of large joins |
| @EntityGraph  |    1    | Reusable fetch plans    | Clean architecture, JPA standard|
| @BatchSize    | ~N/batch| Large lazy-loaded graphs| Reduces queries, keeps lazy     |
| SUBSELECT     |    2    | Read-heavy flows        | Avoids joins, memory-intensive  |

🧩 Key Takeaways

  • The N+1 problem is not a bug, but an access pattern issue.
  • Use JOIN FETCH or @EntityGraph when you know you need all data.
  • Use BatchSize or Subselect for large, lazy-loaded datasets.
  • Combine with DTO projections for big aggregations.

Load what you need, when you need it, using the right strategy.

Mastering these makes Hibernate efficient without losing the benefits of ORM.

ilia-kritiuk.dev