Derik Whittaker

Syndication

News


NHiberante and Join types in your mappings Select vs Join

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,


Posted 01-11-2010 2:34 PM by Derik Whittaker
Filed under: ,

[Advertisement]

Comments

Alec Whittington wrote re: NHiberante and Join types in your mappings Select vs Join
on 01-11-2010 5:08 PM

Can't using Select also lead to a bunch of  N+1 conditions? Using NHProf, when I use Select it complains about N+1 a lot.

Do you have some guidelines on which should be used over the other and when?

Great blog btw.

Martin wrote re: NHiberante and Join types in your mappings Select vs Join
on 01-12-2010 2:55 AM

You have a typo in the post title "NHiber_an_te"

Toran Billups wrote re: NHiberante and Join types in your mappings Select vs Join
on 01-13-2010 11:43 AM

What would this look like w/out FNH - ie the old hbm.xml mapping files?

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)