Solving N+1 Select Problem In JPA Using Entity Graph

Photo by Daria Nepriakhina on Unsplash

I was watching a video from SpringOne 2021 on a topic Building Fast and Scalable Persistence Layers with Spring Data JPA by Thorben Janssen and I learnt so many things from there. Today I will share one of my learnings with you which helped me on my ongoing project where I optimized a JPA query and its effects were phenomenal.

The query was being used in one of the REST based GET calls and taking the average time of 20s to load all the data in a UI table. Since the response was hierarchical with parent-child relationship and consisting of almost 1400 rows. After applying the learning from this video average response time reduces to almost 1s. In the coming paragraph I will discuss how I achieved this.

During that talk on that video, I came to know about the N+1 select problem in spring data JPA. In this problem basically what happens is that when you load an entity and then try to fetch the to-many association with spring data JPA, it fires queries to load that association as it loads it lazily.

For more clear understanding see the code below:-

@Entity
public class Author {
@OneToMany(mappedBy = “author”)
 private Set<Book> books;
}

@Entity
public class Book {
@ManyToOne
 private Author author;
}
List<Author> authors=authorRepository.findAll(); — — — — — -1
for(Author author: authors){
 List<Book> books=author.getBooks(); — — — — — — — — — — 2
}

Can you tell me how many queries will be fired in this code if the authors are of size 10 and each author has 10 books?

The Answer here is 11. Since fetching of to-many associations are lazy by default, they won’t load any books until you use getBooks() on author instance. So here one query on line number 1 and each time getBooks() called on line number two, so in total 11.

Coming back to my problem,I have two to-many associations which I need to complete my task in that rest api call. When I dug deeper by adding these spring boot properties in application.properties file.

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.generate_statistics=true

then I came to know that in total there were almost 4400 queries being called in that single rest api call. I had created one query method to load all the data and I was iterating it to create a response and there I was calling my both associations through a many-2–one relationship and each time I called my associations queries were fired to load data. I had almost 1400 rows and each row had two associations.

Here I am mimicking my entity classes:-

@Entity
public class ABC {
 
 @ManyToOne()
 private XYZ xyz;
}
@Entity
public class XYZ {
@OneToMany(mappedBy = "xyz")
private Set<ABC> abcList;
//XYZ has parent-child relationship
@OneToMany(mappedBy = "parentXYZ")
private Set<XYZ> xyzList;
@ManyToOne()
@JoinColumn(name = "parent_xyz_id")
 private XYZ parentXYZ;
}

And the query I was using was like this:-

List<ABC> findAll();

findAll() was fetching 1400 rows using this method.

And one REST call was taking 20s on an average and most of this time was due to these 4400 queries happening due to association fetching while building the response.

So how do I fix it?

In the video, I learnt two ways to fix it, one was to use JOIN FETCH and other was to use EntityGraph annotation. I used EntityGraph to resolve my issue.

Using this annotation on the query method, you can specify associations which you want to fetch along with your main entity data in one query. So instead of N+1 queries, there will be only one query which loads main entity data and the associations which you mentioned in your EntityGraph annotation.

So if we go to the above authors-books example if put @EntityGraph(attributePaths=”books”) on findAll() method in its repository interface then those 11 queries will be reduced to 1. In my case it reduces to 1 from 4400 and REST response time from 20s to almost 1s.

My JPA query method looks like below after applying EntityGraph annotation.

@EntityGraph(attributePaths = {"xyz.xyzList", "xyz.abcList"})
 List<ABC> findAll();

Now it is fetching 1400 rows with all required associations data in one query.

Some points to note:-

1. Lazy loading is good when we don’t need association during our task.
2. Lazy loading will create a N+1 select problem if your task needs all association’s data as well.
3. EntityGraph is one way to fix this problem, this is not one size fits all solution.
4. You should try different ways to optimize your queries and use the one which is best.

Thanks for reading.

You can follow me on twitter and LinkedIn for much such blogs.