Cache results from sql database, or query each time?

Question

I'm generating pages based on an sql query.

This is the query:

CREATEPROCEDURE sp_searchUsersByFirstLetter 
    @searchQuery nvarchar(1)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT UserName
    FROM Users Join aspnet_Users asp on Users.UserId = asp.UserId
    WHERE (LoweredUserName like @searchQuery + '%')

I can call this procedure for each letter in the alphabet, and get all the users that start with that letter. Then, I put these users into a list on one of my pages.

My question is this: would it be better to cache the list of users to my webserver, rather than query the database each time? Like this:

HttpRuntime.Cache.Insert("users", listOfUsersReturnedFromQuery, null, DateTime.Now.AddHours(1), System.Web.Caching.Cache.NoSlidingExpiration);

Its ok for me if the list of users is an hour out of date. Will this be more efficient that querying the database each time?

Answer 1

Using a cache is best reserved for situations where your query meets the following constraints:

• The data is not time critical, i.e. make sure a cache hit won't break your application by causing your code to miss a recent update of the data.
• The data isn't sequenced, i.e. A, B, C, D, E are cached, F is inserted by another user, your user inserts G and hits the cache, resulting in ABCDEG instead of ABCDEFG.
• The data doesn't change much.
• The data is queried and re-used frequently.

Size isn't really a factor unless it's going to really tax your RAM.

I have found that one of the best tables to cache is a settings table, where the data is practically static, gets queried on nearly every page request, and changes don't have to be immediate.

The best thing for you to do would be to test which queries are performed most, then select those that are taxing the database server highest. Out of those, cache anything you can afford to. You should also take a look at tweaking maximum cached object ages. If you're performing a query 100 times a second, you can cut that rate down by a factor of 99% by simply caching it for 1 second, which negates the update delay problem for most practical situations.