Hibernate Pitfalls: Avoiding The N+1 Selects Problem

Last update, January 17, 2005

Overview

Problem

In a previous pitfall (Avoiding The N+1 Selects Problem) we showed how the N+1 selects problem could inadvertently arise when not using lazy initialization. A more subtle cause for the N+1 selects problem is from not using database joins correctly to return the data our application uses. If an application does use the correct fetching strategy to load the data it needs, it may end up making more round trips to the database than necessary.

We illustrate by returning to the example of the class Contact, which has a one-to-many relationship with Manufacturer (that is, there is one Contact for many Manufacturers) . Since the Contract is uses lazy initialization in its hbm.xml file

<class name="example.domain.Contact" table="CONTACT" lazy = "true">
...
</class>

it will not automatically be lazily initialized as part of the execution of the HQL "from Manufacturer manufacturer". This query will not load the data for Contact, but will instead load a proxy to the real data.

The problem is when you run this query but decide in writing your application that you do want to retrieve all the contacts for the returned set of manufacturers

Query query = getSupport().getSession().createQuery("from Manufacturer manufacturer");
List list = query.list();
for (Iterator iter = list.iterator(); iter.hasNext();)
{
  Manufacturer manufacturer = (Manufacturer) iter.next();
  System.out.println(manufacturer.getContact().getName());
}

Since the initial query "from Manufacturer manufacturer" does not initialize the Contact instances, an additional separate query is needed to do so for each Contact loaded. Again, you get the N+1 selects problem.

Solution

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 the example.

More Hibernate Tips and Pitfalls