Performance Zone is brought to you in partnership with:

I'm Singaram Subramanian, and I work with CSC India as a Software Developer. My blog is an attempt to share my learnings with all (mainly, for those who desperately mine google finding ways to solve problems, fix issues, learn about a Java/Open source software, or deciding on tough choices etc. during software development as I do). Singaram is a DZone MVB and is not an employee of DZone and has posted 36 posts at DZone. You can read more from them at their website. View Full User Profile

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

06.13.2012
| 34652 views |
  • submit to reddit

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.

 

 

Published at DZone with permission of Singaram Subramanian, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags: