If you have a database application that is running slowly, or is straining the server, what do you do? After you’ve eliminated the obvious malaises such as locking problems, do you buy more hardware, tweak the indexes, or do you rewrite the most hard-working queries until they run faster?
My experience tells me that resources spent on the last alternative pay the greatest dividend. You can increase the throughput of a database by tenfold, twentyfold, often more by doing so. What we often lack is a firm basis for our judgment as to what works best. Now, a group of us want to change that.
The competition for the ‘SQL Speed Phreak’ award has proved to be extraordinarily informative. We take a fairly small but realistic sample of data: a million rows seems to be about right; and we take a real-world problem for which there seems to be no consensus solution. Then anyone who wants to can submit as many SQL solutions as they wish to see who can produce the fastest solution with the correct answer.
This is like a sort of SQL Drag race. All sorts of strange SQL gets wheeled up to the lights, to general cheering. As with drag racing, the competition is really between your car and the road: The server and the data.
In our competition, the contestants all seem to catch the spirit and purpose, and try out a wide range of techniques which we then time on a test harness. All the participants seem to be confident enough in their skills that they don’t mind submitting entries that might not work too well. The next one will crack it.
One thing that carried over from Joe’ Celko’s Summer SQL Stumpers was the surprising spirit of cooperation that existed. We tended to try to help each other with their entries, so that each technique got a fair test.
For the ‘Subscription List’ SQL Problem, which was the first competition that splintered from Joe’s series, we used . ASK.SQLServerCentral.com I was privileged to host the first competition, and we eventually decided that the winner of each competition should then host the next one.The problem was a real-world one. It looked rather awkward, with a running total of sorts. I’d hit a similar report request several times, but I’d never been particularly satisfied with the solution. Luckily it had never proved to be critical as such a report is only required occasionally. It bugged me slightly. The most memorable version of the problem I’d experienced was a daily report of the number of live, or open, calls at various times of the day in the millions of calls made the previous day via a telecoms company, so it has a number of real-life applications.
Now Peco, the winner of the first problem, has come up with a second problem, The ‘FIFO Stock Inventory’ SQL Problem - Ask SQL Server Central which I hope you’ll contribute to. It is a real brain-exerciser, but definitely possible, but quite difficult and realistic of the sort of problems that you’ll hit in a real life application.