N+1 problem in Hibernate & Spring Data JPA

Upasana | October 03, 2020 | 4 min read | 3,088 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 & Spring Data JPA provides multiple ways to catch and address this performance 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 Model
@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. Roles are loaded Lazily.

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 -

UserRepository with findAllBy method
public interface UserRepository extends CrudRepository<User, Long> {

    List<User> findAllBy();
}

Equivalent SQL queries executed by ORM will be:

First Get All User (1)

Select * from t_users;

Then get roles for each user executed N times (where N is number of users)

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.MySQL8Dialect
       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 JPA 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

1. 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> findAllBy();             (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

2. Hibernate Approach

If its pure Hibernate, then the following solutions will work.

Using HQL Query
"from User u join fetch u.roles roles roles"
Using 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. Prevent Lost Updates in Database Transaction using Spring Hibernate
  2. What are inheritance mapping strategies in JPA
  3. Table backed global counter in spring hibernate
  4. Redis rate limiter in Spring Boot
  5. Disable SSL validation in Spring RestTemplate
  6. Custom TTL for Spring data Redis Cache
  7. Spring Data ElasticSearch with Basic Auth

Recommended books for interview preparation:

Find more on this topic: