What to do when the solution will never be in front of you

Published 25 April 12 10:46 PM | GavinPayneUK

Sometimes you have a requirement which you don’t have a tool in your toolbox for.  When that happens the solution may not be to just get another tool and add it to your existing toolbox, it might be you have to use an entirely new type of toolbox with tools you’ve never used before in it.  Doing that maybe a bold architectural decision but is often where the biggest performance or functionality gains can come from.

Recently, in the SQL Server space there have been some technologies become available that showed how existing performance bottlenecks were removed by solving the problem in a completely new way to the previous successful but relatively temporary fixes.  Two examples we’ll consider are solid-state storage and Columnstore indexes.

10x faster can’t always come from 10x the power

Tonight I read the keynote presentation that Conor Cunningham gave at SQLBits recently about ColumnStore indexes (available here) but rather than read about how ColumnStore indexes work I read it from an architectural decision making perspective. 

Midway through the presentation is the line “Making something 10x faster is HARD”, followed by a slide questioning how could you make a query run 10x faster.  The answer wasn’t optimising the existing way of doing things, it was to implement a completely new way of working, in this case a new query optimiser mode and index format.  The existing options just couldn’t be tuned anymore to meet the requirement.

Game changing storage

The other example I quoted was the introduction of solid state storage in database servers.  Making storage fast was always a goal of infrastructure teams, they used different RAID levels, different caches and different numbers of disks until the industry realised that 5 and 10% gains weren’t always worth the effort or cost of implementing.  Instead, what we wanted was something that was 10000% faster and the answer wasn’t more disks, it was a totally different approach to persistently storing data, in this case using memory chips instead of spinning platters.


You’re probably never going to be asked to re-write a query optimiser or create a new type of storage hardware, however you maybe asked to keep making something go faster or scale further after having just done all you can do to make it faster.  It could be making reports run quicker or supporting more concurrent users.  My conclusion is that you shouldn’t just think about how to solve today’s problem using today’s options, think about a medium term option that may mean doing something radical but it won’t be a shock to you when you realise you need to do it.


No Comments