Spring JPA N+1

calendar Jun 11, 2019 · 3 min read · spring jpa h2  ·
Share on: linkedin copy

Overview

The N+1 query problem occurs when the framework executes N additional SQL statements to load lazily fetched objects, this happens when you use FetchType.LAZY for your entity associations.

Github: https://github.com/gitorko/project66

N+1 problem

By default, fetch is FetchType.LAZY in hibernate, changing to FetchType.EAGER won't guarantee a fix for N+1 issue either, eager fetch will fetch more data than needed. The @ManyToOne and @OneToOne associations use FetchType.EAGER by default.

Different ways to solve N+1 Problem

  1. FetchType.EAGER - Fetches more data that you need
  2. Join fetch - joins the two tables & initializes the objects. It works with both JOIN and LEFT JOIN statements.
  3. JPA EntityGraphs - allows partial or specified fetching of objects, specify a fetch plan by EntityGraphs in order to determine which fields or properties should be fetched together
  4. Batching - @BatchSize(size = 10)
  5. Subselect - @Fetch(FetchMode.SUBSELECT)
  6. Spring Data JDBC supports Single Query Loading

Fetch Graph vs Load Graph

There are two types of EntityGraphs, Fetch and Load, they define if the entities not specified by attributeNodes of EntityGraphs should be fetched lazily or eagerly.

  1. FETCH - default graph type. When it is selected, the attributes that are specified by attribute nodes of the entity graph are treated as FetchType.EAGER and attributes that are not specified are treated as FetchType.LAZY
  2. LOAD - attributes that are specified by attribute nodes of the entity graph are treated as FetchType.EAGER

More complex and reusable graphs we can describe a fetch plan with its paths and boundaries with @NamedEntityGraph annotation in the entity class.

Code

N+1 query that executes N times

1select c1_0.post1_id,c1_1.id,c1_1.comment from "post1_comments" c1_0 join "post-comment1" c1_1 on c1_1.id=c1_0."comments_id" where c1_0.post1_id=?

Sql with left join

1select p1_0.id,c1_0.post1_id,c1_1.id,c1_1.comment,p1_0.title from post1 p1_0 left join "post1_comments" c1_0 on p1_0.id=c1_0.post1_id left join "post-comment1" c1_1 on c1_1.id=c1_0."comments_id"

Sql with join

1select p1_0.id,c1_0.post1_id,c1_1.id,c1_1.comment,p1_0.title from post1 p1_0 join "post1_comments" c1_0 on p1_0.id=c1_0.post1_id join "post-comment1" c1_1 on c1_1.id=c1_0."comments_id"

Entity Graph

1 select p1_0.id,c1_0.post4_id,c1_1.id,c1_1.comment,p1_0.title from post4 p1_0 left join "post4_comments" c1_0 on p1_0.id=c1_0.post4_id left join "post-comment4" c1_1 on c1_1.id=c1_0."comments_id"

Batch

1select c1_0.post2_id,c1_1.id,c1_1.comment from "post2_comments" c1_0 join "post-comment2" c1_1 on c1_1.id=c1_0."comments_id" where c1_0.post2_id in (?,?,?,?,?,?,?,?,?,?)

Sub-Select

1select c1_0.post3_id,c1_1.id,c1_1.comment from "post3_comments" c1_0 join "post-comment3" c1_1 on c1_1.id=c1_0."comments_id" where c1_0.post3_id in (select p1_0.id from post3 p1_0)

 1package com.demo.project66;
 2
 3import com.demo.project66.service.PostService;
 4import lombok.extern.slf4j.Slf4j;
 5import org.springframework.boot.CommandLineRunner;
 6import org.springframework.boot.SpringApplication;
 7import org.springframework.boot.autoconfigure.SpringBootApplication;
 8import org.springframework.context.annotation.Bean;
 9
10@SpringBootApplication
11@Slf4j
12public class Main {
13
14    public static void main(String[] args) {
15        SpringApplication.run(Main.class, args);
16    }
17
18    @Bean
19    public CommandLineRunner commandLineRunner(PostService postService) {
20        return (args) -> {
21            log.info("Seeding data!");
22            postService.seedData();
23        };
24    }
25
26}

 1spring:
 2  jpa:
 3    hibernate:
 4      ddl-auto: create-drop
 5    show-sql: true
 6logging:
 7  level:
 8    org.springframework.orm.jpa: DEBUG
 9    org.hibernate.event.internal: TRACE
10    org.hibernate.internal.SessionImpl: TRACE

Postman

Import the postman collection to postman

Postman Collection

Setup

 1# Project 66
 2
 3Spring Data JPA N+1
 4
 5[https://gitorko.github.io/spring-jpa-n-plus-1/](https://gitorko.github.io/spring-jpa-n-plus-1/)
 6
 7### Version
 8
 9Check version
10
11```bash
12$java --version
13openjdk 21.0.3 2024-04-16 LTS
14```
15
16### Dev
17
18To run the code.
19
20```bash
21./gradlew clean build
22./gradlew bootRun
23```

References

https://vladmihalcea.com/n-plus-1-query-problem

comments powered by Disqus