A recurring theme in enterprise application performance is the natural conflict between achieving fast writes AND fast complex query responses at the same time in SQL-based RMDS databases* .

This write/read conflict has been around since the very first Hello World 2 tier applications back in the ’60s.  Recently this self-same subject poked its head above the parapet and started pulling faces at a client or ours.

What surprises me is that this is not a hugely difficult problem to solve – once you know how . Equally surprising is how divided the development community is on this topic.

When asked about this particular issue, most app developers reach for some sort of in-memory caching system with all of the cache-miss and eviction policy management nonsense that these things entail. Conversely DB developers swear by a complicated mechanism of stored procedures putting data into staging tables and the shuffling of data to a read table for querying.  Similar issues to caching appear here along with the extra fun of dirty reads and row deadlocks.

So you need miracle akin to passing a camel through the eye of a needle to meet this simple requirement, right?

Er, no.

As with most things in life, the answer is elegantly simple. In fact our DB hackers are on the right track.  THE proven solution is to have TWO databases in the same engine with identical table structures and data. Lets call the 1st DB the Online Transaction Processing (OlTaP) DB and the second DB the (Management Information Services) MIS DB.

The OlTap DB has one purpose in life – to store data as fast as it can.  The target here should be 10ms or less round trip between the application and the DB during a write operation. This DB has only stored procedures to store the data – procedures which have been painstakingly optimised for writing (and that includes updates and deletes – both of which a generally considered to be write operations).  But, and here is the secret, this DB has NO INDEXES other than primary keys (which one could argue are in fact constraints). Fast writes are guaranteed no matter what the table size is, because there is no index to care about such things.

Flipping the coin over, the MIS DB has lots of indexes, tuned to serve up query results, like, NOW.  These are called through stored procedures to allow the DB engine’s execution query optimiser/query planner to properly prepare for volatile materialised views. And here’s the thing about the MIS DB – there are no public stored procedures to allow writing to any of its tables. Not one.

OK, you say, so how does the data get from the OlTaP Db to the MIS DB, huh? By definition they have been designed to be isolated from one another!

Yes, kimosabe, that is the public appearance of these two DBs. However there is a little known trick which in the Oracle world is known as transaction log triggered materialised views.  All other enterprise DBs have similar features.

RMDS databases all have a thing called the transaction log.  Useful if you want to rollback a transaction on error, useful for logging row deletions in a table and generally handy for all DML operations.  You may also have got to know these little beasties when they blow up due to lack of space when you run a massive deletion.  Yes, I am referring to those puppies.

Another baked-in feature of RMDS systems is their ability to handle materialised views or views which cache data from a previous call, ready to serve the same result on second or later call.  You have probably not realised that all DB calls through a client such as TOAD or SQL Server Management Studio or Embarcadero’s Studio Enterprise all cache query results in a volatile materialised view formed in the background.

Bolting the transaction log to preconstructed non-volatile materialised views from tables in the OlTap DB and writing the result set to the same tables in the MIS DB on OlTaP commit allows the two DBs to remain in sync. Permanently. Oh, and this is how DBs replicate in a cluster.

Also it helps if the app only stores the data it needs to store. For example, a lot of systems generate and consume heavy XML messages by way of conforming to a recognised data interface specification.  Better not to store all that useless XML element structure, rather extract the values you need and persist those instead.

Neat, eh?

*yes, there are other NoSQL and BigData solutions which leverage distributed computing paradigms, but assume that your client doesn’t have the money, time or will to bring in a brand new technology stack just to solve a relatively simple performance problem.

Dolly was a Sheep in the Scottish highlands minding her own business, wondering each day what that glowing yellow warm thing in the sky was and marvelling at how good that green stuff on the ground tasted.  The fact that she was cloned rather than created was neither here nor there for her nor her sister clones. They ate, grew, and well you know the rest.

The same is true in IT, particularly with DBs in high performance systems.  Not the marvelling-at-the-sun or the chewing-green-stuff, but the concept of cloning for performance or to put it in database terms, DB Replication.

Some DBA pundits would have you believe that its a non-starter, this replication nonsense.  The School of Hard Knocks has shown us otherwise.  It boils down to the fact that a Relational Database Engine is a very poweful thing in the hands of the skilled because it can be tuned to one of two business operational modes. Namely Online Transaction Processing (OLTP) or Management Information System (MIS) modes.  One DB instance cannot be tuned to do both of these things brilliantly.  Passably maybe, but brilliantly? No, it simply can’t.

So what to so?  Replicate, people, replicate.  The key here is to have 2 instances of the same DB schema running at the same time.  Instance 1 handles OLTP operations from apps that need high throughput which are mainly insert and update operations, so this instance has naff-all indices.  Instance 2 handles MIS requests from spreadsheets and nosy-parkers who want to cut, slice, dice and otherwise extract data via select statements – historical data usually.

How does this work? Instance 2 follows instance 1’s transaction log, replicating all CRUD operations in its own DB.  Its reliable, elegant and its FAST.  The key lies in copying over the delta between the two DB’s transaction logs, so keeping in sync is a cinch.

Oh, and you can load up instance 2  – the MIS instance, remember – with as many indexes as you like.  Just as long as you remember to update your table and index stats daily (see my earlier blog on this), you will have a system that absolutely flies.  Instance 2 will usually keep its indices up to snuff when a transaction log change is detected.  Do check with your DBA/DB vendor.

One client was able to achieve in excess of 20 full object graph inserts into the underlying relational DB tables per second moving to this model. In Dev. Up hill. With a headwind.

You know it make sense so Just Do It (thanks, Nike)

Now we all know that having a DB with a large number of records (over 1 million rows should do it) can beome sluggish over time if the content of the key tables changes often, especially with insertions.  This is despite your best efforts to tune the SQL you have carefully crafted using the Explain Plan/Execution Plan tools, eliminating unnecessary table scans.

Well children, you are missing a simple trick – regularly recalculating stats.  This forces the DB to re-estimate the size of the tables and associated indices.  Without going into the nitty gritty of why, the basic fact is that any modern relational database engine relies on an optimiser (via the execution plan) to work out how best to grab your data.  It relies heavily in fact on the DB table stats and index stats to achieve this.

By recalculating stats on a frequent basis – most enterprise systems do it overnight – the DB response times remain consistent for the same queries irrespective of the data load.

A note to the observant: you will see the DB performance roll off a lot more rapidly the more indexes you add.  This  appears as voodoo to people who spent a large amount of time tuning queries and indexes with smaller data loads, when they suddenly find that their query response times suddenly fall off a cliff after a few 1000 more rows are added to the DB.

The less often you do this necessary bit of housework, the slower the recalculation process becomes.  Remember our old friend the Law of Diminishing Returns.

So look up in your DBA handbook how to housekeep your key table statistics. Regularly.