Tuning NHibernate: Tolerant QueryCache

Before reading this post you should know something about QueryCache and its imply tuning NH.

Resuming:

• Using IQuery.SetCacheable(true) you can put/get the entirely result of a query from the cache.
• The cache is automatically invalidated when the query-space change (mean that the cache will be throw when an Insert/Update/Delete is executed for one of the Tables involved in the query).
• Using IQuery.SetCacheMode(CacheMode.Refresh) you can force the cache refresh (for example if you need to refresh the cache after a Delete/Insert).

The Case

(the picture is a e-Bay snapshot)

Take a look to the left side. Near each option you can see a number and I’m pretty sure that it not reflect exactly the state in the DB. That number is only “an orientation” for the user probably calculated few minutes before.

Now, think about the SQLs, behind the scene, and how many and how much heavy they are. A possible example for “Album Type”, using HQL, could look like:

select musicCD.AlbumType.Name, count(*) from MusicCD musicCD where musicCD.Genre = ‘Classical’ group by musicCD.AlbumType.Name

How much time need each “Refine search” ?

Ah… but there is no problem, I’m using NHibernate and its QueryCache… hmmmm…

Now, suppose that each time you click an article you are incrementing the number of visits of that article. What happen to your QueryCache ? yes, each click the QueryCache will be invalidated and thrown (the same if some users in the world insert/update/delete something in the tables involved).

The Tolerant QueryCache abstract

The Tolerant QueryCache should be an implementation of IQueryCache which understands, through its configuration properties, that updates, to certain tables, should not invalidate the cache of queries based on those tables.

Taken the above example mean that an update to MusicCD does not invalidate all “Refine search” queries, if we are caching those statistics heavy queries.

The integration point

Well… at this point you should know how much NHibernate is extensible and “injectable”.

For each cache-region NHibernate create an instance of IQueryCache through an implementation of IQueryCacheFactory and, as you could imagine, the IQueryCacheFactory concrete implementation can be injected through session-factory configuration.

<property name="cache.query_cache_factory">YourQueryCacheFactory</property>

At this point we know all we should do to have our TolerantQueryCache :

1. Some configuration classes to configure tolerated tables for certain regions.
2. An implementation of IQueryCacheFactory to use the TolerantQueryCache for certain regions.
3. The implementation of TolerantQueryCache.

The Test

Here is only the integration test; all implementations are available in uNhAddIns.

Domain

public class MusicCD
{
public virtual string Name { get; set; }
}

public class Antique
{
public virtual string Name { get; set; }
}

<class name="MusicCD" table="MusicCDs">
<id type="int">
    <generator class="hilo"/>
</id>
<property name="Name"/>
</class>

<class name="Antique" table="Antiques">
<id type="int">
    <generator class="hilo"/>
</id>
<property name="Name"/>
</class>

Configuration

public override void Configure(NHibernate.Cfg.Configuration configuration)
{
base.Configure(configuration);
configuration.SetProperty(Environment.GenerateStatistics, "true");
configuration.SetProperty(Environment.CacheProvider,
    typeof(HashtableCacheProvider).AssemblyQualifiedName);

configuration.QueryCache()
    .ResolveRegion("SearchStatistics")
    .Using<TolerantQueryCache>()
    .TolerantWith("MusicCDs");
}

The configuration is only for the “SearchStatistics” region so others regions will work with the default NHibernate implementation. NOTE: the HashtableCacheProvider is valid only for tests.

The test

// Fill DB
SessionFactory.EncloseInTransaction(session =>
{
for (int i = 0; i < 10; i++)
{
    session.Save(new MusicCD { Name = "Music" + (i / 2) });
    session.Save(new Antique { Name = "Antique" + (i / 2) });
}
});

// Queries
var musicQuery =
new DetachedQuery("select m.Name, count(*) from MusicCD m group by m.Name")
.SetCacheable(true)
.SetCacheRegion("SearchStatistics");

var antiquesQuery =
new DetachedQuery("select a.Name, count(*) from Antique a group by a.Name")
.SetCacheable(true)
.SetCacheRegion("SearchStatistics");

// Clear SessionFactory Statistics
SessionFactory.Statistics.Clear();

// Put in second-level-cache
SessionFactory.EncloseInTransaction(session =>
{
musicQuery.GetExecutableQuery(session).List();
antiquesQuery.GetExecutableQuery(session).List();
});

// Asserts after execution
SessionFactory.Statistics.QueryCacheHitCount
.Should("not hit the query cache").Be.Equal(0);

SessionFactory.Statistics.QueryExecutionCount
.Should("execute both queries").Be.Equal(2);

// Update both tables
SessionFactory.EncloseInTransaction(session =>
{
session.Save(new MusicCD { Name = "New Music" });
session.Save(new Antique { Name = "New Antique" });
});

// Clear SessionFactory Statistics again
SessionFactory.Statistics.Clear();

// Execute both queries again
SessionFactory.EncloseInTransaction(session =>
{
musicQuery.GetExecutableQuery(session).List();
antiquesQuery.GetExecutableQuery(session).List();
});

// Asserts after execution
SessionFactory.Statistics.QueryCacheHitCount
.Should("Hit the query cache").Be.Equal(1);

SessionFactory.Statistics.QueryExecutionCount
.Should("execute only the query for Antiques").Be.Equal(1);

Fine! I have changed both tables but in the second execution the result for MusicCD come from the Cache.

Code available here.