one-to-one relation and n+1 problem

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

Hibernate version:
NHibernate 1.2.1GA

I have a one-to-one relation between two tables/classes. The important parts of the mapping files are the following:

class1.hbm.xml

Code:

<one-to-one name="Two" class="Class2" property-ref="Class1Id" fetch="join" />

class2.hbm.xml
(here I have to take a many-to-one because the culumn for the relation is not the primary key)

Code:

<many-to-one name="One" class="Class1" column="CLASS1_ID" fetch="join" />

The code to fetch a list of Class1 objects:

Code:

return session.CreateCriteria(typeof(Class1))
    .SetFetchMode("Two", FetchMode.Join)
    .Add(Expression.Eq("Enabled", 1))
    .AddOrder(Order.Asc("Name"))
    .CreateCriteria("Two").Add(Expression.Gt("Enabled", 1))
    .List<Class1>();

The result list is the expected, but the type of fetching is strange:

The first select NHibernate creates fetches all data which is required (from both tables with a join!). I thought that NHibernate would stop here and all be fine! The strange thing is that for every line in the resulted list a additional select to fetch the data from the second table (class2) is fired. I don't understand why this happens because the required data is complete after the first select.

Has anybody an idea how to solve this problem so that only one select runs?

Thank you!! [/code]

wolli · **Posted:** Thu Mar 06, 2008 5:46 am

I suppose you have to mark one end of your association as inverse="true".

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

Thanks for the answer, but where I have to set inverse="true" ?
I can find this attribute only for the join tag, but I use one-to-one and many-to-one... Or should I set this in the code, perhaps with ICriteria?

wolli · **Posted:** Thu Mar 06, 2008 6:04 am

Sorry, didn't read your mapping careful enough. "inverse" only exists on the collection, but you don't use a collection here.

wolli · **Posted:** Thu Mar 06, 2008 6:09 am

Have you tried to specify the fetch mode after creating the subcriteria ?

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

Did you mean in this way:

Code:

return session.CreateCriteria(typeof(Class1))
    .Add(Expression.Eq("Enabled", 1))
    .AddOrder(Order.Asc("Name"))
    .CreateCriteria("Two").Add(Expression.Gt("Enabled", 1))
    .SetFetchMode("Two", FetchMode.Join)
    .List<Class1>();

I tried it... with the same result ;(

Matrasinator · **Joined:** Fri Aug 10, 2007 3:34 am **Posts:** 44

i think the reason why NHibernate makes extra selects is because
of the fetch="join" in the many-to-one mapping...

you should follow the best practices:
make all associations lazy and only use fetch at runtime

so try this:
remove the join=fetch from both <one-to-one> and <many-to-one>
and try using the ICriteria

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

I tested this way, too... with no change of behaviour...

BUT: I found the reason and a solution for me:

the reason:
the "connection" between both classes/tablesis done with the column "CLASS1_ID" which is the primary key only in class1, not in class2. here is the point where nhibernate has problems to resolve the references and fetches the data for class2 row by row.

the solution which is capable for me:
I removed the one-to-one part, so that an unidirectional relation exists. And I have to fetch the objects "from the other direction". I have to load class2 objects and class1 is referenced through a property in class2. IN THIS CASE only one select is fired...

I hope it's understandable... my english is not the best ;-)

dionysos1973 · **Joined:** Thu Jul 19, 2007 6:49 am **Posts:** 13

We are suffering from the exact same issue for ALL our one-to-one associations. Why is this? Is this a bug in NHibernate?

dionysos1973 · **Joined:** Thu Jul 19, 2007 6:49 am **Posts:** 13

We are suffering from the exact same issue for ALL our one-to-one associations. Why is this? Is this a bug in NHibernate?

typhos · **Joined:** Thu Mar 06, 2008 5:06 am **Posts:** 68

I don't know wether it's a bug or a wanted situation. I know that NHibernate should get all data with the first statement (the select NHibernate builds is correct).
But this seems to function only in one direction :-(

dionysos1973 · **Joined:** Thu Jul 19, 2007 6:49 am **Posts:** 13

I can't believe this behavior is according to design. Isn't there a NHibernate developer that can elaborate on this?