Solving the N+1 Query Problem in Hibernate & JPA

What is the N+1 Query Problem?

The N+1 query problem is a common performance issue in ORM frameworks like Hibernate and JPA. It occurs when an application executes one query to fetch a list of records (1 query) and then N additional queries to fetch related data for each record (N queries). This results in 1 + N queries instead of a single efficient query, leading to poor database performance.

Example of N+1 Query Problem

Scenario: Fetching Users and Their Orders

Consider an application where a User entity has multiple Order entities (One-to-Many relationship).

JPA Entity Definitions:

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

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

@Entity
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String product;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
}

Fetching Data (N+1 Problem Example)

List<User> users = userRepository.findAll(); // 1 Query

for (User user : users) {
    List<Order> orders = orderRepository.findByUserId(user.getId()); // N Queries
}

SQL Queries Executed (N+1 Issue)

SELECT * FROM users; -- 1 Query
SELECT * FROM orders WHERE user_id = 1; -- N Queries
SELECT * FROM orders WHERE user_id = 2; -- N Queries
...
SELECT * FROM orders WHERE user_id = N; -- N Queries

🚨 Problem: If there are 100 users, the system executes 1 + 100 = 101 queries, leading to slow performance.

How to Solve the N+1 Query Problem

1️⃣ Solution: Using JOIN FETCH (Efficient Querying)

@Query("SELECT u FROM User u JOIN FETCH u.orders")
List<User> findAllUsersWithOrders();

✅ Optimized SQL Query Executed:

SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id; -- Only 1 Query!

👉 Advantage: Loads User and Order data in a single SQL query.

2️⃣ Solution: Using @EntityGraph (Automatic Optimization)

@EntityGraph(attributePaths = {"orders"})
@Query("SELECT u FROM User u")
List<User> findAllUsersWithOrders();

How it Works?

• Automatically optimizes queries without modifying JPQL.
• Works well with pagination (Pageable support), unlike JOIN FETCH.

3️⃣ Solution: Using @BatchSize for Lazy Loading Optimization

For large datasets, using JOIN FETCH can lead to too much data loading at once. Instead, we can batch queries efficiently:

@OneToMany(mappedBy = "user")
@BatchSize(size = 10)
private List<Order> orders;

What Happens?

• Instead of executing one query per user, Hibernate groups multiple queries together.
• Fewer DB hits, improved performance.

4️⃣ Solution: Using Subselect Fetching

Another alternative is using @Fetch(FetchMode.SUBSELECT):

@OneToMany(mappedBy = "user")
@Fetch(FetchMode.SUBSELECT)
private List<Order> orders;

✅ Hibernate will use one query for all users and another single query for all orders, avoiding N+1.

Comparison of N+1 Solutions

Final Takeaway

✅ Use JOIN FETCH when fetching related entities without pagination.

✅ Use @EntityGraph when you want automatic optimizations without modifying queries.

✅ Use @BatchSize or @Fetch(FetchMode.SUBSELECT) for large datasets to avoid memory overload.

By applying these techniques, you can significantly reduce database load and improve application performance! 🚀

Would you like a real-world Spring Boot project example to demonstrate these optimizations? Let me know! 💡