How to cache database tables to prevent many database queries in Asp.net C# mvc

Question

I build my own cms using Asp.net mvc 4 (c#), and I want to cache some database data, likes: localization, search categories (it's long-tail, each category have it's own sub and sub-sub categories), etc..

It's will be overkill to query the database all the time, because it can be more than 30-100 queries for each page request, however the users update those database rarely

So what is the best way (performance and convenience) to do it?

I know how use the OutputCache of the action, but it's not what I need in this situation , it's cache the html, but what I need is for example, that my own helper @html.Localization("Newsletter.Tite") will take the value of the language, or any another helper that interact with data etc.

I think (not really sure) that I need to cache the data I want, only when the application is invoke for the first time, and then work with the cache location, but I don't have any experience even about to it.

Answer 1

You could use the built-in MemoryCache to store entire resultsets you have retrieved from the database.

A typical pattern:

MyModel model = MemoryCache.Default["my_model_key"] as MyModel;
if (model == null)
{
    model = GetModelFromDatabase();
    MemoryCache.Default["my_model_key"] = model;
}

// you could use the model here

Answer 2

I had to cache common database data like data that were displayed in drop downs. I used MemoryCache. And I used Entity Framework code first and Autofac for dependency injection.

Here is part of what I did in my solution, might not work for you but it worked for me, not perfect though but needs a lot of cleaning up to do.

My ICacheManager interface:

public interface ICacheManager
{
     T Get<T>(string key);

     void Set(string key, object data, int cacheTime);

     bool IsSet(string key);

     void Remove(string key);

     void Clear();
}

My CacheManager class:

public class CacheManager : ICacheManager
{
     private ObjectCache Cache
     {
          get
          {
               return MemoryCache.Default;
          }
     }

     public T Get<T>(string key)
     {
          return (T)Cache[key];
     }

     public void Set(string key, object data, int cacheTime)
     {
          if (data == null)
          {
               return;
          }

          CacheItemPolicy policy = new CacheItemPolicy();
          policy.AbsoluteExpiration = DateTime.Now + TimeSpan.FromMinutes(cacheTime);

          Cache.Add(new CacheItem(key, data), policy);
     }

     public bool IsSet(string key)
     {
          return (Cache.Contains(key));
     }

     public void Remove(string key)
     {
          Cache.Remove(key);
     }

     public void Clear()
     {
          foreach (var item in Cache)
          {
               Remove(item.Key);
          }
     }
}

An extension class for my caching:

public static class CacheExtensions
{
     public static T Get<T>(this ICacheManager cacheManager, string key, Func<T> acquire)
     {
          return Get(cacheManager, key, 60, acquire);
     }

     public static T Get<T>(this ICacheManager cacheManager, string key, int cacheTime, Func<T> acquire)
     {
          if (cacheManager.IsSet(key))
          {
               return cacheManager.Get<T>(key);
          }
          else
          {
               var result = acquire();

               cacheManager.Set(key, result, cacheTime);

               return result;
          }
     }
}

And this is how I would use it in my repository class. This method returns a list of all my banks which is displayed in a drop down.

public class BankRepository : RepositoryBase<Bank>, IBankRepository
{
     private readonly ICacheManager cacheManager;
     private const string BanksAllCacheKey = "banks-all";

     public BankRepository(IDatabaseFactory databaseFactory, ICacheManager cacheManager)
          : base(databaseFactory)
     {
          Check.Argument.IsNotNull(cacheManager, "cacheManager");

          this.cacheManager = cacheManager;
     }

     public IEnumerable<Bank> FindAll()
     {
          string key = string.Format(BanksAllCacheKey);

          return cacheManager.Get(key, () =>
          {
               var query = from bank in DatabaseContext.Banks
                           orderby bank.Name
                           select bank;

               return query.ToList();
          });
     }
}

I hope this helps. It's a very simple implementation but it works for me. There are many articles online on how to use a caching strategy in ASP.NET MVC. Just Google it.

Answer 3

There are several things to consider here before choosing your implementation of caching, but one of the main things you have to decide is at what point do you want the caching to occur - at data access, the model creation or UI generation? One or all of these places?

You have several options but for general data caching you can use the System.Runtime.Caching.MemoryCache, or for something much more flexible and rewarding you can look at an implementation using NoSQL like Redis.

You could use MemoryCache for data caching, but use Redis for caching aggregates making up your view models. If you went with Redis, you could of course handle all your caching using this. The benefit being that all your data would become persistent across application restarts. The disadvantage being that it becomes an additional requirement for running your CMS.

Other things to factor in are consistency (using some IoC would help) and allowing data to be voided once it updates. Therefore having some means of updating the cache.

With regards to the best approach, in your case if you are creating a new CMS application, start small/simple and build up in steps. You may not get it perfect first time but as long as you are consistent and clear in your approach it should be easy to build upon what you have done or swap out and try something different/better.