Eager Fetch or Fetch Join?

Ayende has a brilliant idea to fix the famous NHiberate select N+1 problem by using Eager Fetch in Dao operation, either ISession.CreateQuery() or ISession.CreateCriteria(). The same solution can be found in Chapter 8 of the new published book “NHibernate in Action”.

This works great, for one-to-many association (I noticed that it only can reach to one level of association),  for one-to-one, or many-to-one,  the easier way is, just change the fetch option in many-to-one mapping from default ‘select’ to ‘join’.

I tested my app, it improved performance a lot! Maybe this setting conflicts with lazy-load?

From Hibernate doc,

With fetch="join" on a collection or single-valued association mapping, you will actually avoid the second SELECT (hence making the association or collection non-lazy), by using just one “bigger” outer (for nullable many-to-one foreign keys and collections) or inner (for not-null many-to-one foreign keys) join SELECT to get both the owning entity and the referenced entity or collection. If you use fetch=”join” for more than one collection role for a particular entity instance (in “parallel”), you create a Cartesian product (also called cross join) and two (lazy or non-lazy) SELECT would probably be faster.

Advertisements

2 thoughts on “Eager Fetch or Fetch Join?

  1. Mee too, NH must set the default vaule of fetch to ‘select’ for some reason. At least my situation can’t use this ‘select fetch’, it’s way slow. Thousands of queries in behind.

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