Understanding the N+1 Query Problem in Java with Hibernate
From some last few months I was working with relational database using Spring boot JPA library. While getting to know a few things and diving deep into query optimisations, I came across an interesting optimisation problem called N+1 Query Problem.
What is the N+1 Query Problem?
The N+1 query problem is a common performance issue or a botteneck in ORM-based applications. It occurs when you retrieve a collection of entities (e.g., a list of users) along with their related entities (e.g., orders) from a relational database. The problem is that, by default, the ORM framework issues separate SQL queries for each related entity, resulting in N+1 queries.
For example, if you fetch 100 users and each user has associated orders, you end up executing 101 SQL queries — one query to fetch the users and 100 additional queries to fetch their orders. This can lead to significant performance degradation, especially when dealing with large dataset.
Why Does the N+1 Query Problem Occur?
The N+1 query problem arises due to the default lazy loading behavior of ORM frameworks like Hibernate. Also its not limited to ORM frameworks like Hibernate also happens in other frameworks as well like with Django ORM in python.
In lazy loading, related entities are not fetched from the db until they are explicitly accessed. While this can save unnecessary db queries in some cases, it can also lead to the N+1 query problem when not managed properly.
Solving the N+1 Query Problem with Hibernate
Fortunately, Hibernate provides solutions to address the N+1 query problem and optimize database interactions. Here’s how you can do it:
For our use case we will take our entities as user_data and order.
below is SQL for that
CREATE TABLE "user_data" (
"id" uuid default uuid_generate_v4(),
"username" varchar not null,
primary key ("id")
);
CREATE TABLE "orders" (
"id" uuid default uuid_generate_v4(),
"user_id" uuid,
"order_description" varchar not null,
primary key ("id"),
foreign key ("user_id") REFERENCES user_data("id")
);1. Eager Loading:
We can use eager loading to fetch related entities along with the main entity in a single SQL query. Using below we can annotate our user_data entity having relationships with order using @OneToMany(fetch = FetchType.EAGER) to indicate that we want to load related entities eagerly.
@Getter
@Setter
@Entity
@Table(name = "user_data")
public class User {
@Id
@GeneratedValue
private UUID id;
private String username;
@OneToMany(mappedBy = "user", fetch = FetchType.EAGER)
private List<Order> orders;
}@Getter
@Setter
@Entity
@Table(name = "order")
public class Order {
@Id
@GeneratedValue
private UUID id;
private String orderDescription;
@ManyToOne
@JoinColumn(name = "user_id")
private User user;
}2. Join Fetch:
Also we can usejoin fetch clause in your JPQL queries to perform a SQL join and retrieve both the main entity and its related entities in one query. We can do it like as shown below.
@Repository
public interface UserRepository extends JpaRepository<User, UUID> {
@Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.orders")
List<User> findAllWithOrders();
}TIP: Eager loading can be costly if we don’t want load the data when its not needed. Instead we can use join fetch based whenever we want to fetch this type of data.
Conclusion
The N+1 query problem is a performance challenge that developers often encounter when using ORM frameworks like Hibernate in Java applications. By understanding the issue and employing techniques like eager loading, join fetching etc , you can optimize database queries and improve application performance.
Thank you for reading.
If you find this article useful, or you have any queries, reach out me via email at
gupta.chirantar@gmail.com
or via Linkedin
https://www.linkedin.com/in/chirantar/
Hit that clap button and follow me to get more articles on your feed.
You can follow me on Twitter.