Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

· Mobile Zone
Free Resource

Download this comprehensive Mobile Testing Reference Guide to help prioritize which mobile devices and OSs to test against, brought to you in partnership with Sauce Labs.

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.

Analysts agree that a mix of emulators/simulators and real devices are necessary to optimize your mobile app testing - learn more in this white paper, brought to you in partnership with Sauce Labs.

Topics:
java ,architects ,high-perf ,tutorial ,performance

Published at DZone with permission of Singaram Subramanian, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}