Phil Factor SQL Speed Phreak Competition: No 2
This competition is now over, but the winner, Dave, got an Amazon Voucher for $60, and the privilege of being able to display the 'Phil Factor SQL Speed Phreak' award on their own site
It was quite a struggle with some close competition from many of those who participated in this competition. However, Dave came up with a clever solution that produced an FIFO calculation from a million rows in a just a few seconds. The third Phil Factor Speed Phreak competition will soon be held on here. Watch out!
(here is the original preamble.)
This competition is to calculate current items in stock and current stock value in FIFO order.
I have seen many different algorithms to do this and most of them involve a cursor.
Can it be done more quickly without, or even with, a cursor? In other words, what is the fastest way in SQL Server (any version) to provide this stock inventory report?
Beware that the sample file includes the two extra columns; CurrentItems (INT) and CurrentValue (MONEY) !
The table is in this form (simplified from the way we'd do it in a real system of course).
You are welcome to change the two nonclustered indexes to suit your solution. You can download the complete sample data here. I have an idea of my own of the way to do this but I don’t know if it is the fastest.
Explanation of FIFO rules (example, abbreviated)
a) First IN add 738 items (each $245.94) to the stock, for a total of $181,503.72
As mentioned before, the CurrentItems and CurrentValue columns in the sample data are only included for you to validate your routines.
Here are some guidelines for your entries:
1) Include a header in your suggestion. Make sure your name and date is present.
I will allow you to use an existing tally number table (make sure it starts with 0). You can use any kind of object for this competition, except SQLCLR. If you are using a fixed tally number table, it has to be named dbo.TallyNumbers and the column named Number.
The winner will be amongst the tied fastest entrants (generally there is a group of these) and it will be the one with the highest number of votes. We'll announce the winner in three week's time on 16th November.
For a starter, here is a common cursor based solution that you will find in production in many places.
The above is already done. Here is the code to produce the final resultset.
Good luck to you all!
Timings are quite an eye-opener. They do vary quite a bit, and some solutions vary more than others.
I took the average of six timings.
It is a tie between GianLuca and Dave Ballantyne. Because I can only award the competition to one person, I'm going to use as a tie-breaker the fact that the fastest timing in the whole run was by Dave Ballantyne at an amazing 2220 ms. He therefore is awarded the Phil factor Speed Phreak award, and sets the next competition. However, I've asked Red-Gate to award a second $60 Amazon Voucher to Gianluca for his wonderful entry 1a which was so close to Dave's average timings that one couldn't put a cigarette paper between them.
Matt Whitfield's late modification to his CLR comes in at 4300 ms, and gets an honourable mention, as does Scot Hauder (4620 ms) Steve(1a) with 4416 ms
The original run gave the following figures (I've used Matt's second CLR solution)....
**Secs Ms Entry** 3 2560 Dave Ballantyne - Phil Factor Challenge Entry 3.d 2 2750 Gianluca 1a 3 3050 Gianluca 2a 3 3190 Dave Ballantyne - Phil Factor Challenge Entry 3.b 4 3313 Matt (waiting for fixed version!) 4 4360 Matt Whitfield CLR solution v2 4 4560 Steve 1a 5 5033 Scot Hauder 6 5830 Herman 1d 6 5906 RBarryYoung 2d Pure Set-Based, summary calcs 10 10173 Andriy Z. 1 16 16300 Dave Ballantyne Ver 2b 41 41000 Dave Ballantyne Ver 1a 62 61673 RBarryYoung_1b: Naive, Pure Set-Based Solution 102 101430 Peso CURSOR (not an entry) 111 111160 Herman, 30 Oct 2009
Just to show you the sort of variations i was seeing, here is another sample run. In future, I'm going to judge the winner over at least 20 runs, and record the variance as well as the average time.
4 4236 Dave Ballantyne - Phil Factor Challenge Entry 3.b 4 4546 Gianluca 1a 5 4703 Dave Ballantyne - Phil Factor Challenge Entry 3.d 6 5566 Steve 1a 6 6190 Scot Hauder 7 7216 RBarryYoung_2d: Pure Set-Based, summary calcs 7 7450 Herman 1d 9 8610 Gianluca 2a 10 10296 Andriy Z. 1 18 17813 Dave Ballantyne Ver 2b 28 27500 Matt Whitfield CLR solution (V1) 42 42003 Dave Ballantyne Ver 1a 63 62803 RBarryYoung_1b: Naive, Pure Set-Based Solution 114 113116 Herman, 30 Oct 2009 198 198136 Peso CURSOR (not an entry)
Sadly, I left the harness to watch a DVD, and when I got back to do the next lot of runs, windows had decided to upgrade itself and I hadn't saved the latest version, so I'm afraid the umpire's decision is now final
Final order over eight runs:
(I had to disqualify Matt as I haven't got an entry that gives the right answer)
I can't find the Dave 3.a version in answer list.
answered Nov 04 '09 at 08:41 PM
Here is my cursor solution as counterweight to all set-based solutions :-)
answered Nov 11 '09 at 04:47 PM
Ok, well I would post a pure set based solution, except that the site is refusing to let me post the whole thing!! Lame.
Anyway, the SQL is just
It runs in about 6.3 seconds on my box.
There are two source files, one for the support class (mostly the same guts as the CLR type version, except for the fact that IBinarySerialize support is removed, and it is now class, rather than struct based).