What is N+1 SELECT problem in Hibernate? How to identify and resolve it?
The N+1 query problem happens when the data access framework executed N additional SQL statements to fetch the same data that could have been retrieved when executing the primary SQL query.
This N+1 query problem is not specific to JPA or Hibernate, it can be triggered using any data access technology, even with plain SQL.
It has a huge performance impact when the value of N is large. And, unlike the slow query log that can help you find slow running queries, the N+1 issue won’t be spot because each individual additional query runs sufficiently fast to not trigger the slow query log.
e.g: Let's say you have a Person and Bank_Accounts table, each person can have multiple bank accounts (assume a bank account can belong to only one person). This is typical example of OneToMany relationships. Now let's say we need to fetch list of bank accounts and their owners. We can first fetch list of bank accounts and then for each bank account fetch the owners. This will fire N+1 query (N is the number of bank accounts). Instead of this we can simply use a join statement and fetch the details in 1 query.
When using JPA and Hibernate, there are several ways you can trigger the N+1 query issue, so it’s very important to know how you can avoid these situations.
Who to resolve it?
e.g:
1). 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. FYI, @ManyToOne and @OneToOne associations use FetchType.EAGER by default.
If you didn’t need the Bank and Person association at all, you are out of luck when using FetchType.EAGER because there is no way to avoid fetching it. That’s why it’s better to use FetchType.LAZY by default. Even if you switch to using FetchType.LAZY explicitly for all associations, you can still bump into the N+1 issue.
List < BankPerson > comments = entityManager
.createQuery("select bp
from BankPerson bp
", BankPerson.class)
.getResultList();
If you want to use Bank and Person association, then you can use JOIN FETCH to avoid the N+1 query problem:
List < BankPerson > comments = entityManager
.createQuery("select bp
from BankPerson bp
JOIN FETCH bp.person p
", BankPerson.class)
.getResultList();
2). The N+1 query issue can also be triggered when using the second-level cache for fetching collections or query results. The Query Cache only stored the entity identifiers of the matching BankPerson entities. So, if the BankPerson entities are not cached as well, they will be fetched from the database. Hence, you will get N additional SQL statements.
-K Himaanshu Shuklaa..The N+1 query problem happens when the data access framework executed N additional SQL statements to fetch the same data that could have been retrieved when executing the primary SQL query.
This N+1 query problem is not specific to JPA or Hibernate, it can be triggered using any data access technology, even with plain SQL.
It has a huge performance impact when the value of N is large. And, unlike the slow query log that can help you find slow running queries, the N+1 issue won’t be spot because each individual additional query runs sufficiently fast to not trigger the slow query log.
e.g: Let's say you have a Person and Bank_Accounts table, each person can have multiple bank accounts (assume a bank account can belong to only one person). This is typical example of OneToMany relationships. Now let's say we need to fetch list of bank accounts and their owners. We can first fetch list of bank accounts and then for each bank account fetch the owners. This will fire N+1 query (N is the number of bank accounts). Instead of this we can simply use a join statement and fetch the details in 1 query.
When using JPA and Hibernate, there are several ways you can trigger the N+1 query issue, so it’s very important to know how you can avoid these situations.
Who to resolve it?
e.g:
1). 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. FYI, @ManyToOne and @OneToOne associations use FetchType.EAGER by default.
If you didn’t need the Bank and Person association at all, you are out of luck when using FetchType.EAGER because there is no way to avoid fetching it. That’s why it’s better to use FetchType.LAZY by default. Even if you switch to using FetchType.LAZY explicitly for all associations, you can still bump into the N+1 issue.
List < BankPerson > comments = entityManager
.createQuery("select bp
from BankPerson bp
", BankPerson.class)
.getResultList();
If you want to use Bank and Person association, then you can use JOIN FETCH to avoid the N+1 query problem:
List < BankPerson > comments = entityManager
.createQuery("select bp
from BankPerson bp
JOIN FETCH bp.person p
", BankPerson.class)
.getResultList();
2). The N+1 query issue can also be triggered when using the second-level cache for fetching collections or query results. The Query Cache only stored the entity identifiers of the matching BankPerson entities. So, if the BankPerson entities are not cached as well, they will be fetched from the database. Hence, you will get N additional SQL statements.
No comments:
Post a Comment