Resolve Hibernate N+1 problem

In this story, I am talking about What is N+1 Problem and what is the impact on application performance, and how to overcome this in the application. This article will demonstrate how N+1 queries occur and their solution through an example in spring boot.

It is most common question asked in interviews too.

What is the N+1 problem in Hibernate?

The N+1 query problem is said to occur when an ORM, like hibernate, executes 1 query to retrieve the parent entity and N queries to retrieve the child entities. As the number of entities in the database increases, the queries being executed separately can easily affect the performance of the application.

The relation between user_details and address is a one-many mapping from user_details(id) to address(user_id). That means a user can have many addresses.

UserDetails.java

Address.java

SQL DDL Statements

Now let us query for the name contains on user_details table with repository function.

@Repository
public interface UserDetailsRepository extends JpaRepository<UserDetails, String> {
   
   List<UserDetails> findByNameContaining(String text);
}

When we call the function this will generate two different queries one to fetch the from user_details based on name and the other on addresses using user_id . As of now, we have only two users the additional queries are only two. If the number of matching queries increases the additional queries also increase.

Hibernate: select userdetail0_.id as id1_1_, userdetail0_.created_at as created_2_1_, userdetail0_.email as email3_1_, userdetail0_.mobile_number as mobile_n4_1_, userdetail0_.name as name5_1_, userdetail0_.updated_at as updated_6_1_ from user_details userdetail0_ where userdetail0_.name like ? escape ?
Hibernate: select addresses0_.user_id as user_id10_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.address1 as address2_0_1_, addresses0_.address2 as address3_0_1_, addresses0_.city as city4_0_1_, addresses0_.country as country5_0_1_, addresses0_.created_at as created_6_0_1_, addresses0_.state as state7_0_1_, addresses0_.street as street8_0_1_, addresses0_.updated_at as updated_9_0_1_, addresses0_.user_id as user_id10_0_1_ from address addresses0_ where addresses0_.user_id=?
Hibernate: select addresses0_.user_id as user_id10_0_0_, addresses0_.id as id1_0_0_, addresses0_.id as id1_0_1_, addresses0_.address1 as address2_0_1_, addresses0_.address2 as address3_0_1_, addresses0_.city as city4_0_1_, addresses0_.country as country5_0_1_, addresses0_.created_at as created_6_0_1_, addresses0_.state as state7_0_1_, addresses0_.street as street8_0_1_, addresses0_.updated_at as updated_9_0_1_, addresses0_.user_id as user_id10_0_1_ from address addresses0_ where addresses0_.user_id=?

How to fix this?

EntityGraphs provides 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 define if the entities not specified by attribute nodes of EntityGraphs should be fetched lazily or eagerly. Attributes specified by attribute nodes of EntityGraph are always fetched eagerly.

FETCH TYPE: Attributes that are specified by AttributeNodes of EntityGraph are treated as FetchType.EAGER and the 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 the 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 UserDetails with JPA’s @NamedEntityGraph annotation, and then attach the @EntityGraph annotation to the repository method, with the name of the graph.

Now if you run the repository method again, one query is executed.

Hibernate: select userdetail0_.id as id1_1_0_, addresses1_.id as id1_0_1_, userdetail0_.created_at as created_2_1_0_, userdetail0_.email as email3_1_0_, userdetail0_.mobile_number as mobile_n4_1_0_, userdetail0_.name as name5_1_0_, userdetail0_.updated_at as updated_6_1_0_, addresses1_.address1 as address2_0_1_, addresses1_.address2 as address3_0_1_, addresses1_.city as city4_0_1_, addresses1_.country as country5_0_1_, addresses1_.created_at as created_6_0_1_, addresses1_.state as state7_0_1_, addresses1_.street as street8_0_1_, addresses1_.updated_at as updated_9_0_1_, addresses1_.user_id as user_id10_0_1_, addresses1_.user_id as user_id10_0_0__, addresses1_.id as id1_0_0__ from user_details userdetail0_ left outer join address addresses1_ on userdetail0_.id=addresses1_.user_id where userdetail0_.name like ? escape ?

2. Using Adhoc Entity Graph on Repository interface

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.

Now if you run the repository method again, one query is executed.

Hibernate: select userdetail0_.id as id1_1_0_, addresses1_.id as id1_0_1_, userdetail0_.created_at as created_2_1_0_, userdetail0_.email as email3_1_0_, userdetail0_.mobile_number as mobile_n4_1_0_, userdetail0_.name as name5_1_0_, userdetail0_.updated_at as updated_6_1_0_, addresses1_.address1 as address2_0_1_, addresses1_.address2 as address3_0_1_, addresses1_.city as city4_0_1_, addresses1_.country as country5_0_1_, addresses1_.created_at as created_6_0_1_, addresses1_.state as state7_0_1_, addresses1_.street as street8_0_1_, addresses1_.updated_at as updated_9_0_1_, addresses1_.user_id as user_id10_0_1_, addresses1_.user_id as user_id10_0_0__, addresses1_.id as id1_0_0__ from user_details userdetail0_ left outer join address addresses1_ on userdetail0_.id=addresses1_.user_id where userdetail0_.name like ? escape ?

Conclusion

Thus, N+1 queries were reduced to only a single query using JOINS to fetch data from both tables.
EntityGraphs provide a mechanism by which entities can be fetched eagerly from the database in a single select statement, helping in improving the performance of the application. You can also use Subgraphs to define the entities for the child class, that need to be fetched eagerly along with the parent class.