Understanding and Resolving the N+1 Query Problem in Hibernate ORM

Table of Contents

1. Introduction
2. Understanding the N+1 Query Problem
3. Demonstrating the Problem with a Case Study
4. Why the N+1 Problem is Costly
5. Diagnosing the N+1 Problem
6. Solutions to the N+1 Query Problem Using Fetch Joins, Batch Fetching, Entity Graphs, Optimizing Collections with @OneToMany
7. Best Practices for Avoiding N+1 Problems in Hibernate
8. Conclusion

1. Introduction

In Java applications, Hibernate simplifies working with databases by mapping objects to database tables using Object-Relational Mapping (ORM). However, certain challenges arise when dealing with complex relationships, one of which is the "N+1 query problem." This problem can cause severe performance issues if not properly handled. In this article, we will dive into what the N+1 query problem is, how it manifests in Hibernate, and best practices for preventing and resolving it.

2. Understanding the N+1 Query Problem

The N+1 query problem occurs when one initial query to fetch an entity leads to N additional queries to load its related entities.

FetchType.EAGER

Using FetchType.EAGER either implicitly or explicitly for your JPA associations is a bad idea because you are going to fetch way more data that you need. More, the FetchType.EAGER strategy is also prone to N+1 query issues.

FetchType.LAZY

Even if you switch to using FetchType.LAZY explicitly for all associations, you can still bump into the N+1 issue.

3. Demonstrating the Problem

FetchType.EAGER

Let's examine the N+1 problem using the following Customer and Order entities. Each Customer can have multiple Order records associated with them.

import jakarta.persistence.*;
import lombok.*;

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@ToString
@Entity
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
}        

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@ToString
@Entity
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String notes;

    @ManyToOne
    private Customer customer;
}        

for this simple JPQL query

List<Order> orders = entityManager
                .createQuery("""
                                select o
                                from Order o
                            """, Order.class)
                .getResultList();        

this is the resulted database query for above when we only have 4 orders each with associated to one customer

In this example, Hibernate issues:

1. An initial query to fetch all orders.
2. A separate query for each order's customer becuase the default for the ManyToOne mapping is eager so it results into N + 1 query

Hibernate: select o1_0.id,o1_0.customer_id,o1_0.notes from orders o1_0
Hibernate: select c1_0.id,c1_0.first_name,c1_0.last_name from customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.first_name,c1_0.last_name from customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.first_name,c1_0.last_name from customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.first_name,c1_0.last_name from customer c1_0 where c1_0.id=?        

FetchType.LAZY

now lets make the association as lazy

import jakarta.persistence.*;
import lombok.*;

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@ToString
@Entity
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;

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

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@ToString
@Entity
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String notes;

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

Scenario

When querying for a list of customers, we might want to access their associated orders. A common approach would be:

    @Test
    void testFindAllCustomers() {
        List<Customer> customers = customerRepository.findAll();

        for (Customer customer : customers) {
            System.out.println(customer.getOrders().size()); // Triggers an additional query per customer
        }
    }        

this is the resulted database query for above when we only have 4 customers each with 1 order

In this example, Hibernate issues:

1. An initial query to fetch all customers.
2. A separate query for each customer's orders when calling customer.getOrders(), resulting in N additional queries.

Hibernate: select c1_0.id,c1_0.first_name,c1_0.last_name from customer c1_0
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.notes from orders o1_0 where o1_0.customer_id=?
1
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.notes from orders o1_0 where o1_0.customer_id=?
1
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.notes from orders o1_0 where o1_0.customer_id=?
1
Hibernate: select o1_0.customer_id,o1_0.id,o1_0.notes from orders o1_0 where o1_0.customer_id=?
1        

4. Why the N+1 Problem is Costly

This query pattern can lead to serious performance issues. For example:

• If 100 customers are retrieved, Hibernate will execute 101 queries instead of a single query with a join, which is far less efficient.
• This increased database load results in slower application performance and can quickly degrade with larger datasets.

By addressing the N+1 query problem, we avoid unnecessary queries, reduce database roundtrips, and ensure our application scales well.

5. Diagnosing the N+1 Problem

To identify N+1 issues, you can enable SQL logging in Hibernate. With logging enabled, you’ll see each SQL statement generated, allowing you to spot repetitive query patterns.

Enabling SQL Logging in Hibernate

# application.properties
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true        

Once enabled, check for repetitive queries related to the entity relationship. In our example, repeated SELECT statements for Order entities per Customer indicate an N+1 issue.

6. Solutions to the N+1 Query Problem

Several strategies can address the N+1 query problem. Let’s explore some effective solutions.

