What is N+1 SELECT Problem in Hibernate? Strategies to avoid this?

In some cases while using Hibernate for object-relational mapping, you will encounter the N+ 1 select problem. That is, Hibernate will execute (n+1) queries to populate a list of records of size n, I mean 5 SELECT queries will be required to populate 4 records. This happens when each object contains reference of another object which needs to be populated. For example, suppose you have list of authors and you want to print out title of books written by each other. A naive ORM will first do a SELECT query to load all authors and then execute another N SELECT query to load book for each author. That's your N + 1 SELECT problem because it involves N+1 database roundtrips'. There are some mechanisms suggested by Hibernate that can be used to mitigate this risk e.g. EAGER fetching mode.

This is one of the interesting Hibernate interview question which you may encounter in Java web developer interview of 4 to 5 years experienced Hibernate developer. In this article, I'll give you an example of N+1 SELECT problem and tell you the strategy to avoid that. 

An example of N+1 problem in Hibernate

Let's assume that you're writing code that will display the title, publication date and price of programming books. Now, let's say you have a list of objects representing different authors i.e. instance of Author class, and each author has a collection of objects representing the books written by them e.g. list of books.

In short, one-to-many relationship exits between Author and Book tables, we are ignoring the case where a book can be written by multiple authors for simplicity.

Our Author class would look something like below:

public class Author{
private long author_id;
private Book[] books;
....
}

Now, Let's assume you want to print out title, price and publication details of all book. A naive ORM implementation would first SELECT all authors and then do N additional SELECT queries for getting book related information for each author, that's N+1 SELECTs in total.

-- Get all authors
SELECT * FROM Author;

-- For each Author, get Book details
SELECT * FROM Author WHERE author_id=?

As you see, the N+1 problem can happen if the first query populates the primary object and the second query populates all the child objects for each of the unique primary objects returned.

Solution:

One way to solve the N+1 SELECT problem in Hibernate is using Criterion query and using EAGER fetch mode. This will populate all the book object in the same time it loads the Author details, as shown below:

Criteria criteria = session.createCriteria(Author.class);
criteria.setFetchMode("books", FetchMode.EAGER);

This query will returns a list of Author objects with the books collection initialized. Only one query needs to be run to return all the Book and Author details.

Strategies to deal with N+1 SELECTS problem in Hibernate

Hibernate provides couple of strategies to solve the N+1 problem. Each one has their pros and cons and they are basically a compromise between how many SQL query to be executed instead of N+1 e.g. half, two SQL query or just one query. 

Here are the three common strategies to deal with N+1 SELECTs problem in Hibernate:

1) pre-fetching in batches

2) subselect fetching strategy

3) disabling lazy loading

1. Pre-fetching in batches

One of the most common solution of N+1 SELECTs problem is to change your global mapping metadata for collections and allows them to prefetch in batches e.g. if you have a Person with collection of credit cards, you can enable pre-fetching in batches as following:

set name = "creditcards"
inverse = "true"
batch-size ="10"
key column
one to many

This will reduce N+1 SELECTS problem to N/10+1 SELECTs problem because now you need N/10 query to load dependent object due to batch-size of 10. This solution loads the credit cards on demand, but when it load detail of one credit card, it does it also loads for 10 more credit cards, assuming that those will also be needed. 

This is a balanced approach between loading all dependent object when parent is loaded and loading dependent object one by one. If your application only access some and not all object of collection, this seems to be the right strategy to solve the N+1 selects problem.

2. Subselect fetching strategy

Another way to solve the N+1 selects problem in Hibernate is by using the subselect fetching strategy i.e. fetch="subselect". This strategy reduces the number of SELECT query from N+1 to just two. You can enable subselect fetching strategy for your collection as shown below:

In this strategy, Hibernate doesn't load the collection when Parent object is loaded but loads all of them when you hit the first uninitialized collection e.g. in our example, all credit card details will be loaded as soon as you need detail of one credit card.

So, this just required two queries, one to load the parent object and second to load all objects in collection but only when you hit the first uninitialized collection. This strategy solves the N+1 selects problem by reducing number of select queries from N+1 to just two.

3. Disabling Lazy Loading by switching to EAGER fetching

Third way to solve the N+1 selects problem is to effectively disabling lazy loading and switching to EAGER fetching strategy for collections. This will results in only a single SQL SELECT query to populate both Persistent object and its collections. 

You can enable eager fetching for collection in Hibernate as shown below:

fetch=join

Though this strategy just need one SQL query to populate both Parent object and it's collection, In practice its rarely used, because you rarely need a fully initialized collection all the time. This strategy will results in more memory consumption which is waste of resource if you don't use it. It also takes you closer to a more serious problem called Cartesian product problem, which loads too much data in one SQL query. 

So, these were couple of strategies to solve the N+1 SELECTS problem in Hibernate. You can choose from batch pre-fetching, subselect query and turning off lazy loading. Choose the strategy which best suits your need.

That's all about what is N+1 SELECT problem in Hibernate and how to solve that. You can read more about this problem and different strategies to solve this in the classic book of Hibernate, Java Persistence with Hibernate by Gavin King, one of the most recommended book to learn Hibernate for Java developers. You can also read Hibernate documentation to learn more about how Hibernate works so that you can make effective use of it. 

Other Hibernate Articles and Interview Questions you may like

• Difference between First and Second level cache in Hibernate? (answer)
• How to fix NullPointerException due to space in HQL query? (Solution)
• Difference between get() and load() method in Hibernate? (answer)
• Difference between Hibernate, JPA, and MyBatis (answer)
• 5 Spring and Hibernate Training Courses for Java developers (list)
• Difference between first and second level cache in Hibernate? (answer)
• 2 Books to Learn Hibernate for Beginners (books)
• Spring Boot + Hibernate Tutorials for Beginners (tutorial)
• 5 Books to Learn Spring Framework for Beginners (books)
• How to deal with LazyInitializationExcepiton in Hibernate (solution)
• Why Hibernate Entity class should not be final in Java? (answer)
• Difference between Transient, Persistent, and Detached object (answer)
• 10 Hibernate Questions from Java Interviews (list)
• How to fix Unknown Entity Exception in Hibernate (solution)
• My favorite Hibernate and JPA Courses for Beginners (courses)

Thanks for reading this article, if you like this article and the interview question then please share it with your friends and colleagues. If you have any questions or feedback then please drop a comment.