Cacheable queries pitfalls

Monday, 1 July 2013 19:00 UTC

Cacheable queries are a powerful tool in the belt of the caching-aware developer. They save the results of a query as a collection of identifiers, so that if the same query is executed again we can avoid hitting the database and just return the previously loaded results. Even though this sounds simple, there are two common pitfalls when using it: one concerns performance directly, and the other makes your cache return stale results.

The first pitfall, common to all second level cache implementations, is not to cache the entities that are returned by the query. In fact, when NHibernate finds any cached query results, it goes through the list of identifiers to fetch the entities one by one. However, if the entities themselves are not cached we run into a problem – supposing that we cached the results of a query returning 1000 entities, NHibernate is going to execute 1000 selects.

Concretely, to avoid this problem you must make sure to have configured the entities that will be returned by your query to be cached correctly. In this case it is evident that we are degrading the performance of our system instead of improving it. However, there is a lurking misconfiguration affecting the freshness of the cached data instead of raw performance, which is much more difficult to spot.

I have noticed that developers often avoid to define a cache region for cacheable queries – which is not necessarily an issue, especially when using SysCache. Despite the fact that there is no need to set the region, if you leave it out you should do so consciously. While this configuration works fine for SysCache, this deceivingly small detail has deep repercussions on how the cache is invalidated when using SysCache2.

Although there exists a default cache region containing the cached query results (NHibernate.Cache.StandardQueryCache), if you do not explicitly define a region for a cacheable query you get no automatic invalidation via query notifications. This is due to the fact that the standard query cache region is not configured to support query notifications and it might be an issue if the default relative expiration of 5 minutes of StandardQueryCache has been increased. As a matter of fact, we manually configured some of our cache regions to expire after a time frame of multiple hours, meaning that we were seeing several instances of this problem.

In case you are not familiar with query notifications, think of them as a service that receives a query and notifies you if the result of the query has changed due to changes in the underlying data. You can then keep the program running normally until SQL server sends you a notification, at which point you will want to react and, in case you are caching that data, invalidate your stale cache entry. This is all handled for you in the background by SysCache2 and a number of BCL classes. You might have noticed that in order to check the data for changes, SQL Server needs to know which query has to be monitored. It turns out that the default query cache has no such query defined, which makes sense, considering it is the cache region used for all queries and can’t predict which entities will be involved in it.

In order to solve the staleness issue, you can use a custom cache region configured for query notifications. All things considered, if we are using SysCache2 we probably already defined cache regions containing either command- or table-based dependencies, which we can reuse for invalidating stale query results. More precisely, supposing that you are using QueryOver, your cacheable query will look like this:


Sharing the cache region between queries and entities to be cached also has an additional benefit: suppose that you cached the results of the query in a different region, and your entity cache region is invalidated. The next time you are going to perform the query you are going to incur in the first problem that we met: the resulting ids of the query are still cached, but the entities themselves are not cached anymore. If you used the same cache region for both purposes this is not going to happen.

So as you have seen, caching queries is a way to squeeze some performance out of the system, but if not configured correctly it will backfire badly.

Seemingly random clearing of NHibernate second level cache

Thursday, 11 April 2013 00:00 UTC

Imagine spending days carefully setting up and fine tuning NHibernate caches, the whole lot of them: session, second level and query cache. Suppose you have spent way too long in finding out how to set up SysCache2 and query notifications in SQL Server, how to create a perfect combination of permissions so that everything works smoothly. After the web app has been deployed to the test system, you receive a call asking you to check the behavior of the second level cache that may be the cause of a timeout.

Mh, sounds weird, but ok. I look into it and everything looks fine at first glance: reading the debugging output from the second level cache, it appears that everything is being cached and fetched from the cache correctly. Until I hit one button and try to reproduce that timeout, succeeding. The cache is being completely cleared and during the next attempt to fetch a cached model entity the cache is rebuilt again from scratch, blocking due to a transaction that was started by the operation itself. What? Why is it happening?

After all the time spent in the caching code, this defies everything I know about the second level cache. The cache region isn’t expiring, it is being explicitly cleared even if there is no such explicit request from our code. A future me will have to admit that yes, we didn’t have an explicit request to clear the cache, but we had something very close to it. A Clear() in disguise.

Stepping through the code I isolate the method call unleashing the total annihilation of the cache: it is a call to ISQLQuery.ExecuteUpdate. And then it dawns on me – we are performing a native update/delete/insert operation on the database and NHibernate has just to assume that we may have changed something that has already been cached. Which means that the second level cache is completely wiped clean in the name of consistency. Ouch.

