Can NHibernate solve N+1 without using batching or Criteria API for an Order->Customer relationship?

Question

I have been reading and looking around to find this answer in black and white.

Let's talk about the familiar Customer and Order problem. Let's say I load 100 orders, and each order is linked to one and only one customer.

Using Fluent NHibernate, I will use References() to link my Order to Customer and I will define my Not.LazyLoad() and Fetch.Join() as well.

Now I am thinking hypothetically, NHibernate can simply join these two tables and would be pretty easy to hydrate entities. However, in my tests I always see rather N+1 queries (in fact perhaps only the unique IDs). I can share my code and tables but it might bore you, so

Is it possible to overcome N+1 for Order->Customer (one->one or rather Many->One)? Or I have to use batching or Criteria API?
If possible can you please point me to an Fluent NHibernate example?

2 queries and N+1 queries are only the same thing when there is only one item. If a customer has ten orders, do you see 11 selects? — R. Martinho Fernandes, Mar 18 '11 at 12:13
It is N+1. I have updated the question. Although it is only unique values. — Aliostad, Mar 18 '11 at 12:24

score 2 · Answer 1 · answered Mar 18 '11 at 13:29

2

I wouldn't look at the mapping as much as the actual querying you are doing. I leave ALL of my mappings as LazyLoad by default and override as needed.

I use the Criteria API to query, and I use CreateAlias to join other tables as needed. NHProf is highly recommended to find and eliminate situations like this.

answered Mar 18 '11 at 13:29

Mike Cole

14,474
28
114
194

Thanks for suggestion, but I do not fancy using a pre-Linq language. So do you mean that without using Criteria API, there is no way? – Aliostad Mar 18 '11 at 13:46
If you really want to use Linq, you can use the Expand method: http://stackoverflow.com/questions/831794/linq-for-nhibernate-and-fetch-mode-of-eager-loading – Mike Cole Mar 18 '11 at 13:49
BTW, what version on NHibernate are you using? Pre 3's linq provider left a little to be desired. – Mike Cole Mar 18 '11 at 13:50
Did you try the Expand method? I know that works in 2.0. Not sure about 3.0. – Mike Cole Mar 18 '11 at 16:09

score 2 · Accepted Answer · edited May 23 '17 at 10:33

Frequently there is the complain that fetch="join" doesn't work. This is because it is not considered by HQL. You can declare it within HQL.

I used fetch="join" hoping to make performance better but stopped using it in many cases. The problem was that joining to many tables could make SQL server run into a maximal number of columns limit. In some cases you don't need the data at all and therefore it is not very useful to specify it globally in the mapping file.

So I would recommend to

either use explicit join fetching in HQL, because there you know if the data is actually used.
or for any other case, batches are a great solution, because they are transparent (your code doesn't need to know about), make use of lazy loading and reducing the N+1 problem at the same time.

The answer is: use explicit join fetching in HQL if you don't want to use batching. — Stefan Steinegger, Mar 28 '11 at 13:36

score 2 · Answer 3 · answered Mar 19 '11 at 09:12

Hi
There are two ways you can address your problem

a) Using Criteria query It will look something like this

Session.CreateQuery(typeof(Order))
.Add(<Restrictions if any>)
.SetFetchMode("Customer",FetchMode.Eager)
.List<Order>();

b) Using HQL

Session.CreateQuery("select o from Order inner join fetch o.Customer where <conditionifany>").List<Order>();

Hope this helps..

score 0 · Answer 4 · answered Mar 19 '11 at 21:05

0

What query API are you using?

If it's HQL, you can use join fetch to retrieve an association eagerly.

For LINQ and QueryOver, use .Fetch()

answered Mar 19 '11 at 21:05

Diego Mijelshon

52,548
16
116
154

Can NHibernate solve N+1 without using batching or Criteria API for an Order->Customer relationship?

4 Answers4