How to Identify and Resolve Hibernate N+1 SELECT's Problems

Let’s assume that you’re writing code that’d track the price of mobile phones. Now, let’s say you have a collection of objects representing different Mobile phone vendors (MobileVendor), and each vendor has a collection of objects representing the PhoneModels they offer.

To put it simple, there’s exists a one-to-many relationship between MobileVendor:PhoneModel.

MobileVendor Class

Class MobileVendor{
        long vendor_id;
        PhoneModel[] phoneModels;
        ...
 }

Okay, so you want to print out all the details of phone models. A naive O/R implementation would SELECT all mobile vendors and then do N additional SELECTs for getting the information of PhoneModel for each vendor.

-- Get all Mobile Vendors
 SELECT * FROM MobileVendor;

-- For each MobileVendor, get PhoneModel details
 SELECT * FROM PhoneModel WHERE MobileVendor.vendorId=?

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.

Resolve N+1 SELECTs problem

(i) HQL fetch join

"from MobileVendor mobileVendor join fetch mobileVendor.phoneModel PhoneModels"

Corresponding SQL would be (assuming tables as follows: t_mobile_vendor for MobileVendor and t_phone_model for PhoneModel)

SELECT * FROM t_mobile_vendor vendor LEFT OUTER JOIN t_phone_model model ON model.vendor_id=vendor.vendor_id

(ii) Criteria query

Criteria criteria = session.createCriteria(MobileVendor.class);
criteria.setFetchMode("phoneModels", FetchMode.EAGER);

In both cases, our query returns a list of MobileVendor objects with the phoneModels initialized. Only one query needs to be run to return all the PhoneModel and MobileVendor information required.