While it may make sense for NHibernate to keep the caches consistent knowing nothing about their implementation, we are actually using SysCache2 which employs SQL Server’s Query Notification system to be notified about changes in the data, automatically invalidating the related cache item. This is extremely cool – and a huge PITA to setup correctly – but now NHibernate has to ruin our fun and spoil the party. I start looking into options to limit the damage to the cache, supposing that at least it should be possible define some regions to be cleared, or maybe turn off the option completely.

While checking NHibernate sources reveals that there is an half-ported feature of Hibernate called query spaces that could be used to define regions that will be cleared, I wasn’t able to leverage it and I assume it was not fully implemented in NHibernate, though I could be wrong. You can find the relevant code in BulkOperationCleanupAction.

/// <summary>
/// Create an action that will evict collection and entity regions based on queryspaces (table names).
/// </summary>
public BulkOperationCleanupAction(ISessionImplementor session, ISet<string> querySpaces)
    //from H3.2 TODO: cache the autodetected information and pass it in instead.
    this.session = session;

    ISet<string> tmpSpaces = new HashSet<string>(querySpaces);
    ISessionFactoryImplementor factory = session.Factory;
    IDictionary<string, IClassMetadata> acmd = factory.GetAllClassMetadata();
    foreach (KeyValuePair<string, IClassMetadata> entry in acmd)
        string entityName = entry.Key;
        IEntityPersister persister = factory.GetEntityPersister(entityName);
        string[] entitySpaces = persister.QuerySpaces;

        if (AffectedEntity(querySpaces, entitySpaces))
            if (persister.HasCache)
            ISet<string> roles = session.Factory.GetCollectionRolesByEntityParticipant(persister.EntityName);
            if (roles != null)
            for (int y = 0; y < entitySpaces.Length; y++)
    spaces = new List<string>(tmpSpaces);

private bool AffectedEntity(ISet<string> querySpaces, string[] entitySpaces)
    if (querySpaces == null || (querySpaces.Count == 0))
        return true;

    for (int i = 0; i < entitySpaces.Length; i++)
        if (querySpaces.Contains(entitySpaces[i]))
            return true;
    return false;

As you see, it looks like there is a possibility to define query spaces so that the AffectedEntity method can discard only the relevant regions, yet I couldn’t find a public method where to inject the query spaces. So no luck leveraging NHibernate features for me, I would have to solve the issue myself in the less hacky way possible. It turns out that there is a workaround to this issue that is also quite easy to implement.

The solution is simply to remove all the ISQLQuery.ExecuteUpdate calls and execute the relevant statements over ADO.NET, reusing the connection and enlisting the command in the existing transaction. Doing this we effectively perform the update/insert/delete statements behind NHibernate’s back, however we will not need to worry about inconsistencies since SysCache2 is there to save the day. Thank you, SysCache2: the pain of setting up the permissions and double checking that you were working correctly was totally worth it.

Internal blogs: why you need one

Tuesday, 28 August 2012 00:00 UTC
Dry stone wall

Quite often I find myself writing a small note about a subject: sometimes it grows to the size of an article. I discovered that, most of the times, when it grows to that size, it is worth sharing with other developers.

So, I wanted our developers to access the information I was writing, and possibly to comment on that and generate some discussion. How shall I proceed in publishing this sort of information?

Option 1: Email

One option to let our developers read my ramblings could be to abuse use emails, targeting our internal developer distribution list. Although I can instantly reach every developer in an asynchronous way, it is a very inefficient way to communicate ideas for several reasons.

First of all, I am effectively spamming our developers’ (already overloaded) inboxes. Most probably, when they receive the email, they couldn't care less about the topic. In the best case scenario, the developer will ignore it and proceed with her work, until an appropriate moment to check the inbox comes. Most of the times the average developer, to put it simply, has to delve into an humongous amount of emails, of which statistically only a selected few are really relevant. The signal to noise ratio of email is way too high, which means that your message may get lost.

Let’s imagine for a moment that our company has a very efficient email policy, and only highly relevant content is sent to the right people. Who knows, maybe the company banned the use of CC fields altogether. Our developers actually get to find the email and read it at a convenient time: what will the developer do, if he has any comments? Write a (personal) reply to the author? Write a reply to the whole distribution list (which may or may not go against the "efficient email policy" hypothesis)? Chances are that a discussion will either take place between a developer and the author or not at all. Sure, a developer might actually stand up and walk to the author to discuss it – pffft, yeah sure. Bonus points if the developers sits in the same room. Maybe they will meet at the coffee machine. The point is, do you really want to manage internal information sharing like that?

