How can I make sure my DB cache is current?

views:

answers:

How can I make sure my DB cache is current?

I have an expensive operation that calls the database, and populates an array of objects for use in comboboxes. Because it can take ~2 minutes, I cache it on start up.

When the user needs to use these comboboxes, I need to make sure they have the current data. How can I do this?

+1 A:

I'm going to guess the data changes rather frequently. That being the case, I'd create a trigger on INSERT, UPDATE or DELETE that calculates a checksum/CRC that gets stored in some scratchpad table. Retrieve this one value when you populate the listbox, then compare to this value. That will be a good indicator as to whether the data has been changed.

A simpler alternate solution would be to just store the date/time of the last update rather than a checksum.

In one app, we added two columns to the table: IsActive and LastUpdate. IsActive is used in lieu of deleting records outright. LastUpdate is a timestamp indicating the last time each record has been updated. A SELECT WHERE LastUpdate > '<your-previous-check>' will get you a list of just the changes you need to apply.

Bob Kaufman 2009-09-21 20:58:03

The database may be changed by more than one user though.

Malfist 2009-09-21 20:58:36

@Malfist - imagine you create a class with a timestamp, and list of lookupcode/values. On startup you load the list and use the current time. Later you can call a proc with the timestamp of your cache, and if the timestamp in the database is after the time you sent in, it returns you a new loopup list.

overslacked 2009-09-21 21:05:59

er, lookup list.

overslacked 2009-09-21 21:06:46

we use a similar system where the big tables have a timestamp of last update and refresh only when that changes

JDunkerley 2009-09-21 21:12:26

ansaurus

tags:

views:

answers:

How can I make sure my DB cache is current?

related questions