Slow collection loading

vivaeinstein · **Joined:** Thu Oct 25, 2007 3:45 am **Posts:** 22

Hi,

I have a simple case with really bad performance. I am trying to load objects using NHibernate. The object is Template and it has a collection of TemplateItem objects. The mapping looks like this:

----------------------
Template:

<class name="Example.Template, Example" table="Template" >

...

<bag name="Items" table="TemplateItems" cascade="all-delete-orphan" inverse="false" lazy="false">
<key column="TemplateId" />
<one-to-many class="Example.Template, Example"/>
</bag>

...
---------------------------

TemplateItem: very simple.

There are in the database about 100 Templates and about 1000 Items. I tell NHibernate to load the Templates:

IList templates= session.CreateCriteria(Typeof ("Example.Template")).List();

And what happens:
1. Using lazy='false' for loading of the collection (as in the mapping above) the loading takes about 20 sec.
2. Using lazy="true" takes several parts of the second.

The architecture of the application is Client-Server and lazy loading is not (I think) an option. After loading and returning the result to the client, the NHibernat session is disposed and further loading of collection items is not possible.

Is there something I miss? What is the solution in this case?

wolli · **Posted:** Thu Mar 06, 2008 4:27 am

Default fetch stratety is "select" which means a single select for each item. Try fetch="join".

Code:

<bag name="Items" table="TemplateItems" cascade="all-delete-orphan" inverse="false" lazy="false" fetch="join" > 
<key column="TemplateId" /> 
<one-to-many class="Example.Template, Example"/> 
</bag> 

Have a look at http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/performance.html#performance-fetching

wolli · **Posted:** Thu Mar 06, 2008 4:45 am

You may get duplicate template objects in the result set when you use join fetching ! Also have a look at for explanations:

http://forum.hibernate.org/viewtopic.php?t=981173&highlight=fetch+join+duplicate

http://www.covalentsw.com:8080/browse/NH-300

vivaeinstein · **Joined:** Thu Oct 25, 2007 3:45 am **Posts:** 22

Thank you, Wolli, it really helps :).

vivaeinstein · **Joined:** Thu Oct 25, 2007 3:45 am **Posts:** 22

I just saw a new problem with this loading. Now I use fetch="join" option in the collection mapping and criteria.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer()) for the NHibernate criteria. This way the root object is loaded really ones but every item of the collection are loaded a lot of times.
What is the solution of this?