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?
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.
In case if you have few servers memory cashing isn't so good, because it will take memroy in each server and in each w3p process of every server.
It will be also hard to maintain consistent data.
I would advise to choose from:
- basic output cache (assuming you are using MVC this is zero efforts and good imporevement)
- Db cache using smaller pre-calculated table where you have mapping from input string to 10 possible results
It really depends. Do you bottleneck at your database server (I would hope that answer is no)? If you are hitting the database 26 times, that is nothing compared to what typically happens. You should be considering caching data in a Dataset or some other offline model if you are hitting the database hundreds of thousands of times.
So I would say, no. You should be fine with your round trips to the database.
But there is no replacement for testing. That'll tell you for sure.
Considering that each DB call is always expensive in terms of network and DB load I would prefer to avoid such extra operations and cache items even they are requested few times per hour.
Only one opposite case I see - when amount of users in terms of memory consumption is a tons of megabytes.
Well Caching data and get back is fastest but it also depends on the data size...If there is large amount of data than it will cause performance issue.
So it almost depends on your requirement.
I would like you to suggest make use of paging or make use of mix mode by loading half of the user put in cache and than load the other data when require....
