How should I query the DB for a specific key without querying every time the feature is called?

Question

We have a large legacy Java-based project, the availability of certain features throughout the application is determined by its corresponding value in a "feature_enabled" table in a SQL database. Much like Windows enables features based on Registry values.

Lately, we have been working to significantly reduce the number of SQL queries made in a specific part of the app. So we wrote a caching class that calls the query once and caches the results. Unfortunately, we can't rewrite the entire functions as other parts of the app are dependent on it. So, when our team calls the function (which executes the SQL query), we supply the entire class with the cached table property. The function then simply checks if the property has been set. If so, then uses the data from there, otherwise runs a fresh query.

Problem is we would like to add this caching technique as a feature which can be enabled like others. Querying the DB every time the cached db is called to see if the feature is enabled would undo whatever query saving we are doing. So my question is what is a good pattern of design to accomplish this?

Answer 1

Do you need to be able to arbitrarily turn the "Caching Enabled" feature on or off while the app is running?

If you do need to change it during runtime:

Here are a few possibilities:

• Create a separate, single row table that only holds this one value. This will minimize the cost of the query. In the real-world, the advantage you get from this may not even been measurable, especially if the config table contains just a few (dozen) rows.
• Put this flag in a configuration file on the file system. Lot's of problems here. You'll have to continually watch the file for changes, which takes time and adds complexity. Plus, there's the problem of making sure the config file is properly deployed everywhere.
• Build a way for the configuration editor to tell the running app that the caching flag has changed. This probably only makes sense if the editor and the main app are part of the same process. If they are, just add a NotifyCachingSettingChanged method to your caching class.

If you don't:

Have the function check its own cached tabled to see if the feature is enabled. If it is, return the cached table. If not, go query the DB for the table. When you need to change the caching flag, change it in the DB and the restart the app's process to empty the cache and force a reload.

Answer 2

When you find yourself in a polling situation (like wishing you knew when something changes) it's time to turn to events. What are events? Well at the heart it's polling, done better.

Let's say you have ten things that enter the database that you wish you knew the minute they changed. You could hit the database ten times a minute. Or you could just query once a minute.

This is how interrupts work in your computer's architecture. One register with many bits is checked in a tight loop each clock cycle. If any bit is non zero something is interrupting normal program flow and waiting to be dealt with. Which bit tells you what's waiting.

Done at the database level this scheme could be done with a string. Considering query overhead a reasonably sized string should perform nearly as well as an int. So keep a time stamped table of named events. It works like this:

• Query the table for events newer than your last check.
• Delete events older than your last check.
• Deal with events (query whatever needs to be queried)

The order you feel like putting that in has more to do with debugging than performance.

You'll need some way to create events in the event table. That could be done in the db itself or it can be done in whatever updates the db. When you update the Foo table you could also drop a timestamped Foo string in the event table. When you delete the Bar entry from the Baz table you could drop a timestamped Baz-Bar string in the event table. It's gloriously asynchronous and scales very well. 10, 100, 10000 kinds of events load the db the same way.

Now you know when a cached value is out of date so you can update it timely.

If you're not the only one watching these events deleting gets a bit tricky. It is possible to come up with a scheme to register observers of events and only delete them once everyone has consumed them but that's brittle. One observer goes down and the DB is filling up. Better to define a window of time that events will live for and delete ones that have left the window.

Of course all of this could be avoided if you could find a way to use an alternative to the DB such as a messaging system. Make no mistake, this is a hack. It's a hell of a lot better hack than polling everything individually but it's still a hack.

Answer 3

A completely different way to solve the polling problem is to wrap the DB. The wrapper exposes the same interface the DB would. Have everything talk to the DB wrapper. The wrapper talks to the DB saying whatever was said to it.

You might even use a traffic sniffer for this. That way nothing needs to be reconfigured. Just find a way to send it out as a stream.

The advantage here is now nether the DB nor the updating processes need to know what values are tied to events. Only the wrapper does.

Now the wrapper can send out events to registered listeners. Do this over rest and the listener can be on any box.

You could have the wrapper filter down to just what the observers care about to keep traffic down but to make sure this doesn't slow the DB if there are many observers you may want to dedicate one box for an unfiltered feed.

This is closer to how big data people solve this problem. Depending on your real needs consider this approach.