Should you use outer join fetching with NHibernate and Sql Server 2008?

Question

On outer join fetching, the Nhibernate documentation says:

If your database supports ANSI or Oracle style outer joins, outer join fetching might increase performance by limiting the number of round trips to and from the database (at the cost of possibly more work performed by the database itself). Outer join fetching allows a graph of objects connected by many-to-one, one-to-many or one-to-one associations to be retrieved in a single SQL SELECT.

I'm trying to decide if I should use outer join fetching in my current project (which uses NHibernate). To that end I'm going to be testing load times with and without outer join fetching. But I would like to know if it's a good or bad strategy on the whole when using Sql Server 2008.

Is it generally better to use outer join fetching than not with Sql Server 2008?

How does one determine whether to use it or not? (other than through performance testing and query profiling)

Thanks

Answer 1

Weird question. Use outer join fetching if necessary; certain queries will require outer join fetching on some entities. Other queries won't. So, if you do not need it, don't fetch eagerly using outer joins. You can specify the type of fetching for each association when you use the ICriteria API.

It's all a matter of optimization, which can be done afterwards. I mean: suppose you have an object graph, where certain parts are retrieved via lazy loading, and after testing, if it seems that lazy loading affects performance in a negative way for that specific object graph, check if you could gain something by using eager loading (via outer join fetching f.i.).

Answer 2

OUTER JOINs should be used to optionally return data from the "outer joined" table. As required.

Frankly, if you cared about performance you would not use nHibernate. This will decrease performance more then using OUTER JOINs (round tripping, inefficient SQL etc).