Experiments

N_plus_1_query_problem_solution

3 min read

N+1 Query Problem Solution

classDiagram
direction TB
Post --> "Many (*)" PostComment
  • If you want to fetch Post and its comments then following queries will fire
    • 1 Query to fetch list of posts
    • N Queries to fetch post comments for each post, where N = # of Posts
  • Use FetchType.LAZY, by default @OneToMany() and @ManyToOne() are FetchType.EAGER
@Entity(name = "Post")
@Table(name = "post")
public class Post {
    @Id
    private Long id;
    private String title;
}
 
@Entity(name = "PostComment")
@Table(name = "post_comment")
public class PostComment {
    @Id
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY) // Here we are using lazy fetch type
    private Post post;
    private String review;
}
  • Even if you do Lazy Fetch Type, you will still run into issues if you try to access post_comments
  • Hence neither Lazy Fetch type nor Eager Fetch Type solves the N+1 problem completely
  • Best way is to create separate methods, SQL and JPQL queries to tailor them for each case.

Debugging

spring.jpa:
  show-sql: true
  properties.hibernate.format: true

Solutions

classDiagram
direction TB
Department --> "Many (*)"Employee

class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
 
    private String name;
 
    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "dept_id")
    List<Employee> listOfEmployees;
}

Spring Data approach

  • using left join fetch
    • FETCH is JPA-specific statement
    • FETCH works on both JOIN and LEFT JOIN statements
    • instructs persistence provider to do both:
      • join the two database tables contained in the query
      • initialize the association on the returned entity
interface DepartmentRepository extends JpaRepository<Department, Long> {
    @Query("SELECT p FROM Department p LEFT JOIN FETCH p.listOfEmployee)
    List<Department> findWithoutNPlusOne();
}
 
/*
 * Internally it will hit single SQL query like this
 * 
 * select dept.id, dept.name,
 *        emp.id, emp.name, emp.dept_id
 * from
 *     dept_table dept
 * left join
 *     employee_table emp
 *     on dept.id=emp.dept_id
 * 
 */
  • using attributePaths
    • aka EntityGraphs
    • allow partial or specific fetching of objects
    • When an entity (ex. Department) has references to other entities (ex. list of Employee) we can specify a fetch plan by EntityGraphs in order to determine which fields or properties should be fetched together.
interface DepartmentRepository extends JpaRepository<Department, Long> {
    @EntityGraph(attributesPath = {"listOfEmployees"})
    List<Department> findAll();
}
 
/*
 * The findAll() is a customized one and has entity graph defined 
 * 
 * Internally it will hit single SQL query similar to join fetch:
 * 
 * select dept.id, dept.name,
 *        emp.id, emp.name, emp.dept_id
 * from
 *     dept_table dept
 * left join
 *     employee_table emp
 *     on dept.id=emp.dept_id
 */

Hibernate approach

  • using HQL Query - “from Department d join fetch d.listOfEmployees Employee”
  • using Criteria:
Criteria criteria = session.createCriteria(Department.class)
criteria.setFetchMode("listOfEmployees", FetchMode.EAGER)

Backlinks