If those reasons are not enough to turn away from this medium, consider this scenario: a new developer is hired, during the first weeks he asks about how topic X is handled in the application, or what the rationale behind a design choice is. Easy, just point him to the email Jim sent in October 2009, everything is explained in detail there. Oh wait, he never got the email because he was not employed at the time.

Apparently we found out that email is not the right medium for this sort of publication. So why don't we publish it in our internal wiki? (You do have a wiki, right?)

Option 2: Wiki

After all, a wiki is all about quickly editing and sharing stuff. With this solution, we do not need to look for topic X in our mail client, and everyone can access the content. A wiki could work, depending how it is perceived and used by your peers at the moment. I'm supposing that you have a wiki already running – if you don't, consider creating one for easily editing the documentation. The wiki, though, is seen as a collaboration platform and is more about creating permanent content and collaboratively editing it. According to Wikipedia, the essence of the Wiki concept can be expressed through the following concepts:

  • A wiki invites all users to edit any page or to create new pages within the wiki Web site, using only a plain-vanilla Web browser without any extra add-ons.
  • Wiki promotes meaningful topic associations between different pages by making page link creation almost intuitively easy and showing whether an intended target page exists or not.
  • A wiki is not a carefully crafted site for casual visitors. Instead, it seeks to involve the visitor in an ongoing process of creation and collaboration that constantly changes the Web site landscape.

This is obviously not what we are looking for. We would like to have a place where people could publish their thoughts, ideas, technical nuggets, discoveries – the nature of the wiki leaves these out of the door. And that is good, but not useful for purposes that involve users posting opinionated facts and ideas generating discussion. We don't want to write documents in a collaborative way: we want to bring our ideas out. Which brings us to our third option.

Option 3: Internal blog

I am not fond of the definition of "corporate" internal blog. The corporation part makes it sound big, it may make you think that it is useful in a company with a large number of employees. That is not the case –  you can see now that an internal blog can address the previously mentioned needs that exist in companies of any size.

Looking for an old topic that you remember being posted some time ago? Search the blog. Want to provoke discussion? Write a (controversial?) post and let people comment on it. Do you have an idea? Let it run loose.

Why would anyone write a blog post?

All things considered, developers rarely write documentation, why would they want to write a blog post?

Two of the loudest .NET developers that I use to read, Scott Hanselman and Jeff Atwood, have compelling arguments to get developers to blog. The concept at the base of these arguments stems from a thought by Jon Udell: the gist of it is that the time that you have on Earth is limited, which means the amount of keystrokes you have before your time comes is not infinite. When you recognize this and want to treat it economically as a limited resource, you may want to optimize the use of it: why waste your keystrokes for one person only, when you can get the message across a much larger audience and affect a greater amount of people?

For the egomaniacs between us, having articles published under our own name may already be motivating enough. But what about the rest? We may be induced to think that our topics are not interesting enough, or that we may be *gasp* wrong. Are you asking me to publish this? No way, I suck way too much, there are way too many smart people that are ready to criticize me. They will be knocking on my door and screaming "SOMEBODY'S WRONG ON THE INTERNET! Go get him!".

Stop caring. Here's a tip: everybody sucks at something at some level. The only way to improve is to embrace the suck, accept it and move on, keep practicing and you will eventually be an expert. Some people are naturally gifted with the attitude of loving failure, they just keep going at a tough subject until it is not difficult anymore, then the fun is gone and they want to move on. Other people need to train this to reach levels that they never thought were remotely achievable. Malcolm Gladwell wrote that you need to invest 10,000 hour of deliberate practice to become successful in what you are doing. Note that the "deliberate" part of the practice makes a lot of difference, and writing is a huge help to deliberately practice the art and it provides us with immediate feedback and clarity of mind.

Scott says that all developers should blog: I think that every developer should write some notes down, at least. Publishing them in a blog is an improvement over the process of self-improvement, but you can get there step by step, so don't fret, you don't need to publish everything right now.

If you think that you lack the time for blogging, consider this thought also contained in the previously cited post by Jon Udell:

[...] when people tell me they’re too busy to blog I invoke the principle of keystroke conservation. Was the email message you wrote to three people possibly of use to thirty, or three hundred, or thirty thousand? If so, consider blogging it — externally if that’s appropriate, or internally otherwise. Then, if you want to make sure those three people see the message, go ahead and email them a pointer to it.

Page 2 of 4