1. Using Fetch Joins

A fetch join allows you to retrieve entities and their associations in a single query. This approach can solve the N+1 problem by eagerly loading the required entities.

List<Customer> customers = entityManager.createQuery(
    "SELECT c FROM Customer c JOIN FETCH c.orders", Customer.class).getResultList();        

Here, the JOIN FETCH clause tells Hibernate to load both Customer and associated Order entities in one go, eliminating additional queries.

2. Batch Fetching

Batch fetching can help reduce the number of queries by loading related entities in batches. Hibernate’s @BatchSize annotation specifies the batch size for associated entities.

Example:

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

In this case, Hibernate will fetch orders for 10 customers at a time, reducing the total number of queries and improving performance.

3. Entity Graphs

Entity Graphs are useful when you want to customize the fetch plan at runtime, allowing flexibility in controlling which relationships are fetched eagerly, they provide a way to formulate better performing queries by defining which entities need to be retrieved from the database using SQL JOINS.

There are two types of entityGraphs, Fetch and Load, which defines if the entities not specified by attributeNodes of entityGraphs should be fetched lazily or eagerly. Attributes specified by attributeNodes of entityGraph are always fetched eagerly.

FETCH TYPE: Attributes that are specified by attributeNodes of entityGraph are treated as FetchType.EAGER and rest of the attributes are treated as FetchType.Lazy.

LOAD TYPE: Attributes that are specified by attributeNodes of entityGraph are treated as FetchType.EAGER and rest of the attributes are treated according to their specified or default fetchTypes.

EntityGraphs can be defined in two ways:

1. Using NamedEntityGraph Annotation

To use a NamedEntityGraph, first annotate the entity class Publication with JPA’s @NamedEntityGraph annotation , and then attach the @EntityGraph annotation to the repository method, with the name of the graph.

Setup Example:

@NamedEntityGraph(name = "Customer.orders", attributeNodes = @NamedAttributeNode("orders"))
//orders is the name of the attribute to fetch via join when we have lazy loaded one to many association
public class Customer {

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

Usage Example:

        EntityGraph<Customer> graph = (EntityGraph<Customer>) entityManager.createEntityGraph("customer.orders");
        List<Customer> customers = entityManager
                .createQuery("SELECT c FROM Customer c", Customer.class)
                .setHint("jakarta.persistence.fetchgraph", graph)
                .getResultList();
        for (Customer customer : customers) {
            System.out.println(customer.getOrders().size());
        }
the generated query joins with order table:
Hibernate: select c1_0.id,c1_0.first_name,c1_0.last_name,o1_0.customer_id,o1_0.id,o1_0.notes from customer c1_0 left join orders o1_0 on c1_0.id=o1_0.customer_id
        

2. Without NamedEntityGraph Annotation

You can also define an ad-hoc EntityGraph, using attributePaths, without using NamedEntityGraph annotation on the entity. AttributePaths should include the names of the entities to be fetched eagerly.

@EntityGraph(type = EntityGraph.EntityGraphType.FETCH,
attributePaths = "orders")
List<Customer> findByLastName(String lastName);

the generated query joins with order table:
Hibernate: select c1_0.id,c1_0.first_name,c1_0.last_name,o1_0.customer_id,o1_0.id,o1_0.notes from customer c1_0 left join orders o1_0 on c1_0.id=o1_0.customer_id where c1_0.last_name=?
        

Entity Graphs allow you to avoid unnecessary data retrieval by specifying fetch plans only when needed.

4. Optimizing Collections with @OneToMany

For @OneToMany relationships, configure the fetch type and batch size according to the use case. Eager fetching can be beneficial if the data is frequently accessed, but it may hurt performance for rarely accessed collections.

7. Best Practices for Avoiding N+1 Problems in Hibernate

1. Use Fetch Joins Wisely: Fetch joins are efficient but can lead to excessive data retrieval if overused, so use them only when necessary.
2. Apply @BatchSize Where Applicable: This is especially useful for larger collections. It prevents loading all records at once, balancing between eager and lazy fetching.
3. Consider DTOs for Read-Heavy Use Cases: For cases where only specific fields are needed, use DTO projections to avoid loading entire entities.
4. Monitor and Log SQL Queries: Regularly logging SQL can help spot N+1 patterns early in development, before they impact production performance.

8. Conclusion

The N+1 query problem in Hibernate is a common issue that can hinder application performance. By understanding the problem, diagnosing it with SQL logging, and applying the right solution—such as fetch joins, batch fetching, or entity graphs—developers can manage their Hibernate relationships effectively. This not only optimizes database interactions but also ensures scalable and efficient applications.