one-to-one join fetching working

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Hi,

I have a one-to-one relationship mapped as follows:

<class name="ClassA" table="ClassA">
<id name="ID" type="Int32" column="ID">
<generator class="identity"/>
</id>

<cache usage="read-write"/>

<one-to-one name="B" class="ClassB" cascade="all-delete-orphan" fetch="join" outer-join="true"/>

</class>

<class name="ClassB" table="ClassB">
<id name="ID" type="Int32" column="ID">
<generator class="foreign">
<param name="propertty">A</param>
</generator>
</id>

<cache usage="read-write"/>

<one-to-one name="A" class="ClassA" constrained="true" fetch="join" outer-join="true"/>

</class>

When I monitor the SQL against the DB, I am seeing that a single select is being issued for every instance of ClassB that exists for a ClassA. The ClassA instances are being loaded from the cache but the related ClassB objects are being loaded into the session from the DB every time.

I would like the ClassB objects to be join fetched with the ClassA objects and have that cached so that I don't hit the DB every time.

Is there something wrong with my mapping?

Thanks,

Jason

karlchu · **Posted:** Mon Mar 03, 2008 9:16 pm

There are two issues at play here:

1. Whether ClassB is fetched by an outer-join depends on how you are fetching ClassA intances (IQuery, ICriteria, or just Get() or Load()).
2. Whether ClassB (or ClassA for that matter) instances are loaded from cache depends also on how you are fetching ClassA.

(1) If you are using the IQuery interface, that may explains your problem. IQuery ignores the "fetch" attribute for the most part. See docs here:
http://www.hibernate.org/hib_docs/nhibe ... ing-custom
The following HQL will join fetch ClassB:

Code:

from ClassA a join fetch a.B

(2) When speaking of "cache", I assume you meant the second-level cache. Remember queries are not cached automatically; this applied to both the IQuery and ICriteria API. You will have to call SetCacheable(true) to utilize the second-level cache.

As a side note, "all-delete-orphan" is not a valid value for cascade for <one-to-one>. You probably want just "all".

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Excellent...thanks for the detailed explanations Karl.

My actual mapping is obvioulsy more complicated than the sample I posted and so I think I need to elaborate on that to get further clarification from you.

The actual relationships involved are:

Class1 1 - * Class2
Class2 * - 1 ClassA
ClassA 1 - 1 ClassB

I am using IQuery to get a Class1 object and it is this single query that triggers individual loads for every ClassB that is encountered in the graph.

So, I don't reallly have control over the join fetching because I am so far removed from the ClassB relationship when querying Class1.

Yes, I am using the second level cache and have explicitly called SetCacheable(true) on this query. I have also applied the cache="read-write" on all the classes and sets involved above.

So, bearing in mind the above relationships and my query, should it be possible to have NH join fetch the one-to-one rather than hitting the DB to load every single object?

Jason

PS. Thanks for the tip with the cascade setting.

karlchu · **Posted:** Tue Mar 04, 2008 11:53 pm

Lets tackle one thing at a time here. If you have set the 'fetch="join"' on all the relevant associations, the following should automatically use left outer join to get the entire graph:

Code:

session.CreateCriteria(typeof(ClassOne)).List();

Failing that, the following should work for sure:

Code:

session.CreateCriteria(typeof (ClassOne), "one")
    .CreateAlias("Twos", "two", JoinType.LeftOuterJoin)
    .CreateAlias("two.A", "a", JoinType.LeftOuterJoin)
    .CreateAlias("a.B", "b", JoinType.LeftOuterJoin)
    .SetResultTransformer(CriteriaUtil.DistinctRootEntity)
    .List();

The following HQL should also work:

Code:

select distinct one
from ClassOne one
left join fetch one.Twos two
left join fetch two.A a
left join fetch a.B

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Wow...feels like I need to get back to basics because I am clearly making some big incorrect assumptions.

