N+1 Query Problem in Hibernate and JPA

Learn the N+1 Query Problem in Hibernate and JPA with SQL examples, Spring Boot code examples, Fetch Join, EntityGraph, BatchSize, DTO Projection, performance tuning, and interview questions.

What You Will Learn

What is N+1 Query Problem?
Why It Happens
Real SQL Examples
Performance Impact
OneToMany Example
ManyToOne Example
Fetch Join Solution
EntityGraph Solution
BatchSize Solution
DTO Projection Solution
Enterprise Best Practices
Interview Questions

Introduction

One of the most common Hibernate performance issues is:

N+1 Query Problem

Many production systems suffer from:

Slow APIs

High Database Load

Excessive SQL Queries

Poor Performance

because developers don't understand N+1.

What is N+1 Query Problem?

N+1 occurs when:

1 Query

loads Parent Records

+

N Queries

load Child Records

Total:

N + 1 Queries

instead of:

1 Query

Example

Suppose:

10 Customers

Each Customer Has Orders

Hibernate executes:

1 Query For Customers

10 Queries For Orders

Total:

11 Queries

This is N+1.

Real Example Database

Customers

ID | NAME

1  | Venu
2  | John
3  | David

Orders

ID | ORDER_NO | CUSTOMER_ID

101 | O1001 | 1
102 | O1002 | 1
103 | O1003 | 2
104 | O1004 | 3

Entity Relationship

@Entity
public class Customer {

    @Id
    private Long id;

    private String name;

    @OneToMany(
            mappedBy = "customer",
            fetch = FetchType.LAZY
    )
    private List<Order> orders;
}

@Entity
public class Order {

    @Id
    private Long id;

    private String orderNo;

    @ManyToOne
    private Customer customer;
}

N+1 Scenario

Load all customers:

List<Customer> customers =
        customerRepository.findAll();

Generated SQL:

SELECT *
FROM customer;

Only one query.

Problem Starts Here

for(Customer customer : customers){

    System.out.println(
            customer.getOrders().size()
    );
}

Hibernate now loads orders.

Generated Queries

Query 1

SELECT *
FROM customer;

Query 2

SELECT *
FROM orders
WHERE customer_id = 1;

Query 3

SELECT *
FROM orders
WHERE customer_id = 2;

Query 4

SELECT *
FROM orders
WHERE customer_id = 3;

Total Queries

1 Customer Query

+

3 Order Queries

=

4 Queries

For:

1000 Customers

Hibernate executes:

1001 Queries

Huge performance problem.

Visual Flow

flowchart LR

LoadCustomers --> Customer1Orders

LoadCustomers --> Customer2Orders

LoadCustomers --> Customer3Orders

Why Does This Happen?

Because:

@OneToMany(
    fetch = FetchType.LAZY
)

means:

Load Orders Only When Needed

Every access triggers SQL.

Real Enterprise Example

Banking System:

Customer

↓

Accounts

↓

Transactions

Load customers:

List<Customer> customers =
repository.findAll();

Then:

customers.forEach(customer ->
customer.getAccounts().size()
);

Potentially:

500 Customers

↓

501 Queries

Logging SQL

Enable:

spring:
  jpa:
    show-sql: true

logging:
  level:
    org.hibernate.SQL: DEBUG

Then observe SQL explosion.

Example API

@GetMapping("/customers")
public List<Customer> getCustomers() {

    return repository.findAll();
}

Looks innocent.

But internally:

Hundreds Of Queries

may execute.

Solution 1: Fetch Join

Best solution.

Repository

@Query("""
    SELECT DISTINCT c
    FROM Customer c
    LEFT JOIN FETCH c.orders
""")
List<Customer> findAllWithOrders();

Generated SQL

SELECT c.*, o.*
FROM customer c
LEFT JOIN orders o
ON c.id = o.customer_id;

Single query.

Query Comparison

Without Fetch Join:

1001 Queries

With Fetch Join:

1 Query

Huge improvement.

Fetch Join Flow

flowchart LR

Customer --> Orders

Orders --> SingleSQLQuery

Solution 2: EntityGraph

Cleaner Spring Data approach.

@EntityGraph(
    attributePaths = {"orders"}
)
@Query(
 "SELECT c FROM Customer c"
)
List<Customer> findAllCustomers();

Generated SQL

Hibernate automatically performs join fetch.

EntityGraph Benefits

Cleaner Repository

No JPQL Complexity

Easy Maintenance

Solution 3: BatchSize

