caching queries

[meissnersdc]

I have several small tables in the db with data that does not change frequently. Rather then hit the db with every page load, is there a way to cache this data and share it between queries? Maybe in static variables? If the data does change in a table is there an easy way to tell? Check row count? Last write time?

Computer Consultant - www.meissnersd.com

[bruce]

What technology are you using?

ASP.net has built in caching for dataset.


[b]quote:Originally posted by meissnersdc

I have several small tables in the db with data that does not change frequently. Rather then hit the db with every page load, is there a way to cache this data and share it between queries? Maybe in static variables? If the data does change in a table is there an easy way to tell? Check row count? Last write time?

Computer Consultant - www.meissnersd.com
</blockquote id="quote"></font id="quote">

[pjoyce]

Gosh, there are so many answers to that question.. First let me say it's always a good idea to cache if your app allows it. First, it you're not using ASP.NET stop reading. From your description it looks like you want to use the built-in caching mechanism.

Personally, I have a data access component. In that component I have a GetStates() method which returns a dataset filled with a list of US states and abbreviations. The table is used a lot but it isn't likely to change really soon. So my call to the data access object goes like this:

Check the HttpContect.Current.Cache["StateList"] to see if it's null. If it isn't return it. If it is null it means that the cache hasn't been filled yet so I read from the database and then fill the cache. remember that the cache is application-wide so it will apply to every user. You don't want to store "Session-Type" data in the cache.

Check this link for more: msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaspcachingfeatures.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaspcachingfeatures.asp)

In additon to the above, you can also cache a page based on the value of a querystring parameter or a given time, etc.

As for "How to tell if the table has changed," there is always a lot of discussion about that. But IMHO, the best place to deal wit hthat is in the data access layer. Imagine that I have a SaveStateList(stateListDS) method also. In that method I would save the data to the DB and then clear out the cache (set Context.Cache["StateList"]=null) This forces the call to the DB to re-read from the database and re-populate the cache.

Finally, if you need to change the table by hand, the trick is to make a small change to the web.config file. Changing the web.config always forces the application to restart and clear all of the cache, again forcing your DAL to re-populate it.


[b]quote:Originally posted by meissnersdc

I have several small tables in the db with data that does not change frequently. Rather then hit the db with every page load, is there a way to cache this data and share it between queries? Maybe in static variables? If the data does change in a table is there an easy way to tell? Check row count? Last write time?

Computer Consultant - www.meissnersd.com
</blockquote id="quote"></font id="quote">