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!
I had some help with this one from a friend (thanks Mark!). it's just at a second on my machine. Let me know if I did anything wrong
I have a question regarding the rule for Returns. As follows:
You explain that because we can't track which original sales were being returned, therefore we always price returns at the price of the IN immediately preceding the return. However, this does not make sense in the case when there were no OUTS after the IN which immediately precedes the RET. In such a case, because we know that the RET could not have been of the items that just came IN. The rule should be "RET is priced at the price of the IN immediately preceding the OUT immediately preceding the RET."
This way RETs can be consistently ignored in the calculations, because only real INs count. And it is consistent with FIFO.
answered Nov 06, 2009 at 03:03 AM
Even though under FIFO, current value is tracked at the more recent purchase prices, the value of goods already in inventory is not adjusted up or down just because another purchase is made at a different price. The premise of FIFO is not to track the value of current inventory at the latest, or most accurate basis. This is not market based cost tracking, or LCM. It's one of two accounting constructs designed for an environment where a) inventory value is tracked at cost, and b) subtractions from inventory cannot be tracked back to the exact point and price of entry. In such an environment, the arbitrary, yet consistent, assumption that First in First Out provides a method to consistently track value in an inherently inconsistent and unknown environment.
The problem with always using the latest IN price for returns is that in cases where no OUTS occurred after the latest IN, or where the number of items returned exceeds the number of OUT items that sold after the latest IN, the practice violates condition a) above - that value is tracked at cost.
Since RETs can only happen if there are OUTS, it makes sense to treat RETs as cancellations of the OUTS. And just like OUTS can span several INs (that's a premise of the rule), RETs can span, or cancel, several OUTs.
I'm not hung up over it, I can see how treating a RET as another purchase at the most recent price is consistent as well, but that complicates the query:)
answered Nov 06, 2009 at 05:29 AM
Here's my entry. I'm calculating running count in reverse order and grouping INs and RETs based on their rank in the stock. Runs in 1.4 sec on my crappy laptop and requires "just" 53933 scans.
answered Nov 06, 2009 at 06:15 AM
Another try, with a quirky update. It doesn't perform as fast as the others, but it's another way to solve it quite quickly.
answered Nov 06, 2009 at 07:34 PM