For me it's not clear how your cache-implementation avoids database hits when you are performing queries.
Maybe you can explain that deeper?
Gladly.1. Does our versioned cache support query caching?
No.2. Why not?
We don't need query caching for the abovementioned entities of type MyGlobalVariable. So we didn't care about query caching.
By the way, we use Ehcache L2-caching when querying other entity types. Our versioned cache and the Ehcache caches do not impede each other because they work on distinct sets of entities.3. Could our versioned cache support query caching?
I think: Yes. But I have not thought about that before you asked, Pb00067.
The approach would be the same as used by the Ehcache query cache (a good explanation can be found on http://tech.puredanger.com/2009/07/10/h ... ery-cache/
) apart from a small but important difference: The table-specific timestamps are not cached but saved in the database. The query cache and the entity cache would share the same table-specific timestamps. That's all.
Then the query cache would behave like the entity cache: If the table MyGlobalVariable is queried the first time within the current user transaction, the table-specific timestamp is fetched from the database. If the timestamp is a new one, then a new empty internal cache is created for this timestamp. Then the cache delegates to the timestamp-specific internal cache.4. How would the versioned query cache work for queries that span over more than one table?
Example: If a query has to access the tables MyGlobalVariable1 and MyGlobalVariable2 the first time within the current user transaction then the timestamps for those two tables are fetched from the database. If there is already an internal cache for the combination of these two timestamps, then this internal cache is used. Otherwise a new internal cache is created and used.
Hint: Each timestamp is fetched not more than once per user transaction.5. Summarizing: versioned cache and L2-cache - do they fit together?
For me it looks like.
If the term versioned cache
sounds too unfamiliar, you may name it a snapshot cache because the cache holds effectively database snapshots.
And, Pb00067, you might be interested in the final bit as well:6. Is there a way to avoid the database hits entirely or almost entirely?
Yes if you are able to announce changes in the database in advance (see details below). This should be possible for rarely changing entities.
Let me explain the approach in detail by an example:
Let's say the contents of table MyGlobalVariable are rarely changing, like 10 times a month but you don't know exactly when it happens.
-- Configuration --
First you configure five minutes as TTL (time-to-live) for the cache that belongs to the table MyGlobalVariable. By this configuration a property called DeactivateCachingAfterTime (something like "2012-12-31 11:59:59") specific to the table MyGlobalVariable is persisted somewhere in the database. This property is initially and most of the time null.
-- User perspective --
If a user wants to make changes to the table MyGlobalVariable, then the user must set the property DeactivateCachingAfterTime to a time at least five minutes (the configured TTL) in advance. After the DeactivateCachingAfterTime is reached the user is allowed to change the contents of the table MyGlobalVariable. As soon the user has finished his changes, he should set the property DeactivateCachingAfterTime to null again.
If the user is another system or the user wants to schedule his change (he does not want to wait five minutes in front of the application), then think about a cron job or something similar that makes the change automatically when the DeactivateCachingAfterTime is reached.
-- Cache implementation --
How is the versioned cached affected by the property DeactivateCachingAfterTime?
Before the timestamp is fetched from the database the cache checks when the most recent timestamp was fetched from the database. If this happened within the last five minutes (the configured TTL), then the most recent timestamp is used, i.e. the database is not queried (this is the optimization). Otherwise the timestamp and the property DeactivateCachingAfterTime are fetched from the database. If the fetched DeactivateCachingAfterTime is not null, then the versioned cache will fetch the timestamps for each user transaction as soon the DeactivateCachingAfterTime is reached, i.e. the optimization will be switched off. As soon the fetched expiration is null, the optimization is switched on again.
-- Caching efficiency --
As long the user changes the contents of the table (this may add up to an hour in each month) as long the timestamps are fetched from the database for each user transaction (what is a serious problem only if you have a million transactions in this hour). But for the rest of the time (the month minus the hour), the timestamps are fetched only once in five minutes (the configured TTL) whether you have to serve either one hundred or a million transactions, i.e. more than 99% of the cacheable database queries might not hit the database.