@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
}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
| 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_.idOR 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:
- Prevent Lost Updates in Database Transaction using Spring Hibernate
- What are inheritance mapping strategies in JPA
- Table backed global counter in spring hibernate
- Redis rate limiter in Spring Boot
- Disable SSL validation in Spring RestTemplate
- Custom TTL for Spring data Redis Cache
- Spring Data ElasticSearch with Basic Auth
Recommended books for interview preparation:
Book you may be interested in..
Book you may be interested in..
