Hi,
I'm using the NHibernate 1.2.0.CR1, ASP.NET, JetDriver (access database).
A few words about my entities:
I have pretty simple object hierarchy: Categories, Subcategories and Links.
All associations are bidirectional one-to-many (each category has a collection of subcategories, each subcategory has a collection of links).
I want to fetch the following: All categories, each category's subcategories list initialized with 3 top subcategories. Each subcategory's links list initialized with 2 top links.
I couldn't find a way to use here left join fetch, so I'm doing this the ugly way, using the N+1 selects. As expected it performs horribly (2.5 seconds for 10 categories, each having 6 subcategories, each having 5 links).
So I thought that second level cache would come in handy: once the query was run, no additional selects will be performed (for additional runs of that query). But for some reason this didn't happen - the show_sql debug showed the same selects created when the query was performed several times (even in the same session! first level cache failing?)....
Please tell me that I did something wrong and that there is a way to make hibernate to perform sainly in this situation (by the way, the same queries took in ADO.NET 20 miliseconds)
Here is my configuration in web.config:
Code:
<hibernate-configuration xmlns='urn:nhibernate-configuration-2.2'>
<session-factory>
<property name="dialect">NHibernate.JetDriver.JetDialect, NHibernate.JetDriver</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.JetDriver.JetDriver, NHibernate.JetDriver</property>
<property name="connection.connection_string">Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\sites.mdb</property>
<property name="use_outer_join">true</property>
<property name="show_sql">true</property>
<property name="use_reflection_optimizer">true</property>
<property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache</property>
<property name="cache.use_query_cache">true</property>
<property name="relativeExpiration">600</property>
<property name="Expiration">600</property>
</session-factory>
</hibernate-configuration>
Code:
<class name="BL.Entities.Link, BL" table="Links">
<cache usage="nonstrict-read-write" />
<id name="Id">
<generator class="identity" />
</id>
<property name="Href" not-null="true" />
<property name="Text" column="LinkText" not-null="true" />
<property name="Order" column="Pos" />
<many-to-one name="SubCategory" column="SubCategoryId" not-null="true" fetch="join" />
</class>
<class name="BL.Entities.Category, BL" table="Categories">
<cache usage="nonstrict-read-write" />
<id name="Id">
<generator class="identity" />
</id>
<property name="Name" not-null="true" />
<property name="Order" column="Pos" />
<bag name="SubCategores" lazy="true" cascade="all" inverse="true">
<key column="CategoryId" />
<one-to-many class="BL.Entities.SubCategory, BL" />
</bag>
</class>
<class name="BL.Entities.SubCategory, BL" table="SubCategories">
<cache usage="nonstrict-read-write" />
<id name="Id">
<generator class="identity" />
</id>
<property name="Name" not-null="true" />
<property name="Order" column="Pos" />
<many-to-one name="Category" column="CategoryId" not-null="true" fetch="join" />
<bag name="Links" lazy="true" cascade="all" inverse="true">
<key column="SubCategoryId" />
<one-to-many class="BL.Entities.Link, BL" />
</bag>
</class>
Code:
IQuery query = session.CreateQuery("from Category c order by c.Order asc");
query.SetCacheable(true);
IQuery q1 = session.CreateQuery("from SubCategory s where s.Category.Id = :id order by s.Order asc");
q1.SetCacheable(true);
q1.SetMaxResults(3);
IQuery q2 = session.CreateQuery("from Link l where l.SubCategory.Id = :id order by l.Order asc");
q2.SetCacheable(true);
q2.SetMaxResults(2);
IList<Category> categories = query.List<Category>();
foreach (Category category in categories)
{
q1.SetParameter("id", category.Id);
category.SubCategores = q1.List<SubCategory>();
foreach (SubCategory subCategory in category.SubCategores)
{
q2.SetParameter("id", subCategory.Id);
subCategory.Links = q2.List<Link>();
}
}
}
Login
FAQ
Search