Very useful.

@OneToMany(
        mappedBy = "customer",
        fetch = FetchType.LAZY
)
@BatchSize(size = 20)
private List<Order> orders;

What Happens?

Instead of:

100 Queries

Hibernate loads:

20 Customers At Once

Example

Without BatchSize:

SELECT *
FROM orders
WHERE customer_id = 1;

SELECT *
FROM orders
WHERE customer_id = 2;

SELECT *
FROM orders
WHERE customer_id = 3;

With BatchSize

SELECT *
FROM orders
WHERE customer_id IN
(1,2,3,4,5,6,7,8,9,10);

Much better.

Solution 4: DTO Projection

Best for APIs.

DTO

public record CustomerDTO(

        Long customerId,

        String customerName,

        String orderNo

) {
}

Query

@Query("""
SELECT new com.codewithvenu.dto.CustomerDTO(

c.id,

c.name,

o.orderNo

)

FROM Customer c

JOIN c.orders o
""")
List<CustomerDTO> findAllData();

Benefits

Only Required Columns

No Entity Loading

Fastest Approach

ManyToOne N+1 Problem

Most developers think:

Only OneToMany Has N+1

Wrong.

Example

@Entity
public class Order {

    @ManyToOne(
            fetch = FetchType.LAZY
    )
    private Customer customer;
}

Query

List<Order> orders =
        orderRepository.findAll();

Access Customer

for(Order order : orders){

    System.out.println(
            order.getCustomer()
                 .getName()
    );
}

Generated Queries

SELECT *
FROM orders;

Then:

SELECT *
FROM customer
WHERE id=1;

SELECT *
FROM customer
WHERE id=2;

SELECT *
FROM customer
WHERE id=3;

Again N+1.

Production Example

Insurance Application

Claims

↓

Policy

↓

Customer

API:

claims.forEach(claim ->
claim.getPolicy()
     .getCustomer()
     .getName()
);

Can create:

Thousands Of Queries

N+1 Detection Tools

Useful tools:

Hibernate Statistics

P6Spy

Datasource Proxy

New Relic

Dynatrace

Datadog

Enable Hibernate Statistics

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true

Code Example

Statistics stats =
sessionFactory
.getStatistics();

System.out.println(
stats.getQueryExecutionCount()
);

Bad Practice

List<Customer> customers =
repository.findAll();

customers.forEach(c ->
c.getOrders().size()
);

Better Practice

List<Customer> customers =
repository.findAllWithOrders();

Best Practice Architecture

flowchart LR

Controller --> Service

Service --> Repository

Repository --> FetchJoin

FetchJoin --> Database

Performance Comparison

Records	N+1 Queries	Fetch Join
10	11 Queries	1 Query
100	101 Queries	1 Query
1000	1001 Queries	1 Query

Banking Example

Customer Dashboard:

Customer

Accounts

Transactions

Loans

Wrong:

customer.getAccounts();

account.getTransactions();

account.getLoans();

Can generate:

Thousands Of Queries

Better Solution

@EntityGraph(
attributePaths = {
"accounts",
"accounts.transactions",
"accounts.loans"
}
)

Common Mistakes

❌ Returning Entities Directly From APIs

❌ Ignoring SQL Logs

❌ Using EAGER Everywhere

❌ Nested Lazy Loops

❌ No Performance Testing

Best Practices

✅ Prefer LAZY

✅ Use Fetch Join

✅ Use DTO Projections

✅ Use EntityGraph

✅ Monitor SQL

✅ Enable Hibernate Statistics

✅ Load Only Needed Data

Interview Questions

What is N+1 Query Problem?

One query loads parent records and N additional queries load child records.

Why Does It Happen?

Because Hibernate lazily loads relationships.

How To Detect It?

SQL Logs

Hibernate Statistics

P6Spy

Best Solution?

Fetch Join

Alternative Solutions?

EntityGraph

BatchSize

DTO Projection

Does EAGER Solve N+1?

Not always.

Sometimes EAGER creates even larger performance issues.

Which Is Best For REST APIs?

DTO Projection

Key Takeaways

N+1 is one of the biggest Hibernate performance problems.
It happens when related entities are loaded individually.
Fetch Join is usually the best fix.
EntityGraph provides cleaner Spring Data integration.
BatchSize reduces query count significantly.
DTO Projection is ideal for API responses.
Always monitor generated SQL in production.
Understanding N+1 is essential for every Java developer working with Hibernate and Spring Data JPA.