Cache provider with SqlCacheDependencies Implementation

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

I developed a cache provider that is basically an extension to the SysCache cache provider with support for SqlCacheDependencies. It can handle sql 2000 or sql 2005 style sql dependencies. Dependencies can be combined for aggregate dependencies. It also supports time of day expiration for those items that need to expire at a specific time of day maybe because of a nightly process. I would like to offer it to anybody that is interested. I would also give the code the NHibernate guys if they want to integrate it into the project.

The configuration for a cache region would be somtheing similiar to this:

Code:

<sysCache>
    <cacheRegion name="Product" timeOfDayExpiration="11:10:00" priority="High" />
      <cacheRegion name="Videos">
         <dependencies>
            <commands>
               <add name="price"
                  command="Select Price from dbo.VideoTitle"
                   />
            </commands>
         </dependencies>
      </cacheRegion>
      <cacheRegion name="Availability">
         <dependencies>
            <commands>
               <add name="quantity"
                  command="Select SettingsId from dbo.VideoTitleAvailabilitySettings"
                  connectionName="default" 
   </commands>
         </dependencies>
      </cacheRegion>
   <!--Or Table Based dependency for Sql 2000-->   
      <cacheRegion name="Availability">
         <dependencies>
            <tables>
               <add name="quantity"
                  databaseEntryName="Default"
                  tableName="VideoTitleAvailabilitySettings" />
            </tables>
         </dependencies>
      </cacheRegion>
</sysCache>

Just give me an email address and I'll send the code.

sergey · **Posted:** Tue Mar 06, 2007 5:18 am

We would definitely be interested in integrating this into the project. Can you create a JIRA issue and attach it there?

adressin · **Joined:** Wed Apr 07, 2004 6:13 pm **Posts:** 18

Very interested in seeing this! Please keep us posted.

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

Jira Issue created, http://jira.nhibernate.org/browse/NH-936

The code can be downloaded from the issue.

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

We're interested in this too.

Symon.

jbliss1234 · **Joined:** Mon Sep 26, 2005 5:57 pm **Posts:** 39

I am not sure if I understand this correctly. From what I can tell the cache will be invalidated even if a single row changed in the table. Which is ok in SQL server 2000 because there is no easy way to invalidate cache at the row level. However, does this plugin do cache invalidation at the row level in SQL Server 2005?

I am very interested in integrating SQL Server 2005 cache dependency in NH, and I want to do this at the row level. Does this plugin do that? Do you have any suggestions or pointers as to where can I start?

nokiola · **Joined:** Sun Jun 19, 2005 2:21 pm **Posts:** 21

jnapier,

Does this implementation of cache dependency can also be used or modified to be used for OracleCacheDependency from Oracle.DataAccess.dll?

Oracle did their own implementation for their ADO.NET driver.

Regards,
Robert

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

jbliss1234, This cache provider does not invalidate at the row level. All the items in the cache region will be invalidated if a record in the resultset of the query is modified.

The pattern you are talking about would be possible to achieve with SqlCacheDependencies but performance would suffer dramatically. Each time you create a SQL 2005 Cache Dependency you have to attach that dependency to a sql command and then issue that sql command. So if you want to listen for an individual row change, you will have to issue a query for each object that you put into the cache. Since NHibernate knows nothing about SqlCacheDependencies, it cannot attach the dependency to the command that it issues to initially retrieve the data for an object. You must rely on the cache provider to setup the dependency which will cause an extra hit to the database. So if you have a query that returns 30 items and puts 30 objects into the cache, you will have an additional 30 queries back to the db to setup the dependency.

NHibernate issues a thread lock when it places an item in the cache so performance will be hampered even further if an additional query is issued to setup the dependency.

To get what you want and keep performance in mind, I think you will have to work with the low level APIs such as SqlNotificationRequest which requires you to implement the entire listening infrastructure yourself.

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

nokiola, it does not support oracle cache dependencies but I dont think it would be very difficult to add. I am not familiar with the OracleDependency but it looks like it works similiar to SqlDependency.

There would need to be an OracleDependencyEnlister created which implements ICacheDependencyEnlister. This would do the work of enlisting the application with the database for change notifications.

The provider would then need to be tweaked to find out which dialect is being used. NHibernate cache providers arent provided with direct access to the session factory so a little work would have to be done to parse the dialect from the configuration settings which the providers do have access to. The enlisters to use can then be determined from the dialect.

Of course all this would be much more seemless if the dialect could be accessed from the cache providers and the dialects provided information about thier support for dependencies. Although, the changes for this are much more ambitious.