Back in the ’90s I worked on a DEC Alpha system creating a complex costing screen that needed to run a large number of queries for the user pricing a job, usually on the phone to a customer. It was almost unusable until we completed the Oracle 7 upgrade. Together with a colleague, we identified a way to persuade Forms 3 to make use of the V7 Shared Pool. Costing screen times came down from an unusable 40 seconds, to 1 second because the 100MHz (I think) processor no longer had to re-parse a whole bunch of SQL with every execution.
In the mid-noughties, while working for a telecoms bidding provider, I was asked to design a means for an Oracle 9i system on Sun 440/880 systems to be able to price tens of thousands of calls an hour. The simplified pricing scheme would be a bit like Virgin mobile with 15p a minute for the first 15 minutes, then 5p a minute thereafter. Except it was an infinitely flexible scheme, with infinite tariff tiers possible.
I worked out that the overhead of extracting call data and processing it in an external program or munging up various pricing details into PL/SQL tables was likely to outweigh any benefit, and the best chance of success lay with a structured, but very complex SQL statement. It ran to about two A4 pages, divided up into UNIONs of local and long-distance components with sub-queries to specify holiday rates, tariff tiers, and prioritise those tiers so only one applied to each call (used analytic SQL I seem to remember.)
Then there was the “SELECT SYSDATE FROM dual” used so much in the application that it was using one core of the 12 CPU Sun 10K system in use by the customer. I found a way to replace it with a function called once per user per day, supplying the date from a PL/SQL global for all other subsequent calls.
With Intel processors passing 2.5GHz, IBM double that, lots of CPU cores and approaching terabytes of server memory, who cares any more? Developers can get away with Cartesian products in joins – if you’re a new developer reading this, don’t take that on board, as well as poor performance, Cartesians will usually produce wrong results – so why bother spending hours writing correct SQL?
Nope, with increasing data volumes – terabytes instead of gigabytes – writing queries the optimiser can deal with efficiently is still as important as ever. Processor speeds and system memory may have gone up a factor of 25 from the 1990’s but data volumes have gone up more, as has the number and complexity of SQL statements.
R = S + W applies as much today as it ever did. Developers can still kill a system’s performance if possibly now in subtler ways more difficult to find.
The answer’s probably the same as it’s ever been, get your SQL experts to write SQL, and give the front-end developers an API to call, as advocated by Bryn Llewellyn amongst others.