Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Fighting Tooth and Nail for Performance

By Tony Davis,

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.

Total article views: 117 | Views in the last 30 days: 1
 
Related Articles
FORUM

SQL Server High Avilabilty Solutions

SQL Server High Avilabilty Solutions

ARTICLE

Divide and Conquer - Performance Tuning

Sometimes we need to break down a complex problem into a multiple stage solution to achieve optimal ...

ARTICLE

Achieve Better Performance

This week Steve Jones talks performance and a few things you might want to do that can help your car...

ARTICLE

Achieving Server Redundancy at Remote Offices

Everyone wants a highly available system, but achieving 4 or 5 9s of uptime is hard to do. Especiall...

FORUM

SQL Server Memory Calculation

SQL Server Memory Calculation

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones