What is the best approach for caching database queries

I'm writing a management intelligence application which requires quite a lot of complex database querying with some queries being quite expensive. To aid performance I'm using Memcached quite heavily to store as much as I can in memory.

This has led to quite a lot of duplication in my code which I'm eager to get rid of and build a cleaner data access solution. Quite a lot of my data access functions have ended up looking like this..

public int NumberOfTimeouts(DateTime date, int? applicationId)
{
    var functionCacheKey = "NumberOfTimeouts";
    var cacheKey = string.Format("{0}-{1}-{2}-{3}", RepositoryCacheKey, functionCacheKey, date, applicationId);
    var cachedNumberTimeouts = _cache.Retrieve(cacheKey);
    if (cachedNumberTimeouts != null)
    {
        return (int)cachedNumberTimeouts;
    }

    //query logic here, calculates numberOfTimeouts

    UpdateCache(date, cacheKey, numberOfTimeouts);
    return numberOfTimeouts;
}

I'm just not too sure what the standard approach is to this, could it involve using a custom attribute class or something similar?

Upvotes: 2

Answers (2)

Fendy

Reputation: 4643

This is a cross-cutting concern. The Decorator pattern may be applicable here. I may be inexperienced in this pattern, however I will give it a shot

// model
public class CustomObject
{
    public int Id { get; set; }
}
// interface
public interface IRepository<T>
{
    IEnumerable<T> Find(Expression<Func<T, bool>> expression);
}
public interface ICacheableRepository<T>
{
    IEnumerable<T> Find(Expression<Func<T, bool>> expression, Func<int> cacheKey);
}
public interface IRepositoryCacheManager<T>
{
    IEnumerable<T> Get(int key);
    bool Any(int key);
    void Add(int key, IEnumerable<T> result);
}
// cache manager
public class RepositoryCacheManager<T> : IRepositoryCacheManager<T>
{
    private Dictionary<int, IEnumerable<T>> cache = new Dictionary<int,IEnumerable<T>>();
    #region IRepositoryCache<T> Members

    public IEnumerable<T> Get(int key)
    {
        return cache[key];
    }

    public bool Any(int key)
    {
        IEnumerable<T> result = null;
        return cache.TryGetValue(key, out result);
    }

    public void Add(int key, IEnumerable<T> result)
    {
        cache.Add(key, result);
    }

    #endregion
}

// cache repository decorator
public class CachedRepositoryDecorator<T> : IRepository<T>, ICacheableRepository<T>
{
    public CachedRepositoryDecorator(IRepositoryCacheManager<T> cache
        , IRepository<T> member)
    {
        this.member = member;
        this.cache = cache;
    }

    private IRepository<T> member;
    private IRepositoryCacheManager<T> cache;

    #region IRepository<T> Members

    // this is not caching
    public IEnumerable<T> Find(Expression<Func<T, bool>> expression)
    {
        return member.Find(expression);
    }

    #endregion

    #region ICacheableRepository<T> Members

    public IEnumerable<T> Find(Expression<Func<T, bool>> expression, Func<int> cacheKey)
    {
        if (cache.Any(cacheKey()))
        {
            return cache.Get(cacheKey());
        }
        else
        {
            IEnumerable<T> result = member.Find(expression);
            cache.Add(cacheKey(), result);
            return result;
        }
    }

    #endregion
}
// object repository
public class CustomObjectRepository : IRepository<CustomObject>
{
    #region IRepository<CustomObject> Members

    public IEnumerable<CustomObject> Find(Expression<Func<CustomObject, bool>> expression)
    {
        List<CustomObject> cust = new List<CustomObject>();
        // retrieve data here
        return cust;
    }

    #endregion
}
// example
public class Consumer
{
    // this cache manager should be persistent, maybe can be used in static, etc
    IRepositoryCacheManager<CustomObject> cache = new RepositoryCacheManager<CustomObject>();
    public Consumer()
    {
        int id = 25;

        ICacheableRepository<CustomObject> customObjectRepository =
            new CachedRepositoryDecorator<CustomObject>(
                cache
                , new CustomObjectRepository()
                );
        customObjectRepository.Find(k => k.Id == id, () => { return id; });
    }
}

Please note:

I haven't tested this code, don't know whether it is fully functional or not. I just describe the illustration
Yes, this has code smell by having the ICacheableRepository overloading for Find, however I am incapable in using Expression as Key in Dictionary

The pros:

This CachedRepositoryDecorator can be used to ANY generic repository (reusable)
No caching logic inside the select process, emphasize SRP

The cons:

Hard to implement without ORM, maybe you will need some tweaks with reflection to make it works without ORM
Hard to understand at beginning
Hard to wire without DI Container

Credit to this article :)

Upvotes: 1

Loetn

Reputation: 4030

I think the Unit of Work pattern is what you need. More info:

Or a framework that contains the UoW pattern: https://github.com/riteshrao/ncommon

Upvotes: 0

What is the best approach for caching database queries

Answers (2)

Related Questions