Using nHibernate version:1.2 I am try to invalidate the cache using SQL Query notifications. I have 4 records in a lookup table in the database that I would want cached. The initial request gets it from the database and caches it. I can see that in the SQL Profiler and the log4Net logs. Here is the log file entries.
2007-07-10 14:29:07,168 [5] DEBUG NHibernate.Cache.StandardQueryCache - checking cached query results in region: NHibernate.Cache.StandardQueryCache 2007-07-10 14:29:07,168 [5] DEBUG NHibernate.Cache.StandardQueryCache - query results were not found in cache 2007-07-10 14:29:07,168 [5] DEBUG NHibernate.SQL - select patientsta0_.PatientStatusKey as PatientS1_80_, patientsta0_.Version as Version80_, patientsta0_.Code as Code80_, patientsta0_.Description as Descript4_80_, patientsta0_.SortOrder as SortOrder80_, patientsta0_.LongDescription as LongDesc6_80_ from paceart.PATIENT_STATUS patientsta0_ 2007-07-10 14:29:07,183 [5] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79 2007-07-10 14:29:07,215 [5] DEBUG NHibernate.Cache.StandardQueryCache - caching query results in region: NHibernate.Cache.StandardQueryCache
When I request it again, it finds the data in the cache and does not go to the database.
2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.StandardQueryCache - checking cached query results in region: NHibernate.Cache.StandardQueryCache 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.StandardQueryCache - Checking query spaces for up-to-dateness [Iesi.Collections.HashedSet] 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.StandardQueryCache - returning cached query results 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache hit: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache hit: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache hit: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79 2007-07-10 14:30:22,436 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache hit: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79
Now I update one of the four rows in the database external to the app. When the app requests the data there is a cache miss and it gets the rows from the database.
2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache miss: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.SQL - SELECT patientsta0_.PatientStatusKey as PatientS1_80_0_, patientsta0_.Version as Version80_0_, patientsta0_.Code as Code80_0_, patientsta0_.Description as Descript4_80_0_, patientsta0_.SortOrder as SortOrder80_0_, patientsta0_.LongDescription as LongDesc6_80_0_ FROM paceart.PATIENT_STATUS patientsta0_ WHERE patientsta0_.PatientStatusKey=@p0; @p0 = '874f8621-44ff-4e08-8572-87ffa3b2b777' 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#874f8621-44ff-4e08-8572-87ffa3b2b777 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache miss: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.SQL - SELECT patientsta0_.PatientStatusKey as PatientS1_80_0_, patientsta0_.Version as Version80_0_, patientsta0_.Code as Code80_0_, patientsta0_.Description as Descript4_80_0_, patientsta0_.SortOrder as SortOrder80_0_, patientsta0_.LongDescription as LongDesc6_80_0_ FROM paceart.PATIENT_STATUS patientsta0_ WHERE patientsta0_.PatientStatusKey=@p0; @p0 = '1a142f19-5854-4635-a0aa-a8597b9dfb77' 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb77 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache miss: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.SQL - SELECT patientsta0_.PatientStatusKey as PatientS1_80_0_, patientsta0_.Version as Version80_0_, patientsta0_.Code as Code80_0_, patientsta0_.Description as Descript4_80_0_, patientsta0_.SortOrder as SortOrder80_0_, patientsta0_.LongDescription as LongDesc6_80_0_ FROM paceart.PATIENT_STATUS patientsta0_ WHERE patientsta0_.PatientStatusKey=@p0; @p0 = '1a142f19-5854-4635-a0aa-a8597b9dfb78' 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb78 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache lookup: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cache miss: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.SQL - SELECT patientsta0_.PatientStatusKey as PatientS1_80_0_, patientsta0_.Version as Version80_0_, patientsta0_.Code as Code80_0_, patientsta0_.Description as Descript4_80_0_, patientsta0_.SortOrder as SortOrder80_0_, patientsta0_.LongDescription as LongDesc6_80_0_ FROM paceart.PATIENT_STATUS patientsta0_ WHERE patientsta0_.PatientStatusKey=@p0; @p0 = '1a142f19-5854-4635-a0aa-a8597b9dfb79' 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Caching: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79 2007-07-10 14:31:40,345 [10] DEBUG NHibernate.Cache.ReadWriteCache - Cached: Paceart.Domain.Lookups.PatientStatus, Paceart.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9951c8675d8c08ec#1a142f19-5854-4635-a0aa-a8597b9dfb79
What I notice is instead of grabbing the entire resultset in one select it grabs the rows one row at a time. I can see this behaviour in the SQL profiler too. In the SQL profiler I also see an extra select for all the rows too
The other problem I have encountered is if I insert a fifth row in the database after the results have been cached on the next round trip it does not grab the new row but just the changes in the original four. Is this a flaw in the implementaion of the Query notifications by nHibernate or is this behavious by design?
Configuration
configuration.SetProperty("hibernate.cache.use_second_level_cache", "true");
configuration.SetProperty("hibernate.cache.use_query_cache", "true");
<section name="syscache2" type="NHibernate.Caches.SysCache2.SysCacheSection, NHibernate.Caches.SysCache2"/>
<syscache2>
<cacheRegion name="PatientStatus">
<dependencies>
<commands>
<add name="patientstatus" command="SELECT [PatientStatusKey], [SortOrder], [Code], [Description], [LongDescription], [LastUpdate], [Version] FROM [Paceart].[PATIENT_STATUS]"/>
</commands>
</dependencies>
</cacheRegion>
</syscache2>
Mapping file
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="paceart" default-lazy="false">
<class name="Paceart.Domain.Lookups.PatientStatus, Paceart.Domain" table="PATIENT_STATUS">
<cache usage="read-write" region="PatientStatus"/>
<id name="Key" type="Guid" column="PatientStatusKey">
<generator class="assigned" />
</id>
<version name="Version" column="Version" type="Int32" unsaved-value="null" />
<property name="Code" column="Code" type="String" />
<property name="Description" column="Description" type="String" />
<property name="SortOrder" column="SortOrder" type="Int32" />
<property name="LongDescription" column="LongDescription" type="String" />
</class>
</hibernate-mapping>
Get functionpublic DomainCollection<T> GetLookupList<T>() where T : ILookup, new()
{
try
{
ISession session = GetSession();
IQuery query = session.CreateQuery("from " + typeof(T).ToString());
query = query.SetCacheable(true);
IList list = query.List();
DomainCollection<T> listReturn = new DomainCollection<T>(list, null);
return listReturn;
}
catch (NHibernate.HibernateException hibernateException)
{
throw ConvertHibernateException(hibernateException, null);
}
}
|