Hello,

I would like to load data from three tables :

Dictionary <-1-n-> DictionaryEntry <-1-n-> DictionaryResult

The problem is that the loading is very slow : 30 seconds and thousands of queries sent to the database...

Here is the mapping file I used :

<class name="Dictionary" lazy="false">
<id name="ID">
<generator class="assigned" />
</id>
<property name="Kwrd" />
<bag name="DictionaryEntry_List" lazy="false">
<key column="Dictionary_ID"/>
<one-to-many class="DictionaryEntry" />
</bag>
</class>

<class name="DictionaryEntry" lazy="false">
<id name="ID">
<generator class="assigned" />
</id>
<property name="Kwrd" />
<many-to-one name="Dictionary" column="Dictionary_ID" class="Dictionary" />
<bag name="DictionaryResult_List" lazy="false">
<key column="DictionaryEntry_ID"/>
<one-to-many class="DictionaryResult" />
</bag>
</class>

<class name="DictionaryResult" lazy="false">
<id name="ID">
<generator class="assigned" />
</id>
<property name="Word" />
<many-to-one name="DictionaryEntry" column="DictionaryEntry_ID" class="DictionaryEntry" />
</class>

If I add fetch="join"on both bag of Dictionary and DictionaryEntry, the loading is much faster (about 3 seconds) but I get multiple instances of the same Dictionaries...

Do you have a solution for that problem ?

Regards,

mathmax

Does anybody know how to have efficiency of the second method without doubloons?

If you have a criteria query, you can use a result transformer:

.SetResultTransformer(CriteriaUtil.DistinctRootEntity)

which removes the duplicate root entities. If that's not possible, try searching the forum for "duplicate items" or "DistinctRootEntity", there are lot's of posts about this.

But why does nHibernate not generate the duplicate instances automatically?
The first method (without using fetch=join) doesn't generate duplicate instances but it is very slow...

That's the documented behaviour with outer join fetching ...

http://www.hibernate.org/117.html#A12

.SetResultTransformer(CriteriaUtil.DistinctRootEntity) do the trick, but I wonder if the duplicate instances are created and then removed or if the duplicate items are filtered before instantiation ?

If I simply have a parent-children relation with a fetch=join, then there is no duplicated instances although it should be the default behavior as explained in the link you gave. I should have three tables in relations with two fetch=join to get duplicated items. Why ?

I think it depends on the way you do the query, UniqueResult() or List(). Actually you should get duplicates with a simple parent child relationship. How did you write the query ?



You only get one instance of the object. After the first one is created, hibernate pulls that one from the session cache instead of reinstantiating it over and over again.

I just use a criteria to get the result.

criteria.List() ord criterial.UniqueResult() ?

criteria.List()

I assume the reason for that is that crit.List() returns an System.ArrayList which itself takes care of the duplicates. Your collections are bags which allow duplicate items.

Sorry, I use Criteria.List<T>(). (the generic version)

Shouldn't matter, still the same explanation ... hibernate uses its own implementation for bags (PersistentGenericBag).