2

I've inherited a system where data from a SQL RDBMS that is unlikely to change is cached on a web server.

Is this a good idea? I understand the logic of it - I don't need to query the database for this data with every request because it doesn't change, so just keep it in memory and save a database call. But, I can't help but think this doesn't really give me anything. The SQL is basic. For example:

SELECT StatusId, StatusName FROM Status WHERE Active = 1

This gives me fewer than 10 records. My database is located in the same data center as my web server. Modern databases are designed to store and recall data. Is my application cache really that much more efficient than the database call?

The problem comes when I have a server farm and have to come up with a way to keep the caches in sync between the servers. Maybe I'm underestimating the cost of a database call. Is the performance benefit gained from keeping data in memory worth the complexity of keeping each server's cache synchronized when the data does change?

gilly3
  • 87,962
  • 25
  • 144
  • 176
  • 1
    At least, it saves you from borrowing a connection from the pool, letting connections to be used for something that really needs the database. This can reduce connection pool latency in high load scenarii. But we all know that caching is a tough subject, so your question really makes sense. – Guillaume Darmont Jun 18 '13 at 21:50

2 Answers2

3

Benefits of caching are related to the number of times you need the cached item and the cost of getting the cached item. Your status table, even though only 10 rows long, can be "costly" to get if you have to run a query every time: establish connection, if needed, execute a query, pass data over the network, etc. If used frequently enough, the benefits could add up and be significant. Say, you need to check some status 1000 times a second or every website request, you have saved 1000 queries and your database can do something more useful and your network is not loaded with chatter. For your web server, the cost of retrieving the item from cache is usually minimal (unless you're caching tens of thousands or hundreds of thousands of items). So pulling something from the cache will be quicker than querying a database almost every time. If your database is the bottleneck of your system (which is the case in a lot of systems) then caching definitely is useful.

But bottom line is, it is hard to say yes or no without running benchmarks or knowing the details of how you're using the data. I just highlighted some of the things to consider.

Tombala
  • 1,660
  • 9
  • 11
1

There are other factors which might come into play, for example the use of EF can add considerable extra processing to a simple data retrieval. Quantity of requests, not just volume of data could be a factor.

Future design might influence your decision - perhaps the cache gets moved elsewhere and is no longer co-located.

There's no right answer to your question. In your case, maybe there is no advantage. Though there is already a disadvantage to not using a cache - you have to change existing code.

RJ Lohan
  • 6,497
  • 3
  • 34
  • 54
  • That's just the thing. I *do* have change existing code - to support a server farm. I'd rather just remove all the caching code, so I guess I'm trying to justify that. – gilly3 Jun 18 '13 at 21:48
  • 1
    if you're trying to move to a server farm and are worried about stale cached items, then take a look at your DB server. If it is not under pressure and handles your load just fine, then get rid of caching. In the future, if SQL becomes the bottleneck, then you can add it back in a thoughtful way, as needed. – Tombala Jun 19 '13 at 15:07