When you are setting up your NHibernate mappings you must pay close attention to the way you setup your relationships between your entities.  Recently I started profiling some of our statements and was shocked to see how some of them had a metric-crap ton of joins when they only needed one or two.  After a bit of looking I realized that we had explicitly set FetchType to .Join (via Fluent NHibernate) and this was a big no-no. 

When you setup your associations to be join associations each time you use a entity you will get (for free :) ) the associations for that entity.  However, as you may guess this can be very, very expensive and not the desired outcome.  Below is how changing my FetchType from .Join to .Select (also, .Select is the default if you do not specify any FetchType).

This is the SQL output when everything was setup for .Join

FROM   RES_Basic this_
       inner join Res_Role roles1_
         on this_.RESOURCE_ID = roles1_.RESOURCE_ID
       left outer join O_BRANCH branch4_
         on roles1_.BRANCH_ID = branch4_.BRANCH_ID
       left outer join A_PROVIDER_TAXONOMY providerta5_
         on roles1_.TAXONOMY_ID = providerta5_.TAXONOMY_ID
       left outer join O_TEAM team6_
         on roles1_.TEAM_ID = team6_.TEAM_ID
       left outer join RES_Basic resource7_
         on roles1_.RESOURCE_ID = resource7_.RESOURCE_ID

This is the output after we changed to .Select (and setup my criteria to explicitly reference my Res_Role table)

FROM   RES_Basic this_
       inner join Res_Role roles1_
         on this_.RESOURCE_ID = roles1_.RESOURCE_ID

As you can see, when I do it right I am only joining to the tables I want and this can lead to a huge performance savings.

Till next time,