In an earlier blog post, I wrote about how we used SQL Server’s Query Store feature to identify and troubleshoot query performance. In this blog post, I’ll be examining how we improved our use of one of these queries by leveraging caching in our application layer.
The query and code samples below have been simplified for brevity and clarity.
The Query and Code
The query we’re troubleshooting in this article was our most frequently executed query:
SELECT * FROM ConfigurationValue WHERE Id = @id;
The query itself is simple enough, and while not being one of our most expensive queries in terms of total time spent, this query was called about 500,000 times per hour in peak traffic because of code pulling values from this table multiple times per page load or API call.
However, even though the Query Store reports didn’t mark this as a top contender in total execution time, it certainly is one of the most taxing in terms of network activity, accounting for nearly 12% of our total query executions per day.
Analyzing the Problem
The table that we’re querying here contains a reasonably small amount of data that is rarely updated, giving us a great opportunity to reduce database queries by caching the table’s data in our application layer.
It is important that updates to this table’s data are picked up promptly by the application layer, however, so we’ll need to consider a way to push the update up to the cache as well.
Building the Solution
The first step would be to select a method of caching the data. We already made use of caching for other data, so we opted to use the same system for caching this data. Generally, any choice of cache, such as .NET’s MemoryCache class, should be fine to utilize for this purpose.
The second step was to integrate the cache into the data access. In our case we have an interface and class in place for querying this data, so we were able to create a new implementation of the interface that would populate/refresh the cached data if needed, and then query against the cached data. We then plugged in this new class in place of the old data access class in our dependency injection setups.
<example code for this data access class>
public class CachedConfigurationValueAccessor : IConfigurationValueAccessor
{
private ConfigurationValueAccessor _accessor;
private IMemoryCache _memoryCache;
private void UpdateCache()
{
var cacheData = _accessor.GetAll()
.ToDictionary<int, ConfigurationValue>(v => v.Id, v => v);
_memoryCache.Set("ConfigurationValues", cacheData, TimeSpan.FromHours(1));
}
public ConfigurationValue GetById(int id)
{
if(!_memoryCache.TryGetValue("ConfigurationValues", out cacheData)
{
UpdateCache();
}
return cacheData[id];
}
//Remaining method implementations
}
<example code end>
Lastly, we need to consider the wrinkle of making sure any changes are recognized in real time. Depending on how much of a delay is acceptable, one possible solution would simply be to have a relatively short cache expiration policy so that changes get picked up quickly as the stale cached data gets overwritten by with the fresh data. In our case we opted to alter the logic of our edit pages that update this data so that they automatically push the updated data to the cache in real time.
The Impact
After implementing this caching solution, we saw those 500,000 queries per hour reduced to just 25 queries for the full table per hour, with that number determined by our chosen caching policy. This resulted in a 99.9956% reduction in the number of queries against that table, and a 99.975% reduction in query processing time for this query!
While the impact of any individual execution of this query wouldn’t be particularly noticeable, the drastic reduction in the number of times we executed the query contributed significantly to the 12% reduction in CPU usage that we saw in this maintenance cycle, helping keep our servers and website responsive!