I used to have a programming rule that said “never do with application code what you can do with SQL.” Because my latest programming adventure included three weeks of banging my head against the wall, I’ve decided to reconsider. Perhaps cranial impact has softened my resolve. My new rule says “the solution will come to me when I stop being stubborn.”

At some point, queries just become too complex to execute completely on demand and still see an acceptable response time. Either there are too many tables to join, too much data in the tables to sift through, too many calculations to perform, or too many users hitting the database all at once. Some pre-work may be required to put the data into a more accessible format.

The problem with pre-work is that it means doing some sort of processing on the data at a point before the user asks for information. Any difference in time between the processing and the request means that the user isn’t seeing up-to-the minute results, and the system is not operating in “real-time.”

An example of this is data warehousing. Records are copied from business systems to a secondary repository which is primed for analytical queries. One shortcoming of most data warehouse implementations is that when a transaction enters a business system, some time elapses before that transaction is fed to the data warehouse and becomes available to be analyzed.

One question raised by the dawn of real-time systems is, “if a tree falls in the forest and you aren’t asking your system about the tree, does your system need to know it fell?” This question addresses the level of awareness of changes in data required in a system in order that we may consider it real-time. Let’s apply the question to the data warehouse example: must a real-time data warehouse implementation have every transaction fed to it instantly, or is it enough that the data warehouse can quickly acquire transactions the user is trying to analyze?

The answer depends on whether the system is designed for “push” or “pull” operation.

A push system may also be called a “notification” system. These types of systems need to be aware of every change in data right away, because their purpose is to notify the user (push information to the user) when certain conditions occur. A business owner may want to receive a system message when a large order is received. A technologist may want to receive a message when the server is on fire. The technologist would expect to be told this, rather than having to ask the system every few minutes “is the server on fire?” In such systems, immediate data awareness is critical.

Pull systems don’t need to be aware of changes in data until the user makes a request. These systems can use what I like to call a real-time cache to improve performance. When the cache is created, it will populate itself with all of the currently available data. Any processing will take place to make that data more useable. Every time the cache is accessed, before returning its contents it will check to see if new data is available, process it, and update the cache. Queries will execute much more quickly, because the only processing happening in real-time is on the subset of records that have been created since the last time the cache was accessed. The real-time requirement is met because the information returned is completely current as request time.

I solved my programming dilemma by implementing a real-time cache as an object. The object contained a growable array of Record objects, which was only accessible through methods that checked the data source for new records and added them to the array before returning.

I’d love to hear from anyone who has thoughts on what defines a real-time system, or what rules need to be followed by its behaviour.

Cooney is a managing partner and chief software architect for Rivervine Technologies Inc. robert.cooney@rivervine.com.