How to get 2nd level cache to work?

Nels_P_Olsen · **Posted:** Thu Mar 01, 2007 11:42 am

Hibernate version: 1.2.0beta3

Mapping documents:

In hibernate.cfg.xml:

Code:

<property name="cache.use_query_cache">false</property>
<property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider, NHibernate.Caches.SysCache</property>
<property name="expiration">600</property>

In MyClass.hbm.xml:

Code:

<class name="MyEntity" table="my_entity" lazy="true" ...>
    <cache usage="nonstrict-read-write" />
    ...

Name and version of the database you are using:
SQL Server 2005

Debug level Hibernate log excerpt:

Code:

2007-03-01 09:19:07,041 [11] DEBUG NHibernate.Caches.SysCache.SysCacheProvider - building cache with region: MyCompany.Entities.MyEntity, properties: blah blah blah ...
2007-03-01 09:19:07,057 [11] DEBUG NHibernate.Caches.SysCache.SysCache - new expiration value: 600
2007-03-01 09:19:07,057 [11] DEBUG NHibernate.Caches.SysCache.SysCache - no regionPrefix value given, using defaults

The SysCache appears to recognize that it should cache MyEntity, but if I repeatedly run the same HQL query within the expiration time, a trace through SQL Server Profiler shows that it gets all the rows from the my_entity table each time the HQL query is executed.

What am I doing wrong? Does the 2nd level cache only work when Get() or Load() is used? We rarely use them, we use HQL queries for most stuff. I did not enable the query cache because I read it only caches the IDs returned by the query, not the entity instances.

The behavior I wanted is that if a query is run multiple times within the expiration time, those entities already in the 2nd level cache are not re-selected, but any that aren't in the 2nd level cache do get selected. In other words, caching doesn't assume that the set of IDs returned by the query are the same as the last time the query was run, but it still only selects what it doesn't have cached. Usually it will be the case that the query result set has the same set of IDs, in which case nothing should be selected when the query is re-run. Is that possible?

sergey · **Posted:** Thu Mar 01, 2007 11:54 am

Second-level cache only caches objects by id since it's a cache, not a database. Query cache caches only the ids since it's designed to be used in conjunction with the second-level cache.

Nels_P_Olsen · **Posted:** Thu Mar 01, 2007 12:24 pm

If we want this behavior (which is probably dangerous, but for the sake of understanding caching ...):

When a given query with identical parameter values is run subsequently within the cache expiration time, the database should not be hit at all. Both the set of IDs comprising the query result set, and the actual entity instances, should be pulled from cache.

How do we set that up?

I set use_query_cache to true, and call myQuery.SetCacheable(true), but I get this in the log when I run my query twice:

Code:

2007-03-01 10:02:46,948 [11] DEBUG NHibernate.Cache.StandardQueryCache - checking cached query results in region: NHibernate.Cache.StandardQueryCache
2007-03-01 10:02:46,948 [11] DEBUG NHibernate.Caches.SysCache.SysCache - Fetching object 'NHibernate-Cache:NHibernate.Cache.StandardQueryCache:sql: select my_entity.blah ... from my_entity myentity0_ where (myentity0_.my_entity_id=?); parameters: ; named parameters: {myEntityId=AAT68MOPSO}; first row: 0@1841479977' from the cache.
2007-03-01 10:02:46,948 [11] DEBUG NHibernate.Cache.StandardQueryCache - query results were not found in cache
2007-03-01 10:02:48,245 [11] DEBUG NHibernate.Cache.StandardQueryCache - caching query results in region: NHibernate.Cache.StandardQueryCache
2007-03-01 10:02:48,245 [11] DEBUG NHibernate.Caches.SysCache.SysCache - adding new data: key=NHibernate-Cache:NHibernate.Cache.StandardQueryCache:sql: select my_entity.blah ... from my_entity myentity0_ where (myentity0_.my_entity_id=?); parameters: ; named parameters: {myEntityId=AAT68MOPSO}; first row: 0@1841479977&value=System.Collections.ArrayList

2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Cache.StandardQueryCache - checking cached query results in region: NHibernate.Cache.StandardQueryCache
2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Caches.SysCache.SysCache - Fetching object 'NHibernate-Cache:NHibernate.Cache.StandardQueryCache:sql: select my_entity.blah ... from my_entity myentity0_ where (myentity0_.my_entity_id=?); parameters: ; named parameters: {myEntityId=AAT68MOPSO}; first row: 0@1841479977' from the cache.
2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Cache.StandardQueryCache - Checking query spaces for up-to-dateness [Iesi.Collections.HashedSet]
2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Caches.SysCache.SysCache - Fetching object 'NHibernate-Cache:UpdateTimestampsCache:my_entity@-1665100006' from the cache.
2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Cache.StandardQueryCache - returning cached query results
2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Cache.NonstrictReadWriteCache - Cache lookup: MyCompany.Entities.MyEntity, MyCompany.Entities Version=6.0.0.0, Culture=neutral, PublicKeyToken=null#AAT68MOPSO
2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Caches.SysCache.SysCache - Fetching object 'NHibernate-Cache:MyCompany.Entities.MyEntity:MyCompany.Entities.MyEntity, MyCompany.Entities, Version=6.0.0.0, Culture=neutral, PublicKeyToken=null#AAT68MOPSO@-2031693889' from the cache.
2007-03-01 10:03:07,026 [11] DEBUG NHibernate.Cache.NonstrictReadWriteCache - Cache miss

Why the "cache miss"?

jnapier · **Joined:** Sat Sep 10, 2005 3:46 pm **Posts:** 178

I didnt know it was required but you may have to declare a region for the entity cache and specify this in the configuration for syscache:

Code:

<configuration>
   <configSections>
      <section name="syscache" type="NHibernate.Caches.SysCache.SysCacheSectionHandler,NHibernate.Caches.SysCache" />
   </configSections>

   <syscache>
      <cache region="foo" expiration="500" priority="4" />
      <cache region="bar" expiration="300" priority="3" />
   </syscache>
</configuration>

thinkrama · **Joined:** Thu Mar 15, 2007 10:34 am **Posts:** 8

How to store lookups in Second level cache or any other cache that NHibernate supports enterprise wide.

Scenario:

User 1 queries for list of countries - resultset must be stored after the first execution in the Cache.

Subsequent users when querying for the Countries lookup, NHibernate must fetch the results from Cache instead of DB.

Please advise

thanks guys