Optimise in-memory caching of hierarchical structure from SQL Server 2008 R2

I'm implementing a content database for a C# Asp.Net MVC3 website in Sql Server.

The table structure is basically an adjacency list, but with my files and folders partitioned into FileSystems so that I can isolate individual users/accounts content:

FileSystem:
  [ID]

Folder:
  [ID]
  [FileSystemID]
  [ParentFolderID] (null)
  [Name]

File:
  [ID]
  [FileSystemID]
  [P开发者_运维问答arentFolderID]
  [Name]
  [Content]

Allowing for the inefficient normalisation here, it's pretty basic stuff.

I also have columns for [Created] and [Modified] where applicable.

These files are going to be used to perform dynamic branding of pages in an Asp.Net MVC 3 site, where every request is going to be looking for more specific branded versions of css/image files based on an original Asp.Net MVC content Url - e.g. "~/Content/Site.css" might become "~/[dynamic content root]/[accountid]/[theme]/Site.css".

This basic mechanism all works already; my main concern is caching and versioning.

Clearly instead of probing the database all the time for the same files and folders, it makes sense to build an in-memory cache of the content file system(s) to speed up content lookup and delivery. However, I need an efficient way to ensure that all web servers in the web farm detect a change in the account's virtual filesystem (any file or folder changed/delete/created) to ensure that a theme change is reflected instantly on all servers, for the next request.

Since hierarchical queries are potentially expensive, I have discounted running a sanity check on all the created/lastmodified dates in the filesystem each time. What I have considered, however, is a cascading version number on the whole filesystem.

So, any change in a filesystem leads to an increment of a version number on the filesystem itself, and possibly all parent folders up from the changed item. Thus a reader can simply attach to the whole file system or a particular part of it and run an inexpensive query each time to check the current version against the cache version.

This does have the downside of slowing down updates, but I expect the file system to change infrequently, whilst it will be read very frequently. My only concern with this approach is concurrency on the updates, and how to manage it.

Would this be a good approach? Is there something better I could consider?

Any thoughts welcome!

Since you are using SQL Server I recommend using something like the SqlCacheDependency object or SqlDependency object as part of the Query Notifications services in SQL Server.

I have successfully used this in various projects causing the burden of notification to be on the database instead of some polling mechanism that I write myself. Here is an example of how I use it for caching roles information:

public CacheDependency GetRoleActionCacheDependency()
    {
        using (var connection = new SqlConnection(Database.Database.Connection.ConnectionString))
        {
            connection.Open();
            using (SqlCommand sc = new SqlCommand("select roleid, actionid from dbo.RoleAction", connection))
            {
                var dependency = new SqlCacheDependency(sc);
                sc.ExecuteNonQuery();
                connection.Close();
                return dependency;
            }
        }
    }

This cache dependency invalidates the cache whenever anything in the roleaction table changes. I can get row-level notifications by having a parameter on the query.

Here is how I call this code. You could make your actual dependent object stored in the cache but in my particular case the object instance itself lives on the application (static) so I didn't need to cache it per se, I just needed to invalidate it. Which I am doing here by setting it to null (the getter manages repopulating it).

CacheDependency rolePathAccessCacheDependency = GetRoleActionRepository().GetRoleActionCacheDependency();
    HttpContext.Current.Cache.Add("anything will do", new object(), rolePathAccessCacheDependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Normal,
                    (key, value, reason) =>
                    {
                        _rolePathAccess = null; 
                    });

Also to complete the example you need this in the global.asax application start:

 SqlDependency.Start(ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString);

And application end:

SqlDependency.Stop(ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString);

One other thing I forgot is that it depends on broker services in SQL Server to be enabled. Here is a way to enable it, but note that the first statement is magically giving you exclusive access to the Database and rolling back every thing else, so only use it in production if you know what you are doing. The second statement is all you really need if you have exclusive access already.

ALTER DATABASE MYDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MYDatabase SET ENABLE_BROKER
ALTER DATABASE MYDatabase SET MULTI_USER
GO        

Just a few random thoughts from me:

do you actually have performance issues and could you trace them back to this specific issue? Do you need a quick fix or are you diving into the wider issue. What is the throughput your looking for, some numbers?

There's a couple of layers between the browser and your servers, 'the web' can do caching too in browsers proxies etc if you let it . Then there is IIS itself that is designed to handle caching. The way mvc works with those (relatively) static url's, those two come 'out of the box'.

Next layer would be your MVC object model, can you keep the whole tree into memory? That way you wouldn't have to make out of process calls. That saves quite a few cycles, let alone go to disk. If I understand it correctly, you still be sharing a lot of the files, so you could share the instance of those (flyweight pattern). Lazy load them when needed and have a mechanism to release the when you need it. Memory is cheap compared to your time.

rgds GJ