1. I thought that NH used join fetching by default for many-to-one relationships but the docs clearly state that the default is select.

2. I did not realise that you had to explicitly left join fetch many-to-one associations in the HQL...again I assumed that this was the default behaviour.

Not quite sure where I got these assumptions from to be honest! I guess I have been using the second level cache a lot so have never really noticed this as a problem.

So, I have changed the HQL as you suggested with the left join fetch down to the ClassB level but am still seeing individual selects getting fired for every instance of ClassB when the HQL is executed in a new session. A subsequent call within the same session does not result in hits to the DB as they are getting pulled from the session cache.

Obvioulsy, I want the ClassB objects to also be pulled from the second level cache too but this is not happening. I have definitely decorated all the classes with <cache usage="read-write"/>.

Also, do you have any rules of thumb for when to use fetch="join" for many-to-one relationships? Does the use of the second level cache reduce the significant of this a little (other than at app start)?

Jason

karlchu · **Posted:** Wed Mar 05, 2008 10:02 am

For choosing fetch="join" or "select" for many-to-one, the only hard-and-fast rule I follow myself is as follows: When the associated entity is static reference data (e.g. Country), I use the second-level cache and fetch="select". The rest is a bit more ambiguous and it depends on the application. I generally only turn on caching for reference data that does not change at all. On occasions, I will turn on caching for application data that only change infrequently (e.g. Products).

The choice of fetch="join" or "select" depends mostly on application logic and retrieval pattern. If the retrieval of the "many" side generally means the retrieval of the "one" side as well (e.g. ShoppingCartItem and Product), I use fetch="join". If the contrary is true, I use fetch="select" along with lazy="proxy" (or lazy="true" in 1.2?). In this case, I need to be mindful of the n+1 select problem and craft my HQL or ICriteria accordingly.

In any event, these are my personal preferences and your application is different. Experiment and do some profiling to find out what works for you.

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

I can still not figure out why ClassB is not being pulled from the second level cache with my revised HQL. Is there anything else I need to be aware of other than:

- I have <cache usage="read-write"/> for all the entities involved.
- I have left join fetch in my HQL that includes all the entities involved.
- I have fetch="join" on the on-to-one (although I realise that this is ignored for HQL)

Thanks,

Jason

karlchu · **Posted:** Wed Mar 05, 2008 3:48 pm

Let's go back to something fundamental here; have you configured NHibernate to use query cache?

Code:

Also have a look at the following two threads to see if it helps:

http://forum.hibernate.org/viewtopic.php?t=981567
http://forum.hibernate.org/viewtopic.php?t=976960

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Yes, I have that setting in the config and I know it is working because all of the other entities are being retrieved from the second level cache when I issue the HQL except the one-to-one ClassB.

According to one of those thread comments, using fetch="join" will result in the second-level cache not being used for associations...is that correct?

karlchu · **Posted:** Wed Mar 05, 2008 4:25 pm

jason.hill wrote:

According to one of those thread comments, using fetch="join" will result in the second-level cache not being used for associations...is that correct?

Yes. It is because you are instructing NHibernate to join fetch ClassB. Essentially, you are telling NHibernate to re-fetch the ClassBs, thus bypassing the second-level cache. For the most part, if you are going to the database anyway, you might as well bypass the cache. However, if the data of ClassB rarely changes, it may be advantageous to cache them. In that case, change the <many-to-one> to fetch="select". You will encounter the n+1 select problem the first time you execute the query. However, instances of ClassB are now cached. Subsequent calls of the same query will cause NHibernate to get the ClassBs by ID; but instead of going to the database, you will get cache hit and the ClassBs are retrieved from cache instead.

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

OK...so I went and created a test project with just the sample object graph that we have been discussing (full mapping appended at the end of this post).

I then used the following HQL, with SetCacheable(true), and retrieved a single Class1 object from the DB:

Code:

from Class1 where Name = '1'

This is followed by a simple iteration of the Class2List:

Code:

