What Is the Solution for the N+1 Issue in JPA and Hibernate

What is the solution for the N+1 issue in JPA and Hibernate?

Suppose we have a class Manufacturer with a many-to-one relationship with Contact.

We solve this problem by making sure that the initial query fetches all the data needed to load the objects we need in their appropriately initialized state. One way of doing this is using an HQL fetch join. We use the HQL

"from Manufacturer manufacturer join fetch manufacturer.contact contact"

with the fetch statement. This results in an inner join:

select MANUFACTURER.id from manufacturer and contact ... from 
MANUFACTURER inner join CONTACT on MANUFACTURER.CONTACT_ID=CONTACT.id

Using a Criteria query we can get the same result from

Criteria criteria = session.createCriteria(Manufacturer.class);
criteria.setFetchMode("contact", FetchMode.EAGER);

which creates the SQL :

select MANUFACTURER.id from MANUFACTURER left outer join CONTACT on 
MANUFACTURER.CONTACT_ID=CONTACT.id where 1=1

in both cases, our query returns a list of Manufacturer objects with the contact initialized. Only one query needs to be run to return all the contact and manufacturer information required

for further information here is a link to the problem and the solution.

What is the N+1 selects problem in ORM (Object-Relational Mapping)?

Let's say you have a collection of Car objects (database rows), and each Car has a collection of Wheel objects (also rows). In other words, Car → Wheel is a 1-to-many relationship.

Now, let's say you need to iterate through all the cars, and for each one, print out a list of the wheels. The naive O/R implementation would do the following:

SELECT * FROM Cars;

And then for each Car:

SELECT * FROM Wheel WHERE CarId = ?

In other words, you have one select for the Cars, and then N additional selects, where N is the total number of cars.

Alternatively, one could get all wheels and perform the lookups in memory:

SELECT * FROM Wheel;

This reduces the number of round-trips to the database from N+1 to 2.
Most ORM tools give you several ways to prevent N+1 selects.

Reference: Java Persistence with Hibernate, chapter 13.

Related Topics