Spring JPA N+1
Overview
The N+1 query problem occurs when the framework executes N additional SQL statements to fetch the same data that could have been retrieved when executing the primary SQL query.
Github: https://github.com/gitorko/project66
N+1 problem
N+1 problem is a performance issue in ORM that fires multiple select queries By default fetch is FetchType.LAZY in hibernate, changing to FetchType.EAGER wont guarantee a fix either also eager fetch will fetch more data than needed. The @ManyToOne and @OneToOne associations use FetchType.EAGER by default.
Code
1package com.demo.project66;
2
3import java.util.Arrays;
4import java.util.List;
5import javax.persistence.CascadeType;
6import javax.persistence.Entity;
7import javax.persistence.GeneratedValue;
8import javax.persistence.GenerationType;
9import javax.persistence.Id;
10import javax.persistence.OneToMany;
11import javax.transaction.Transactional;
12
13import lombok.AllArgsConstructor;
14import lombok.Builder;
15import lombok.Data;
16import lombok.NoArgsConstructor;
17import lombok.RequiredArgsConstructor;
18import org.springframework.beans.factory.annotation.Autowired;
19import org.springframework.boot.CommandLineRunner;
20import org.springframework.boot.SpringApplication;
21import org.springframework.boot.autoconfigure.SpringBootApplication;
22import org.springframework.context.ApplicationContext;
23import org.springframework.context.annotation.Bean;
24import org.springframework.data.jpa.repository.EntityGraph;
25import org.springframework.data.jpa.repository.Query;
26import org.springframework.data.repository.CrudRepository;
27import org.springframework.stereotype.Component;
28
29@SpringBootApplication
30public class Main {
31
32 public static void main(String[] args) {
33 SpringApplication.run(Main.class, args);
34 }
35
36 @Bean
37 public CommandLineRunner commandLineRunner(MyService myService) {
38 return args -> {
39 myService.seedData();
40 myService.getData();
41 };
42 }
43
44}
45
46@Entity
47@Data
48@Builder
49@AllArgsConstructor
50@NoArgsConstructor
51class Post {
52 @Id
53 @GeneratedValue(strategy = GenerationType.AUTO)
54 private Long id;
55 private String title;
56 @OneToMany(cascade = { CascadeType.ALL })
57 private List<PostComment> comments;
58}
59
60@Entity
61@Data
62@Builder
63@AllArgsConstructor
64@NoArgsConstructor
65class PostComment {
66 @Id
67 @GeneratedValue(strategy = GenerationType.AUTO)
68 private Long id;
69 private String comment;
70}
71
72interface PostRepository extends CrudRepository<Post, Long> {
73 @Query("SELECT p FROM Post p LEFT JOIN FETCH p.comments")
74 List<Post> findAllFixed();
75
76 @EntityGraph(attributePaths = {"comments"})
77 List<Post> findAll();
78}
79
80@Component
81@RequiredArgsConstructor
82class MyService {
83
84 PostRepository postRepository;
85
86 @Transactional
87 public void getData() {
88 Iterable<Post> all = postRepository.findAll();
89 //Iterable<Post> all = postRepository.findAllFixed();
90 for (Post post : all) {
91 System.out.println(post.getTitle());
92 List<PostComment> comments = post.getComments();
93 for (PostComment comment : comments) {
94 System.out.println(comment);
95 }
96 }
97 }
98
99 public void seedData() {
100 postRepository.deleteAll();
101 for (int i = 1; i <= 5; i++) {
102 List<PostComment> comments = Arrays.asList(
103 PostComment.builder().comment("Comment 1 for " + i).build(),
104 PostComment.builder().comment("Comment 2 for " + i).build(),
105 PostComment.builder().comment("Comment 3 for " + i).build()
106 );
107 postRepository.save(Post.builder().title("My Post " + i).comments(comments).build());
108 }
109
110 }
111}
1spring:
2 main:
3 web-application-type: none
4 jpa:
5 hibernate:
6 ddl-auto=none
7 show-sql: trueTesting
Run the project
1./gradlew bootRun
You will see a sql query being fired to fetch each post comment object.
1Hibernate: select post0_.id as id1_0_, post0_.title as title2_0_ from post post0_
2My Post 1
3Hibernate: select comments0_.post_id as post_id1_1_0_, comments0_.comments_id as comments2_1_0_, postcommen1_.id as id1_2_1_, postcommen1_.comment as comment2_2_1_ from post_comments comments0_ inner join post_comment postcommen1_ on comments0_.comments_id=postcommen1_.id where comments0_.post_id=?
4PostComment(id=2, comment=Comment 1 for 1)
5PostComment(id=3, comment=Comment 2 for 1)
6PostComment(id=4, comment=Comment 3 for 1)
7My Post 2
8Hibernate: select comments0_.post_id as post_id1_1_0_, comments0_.comments_id as comments2_1_0_, postcommen1_.id as id1_2_1_, postcommen1_.comment as comment2_2_1_ from post_comments comments0_ inner join post_comment postcommen1_ on comments0_.comments_id=postcommen1_.id where comments0_.post_id=?
9PostComment(id=6, comment=Comment 1 for 2)
10PostComment(id=7, comment=Comment 2 for 2)
11PostComment(id=8, comment=Comment 3 for 2)
12My Post 3
13Hibernate: select comments0_.post_id as post_id1_1_0_, comments0_.comments_id as comments2_1_0_, postcommen1_.id as id1_2_1_, postcommen1_.comment as comment2_2_1_ from post_comments comments0_ inner join post_comment postcommen1_ on comments0_.comments_id=postcommen1_.id where comments0_.post_id=?
14PostComment(id=10, comment=Comment 1 for 3)
15PostComment(id=11, comment=Comment 2 for 3)
16PostComment(id=12, comment=Comment 3 for 3)
17My Post 4
18Hibernate: select comments0_.post_id as post_id1_1_0_, comments0_.comments_id as comments2_1_0_, postcommen1_.id as id1_2_1_, postcommen1_.comment as comment2_2_1_ from post_comments comments0_ inner join post_comment postcommen1_ on comments0_.comments_id=postcommen1_.id where comments0_.post_id=?
19PostComment(id=14, comment=Comment 1 for 4)
20PostComment(id=15, comment=Comment 2 for 4)
21PostComment(id=16, comment=Comment 3 for 4)
22My Post 5
23Hibernate: select comments0_.post_id as post_id1_1_0_, comments0_.comments_id as comments2_1_0_, postcommen1_.id as id1_2_1_, postcommen1_.comment as comment2_2_1_ from post_comments comments0_ inner join post_comment postcommen1_ on comments0_.comments_id=postcommen1_.id where comments0_.post_id=?
24PostComment(id=18, comment=Comment 1 for 5)
25PostComment(id=19, comment=Comment 2 for 5)
26PostComment(id=20, comment=Comment 3 for 5)
Now use the 'findAllFixed' method you will see that only 1 query is fired. You can also use the @EntityGraph function to achieve the same result.
1Hibernate: select post0_.id as id1_0_0_, postcommen2_.id as id1_2_1_, post0_.title as title2_0_0_, postcommen2_.comment as comment2_2_1_, comments1_.post_id as post_id1_1_0__, comments1_.comments_id as comments2_1_0__ from post post0_ left outer join post_comments comments1_ on post0_.id=comments1_.post_id left outer join post_comment postcommen2_ on comments1_.comments_id=postcommen2_.id
2My Post 1
3PostComment(id=2, comment=Comment 1 for 1)
4PostComment(id=3, comment=Comment 2 for 1)
5PostComment(id=4, comment=Comment 3 for 1)
6My Post 1
7PostComment(id=2, comment=Comment 1 for 1)
8PostComment(id=3, comment=Comment 2 for 1)
9PostComment(id=4, comment=Comment 3 for 1)
10My Post 1
11PostComment(id=2, comment=Comment 1 for 1)
12PostComment(id=3, comment=Comment 2 for 1)
13PostComment(id=4, comment=Comment 3 for 1)
14My Post 2
15PostComment(id=6, comment=Comment 1 for 2)
16PostComment(id=7, comment=Comment 2 for 2)
17PostComment(id=8, comment=Comment 3 for 2)
18My Post 2
19PostComment(id=6, comment=Comment 1 for 2)
20PostComment(id=7, comment=Comment 2 for 2)
21PostComment(id=8, comment=Comment 3 for 2)
22My Post 2
23PostComment(id=6, comment=Comment 1 for 2)
24PostComment(id=7, comment=Comment 2 for 2)
25PostComment(id=8, comment=Comment 3 for 2)
26My Post 3
27PostComment(id=10, comment=Comment 1 for 3)
28PostComment(id=11, comment=Comment 2 for 3)
29PostComment(id=12, comment=Comment 3 for 3)
30My Post 3
31PostComment(id=10, comment=Comment 1 for 3)
32PostComment(id=11, comment=Comment 2 for 3)
33PostComment(id=12, comment=Comment 3 for 3)
34My Post 3
35PostComment(id=10, comment=Comment 1 for 3)
36PostComment(id=11, comment=Comment 2 for 3)
37PostComment(id=12, comment=Comment 3 for 3)
38My Post 4
39PostComment(id=14, comment=Comment 1 for 4)
40PostComment(id=15, comment=Comment 2 for 4)
41PostComment(id=16, comment=Comment 3 for 4)
42My Post 4
43PostComment(id=14, comment=Comment 1 for 4)
44PostComment(id=15, comment=Comment 2 for 4)
45PostComment(id=16, comment=Comment 3 for 4)
46My Post 4
47PostComment(id=14, comment=Comment 1 for 4)
48PostComment(id=15, comment=Comment 2 for 4)
49PostComment(id=16, comment=Comment 3 for 4)
50My Post 5
51PostComment(id=18, comment=Comment 1 for 5)
52PostComment(id=19, comment=Comment 2 for 5)
53PostComment(id=20, comment=Comment 3 for 5)
54My Post 5
55PostComment(id=18, comment=Comment 1 for 5)
56PostComment(id=19, comment=Comment 2 for 5)
57PostComment(id=20, comment=Comment 3 for 5)
58My Post 5
59PostComment(id=18, comment=Comment 1 for 5)
60PostComment(id=19, comment=Comment 2 for 5)
61PostComment(id=20, comment=Comment 3 for 5)
References
comments powered by Disqus