foreach (Class2 class2 in class1.Class2List)
{
}

With SQL Profiler, I see the first SQL statement to get the Class1 object and then a second SQL statement to load the child Class2List. I also notice that the second SQL statement includes an inner join to ClassA and a left outer join to ClassB even though I have not specified fetch="join" anywhere in my mapping.

I then decorate all the relationships with fetch="select" and I start to see a lot more hits to the DB as each relationship is loaded individually.

So, it certainly appears that my previous assumption about many-to-ones being auto join fetched is correct and that fetch="select" is not in fact the default behaviour. Am I missing something?

Another thing I notice is that subsequent HQL calls with the cached query result in no hits to the database at all, even for my one-to-one relationship. This surprised me as I thought I was mirroring the relationships that I have in my real application where I get extra hits to the DB every query. I will have to dig into this further and report back.

Jason

Code:

  <class name="Fetching.Domain.Class1, Fetching.Domain" table="Class1">

    <cache usage="read-write"/>

    <id name="ID" type="Int32" column="ID">
      <generator class="identity"/>
    </id>

    <property name="Name" column="Name" type="String" length="50" not-null="true" />

    <set name="Class2List" inverse="true" lazy="true" cascade="all-delete-orphan">
      <cache usage="read-write"/>
      <key column="Class1ID"/>
      <one-to-many class="Fetching.Domain.Class2, Fetching.Domain"/>
    </set>

  </class>

  <class name="Fetching.Domain.Class2, Fetching.Domain" table="Class2">

    <cache usage="read-write"/>

    <id name="ID" type="Int32" column="ID">
      <generator class="identity"/>
    </id>

    <property name="Name" column="Name" type="String" length="50" not-null="true" />

    <many-to-one name="Class1" class="Fetching.Domain.Class1, Fetching.Domain">
      <column name="Class1ID" not-null="true" />
    </many-to-one>

    <many-to-one name="ClassA" class="Fetching.Domain.ClassA, Fetching.Domain">
      <column name="ClassAID" not-null="true" />
    </many-to-one>

  </class>

  <class name="Fetching.Domain.ClassA, Fetching.Domain" table="ClassA">

    <cache usage="read-write"/>

    <id name="ID" type="Int32" column="ID">
      <generator class="identity"/>
    </id>

    <property name="Name" column="Name" type="String" length="50" not-null="true" />

    <one-to-one name="ClassB" class="Fetching.Domain.ClassB, Fetching.Domain" cascade="all" />

  </class>

  <class name="Fetching.Domain.ClassB, Fetching.Domain" table="ClassB">

    <cache usage="read-write"/>

    <id name="ID" type="Int32" column="ID">
      <generator class="foreign">
        <param name="property">ClassA</param>
      </generator>
    </id>

    <property name="Name" column="Name" type="String" length="50" not-null="true" />

    <one-to-one name="ClassA" class="Fetching.Domain.ClassA, Fetching.Domain" constrained="true" />
  
  </class>

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

The other thing I noticed with your recommended HQL is that when I use a left join fetch instead of a left join, the distinct does not work and I get back multiple root entities.

Am I able to use a result transformer with an HQL query to get back distinct root elements only?

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Not on any of the released versions - although I can't speak for the trunk.

You can easily filter the entities by creating a Set and passing the results of the list to the constructor. The set will only contain the unique list of entities in from the original list.

Cheers,

Symon.

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

Thanks Symon.

I actually used LINQ instead although not sure if your version would perform better:

return query.List<Class1>().Distinct().ToList();

jason.hill · **Joined:** Sat Sep 03, 2005 12:54 am **Posts:** 139

OK...did some more testing with my real application and the ClassB objects actually end up getting initially left join fetched automatically via another relationship in my domain model.

The strange thing is that after that initial load, which pulls in quite a lot of different objects, all of those objects are subsequently being pulled from the second level cache *except* the ClassB objects. I am still seeing a single select for every one of those in the profiler.