Phil Factor SQL Speed Phreak Competition

Phil Factor, 2009-10-25

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 . 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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads