What is N+1 problem in Hibernate, how will you identify and solve it?

Carvia Tech | December 23, 2017 | 4 min read | 321 views


N+1 problem is a performance issue in Object Relational Mapping that fires multiple select queries (N+1 to be exact, where N= number of records in table) in database for a single select query at application layer. Hibernate provides multiple ways to catch and prevent this problem.

What is N+1 Problem?

To understand N+1 problem, lets consider a scenario. Let’s say we have a collection of User objects mapped to t_users table in database, and each user has collection or Role mapped to t_roles table using a joining table t_user_roles. At the ORM level a User has many to many relationship with Role.

@Entity
@Table(name = "t_users")
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String name;

    @ManyToMany(fetch = FetchType.LAZY)                    (1)
    private Set<Role> roles;
    //Getter and Setters removed for brevity
 }

@Entity
@Table(name = "t_roles")
public class Role {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    private String name;
    //Getter and Setters removed for brevity
 }
1 A user can have many roles.

Now lets say we want to fetch all users from this table and print roles for each one. Very naive Object Relational implementation could be -

First Get All User

Select * from t_users;

Then get roles for each user

Select * from t_user_roles where userid = <userid>;

So we need one select for User and N additional selects for fetching roles for each user, where N is total number of users. This is a classic N+1 problem in ORM.

How to identify it?

Hibernate provide tracing option that enables SQL logging in the console/logs. using logs you can easily see if hibernate is issuing N+1 queries for a given call.

Enabling SQL logging in application.yml
spring:
    jpa:
       show-sql: true             (1)
       database-platform: org.hibernate.dialect.H2Dialect
       hibernate:
         ddl-auto: create
         use-new-id-generator-mappings: true
       properties:
          hibernate:
            type: trace            (2)
1 Enables SQL logging in trace.
2 We have to enable this too in order to show sql queries in logs.
Typical N+1 SQL logs printed in logs
2017-12-23 07:42:30.923  INFO 11657 --- [           main] hello.UserService                        : Customers found with findAll():
Hibernate: select user0_.id as id1_1_, user0_.name as name2_1_ from user user0_
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?
Hibernate: select roles0_.user_id as user_id1_2_0_, roles0_.roles_id as roles_id2_2_0_, role1_.id as id1_0_1_, role1_.name as name2_0_1_ from user_roles roles0_ inner join role role1_ on roles0_.roles_id=role1_.id where roles0_.user_id=?

If you see multiple entries for SQL for a given select query, then there are high chances that its due to N+1 problem.

N+1 Resolution

Hibernate & spring Data provide mechanism to solve the N+1 ORM issue.

at SQL level, what ORM needs to achieve to avoid N+1 is to fire a query that joins the two tables and get the combined results in single query.

Fetch Join SQL that retrieves everything (user and roles) in Single Query
Hibernate: select user0_.id as id1_1_0_, role2_.id as id1_0_1_, user0_.name as name2_1_0_, role2_.name as name2_0_1_, roles1_.user_id as user_id1_2_0__, roles1_.roles_id as roles_id2_2_0__ from user user0_ left outer join user_roles roles1_ on user0_.id=roles1_.user_id left outer join role role2_ on roles1_.roles_id=role2_.id
OR Plain SQL
select user0_.id, role2_.id, user0_.name, role2_.name, roles1_.user_id, roles1_.roles_id from user user0_ left outer join user_roles roles1_ on user0_.id=roles1_.user_id left outer join role role2_ on roles1_.roles_id=role2_.id

Spring Data JPA Approach

If we are using Spring Data JPA, then we have two options to achieve this - using EntityGraph or using select query with fetch join.

public interface UserRepository extends CrudRepository<User, Long> {

    List<User> findAllByRolesIn(List<Role> roles);             (1)

    @Query("SELECT p FROM User p LEFT JOIN FETCH p.roles")  (2)
    List<User> findWithoutNPlusOne();

    @EntityGraph(attributePaths = {"roles"})                      (3)
    List<User> findAll();
}
1 N+1 queries are issued at database level.
2 using left join fetch, we resolve the N+1 problem
3 using attributePaths, Spring Data JPA avoids N+1 problem

Hibernate Aproach

if its pure Hibernate, then the following solution will work.

HQL Query
"from User u join fetch u.roles roles roles"
Hibernate Criteria API
Criteria criteria = session.createCriteria(User.class);
criteria.setFetchMode("roles", FetchMode.EAGER);

under the hood, all these approaches work similar and they issue a similar database query with left join fetch

That’s all!


Top articles in this category:
  1. How will you handle unresolved circular dependency in spring dependency injection?
  2. Prevent Lost Updates in Database Transaction using Spring Hibernate
  3. How to implement thread safe table backed global counter in spring hibernate?
  4. Custom banner in spring boot
  5. What are inheritance mapping strategies in JPA
  6. How will you test web layer in Spring Boot using WebMvcTest annotation?
  7. Setting a Random Port in Spring Boot Application at startup



Find more on this topic:
Spring Framework image
Spring Framework

Spring Framework - MVC, Dependency Injection, Spring Hibernate, Spring Data JPA, Spring Boot and Spring Cloud for Microservices Architecture.

Last updated 1 week ago


Recommended books for interview preparation:

This website uses cookies to ensure you get the best experience on our website. more info