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.