NHibernate second level cache: Query cache doesn't work as expected

Question

The packages I use:

NHibernate 5.2.1
NHibernate.Caches.SysCache 5.5.1

The NH cache config:

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

    <syscache>
        <!-- 3.600s = 1h; priority 3 == normal cost of expiration -->
        <cache region="GeoLocation" expiration="3600" sliding="true" priority="3" />
    </syscache>
</configuration>

I want to query a bunch of locations using their unique primary keys. In this unit test I simulate two requests using different sessions but the same session factory:

[TestMethod]
public void UnitTest()
{
    var sessionProvider = GetSessionProvider();

    using (var session = sessionProvider.GetSession())
    {
        var locations = session
            .QueryOver<GeoLocation>().Where(x => x.LocationId.IsIn(new[] {147643, 39020, 172262}))
            .Cacheable()
            .CacheRegion("GeoLocation")
            .List();

        Assert.AreEqual(3, locations.Count);
    }

    Thread.Sleep(1000);

    using (var session = sessionProvider.GetSession())
    {
        var locations = session
            .QueryOver<GeoLocation>().Where(x => x.LocationId.IsIn(new[] { 39020, 172262 }))
            .Cacheable()
            .CacheRegion("GeoLocation")
            .List();

        Assert.AreEqual(2, locations.Count);
    }
}

If the exact same IDs are queried in the exact same order, the second call would fetch the objects from the cache. In this example however, the query is called with only two of the previously submitted IDs. Although the locations have been cached, the second query will fetch them from the DB.

I expected the cache to work like a table that is queried first. Only the IDs that have not been cached yet, should trigger a DB call. But obviously the whole query seems to be the hash key for the cached objects.

Is there any way to change that behavior?

Answer 1

There is no notion of a partial query cache, it's all or nothing: if the results for this exact query are found - they are used, otherwise the database is queried. This is because the query cache system does not have specific knowledge about the meaning of the queries (eg. it cannot infer the fact that result of a particular query is a subset of some cached result).

In other words the query cache in NHibernate acts as a document storage rather than a relation table storage. The key for the document is a combination of the query's SQL (in case of linq some textual representation of the expression tree), all parameter types, and all parameter values.

To solve your particular case I would suggest to do some performance testing. Depending on the tests and a dataset size there are some possible solutions: filter cached results on a client (something like following), or not use query cache, or you can implement some caching mechanism for the particular query on the application level.

[TestMethod]
public void UnitTest()
{
    var sessionProvider = GetSessionProvider();

    using (var session = sessionProvider.GetSession())
    {
        var locations = session
            .QueryOver<GeoLocation>()
            .Cacheable()
            .CacheRegion("GeoLocation")
            .List()
            .Where(x => new[] {147643, 39020, 172262}.Contains(x.LocationId))
            .ToList();

        Assert.AreEqual(3, locations.Count);
    }

    Thread.Sleep(1000);

    using (var session = sessionProvider.GetSession())
    {
        var locations = session
            .QueryOver<GeoLocation>().
            .Cacheable()
            .CacheRegion("GeoLocation")
            .List()
            .Where(x => new[] {39020, 172262}.Contains(x.LocationId))
            .ToList();

        Assert.AreEqual(2, locations.Count);
    }
}

More information on how the (N)Hibernate query cache works, can be found here.