N+1 Problem

1. Overview

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 a table) in a database for a single select query at the application layer.

2. Problem

To understand N+1 problem, let's consider a scenario. Let’s say we have a collection of User objects mapped to t_users table in a 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 relationships with Role.

2.1 A user can have many roles.

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

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

    @ManyToMany(fetch = FetchType.LAZY)                    
    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
 }

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

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.

2.2 How to identify

2.2.1 Enabling SQL logging in application.yml

spring:
    jpa:
       show-sql: true  # Enables SQL logging in trace           
       database-platform: org.hibernate.dialect.H2Dialect
       hibernate:
         ddl-auto: create
         use-new-id-generator-mappings: true
       properties:
          hibernate:
            type: trace    # We have to enable this too in order to show sql queries in logs

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

# 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=?

3. Resolution

Hibernate & Spring Data provide a 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 a single query.

3.1 Use the HQL with the fetch statement.

from Manufacturer manufacturer join fetch manufacturer.contact contact"

This results in an inner join:

select MANUFACTURER.id from manufacturer and contact ... from 
MANUFACTURER inner join CONTACT on MANUFACTURER.CONTACT_ID=CONTACT.id

3.2 Using a Criteria query

Criteria criteria = session.createCriteria(Manufacturer.class);
criteria.setFetchMode("contact", FetchMode.EAGER);

which creates the SQL :

select MANUFACTURER.id from MANUFACTURER left outer join CONTACT on 
MANUFACTURER.CONTACT_ID=CONTACT.id where 1=1

in both cases, our query returns a list of Manufacturer objects with the contact initialized. Only one query needs to be run to return all the contact and manufacturer information required

3.3 Using batch fetching

Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways we can configure batch fetching:

3.3.1 Class level

@Entity
@BatchSize(size=25)
@Table(...
public class MyEntity implements java.io.Serializable {...

3.3.2 Collection level

@OneToMany(fetch = FetchType.LAZY...)
@BatchSize(size=25)
public Set<MyEntity> getMyColl() 

Lazy loading and batch fetching together represent optimization, which:

• Does not require any explicit fetching in our queries
• Will be applied on any amount of references which are (lazily) touched after the root entity is loaded (while explicit fetching effects only these named in query)
• Will solve issue 1 + N with collections (because only one collection could be fetched with root query) without need to farther processing To get DISTINCT root values

3.4 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 a fetch join.

public interface UserRepository extends CrudRepository<User, Long> {

    List<User> findAllByRolesIn(List<Role> roles);   # N+1 queries are issued at database level.          

    @Query("SELECT p FROM User p LEFT JOIN FETCH p.roles")  # Solution 1:using left join fetch, we resolve the N+1 problem
    List<User> findWithoutNPlusOne();

    @EntityGraph(attributePaths = {"roles"}) # Solution 2:using attributePaths, Spring Data JPA avoids N+1 problem
            
    List<User> findAll();
}

 

4. Reference

https://www.sitepoint.com/silver-bullet-n1-problem/

https://www.javacodemonk.com/what-is-n-1-problem-in-hibernate-how-will-you-identify-and-solve-it-894097b9

https://stackoverflow.com/questions/32453989/what-is-the-solution-for-the-n1-issue-in-jpa-and-hibernate

https://thecodingmachine.io/solving-n-plus-1-problem-in-orms

https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping