Hello,

I got bad performances with nhibernate when loading two table with a 1-n relation.

The two tables are : Element and Item

Here is the mapping file :

<class name="Element" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Name" />
<bag name="Items" lazy="false" inverse="true">
<key column="Element_ID"/>
<one-to-many class="Item" />
</bag>
</class>

<class name="Item" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Name" />
<many-to-one name="ParentElement" class="Element" column="Element_ID" />
</class>

I had a look at the queries, and I noticed that hundreds of queries are sent to the database. They look like this :

NHibernate: SELECT items0_.Element_ID as Element6___1_, items0_.Id as Id1_, items0_.Id as Id5_0_, items0_.Name as Name5_0_, items0_.Element_ID as Element6_5_0_ FROM Item items0_ WHERE items0_.Element_ID=@p0; @p0 = '22057'

Why is there one query per line in the child table ? Why not using a INNER JOIN statement ? Is there a way to force nhibernate to use INNER JOIN ?

Regards,

mathmax

yes, there is a way to force the use of join:
set fetch="join" on the bag in the Element class

this will result in nhibernate making an outer join, retrieving the
element and all the items for each element.
careful though, nhibernate might return duplicated Element's.

Hi,

the problem remains the same although I set fetch="join" on the bag in the Element class. I also tried to add this attribute in the many-to-one tag of the class Item but still the same...

You said nhibernate might return duplicated Elements when using this attribute. Is it a bug ? Is there any known problem with this attribute ?

Please post the code you use for retreval HQL/ICriteria, and your mappingfiles with the modified attributes. Then it should be pretty easy to get you back on track :-)

Here is my mapping file :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="TestsHierrarchieFvb" assembly="TestsHierrarchieFvb">
<class name="Collection" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Keyword" />
<bag name="Collection_Collection_List" lazy="false">
<key column="collection_id"/>
<one-to-many class="Collection_Collection" />
</bag>
<bag name="Collection_Element_Element_List" lazy="false">
<key column="collection_id"/>
<one-to-many class="Collection_Element_Element" />
</bag>
</class>
<class name="Element" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Keyword" />
<property name="Name" />
<property name="StartName" />
<bag name="Items" lazy="false" fetch="join">
<key column="Element_ID"/>
<one-to-many class="Item" />
</bag>
</class>
<class name="Collection_Collection" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<many-to-one name="Parent" column="collection_id" class="Collection" />
<many-to-one name="Child" column="collection_2_id" class="Collection" />
</class>
<class name="Element_Element" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<many-to-one name="Parent" column="element_id" class="Element" />
<many-to-one name="Child" column="element_2_id" class="Element" />
</class>
<class table="Collection_Element_Element" name="Collection_Element_Element" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<many-to-one name="Parent" column="collection_id" class="Collection" />
<many-to-one name="Child" column="element_element_id" class="Element_Element" />
</class>
<class name="Item" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Artcoar" />
<property name="Artdesi" />
<property name="Artpere" />
<many-to-one name="ParentElement" class="Element" column="Element_ID" fetch="join" />
</hibernate-mapping>

here are my business objects :


public class Link<TParent, TChild>
{
public int Id { get; set; }
[NotifyPropertyChanged]public TParent Parent { get; set; }
[NotifyPropertyChanged]public TChild Child { get; set; }
}

public class Element_Element : Link<Element, Element> { }
public class Collection_Collection : Link<Collection, Collection> { }
public class Collection_Element_Element : Link<Collection, Element_Element> { }

public class Element
{
public Element() {}

public Element(string name)
{
Name = name;
Items = new List<Item>();
}

public int Id { get; set; }
public string Keyword { get; set; }
public string Name { get; set; }
public bool StartName { get; set; }
public IList<Item> Items { get; set; }
}

public class Collection
{
public Collection()
{
Collection_Collection_List = new List<Collection_Collection>();
Collection_Element_Element_List = new List<Collection_Element_Element>();
}

public int Id { get; set; }
public string Keyword { get; set; }
public IList<Collection_Element_Element> Collection_Element_Element_List { get; set; }
public IList<Collection_Collection> Collection_Collection_List { get; set; }
}

public class Item
{
public int Id { get; set; }
public string Artcoar { get; set; }
public string Artpere { get; set; }
public string Artdesi { get; set; }
public Element ParentElement { get; set; }
}

And here is the query I use to load the data :

session.CreateQuery("select c from Collection as c where c.Keyword='Standard'");

I there any other information you need to understand my problem ? In particular, I'm not sure to understand what do you mean by "the code you use for retreval HQL/ICriteria".

Thank you for your help.

I have a suspision that you could be using Enumerator to iterate through the items? So how do you use the query specified by:

session.CreateQuery("select c from Collection as c where c.Keyword='Standard'");

No that's not the problem. I use query.List<Collection>();.

Okay, you have some bad naming in your mapping so its a little hard to keep track. But youre many selects are for Item objects and you are querying for Collection object. I fail to see how they are related. Trý to reproduce your problem with a more simple mapping. for instances Cat and Kittens like in the reference manual ;-)

I simplify the problem. I load only two objects : Element and Item.

Here is the mapping file :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="testNhibernateQueries" assembly="testNhibernateQueries">
<class name="Element" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Name" />
<bag name="Items" lazy="false" fetch="join">
<key column="Element_ID"/>
<one-to-many class="Item" />
</bag>
</class>
<class name="Item" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Artcoar" />
<many-to-one name="ParentElement" class="Element" column="Element_ID" fetch="join" />
</class>
</hibernate-mapping>


Here is the c# code :

class Program
{
static void Main(string[] args)
{
Stopwatch sw = new Stopwatch();
var session = NHibernateSessionManager.GetInstance().GetSession();
var query = session.CreateQuery("select e from Element as e");
var elmts = query.List<Element>();
}
}


public class Element
{
public int Id { get; set; }
public string Name { get; set; }
public IList<Item> Items { get; set; }
}

public class Item
{
public int Id { get; set; }
public string Artcoar { get; set; }
public Element ParentElement { get; set; }
}

and here is the app.config content :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="nhibernate" type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</configSections>
<nhibernate>
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect"/>
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver"/>
<add key="hibernate.connection.connection_string" value="Server=server;Database=Orkos.com;uid=sa;pwd=celmdpps;"/>
<add key="hibernate.show_sql" value="true" />
</nhibernate>
</configuration>


The problem is still the same. Do you have an idea of the problem ?

Ok, I have to write this query to generate a left join statement :

select e from Element as e left join fetch e.Items

However I still have a problem as Item also contains a collection of SubItem.
I thought I could write this :

select e from Element as e
left join fetch e.Items as i
left join fetch i.SubItems

but this is not allowed... How can I get around this problem? Is there a possibility to load these data in two steps ?

First Element and Items
Second Items and SubItems

It sounds strange that your specification in the mapping files isnt used. Have you tried using Criteria queries?

Could you give me a short exemple of how do think to use criteria ?

IList<Element> elements = session.CreateCriteria(typeof(Element).List<Element>();

and to specify eager fetching you can use SetFetchMode but it should use the specified form of fetching from your mapping files

Have a look at this post:

http://forum.hibernate.org/viewtopic.php?p=2361474#2361474

The "fetch" attribute is not used for HQL. You could do a

session.CreateQuery( "from Element as e left join fetch e.Items" )

Hello,

I used the criteria from jta but the fetch attributes from the mapping files are still not taken into account. Is there some configuration options to specify ?