Hey guys,
for two days I've been trying to get the the SysCache2 2nd level cache working. I got it working somewhat but currently I'm stuck and just have no idea what to try next. In short, the child objects of a one-to-many relationship get requeried individually from the second query execution on. In the end this of course is much slower than the uncached version.
I'm trying to cache two tables, which are connected with a FK relationship. In my object model I have a one-to-many relationship mapped as a bag.
The behavior in detail is the following: I'm querying the parent objects using a Criteria query with SetCacheable
Code:
return criteria.Add(Expression.Eq("IsTemplate", isTemplate)).SetCacheable(true).List<FaultEntry>();
The child objects are mapped the following way, so that the child object collections are immediately loaded with a second select:
Code:
[Bag(0, Cascade = CascadeStyle.All, OrderBy = "Name asc", Fetch = CollectionFetchMode.Subselect, Lazy = false)]
Both domain objects are attributed with
Code:
[Cache(1, Region = "KnowledgeRegion", Usage = CacheUsage.ReadWrite)]
After application start when I execute the query, I see two selects as expected. With the second execution I don't see a SELECT for the parent objects but the child objects get requeried by their foreign key column.
From hibernate.cfg.xml:
Code:
<property name="hibernate.cache.provider_class">NHibernate.Caches.SysCache2.SysCacheProvider, NHibernate.Caches.SysCache2</property>
<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.use_query_cache">true</property>
From web.config file:
Code:
<section name="syscache2" type="NHibernate.Caches.SysCache2.SysCacheSection, NHibernate.Caches.SysCache2"/>
<connectionStrings>
<add name="MyDbConnection" connectionString="Server=localhost;initial catalog=MyDB;Integrated Security=SSPI;Min Pool Size=2" />
<connectionStrings>
<syscache2>
<cacheRegion name="KnowledgeRegion">
<dependencies>
<tables>
<add name="Fault" databaseEntryName="MyDB" tableName="Fault" />
<add name="SearchExpression" databaseEntryName="MyDB" tableName="SearchExpression" />
</tables>
</dependencies>
</cacheRegion>
</syscache2>
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="60000">
<databases>
<add name="MyDB" ConnnectionStringName="MyDbConnection" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
I put log4net in DEBUG mode for NHibernate.Caches.SysCache2 and in the part where it loads the child objects looks like this:
Code:
2007-11-05 19:07:52,484 [7] INFO NHibernate.Loader.Loader [(null)]
SELECT searchexpr0_.ForeignID as ForeignID__1_, searchexpr0_.ID as ID1_, searchexpr0_.ID as ID4_0_, searchexpr0_.ForeignID as ForeignID4_0_, searchexpr0_.Name as Name4_0_, searchexpr0_.Description as Descript4_4_0_, searchexpr0_.Regex as Regex4_0_, searchexpr0_.Negate as Negate4_0_, searchexpr0_.XPath as XPath4_0_ FROM [SearchExpression] searchexpr0_ WHERE searchexpr0_.ForeignID=@p0 ORDER BY searchexpr0_.Name asc
-------
2007-11-05 19:07:52,484 [7] DEBUG NHibernate.Caches.SysCache2.SysCacheRegion [(null)]
Fetching object 'NHibernate-Cache:KnowledgeRegion:AISDomainModel.SearchExpressionEntry, AISDomainModel, Version=1.5.1.2523, Culture=neutral, PublicKeyToken=7d33944ba3a00ede#5855@5855' from the cache.
-------
2007-11-05 19:07:52,484 [7] DEBUG NHibernate.Caches.SysCache2.SysCacheRegion [(null)]
Fetching object 'NHibernate-Cache:KnowledgeRegion:AISDomainModel.SearchExpressionEntry, AISDomainModel, Version=1.5.1.2523, Culture=neutral, PublicKeyToken=7d33944ba3a00ede#5856@5856' from the cache.
-------
2007-11-05 19:07:52,484 [7] DEBUG NHibernate.Caches.SysCache2.SysCacheRegion [(null)]
Fetching object 'NHibernate-Cache:KnowledgeRegion:AISDomainModel.SearchExpressionEntry, AISDomainModel, Version=1.5.1.2523, Culture=neutral, PublicKeyToken=7d33944ba3a00ede#5857@5857' from the cache.
-------
2007-11-05 19:07:52,484 [7] DEBUG NHibernate.Caches.SysCache2.SysCacheRegion [(null)]
Fetching object 'NHibernate-Cache:KnowledgeRegion:AISDomainModel.SearchExpressionEntry, AISDomainModel, Version=1.5.1.2523, Culture=neutral, PublicKeyToken=7d33944ba3a00ede#5858@5858' from the cache.
So it seems it first queries the child objects for each parent object but then additionally gets them out of the cache. This just doesn't make sense to me and I have a feeling I'm doing something wrong.
Beside that, the expiration thing seems to be working because when I change a record in one of the tables NHibernate will submit the two SELECTs as in the very beginning.
FYI, someone else had a similar problem:
http://forum.hibernate.org/viewtopic.ph ... =syscache2 but the posting didn't contain any conclusive solution (at least none obvious to me).
Thanks a lot for your help,
Christoph