Better performance to Query the DB or Cache small result sets?

Say I need to populate 4 or 5 dropdowns w/ items from a database. Each drop down will have < 15 items in it. These items almost never change.

Now I could query the DB each time the page is accessed or I could grab the values from a custom class that would check to see if they already exist in ASP.Net's cache and only if they don't query the DB to update the cache.

It's trivial for me to write but I'm unsure if the performace would be better or not. I think it woul开发者_如何学编程d be (although not likely anything huge).

What do you think?

---

When dealing with performance issues you should always:

1. Do things the simplest way first (avoid premature optimisation)
2. Performance test your code with set performance goals (e.g. 200ms response time under load of N concurent users)
3. Then, IF your code doesn't perform then profile your code to determine what is slow, and profile your proposed performance fixes to accurately measure what the real-world performance change will be.

Having said that then yes, what you are suggesting seems sensible (you would usually expect an in-memory cache to be quicker than a database), however it also depends on what data is being returned, what the memory load of your application is, how expensive the query is, what the query parameters are etc...

You should performance test your changes before and after to determine the actual effect of your changes (including things like memory load), and you should only really be doing things like this once you have identified that these dropdowns are the cause of an unacceptable performance problem.

---

That's what System.Web.Helpers.WebCache class exists for.

---

IO is usually more expensive than memory operations (by orders of magnitude). Especially if your database is in another machine, then you would even be using network resources, and it will definitely be faster to just use the cache.

But indeed, optimize in the end when you have really identified it as a performance bottleneck by measuring.

---

Quick answer to your question:

1. Use the built in .Net cache.

Additional points to ponder over..

1. Preferably, retrieve all master data in a single database retrieval (think stored procedure and dataset): though, I do not advocate the used of stored procs in all scenarios.
2. As you rightly said, ensure that your data access layer checks the cache before making a round trip to the database
3. Also, as your drop down values do not change very often; do remember to keep a long expiry duration
4. Finally, based on your page design you could also look at Fragment Caching (partial page caching: user controls) which could give you bigger benefits since now you neither access the data cache nor the database.

Performance: Again, the performance depends more on the application's load as compared to your direct round trips for fetching the master data. Put simply, As Thomas suggested use the cache class!