Spring JPA generating many queries : n+1 query problem

If you are using JPA and you haven’t seen this problem then you haven’t fully understood how jpa works internally yet. To understand and learn how JPA works internally as a first step you need to add the below property in application.properties of spring boot application

spring.jpa.show-sql: true

Lets try to create this problem. Consider a below database design with one to many relationship between tweet and comments

Database design. Tweet -> comments (One to many realtionship)

Please find below the entity class of the ablove design so that we all are in same page

Tweet Entity

Now Lets insert some sample data into tweets table and comments table. Tweet table has 4 tweets and each tweet has some comments added to it

sample date into tweet and comments table

Nows lets try to pull all the tweets that starts with D and comments associated with it. Just pause and think how the query will be formed by jpa and how much query does it creates to fetch the tweets with comments?

Run the above jpa query and print the tweet and comments associated with it. Boom you will see 5 queries executed by JPA. one to retrieve tweet and other 4 to retrieve comments associated with it.

Queries run by JPA to fetch tweet and comments

Okay I am hearning what you are thinking -> why JPA is doing this to me right? Instead of runing one query to retrieve 4(n) values from database it is taking 5(n+1) queries which explains why n+1 name.

Reasons for n+1 query problem

FetchType.LAZY

Since comment is fetched lazily, a second SQL query will be executed to get the comments associated for each tweet. you can catch the same in query log that comments query uses where condition tweet_id = ?. This implies it is forming comments query for each tweets we retrieved

FetchType.EAGER

Okay hearing again use eager fetching why to use this. Try that it will generate 5 queeies in that case well because tweet values has to be fetched before comments by JPA to pull comments values from database. Using FetchType.EAGER either implicitly or explicitly for your JPA associations is a bad idea because you are going to fetch way more data that you need. More, the FetchType.EAGER strategy is also prone to N+1 query issues.

How to solve n+1 query problem

Entity Graph comes to the rescue. JPA 2.1 introduces it and provies a better way to handle this performance issue. Now we have to tell JPA to fetch comments record also while retrieving Tweets record. That is we have to join fetch the records from both tweets table and comments table.

Lets do the below change in repository class. Above the JPA query specify the fields you have to join fetch with tweets and wait for tha magic to happen

Magic Happened? you will see that JPA fetched Tweets and associated comments with it using one query and the records got fetched in less than second. Please find below the executed query

Query after adding field in entity graph annotation

Performace Boost table

Time taken before and after fixing n+1 query problem

Performace impact of before fixing n+1 query vs after fixing it

Conclusion

JPA fetches all the fields that are specified in the attribute path array of entity graph. This is considered to be a good performance.

The JPA documentation recommends using the FetchType.LAZY strategy whenever possible, and the Entity Graph when we need to load an association.

Please find my Gihub repo for the code here.