Phil Factor SQL Speed Phreak Competition: No 2This 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 siteIt 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! Peter Larsson
(comments are locked)

Dave Ballantyne  Phil Factor Challenge Entry 3.d Peso had adding some index hints and shuffled a calculation to my initial 3.a. So this is 3.a(+) with comments with 3.d doing the TotalStock sum in one operation shaves some more time off “A problem well stated is a problem half solved”  Charles F. Kettering Let us think about what has been asked within the challenge. We have a warehouse which starts empty , the data contains the stock movements in (including returns) and out. The stock movements ALWAYS happen in a first in first out basis. With this we know that if the present stock level (sum(in's)  sum(out's) is 50 that will be the last 50 that have entered the warehouse. The key to this query is to efficiently find the cost of those 50 items.
(comments are locked)

Great job Dave! I was able to get mine down to 2900ms with a couple index hints but still not fast enough. Cheers Scot
(comments are locked)

Here's an alternative solution, based on a tally table triangular join. I would have expected it to be quicker than my previous entry, but it's not. I'm posting just for the fun of it and for those interested in taking a look at a different approach. The cost is 92% in the aggregate after the triangular join and I wasn't able to get rid of it. Maybe some of you can squeeze something more out of this query.
SELECT ArticleId, CurrentItems = SUM(CASE Number WHEN 1 THEN Items  ISNULL(OutItems,0) ELSE 0 END), CurrentValue = SUM(CASE RN4 WHEN 1 THEN Items  ISNULL(OutItems,0) ELSE LastItem END * LastPrice) FROM ( SELECT *, RN4 = ROW_NUMBER() OVER (PARTITION BY ArticleId ORDER BY Number DESC) FROM ( SELECT ArticleId, Number, SUM(Items) AS Items, SUM(CASE WHEN RN3 = Number THEN Items ELSE 0 END) AS LastItem, SUM(CASE WHEN RN3 = Number THEN Price ELSE 0 END) AS LastPrice FROM ( SELECT ArticleId, RN3 = ROW_NUMBER() OVER (PARTITION BY ArticleId ORDER BY MIN(StockId) DESC), SUM(Items) Items, Price = SUM(Price) FROM ( SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY ArticleId ORDER BY StockId) FROM ( SELECT StockId, ArticleId, TranCode, Items, Price, RN2 = ROW_NUMBER() OVER (PARTITION BY ArticleId ORDER BY StockId) FROM Stock WHERE TranCode = 'IN' UNION ALL SELECT StockId, ArticleId, TranCode, Items, Price, RN2 = ROW_NUMBER() OVER (PARTITION BY ArticleId ORDER BY StockId) FROM Stock WHERE TranCode = 'RET' ) AS S ) AS A GROUP BY ArticleId, CASE TranCode WHEN 'IN' THEN RN2 ELSE RN  RN2 END ) AS B INNER JOIN TallyNumbers AS T ON RN3 >= T.Number GROUP BY ArticleId, Number ) AS C CROSS APPLY ( SELECT SUM(Items) AS OutItems FROM Stock WHERE ArticleId = C.ArticleId AND TranCode = 'OUT' ) AS D WHERE Items >= ISNULL(OutItems,0) ) AS E GROUP BY ArticleId ORDER BY ArticleId
(comments are locked)

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). FIFOStock Procedure:
StockStack class:
(comments are locked)

Here is my cursor solution as counterweight to all setbased solutions :)
(comments are locked)

Was there any particular reason you disallowed CLR functionality?
On an initial look, this would be the ideal scenario in which to use a CLR type...