Now here's a luxury we don't see every day. After all, if we're a car manufacturer, perhaps an aircraft builder, we have to get it right and make it fast all as part of the original design.

Which is not to say that we should just choke up a bunch of data structures and expect Netezza to cover our backs - oh wait - perhaps we really can do this, but it's not always practical.

Early in my career I worked with embedded / real-time systems, and while some really believe that they work with "real time" - here's the purist definition: A robot balancing a broom on its open palm. The robot has to make infinitesimal adjustments, in real time at the microsecond level, to keep the broom from falling. In "business" real time, however, we have the luxury of whole seconds to make a decision!

In these embedded systems, we had to take things through to a complete functional shakeout. Only then could we see where events collided or didn't make sense. So-called "race" conditions and meta-stable conditions - yep - for those of you who know what these things mean, it can cause you do go gray early and stay that way.

Ahem.

So the maxim here was always "get it right, and then make it fast". I took this "RTI" (little three-letter-acronym (TLA) for Run-Time-Improvement), into a commercial venture where I developed an expert system engine for medical claims processing. Only when the system was behaving correctly could we then find ways to pinpoint the hot-spots. In one case, one percent of the claims took over 90 percent of the processing. We dug deep to find the issue, optimized for it and voila - the system screams like a banshee. In fact, these improvements boosted processing speed for the areas that were already fast - so it was a double-win.

People asked us at the time why we allowed the development process to "suffer" with "poor" performance up until the very end - when we knew good and well that attempting to optimize it while it was still in functional flux, didn't make any sense. We can do sweeping improvements only so much and only so deep. But what if we spent all of our time improving something that three weeks later the client says they don't want any more, or want to go in another direction, and all of our improvements are for nought?

Nay, wait a little longer - make it right, then make it fast.

Fortunately - and quite luxuriously - in our space the Netezza platform dovetails directly into this approach. It already has all the juice to help us succeed even if we do things badly or inefficiently. But when the smoke clears and we have a working prototype, it's time to roll up sleeves and pop-the-hood, so to speak, to do some RTI on a working system. This is where it gets fun.

The following is a short list and is by no means comprehensive

(A) - line up the operations in their natural sequence. Find the ones that are longer-running and optimize them. This will provide some degree of relief, but it's still just low-hanging fruit.

(B) Locate in-line calculations in the where-clause or join-clause, and reduce or eliminate them. In one case, we need to join on a date-time where the "drift" allowed the timestamps to be different by plus-or-minus three seconds. Rather than put the time+3 and time-3  calculation in the actual query, we precalculated an additional two columns, one with the time minus three and one with time plus 3. We then used a simple "between" operator to get the answer. Time to market - 1000:1 difference in the two run times.  In-line calculations in the where-clause are an invisible power drain. Get them outta there.

(C) Find the processing patterns and consolidate them. For example, we had a BI tool executing eight queries to achieve a report output - and each query took approximately 20 seconds. Not bad for having to plumb the equivalent of 53 terabytes of information. Yet eight queries like this delayed the report's display by 160 seconds - almost three minutes. Users don't like to wait this long. So to optimize, we focused on the pattern, that the same basic query existed at the core of each of the eight. By taking the "hard part" and performing an up-front query that did all the heavy-lifting at once, we were able to take the 20 second penalty only once, and the 8 downstream queries returned in 4 seconds or less. So - 160 seconds to 34 seconds with a simple logistical change.

(D) Pre-filter or precalculate when consolidating. This means taking a common downstream operation, especially a repetitive one, and moving it upstream to another, even unrelated operation. The above time-drift is one example. Calculate and filter as early as possible, and then all the downstream operations benefit from it. This can offer up more than just a "spot" boost, because if it shaves a few seconds off every downstream query, this can quickly add up to shaving tens of minutes and even hours off our overall processing time.

(E) Mind the gap - of what we understand about how an RDBMS works versus Netezza. For example, if we want to leverage filter power in Netezza, we could use a "where exists" clause rather than a regular join. If the regular join cannot leverage a distribution key, then the where-exists is a highly performant option. Likewise if we have a view in the join that does more than just serve up data, like doing a sub-join itself. This can be very costly, and is another hidden drain on the performance that we can pull into a where-exists. So another "gap" is in merging two data sets where they have no common distribution key. The where-exists and similar operations force the machine to obey our optimizations, because we actually know the data, where the machine simply exhanges it with us.

(F) Avoid squeezing blood from a stone - It is tempting for said adrenalin junkie to see a cool way to reduce 2 minutes to several seconds - after a rush like this, it becomes addictive. We should not let it go to our head. In one case of processing a nightly batch, one of the client's 14-hour processes had been reduced to less than fifteen minutes. Yet some in the room still groused for more - they came up with an outrageous plan to reduce the processing to less than five minutes, but only four people on the entire planet could ever maintain it, much less enhance or improve it. At some point we have to agree that enough is enough - especially when we are sacrificing valuable things (like extensibility, flexibility etc) for the sake of a few more minutes of adrenalin rush. We must resist!

(G) Focus on the target system, not the one you are leaving behind. I've never known a case where someone moves into a new home and refuses to use the appliances in the home just because they didn't exist in their old one. Nor have I ever seen someone buy an new home and then attempt to fill it with new furniture that would only fit in their former home. Who does stuff like this? No, we should use the former system as a functional baseline (describing what)  but not focus on how we implemented the baseline - rather our new technology gives us the ability to spread our wings and fly in ways that we never could in the old environment. Example: One client had over 400 stored procedures to convert, and regarded these stored procedures as the baseline for the actual work, rather than the baseline for the functionality alone. When re-characterized, it reduced to four flows with a handful of core operations each - all with very simple implementations. Trust me, when it takes its final form in Netezza, it will look nothing like its former self.

Haven't yet experienced a run-time improvement cycle, or committed time to making it happen? It's worth it - even if only for a sanity-check on an existing implementation or a proof-of-concept on an upcoming one.

Either way, we can reach functional closure in a fraction of the time of any other system, and once the functionality is stable (not necessarily locked down, just stable) we can find surprising and dramatic boosts with little additional effort.

Make it right (functionally speaking) - and then make it fast -