Imagine that you are tasked with delivering a SQL Server-based application that must work blazingly fast with huge amounts of data and must perform calculations of startling complexity. Currently, it doesn't perform fast enough, or scale adequately, even with the highest-spec hardware, well-tuned T-SQL and clever indexing. What do you do next?
At a recent SQL Rally event, Adam Machanic described such a predicament, involving 'four years of pain' wrestling towards a solution for a financial reporting system that performed highly complex analytical calculations, but not as fast as the business required.
Initially defeated by attempts to conjure up an efficient pattern to solve complex "overlapping date interval" calculations, he sought help from Itzik Ben-Gan who, over the course of a few days, wrote several possible T-SQL solutions. Adam then wrote his own CLR solution in an attempt to squeeze out higher performance, but was initially disappointed. The need to use the CLR context connection essentially meant that his solution was single threaded, and so was unable to exploit all available processing cores, and would never scale in the manner he hoped. He ended up writing his own "thread scheduling" class in order to force SQL Server to parallelize the workload. However the result, in his own words, was the "un-safest of unsafe" CLR assemblies. Nevertheless, the performance was impressive, and he used the solution for a while, only crashing the server "a couple of times".
Finally, it dawned on him that what he needed to achieve was similar to what one could do with a User-Defined Aggregate, which is a workload that SQL Server will parallelize, but he needed a UDA that would return a table rather than a scalar. His ingenious solution was to devise a function that returned a scalar that was, in fact, a pointer to the result set, held in native CLR memory, which he then extracted via a table-valued function and returned. The result? A safe assembly, full parallelization, blazing fast performance and graceful scalability.
So was it worth all that effort, the four years of pain? At the end of Adam's session, I mingled with the audience as it left the room and got the impression that some thought not. A few seemed genuinely gob-smacked both at what he'd achieved and how he'd done it, but others seemed incredulous, even wary of these CLR techniques ("wow…but I'm not going to try that at home").
I was a little surprised. If a company's business model relies on blazing fast answers to hard problems, then the solution may not be pretty, at least not initially, certainly not easy, and maybe not even completely safe, but still, a solution must be found, and the company must understand and be prepared to accept any associated risk. In this case they did, and personally, I'd been incredibly impressed by what Adam achieved, and achieved the hard way.
If you've used techniques that have stretched your comfort levels, or been viewed with skepticism by others, in order to deliver the required performance, I'd love to hear about them.
Happy New Year to all Database Weekly readers!
Tony.