N+1 Problem in Hibernate

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.

Advertisements

Currently Working as Game Developer at Gaussian Networks Private Limited , ( www.adda52.com ). Did B.Tech in computer science Engineering from G.G.S.I.P University , new delhi. Using Java , ActionScript 3.0 and PHP for development. I am a programmer who loves to adapt new platforms for coding. Reading techs & specs of gadgets is my hobby as i am a 24x7 active web crawler. I consider learning as a best helping aid to yourself as well as for others because it's the best means for diversifying your knowledge.

Posted in hibernate, J2EE, JPA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: