Definition
- N+1 problem – query 1 loads N parent entities, then N additional queries fire to load each parent's lazy collection. For 100 accounts with trades, that is 101 queries instead of 1.
- JOIN FETCH – a JPQL clause that loads the parent and its collection in one SQL query using a JOIN. Eliminates N+1 completely for a single collection.
- @EntityGraph – a declarative fetch plan on a repository method. Hibernate builds the JOIN automatically without writing JPQL.
- @BatchSize – loads lazy collections in batches using
WHERE id IN (?, ?, ...)instead of one query per parent. Reduces N+1 to N/batchSize+1 queries. - MultipleBagFetchException – thrown when JOIN FETCH is used on two
Listcollections in one query, creating a Cartesian product. Fix by usingSetor fetching one collection per query.
N+1 is like asking the National Bank of India clerk for 100 account holders (1 trip), then making 100 separate trips to the vault to fetch each holder's transactions. JOIN FETCH is telling the clerk to bring accounts WITH their transactions in one trip. @BatchSize is fetching transactions 50 accounts at a time instead of one at a time. The Cartesian pitfall is asking for accounts, transactions, AND nominees in one trip – 100 x 10 x 3 = 3,000 rows overflowing the cart.
Understanding the Concepts
What is the N+1 query problem?
The N+1 problem occurs when you load a parent entity that has a LAZY collection. Query 1 loads N parents. Then, the first time you access each parent's collection, Hibernate fires one query per parent to load that collection. The total is 1 + N queries.
At National Bank of India, Rajesh loads 100 accounts. Each account has a list of trades. Without any optimization, Hibernate fires 101 queries.
package com.bank.service;
import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class AccountReportService {
private final AccountRepository accountRepo;
public AccountReportService(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
@Transactional(readOnly = true)
public void generateReport() {
List<Account> accounts = accountRepo.findAll();
// Query 1: SELECT * FROM accounts (loads 100 accounts)
for (Account account : accounts) {
account.getTrades().size();
// Query 2: SELECT * FROM trades WHERE account_id = 'SAV2001'
// Query 3: SELECT * FROM trades WHERE account_id = 'SAV2002'
// ... Query 101: SELECT * FROM trades WHERE account_id = 'SAV2100'
}
// Total: 1 + 100 = 101 queries!
}
}
How do you detect N+1 queries?
The simplest detection method is enabling SQL logging and counting queries per request. You can also enable Hibernate statistics for a detailed query count.
# application.yml: enable SQL logging
spring:
jpa:
show-sql: true
properties:
hibernate:
format_sql: true
generate_statistics: true
# Hibernate statistics log: "Session Metrics { 101 queries executed }"
package com.bank.config;
import org.hibernate.SessionFactory;
import org.hibernate.stat.Statistics;
import jakarta.persistence.EntityManagerFactory;
public class QueryCounter {
public void countQueries(EntityManagerFactory emf) {
SessionFactory sf = emf.unwrap(SessionFactory.class);
Statistics stats = sf.getStatistics();
stats.setStatisticsEnabled(true);
long before = stats.getQueryExecutionCount();
// ... run your service method ...
long after = stats.getQueryExecutionCount();
System.out.println("Queries executed: " + (after - before));
// Output: Queries executed: 101 (N+1 detected!)
}
}
How does JOIN FETCH fix N+1?
JOIN FETCH loads the parent and its collection in one SQL query using a JOIN. Instead of 101 queries, you get exactly 1 query.
package com.bank.repository;
import com.bank.model.Account;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
@Query("SELECT a FROM Account a JOIN FETCH a.trades")
List<Account> findAllWithTrades();
// Generates ONE query:
// SELECT a.*, t.* FROM accounts a
// INNER JOIN trades t ON a.account_id = t.account_id
// Result: 1 query instead of 101!
}
Expected SQL output:
SELECT a.account_id, a.holder_name, a.balance,
t.trade_id, t.symbol, t.amount
FROM accounts a
INNER JOIN trades t ON a.account_id = t.account_id
-- 1 query, all data loaded
How does @EntityGraph fix N+1?
@EntityGraph is a declarative fetch plan. You annotate the repository method and Hibernate builds the JOIN automatically. No JPQL needed.
package com.bank.repository;
import com.bank.model.Account;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
@EntityGraph(attributePaths = {"trades"})
@Query("SELECT a FROM Account a")
List<Account> findAllWithTradesGraph();
// Same result as JOIN FETCH but declarative.
// Generates: SELECT a.*, t.* FROM accounts a
// LEFT JOIN trades t ON a.account_id = t.account_id
// EntityGraph with multiple attributes:
@EntityGraph(attributePaths = {"trades", "nominees"})
@Query("SELECT a FROM Account a WHERE a.accountId = :id")
Account findWithTradesAndNominees(String id);
}
How does @BatchSize fix N+1?
@BatchSize does not eliminate lazy loading. Instead, it batches the lazy loads using an IN clause. Instead of 100 separate queries, Hibernate fires 100/batchSize queries.
package com.bank.model;
import jakarta.persistence.*;
import org.hibernate.annotations.BatchSize;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
@Entity
@Table(name = "accounts")
public class Account {
@Id
@Column(name = "account_id")
private String accountId;
@Column(name = "holder_name")
private String holderName;
@Column(precision = 15, scale = 2)
private BigDecimal balance;
@OneToMany(mappedBy = "account", fetch = FetchType.LAZY)
@BatchSize(size = 50)
private List<Trade> trades = new ArrayList<>();
// Without @BatchSize: 100 accounts = 100 queries for trades
// With @BatchSize(50): 100 accounts = 2 batch queries (50 + 50)
// Batch query: SELECT * FROM trades WHERE account_id IN (?, ?, ... x50)
}
Without @BatchSize (100 accounts):
Query 1: SELECT * FROM accounts -- 1 query
Query 2: SELECT * FROM trades WHERE account_id = 'SAV2001' -- per account
... 100 more queries
Total: 101 queries
With @BatchSize(size = 50):
Query 1: SELECT * FROM accounts -- 1 query
Query 2: SELECT * FROM trades WHERE account_id IN (SAV2001..SAV2050) -- batch 1
Query 3: SELECT * FROM trades WHERE account_id IN (SAV2051..SAV2100) -- batch 2
Total: 3 queries!
How is JOIN FETCH different from @EntityGraph and @BatchSize?
All three fix N+1, but they work differently and have different trade-offs.
| Feature | JOIN FETCH | @EntityGraph | @BatchSize |
|---------|-----------|-------------|------------|
| Query count | 1 query | 1 query | N/batch + 1 |
| Approach | Explicit JPQL | Declarative annotation | Batched lazy load |
| Pagination | Breaks Pageable | Breaks Pageable | Works with Pageable |
| Multiple collections | MultipleBagFetchException | MultipleBagFetchException | Works fine |
| Memory | All data in one result set | All data in one result set | Loads in batches |
| Use case | Known fetch pattern | Same, cleaner syntax | Default for all lazy loads |
package com.bank.repository;
import com.bank.model.Account;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// JOIN FETCH: breaks pagination (Hibernate warns "firstResult/maxResults with collection fetch")
@Query("SELECT a FROM Account a JOIN FETCH a.trades")
List<Account> findAllWithTrades(); // returns List, not Page
// @BatchSize: works with pagination (lazy loads fire in batches after page loads)
Page<Account> findAll(Pageable pageable);
// Trades loaded lazily in batches of 50 when accessed
}
What is MultipleBagFetchException?
When you JOIN FETCH two List collections in one query, Hibernate creates a Cartesian product. For 100 accounts x 10 trades x 3 nominees = 3,000 rows. Hibernate treats an unordered List as a "bag" and throws MultipleBagFetchException.
package com.bank.repository;
import com.bank.model.Account;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
public interface AccountRepository extends JpaRepository<Account, String> {
// WRONG: JOIN FETCH on two Lists throws MultipleBagFetchException
@Query("SELECT a FROM Account a JOIN FETCH a.trades JOIN FETCH a.nominees")
List<Account> findWithTradesAndNominees();
// org.hibernate.loader.MultipleBagFetchException:
// cannot simultaneously fetch multiple bags
}
Three ways to fix this:
package com.bank.model;
import jakarta.persistence.*;
import org.hibernate.annotations.BatchSize;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
@Entity
@Table(name = "accounts")
public class Account {
@Id
private String accountId;
// FIX 1: Change one collection from List to Set
@OneToMany(mappedBy = "account")
private List<Trade> trades = new ArrayList<>(); // keep as List
@OneToMany(mappedBy = "account")
private Set<Nominee> nominees = new HashSet<>(); // change to Set
// Now JOIN FETCH on both works (Set is not a "bag")
// FIX 2: Use @BatchSize on the second collection
// @OneToMany(mappedBy = "account")
// @BatchSize(size = 50)
// private List<Nominee> nominees = new ArrayList<>();
// JOIN FETCH only trades; nominees load lazily in batches
// FIX 3: Fetch one collection per query (in service layer)
}
What are common mistakes with N+1?
The most common mistake is not detecting N+1 at all. The second most common mistake is applying JOIN FETCH everywhere without considering the Cartesian product.
package com.bank.service;
import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class AccountMistakeDemo {
private final AccountRepository accountRepo;
public AccountMistakeDemo(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
// WRONG: using findAll() and then accessing lazy collections
@Transactional(readOnly = true)
public void wrongApproach() {
List<Account> accounts = accountRepo.findAll();
for (Account a : accounts) {
System.out.println(a.getTrades().size()); // N+1!
}
}
// CORRECT: use JOIN FETCH or @EntityGraph
@Transactional(readOnly = true)
public void correctApproach() {
List<Account> accounts = accountRepo.findAllWithTrades();
for (Account a : accounts) {
System.out.println(a.getTrades().size()); // already loaded, no extra queries
}
}
}
What should a senior engineer know about N+1?
A senior engineer should set spring.jpa.properties.hibernate.default_batch_fetch_size=25 as a global safety net. This applies @BatchSize to all lazy collections application-wide, preventing the worst N+1 scenarios without changing any entity code.
# application.yml: global safety net for N+1
spring:
jpa:
properties:
hibernate:
default_batch_fetch_size: 25
# All lazy collections now load in batches of 25
# 100 accounts: 1 + 4 batch queries instead of 1 + 100
package com.bank.config;
// Senior-level N+1 prevention checklist:
// 1. Set default_batch_fetch_size = 25 globally
// 2. Override @ManyToOne to LAZY (default is EAGER)
// 3. Use JOIN FETCH for known access patterns
// 4. Use @EntityGraph for repository methods
// 5. Disable OSIV: spring.jpa.open-in-view=false
// 6. Use DTO projections for read-only API responses
// 7. Enable Hibernate statistics in dev/staging
// 8. Add query count assertions in integration tests
Visual Overview
N+1 Problem:
┌─────────────────────┐
│ Query 1: findAll() │ → SELECT * FROM accounts (N rows)
└──────────┬──────────┘
│
▼
┌───────────────────────────────────────────┐
│ For EACH of N accounts: │
│ Query 2: SELECT * FROM trades WHERE acct=1 │
│ Query 3: SELECT * FROM trades WHERE acct=2 │
│ ... │
│ Query N+1: SELECT * FROM trades WHERE acct=N │
└───────────────────────────────────────────┘
Total: 1 + N queries
Fix with JOIN FETCH:
┌───────────────────────────────────────────┐
│ Query 1: SELECT a.*, t.* │
│ FROM accounts a │
│ JOIN trades t ON a.id = t.account_id │
└───────────────────────────────────────────┘
Total: 1 query!
Fix with @BatchSize(50):
┌───────────────────────────────────────────┐
│ Query 1: SELECT * FROM accounts │
│ Query 2: SELECT * FROM trades │
│ WHERE account_id IN (1..50) │
│ Query 3: SELECT * FROM trades │
│ WHERE account_id IN (51..100) │
└───────────────────────────────────────────┘
Total: 3 queries!
Key Takeaway
The N+1 problem occurs when loading N parent entities triggers N additional queries for their lazy collections. For 100 accounts with trades, that is 101 queries for what should be a single logical read. Detection is the first step: enable spring.jpa.show-sql=true and count queries per request, or enable Hibernate statistics to get an exact query count.
Three fix strategies exist. JOIN FETCH loads the parent and collection in one SQL query – the most direct solution. @EntityGraph is a declarative fetch plan on the repository method that achieves the same result without writing JPQL. @BatchSize loads lazy collections in batches using an IN clause, reducing 100 queries to 2 or 3. At National Bank of India, Rajesh uses JOIN FETCH for the account detail page (one account with trades) and @BatchSize as a global safety net with default_batch_fetch_size=25.
The critical pitfall is JOIN FETCH on two List collections, which creates a Cartesian product and throws MultipleBagFetchException. The fix is changing one collection to Set, fetching one collection per query, or using @BatchSize for the second collection. A senior engineer sets default_batch_fetch_size globally, overrides all @ManyToOne to LAZY, disables OSIV, and uses DTO projections for read-only endpoints.
Remember one sentence: N+1 fires one query per parent's lazy collection – fix it with JOIN FETCH for known patterns, @BatchSize as a global safety net, and never JOIN FETCH two Lists.
---