NHibernate second-level caching with multi-query

Question

We're extensively using NHibernate multi-query functionality and experiencing strange behavior. Seems like NHibernate does not cache multiqueries and they always hit the database. We're using QueryOver, all queries are set to be cacheable, but when testing home page with blitz.io with -p 1-250:30 pattern I can see that the only multi-query that hits the database gets executed 2000+ times, whereas other queries (like selecting currently signed in user) are executed only once or twice.

So the question is: am I missing something or NHibernate does not really cache multi-query results?

score 4 · Accepted Answer · answered Sep 08 '11 at 08:16

Aha, got it! As it turned out, it was not all second-level cache's fault, but rather our usage of QueryOver is to blame.

As we're writing a multi-tenant SaaS application, most of our queries looked like this:

return
    Session.QueryOver<Article>().Cacheable().
        Where(a => a.Site == site && a.PublishedAt <= publishedAt).
        OrderByCoalesceDesc(typeof(DateTime), a => a.UpdatedAt, a => a.CreatedAt).
        Take(count).
        Future();

And a.Site == site was the problem. Apparently, the way query cache checks whether query results are cached or not is basically by using a combination of an SQL statement and all paramters as a key to the cache "hashtable". SQL statement text is always the same for our multi-queries, but the site parameter was the culprit. NH checks to see whether all supplied parameters match those already in cache and naturally we don't have Equals() implemented in our Site class, so the check always failed.

What we ended up with was rewriting our queries like this:

var siteID = site.ID;
return
    Session.QueryOver<Article>().Cacheable().
        Where(a => a.Site.ID == siteID && a.PublishedAt <= publishedAt)...

NHibernate second-level caching with multi-query

1 Answers1