Caching at startup

Originally posted by downeytim We have a large DB with around 75 "code" tables.  These are tables that supply values for Drop Down style fields on screens such as State in an address.  Many of these lists are user maintained but change infrequently after the intial configuration of the software.  We want to add these to the cache at startup if possible.    I was trying to find a way to maybe store them locally and load them from a file then refresh the list with any changes.  All of our tables have a SQL Server timestamp field on each row for concurrency.  Some of our customers have a bandwidth consideration to the local office where the application runs.  So moving massive amounts of data accross the wire from the DB server to the client is an issue in some places.   Can anyone give me a hint as to the best method for doing this?   Thanks.

You can store the reference data locally quite easily using the DevForce EntityManager's

     _em1.CacheStateManager.SaveCacheState(path)

method. Just load up an empty cache from the database (once) with the entities you want, then use that method to save them to the local disk. Thereafter at startup you can import the saved items back into the local cache using

     _em1.CacheStateManager.RestoreCacheState(path);

There are various schemes you could use to permit the local app to determine if changes have been made in the reference tables. One very efficient one is to put triggers on each of the reference tables that write an audit record to another table whenever the reference table gets updated. The audit record should include the name of the table updated, the primary key for the record updated, and the nature of the update (e.g., delete, mod, or insert).

Then you can base an entity in your model on the audit table, and at app startup always retrieve all records entered there after the time of your last reference cache update.  If the audit table shows no activity since your last reference cache update, you just load the one you've got.  Otherwise, you'll have an exact record of what has changed and how, so that you can very efficiently update your reference cache.





Edited by IdeaBlade - 01-Apr-2010 at 10:37am