The ‘FIFO Stock Inventory’ SQL Problem

Phil Factor SQL Speed Phreak Competition: No 2

alt text

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'll tell you the business rules for the algorithm and provide a sample cursor-based routine that generates the correct result, but slowly (about 40 minutes).

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?
It is a reasonable request. We have a stock transaction list (1,000,001 records, 17.6MB zipped) with 15,002 unique articles and we need to do a stock inventory report that gives the current breakdown of the ArticleID, the number of items in stock and the current stock value according to FIFO rules. The list should be in ArticleID order. The sample stock transaction list will include two running totals so you can check the results of your routine. They are NOT part of the competition, and you are not allowed to use them in your calculation.

Beware that the sample file includes the two extra columns; CurrentItems (INT) and CurrentValue (MONEY) !
If you want a smaller subset to test on, import all records and remove all articles but two or three. These are the samples for which all suggestions will be measured with.

The table is in this form (simplified from the way we'd do it in a real system of course).
In this made-up but realistic problem, you have an existing table and database design which you can’t do anything about. (we can all relate to that!) You have an existing cursor-based solution that is taking several minutes to run. (Yes, we all relate to that). The database is performing badly, and you need to take effective steps to remedy this. The only thing you can do is to come up with a better-performing routine. Redesigning the database isn’t an option very often, in the real world, because this requires team sign-in. This is a competition based on the real, sometimes imperfect, world: not an exposition of database design. The point is that we are faced with designs like this and we have to heal them. The competition is all about making stuff go faster.

CREATE TABLE    dbo.Stock
                    StockID INT IDENTITY(1, 1) NOT NULL,
                    ArticleID SMALLINT NOT NULL,
                    TranDate DATETIME NOT NULL,
                    TranCode VARCHAR(3) NOT NULL,
                    Items INT NOT NULL,
                    Price MONEY NULL,
                    CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED 
                        StockID ASC

CREATE NONCLUSTERED INDEX IX_Input ON dbo.Stock (TranCode, ArticleID)
--INCLUDE (TranDate, Items, Price) -- Remove comment for SQL Server 2005 and later
--WHERE TranCode IN ('IN', 'RET')   -- Remove comment for SQL Server 2008

CREATE NONCLUSTERED INDEX IX_Output ON dbo.Stock (TranCode, ArticleID)
-- INCLUDE (TranDate, Items) -- Remove comment for SQL Server 2005 and later
--WHERE TranCode = 'OUT'  -- Remove comment for SQL Server 2008

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)

StockID ArticleID TranDate TranCode Items  Price CurrentItems CurrentValue
   4567     10000 10:45:07 IN         738 245.94          738   181,503.72
  21628     10000 12:05:25 OUT        600                 138    33,939.72
  22571     10000 14:39:27 IN          62 199.95          200    46,336.62
  30263     10000 16:14:13 OUT        165                  35     6,998.25
  42090     10000 18:18:58 RET          5                  40     7,998.00
  58143     10000 20:18:54 IN         500 135.91          540    75,953.00

a) First IN add 738 items (each $245.94) to the stock, for a total of $181,503.72
b) Then we take out 600 items (each 245.94) from the stock, leaving a total of $33,939.72
c) Then we insert 62 items (each 199.95) to the stock, for a total of $46,336.62
d) Then we take out 165 items (138 each 245.94 and 27 each 199.95), leaving a total of $6,998.25
e) Then we return 5 items. We can’t track at which price we took them out; so all returns are priced at the price of the latest ones inserted before the return. Even if there should be items left for the price of 245.94, the returned items are valued for 199.95. After the return, the current stock value is $7,998.00
f) The final insert adds $67,995.00 to the stock value, for a total of $75,953.00

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.
2) Include an edition number. First edition is 1. If you later improve your current suggestion post it again as version 2. Example: “Peso 1” and if improved, “Peso 1b”, “Peso 1c” etc.
3) If you are trying a new algorithm, change the edition to “Peso 2”. If you improve this algorithm, change the version to “Peso 2b”, “Peso 2c” etc. This will save Phil hours of work in the test harness!
4) If you create a temp table, make sure you delete it in the script.
5) Keep the order of columns in output as ArticleID, CurrentItems, CurrentValue

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 time taken for their creation will not be included in the timings. The time measured is the “Main” script/procedure. If you want to call sub-procedures, go ahead.

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.

                    RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                    Price MONEY

DECLARE @ArticleID INT = 1,
        @PrevID INT = 0,
        @TranCode VARCHAR(3),
        @Items INT,
        @Price MONEY,
        @Loop INT = 0,
        @StockID INT,
        @LatestPrice MONEY,
        @Total INT = (SELECT COUNT(*) FROM dbo.Stock)

                SELECT      ArticleID,
                FROM        dbo.Stock
                ORDER BY    ArticleID,

OPEN    curYak

FROM    curYak
INTO    @ArticleID,

        IF @ArticleID > @PrevID
                TRUNCATE TABLE  #Work

                SET @LatestPrice = NULL

        IF @TranCode = 'IN'
                INSERT  #Work
                SELECT  @Price
                FROM    dbo.TallyNumbers
                WHERE   Number < @Items

                SET     @LatestPrice = @Price

        IF @TranCode = 'RET'
            INSERT  #Work
            SELECT  @LatestPrice
            FROM    dbo.TallyNumbers
            WHERE   Number < @Items

        IF @TranCode = 'OUT'
            DELETE  w
            FROM    (
                        SELECT      TOP(@Items)
                        FROM        #Work
                        ORDER BY    RowID
                    ) AS w

        UPDATE      s
        SET         s.CurrentItems = w.CurrentItems,
                    s.CurrentValue = COALESCE(w.CurrentValue, 0)
        FROM        dbo.Stock AS s
        INNER JOIN  (
                        SELECT  COUNT(*) AS CurrentItems,
                                SUM(Price) AS CurrentValue
                        FROM    #Work
                    ) AS w ON s.StockID = @StockID

        SELECT  @PrevID = @ArticleID,
                @Loop += 1

        IF @Loop % 1000 = 0
            RAISERROR('Now updating record %d of %d.', 10, 1, @Loop, @Total) WITH NOWAIT

        FETCH   NEXT
        FROM    curYak
        INTO    @ArticleID,


CLOSE       curYak

The above is already done. Here is the code to produce the final resultset.

SELECT      ArticleID,
FROM        (
                SELECT  ArticleID,
                        ROW_NUMBER() OVER (PARTITION BY ArticleID ORDER BY TranDate DESC) AS recID
                FROM    dbo.Stock
            ) AS d
WHERE       recID = 1
ORDER BY    ArticleID

Good luck to you all!

Peter Larsson

more ▼

asked Oct 23 '09 at 03:56 PM in Default

Peso gravatar image

1.6k 5 6 8

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...
Oct 23 '09 at 04:34 PM Peso
Two things had us made this decision: 1) We have tried CLR before and there was no performance gain. 2) To test and verify, poster need to disclode the source code for the suggestion. But if you have a CLR routine that runs on 15 seconds or less, I think Phil will be interested in testing it.
Oct 23 '09 at 05:08 PM Peso
Questions: 1) Is the Stock Transaction List referred to the one that will be used for Final Evaluation? If not, can you describe the differences? 2) What is the configuration that entries will be (finally) evaluated on?
Oct 23 '09 at 05:09 PM RBarryYoung
Yes, the downloadable file is the data against all suggestions will be measured. Phil will drop or rename the two last columns so that no suggestion can use them. I guess Phil will be using same test harness as last time with 'Subscription List' competition.
Oct 23 '09 at 05:16 PM Peso
Those filtered indexes will only work on SQL Server 2008, right?
Oct 23 '09 at 05:46 PM RBarryYoung
(comments are locked)
10|1200 characters needed characters left

28 answers: sort voted first

Hey, all! I've implemented simple approach learned from Celko DW challenge. Here it is:

    -- Andriy Z. 1
CREATE NONCLUSTERED INDEX IX_Input_2 ON dbo.Stock (TranCode, ArticleID,TranDate)
INCLUDE (Price) -- Remove comment for SQL Server 2005 and later
WHERE TranCode IN ('IN')   -- Remove comment for SQL Server 2008
select s.ArticleID
      ,CurrentItems = sum(Items * InOut.s)
      ,CurrentValue = sum(P.Price * Items * InOut.s)       
  from Stock s
       cross apply (
           select case when s.TranCode in('IN', 'Ret') then 1 else -1 end 
       cross apply (
           select top 1 Price
             from Stock ss
            where ss.ArticleID = s.ArticleID
              and ss.TranCode = 'IN'
              and ss.TranDate <= s.TranDate
            order by ss.TranDate desc  
 group by

Regards, Andriy Zabavskyy

more ▼

answered Oct 27 '09 at 06:40 AM

Andriy Zabavskyy gravatar image

Andriy Zabavskyy
11 1

Hi andriy! Looks like a godd suggestion. However I need to you add an ORDER BY at the end to follow the guidelines.
Oct 27 '09 at 09:47 AM Peso
It runs very fast (7 second) but returns wrong value. It does return the correct Items.
Oct 27 '09 at 09:54 AM Peso
I think that it is making the same mistake that I did at first, it seems to be using the most recent price for the OUT items. However, it should be applying the prices to the OUT in a FIFO manner, that is, applying the earliest prices to the earliest OUT items, until all of the IN,RET items at that price are consumed, and then it should start applying the next earliest prices, and so on...
Oct 27 '09 at 12:56 PM RBarryYoung
Cool approach though, it really has me thinking...
Oct 27 '09 at 12:56 PM RBarryYoung
Thanks, for the comments. Now I see my mistake. Will work on that..
Oct 27 '09 at 03:49 PM Andriy Zabavskyy
(comments are locked)
10|1200 characters needed characters left

Now the query should be correct:

-- Andriy Z. 1 // still version 1, because first one was incorrect
create nonclustered index [IX_StockIdx] on [dbo].[Stock]([ArticleID] asc ,[TranDate] asc)
include([TranCode] ,[Items]) 

select si.ArticleID
      ,CurrentItems = sum(itemNums.val)
      ,CurrentValue = sum(si.Price * itemNums.val) 
  from Stock si
       outer apply (
           select sum(I)
             from (select case when sPrev.TranCode = 'IN' and sPrev.TranDate < si.TranDate then sPrev.Items
                               when sPrev.TranCode = 'IN' and sPrev.TranDate > si.TranDate then 0
                               when sPrev.TranCode = 'RET' then sPrev.Items  
                               when sPrev.TranCode = 'OUT' then -1 * sPrev.Items 
                     from Stock sPrev
                    where sPrev.ArticleID = si.ArticleID
                      and sPrev.TranDate <> si.TranDate
       cross apply (
           select case when (si.Items + isnull(prevB.val, 0) ) > 0 
                       then (si.Items + isnull(prevB.val, 0) )
                       else 0
 where si.TranCode = 'IN' 
 group by si.ArticleID 
 order by si.ArticleID

Regards, Andriy Z.

more ▼

answered Oct 28 '09 at 10:22 AM

Andriy Zabavskyy gravatar image

Andriy Zabavskyy
11 1

Andriy - still not quite... For article 10005 your query gives 1069 items @ 133818.95, whereas the reference query gives 1065 items @ 133316.75... Performance was 21.678 seconds on my box...
Oct 28 '09 at 10:38 AM Matt Whitfield ♦♦
This is suggestion 1c?
Oct 28 '09 at 12:25 PM Peso
(comments are locked)
10|1200 characters needed characters left

## Herman, 30 Oct 2009 ##

Hi there, I decided to give it a try since I solved this problem for the stock market once before, using a slow cursor solution, and I always wanted to write it as a Set based solution.

This is my first attempt, and I am happy I got the answers right (I think), but I did not optimize it yet. It ran 86 secs on my laptop. I know I should how to do it different, but anyway decided to send this version.

--For my solution I need to create a function to determine the minimum of 4 values IF (SELECT OBJECT_ID('dbo.Min4')  ) IS NOT NULL  DROP FUNCTION dbo.Min4 GO



WITH -- First generate a transaction order per aticle, the last transaction has TransOrder=1 OrderedTransactions AS (SELECT ROW_NUMBER() OVER(PARTITION BY ArticleID ORDER BY TranDate DESC) as TranOrder, StockID,ArticleID, TranDate, TranCode, Items, Price FROM dbo.Stock), --Calculate running totals and determine for every transaction the last date a price was defined for the article RunningTotals AS (SELECT Tr.*,Tot.* FROM OrderedTransactions as Tr CROSS APPLY (select SUM(case when trancode = 'OUT' then -Items else Items end) as CurrentItems, SUM(case when trancode <> 'OUT' then Items else 0 end) as CurrentInItems, MAX(case trancode when 'IN' then TranDate else CAST(0 as datetime) end) as LastPriceDate from OrderedTransactions as PrevTr where Tr.TranDate >= PrevTr.TranDate and Tr.ArticleID = PrevTr.ArticleID group by ArticleId ) as Tot), --Use LastPriceDate to determine the price of The 'RET' transactions PricedTotals AS (SELECT Tr.TranOrder, Tr.ArticleID, Tr.TranDate, Tr.TranCode, Tr.Items, Tr.CurrentItems, Tr.CurrentInItems, case Tr.TranCode when 'IN' THEN Tr.Price when 'RET' THEN Pr.Price else 0 end as Price
FROM RunningTotals as Tr LEFT JOIN OrderedTransactions Pr on Tr.ArticleID = Pr.ArticleID and Tr.LastPriceDate = Pr.TranDate), --Now match the 'OUT' transactionjs with their FIFO 'INI' and 'RET' transactions MatchedTransactions as
(SELECT Tr.TranOrder, Tr.ArticleID, Tr.TranDate, Tr.TranCode, Tr.Price, Tr.Items, Tr.CurrentItems, IT.TranOrder AS InTranOrder, IT.TranDate as InTranDate, IT.TranCode as InTranCode, IT.Price AS InPrice, IT.Items as InItems, case when Tr.TranCode = 'OUT' then dbo.Min4(Tr.CurrentINItems-IT.CurrentINItems+IT.Items-Tr.CurrentItems, Tr.CurrentItems+Tr.Items - (Tr.CurrentINItems-IT.CurrentINItems), IT.Items, Tr.Items) end as Usage FROM PricedTotals as Tr LEFT JOIN PricedTotals as IT on Tr.TranCode = 'OUT' and IT.TranCode <> 'OUT' and Tr.TranDate > IT.TranDate and Tr.ArticleID = IT.ArticleID where ((Tr.CurrentINItems-IT.CurrentINItems+IT.Items>Tr.CurrentItems and Tr.CurrentINItems-IT.CurrentINItems<=Tr.CurrentItems+Tr.Items) OR Tr.TranCode <> 'OUT')) --And finally calculate the SUM of the Value of all transactions, and get CurrentItems from the last transaction. SELECT T.ArticleID, MAX(CASE WHEN T.TranOrder=1 THEN T.CurrentItems ELSE 0 END) as CurrentItems, SUM(CASE WHEN T.TranCode='OUT' THEN (-T.Usage*T.InPrice) ELSE (T.Items*T.Price) END) as CurrentValueValue FROM MatchedTransactions T GROUP BY T.ArticleID GO

more ▼

answered Oct 30 '09 at 11:05 AM

Herman gravatar image

11 1 1 2

It's a good attempt Herman. However, I need you to add an ORDER BY to your final SELECT.
Oct 30 '09 at 02:26 PM Peso
Thanks for the commenrt, I did forget it. Anyway my solution was a first try and is calculating much more than it is displaying, so I wil post a new solution.
Nov 01 '09 at 12:40 PM Herman
(comments are locked)
10|1200 characters needed characters left

Herman 1d, 9 nov 2009

I was calculating much more than I was displaying, so I reconsidered my first algorithm to get to the requested result.
By using some new indexes, and optimizing the code so that it gets the price for RET transaction only when required, I was able to get my solution to work even faster.
The only thing to do is to somehow stop the RunningTotals to be calculated for transactions which are not needed in the final result.
I guess that is why some of you get amazing fast results.

I did some attempts to stop the RunningTotals when enough In transacrtions have been processed to cover the stock, but without success. I would never come up with Dave's top 1 (LastStockID) solution.
I did however improve my solution by replacing the SUM() OVER .. with SUM() GROUP BY. I initially expected the new SUM() OVER to be as efficient as the GROUP BY, but it turned out that it better to calculate totals per article and not per transaction. Which sound very reasonable actually...

I am using the same indexes:

CREATE NONCLUSTERED INDEX [IX_GetPrice] on [dbo].[Stock]([ArticleID] asc, [TranCode] , [TranDate] asc)
  WHERE (TranCode='IN') 

CREATE NONCLUSTERED INDEX [IX_Transactions] on [dbo].[Stock]([ArticleID] asc ,[TranDate] desc)

CREATE NONCLUSTERED INDEX IX_InItems ON dbo.Stock (TranCode, ArticleID asc , TranDate asc )
  INCLUDE (Items) -- Remove comment for SQL Server 2005 and later
  WHERE TranCode IN ('IN', 'RET')   -- Remove comment for SQL Server 2008

And here is my 1d solution

WITH Transactions AS  (SELECT ArticleID, TranDate, TranCode, Items, Price--,  FROM dbo.Stock),  SummedTrans AS --Calculate the totals for In and Out transactions  (SELECT ArticleID,  SUM(CASE WHEN TranCode ='OUT' THEN 0 ELSE Items END) AS TotalsInItems,  SUM(CASE WHEN TranCode ='OUT' THEN Items ELSE 0 END) AS TotalsOutItems  FROM Transactions  GROUP BY ArticleID),  RunningTotals AS -- Determine the corresponding In tranacions to be matched to an Out transaction  (SELECT Tr.*, S.TotalsInItems, S.TotalsOutItems,  CASE WHEN TranCode IN ('IN', 'RET')  THEN (SELECT SUM(NextTr.Items) FROM Transactions AS NextTr  WHERE Tr.TranDate <= NextTr.TranDate  and Tr.ArticleID = NextTr.ArticleID  AND NextTr.TranCode IN ('IN', 'RET')  GROUP BY NextTr.ArticleID  HAVING SUM(NextTr.Items)-Tr.Items <= S.TotalsInItems-S.TotalsOutItems)  END AS CurrentInItemsRev  FROM Transactions as Tr  inner join SummedTrans s  ON Tr.ArticleID = S.ArticleID),  MatchedTotals AS --Match the In/Out transactions for current Articles on stock  (SELECT *,  --CASE WHEN CurrentInItemsRev-Items <= TotalsInItems-TotalsOutItems  -- THEN  CASE WHEN CurrentInItemsRev < TotalsInItems-TotalsOutItems THEN Items  ELSE -(CurrentInItemsRev-Items)+TotalsInItems-TotalsOutItems  -- END  --ELSE 0  END Usage  FROM RunningTotals  WHERE (CurrentInItemsRev-Items <= TotalsInItems-TotalsOutItems)),  PricedTotals AS -- Determine the value of the In transacions  (SELECT *,  CASE WHEN Price IS NOT NULL THEN Price  ELSE (SELECT TOP 1 Price  FROM RunningTotals P  WHERE P.ArticleID = T.ArticleID  and P.TranCode ='IN'  AND P.TranDate < T.TranDate  ORDER BY P.TranDate DESC)  END * Usage AS Value
FROM MatchedTotals T WHERE TranCode IN ('IN', 'RET') )
--And Sum everything up SELECT ArticleID, max(TotalsInItems-TotalsOutItems) as CurrentItems, sum(Value) as CurrentValue FROM PricedTotals GROUP BY ArticleID ORDER BY ArticleID GO
more ▼

answered Nov 01 '09 at 01:08 PM

Herman gravatar image

11 1 1 2

Thank you. I will try it later when I get clean access to my preliminary test machine.
Nov 02 '09 at 10:38 AM Peso
(comments are locked)
10|1200 characters needed characters left

Scot Hauder v1, 2 Nov 2009

I modified one index as shown below

CREATE NONCLUSTERED INDEX IX_Input ON dbo.Stock (TranCode, ArticleID, trandate desc) INCLUDE (Items, Price) WHERE TranCode IN ('IN', 'RET')

;WITH voodoomagick AS ( SELECT ROW_NUMBER() OVER (PARTITION BY ArticleID ORDER BY TranDate) [r] ,ArticleID ,Items ,LastPrice ,(SELECT COALESCE(SUM(items),0) FROM Stock s0 WITH (NOLOCK) WHERE TranCode IN ('RET','IN') AND s0.TranDate <= s1.TranDate
AND s0.ArticleID = s1.ArticleID) [in]

,(SELECT COALESCE(SUM(Items),0) FROM Stock s0 WITH (NOLOCK) WHERE TranCode = 'OUT' AND s0.ArticleID=s1.ArticleID) [out]

FROM Stock s1

CROSS APPLY (SELECT TOP (1) Price [LastPrice] FROM Stock s3 WITH (NOLOCK) WHERE s3.TranCode = 'IN'

AND s3.ArticleID = s1.ArticleID 
AND s3.TranDate &lt;= s1.TranDate 
ORDER BY TranDate DESC) [x1]

WHERE (SELECT COALESCE(SUM(Items),0) FROM Stock s0 WITH (NOLOCK) WHERE TranCode IN ('IN','RET') AND s0.TranDate <= s1.TranDate AND s0.ArticleID = s1.ArticleID)

&gt;=(SELECT COALESCE(SUM(Items),0) 
    FROM Stock s0 WITH (NOLOCK) 
    WHERE TranCode = 'OUT' 
    AND s0.ArticleID = s1.ArticleID)
AND TranCode IN ('IN','RET') ) SELECT ArticleID ,MAX([in])-MAX([out]) [CurrentItems] ,SUM(CASE WHEN r = 1 THEN [in]-[out] ELSE Items END * LastPrice ) [CurrentValue] FROM voodoomagick WITH (NOLOCK) GROUP BY ArticleID ORDER BY ArticleID
more ▼

answered Nov 03 '09 at 02:26 AM

ScotHauder_ gravatar image


Hi Scot! Great to see you enter the competition. I will test this suggestion when I have clean access to preliminary test machine.
Nov 03 '09 at 06:00 AM Peso
My index seek still has a lot of executions compared to Dave's entry. If I have time this weekend I will see if I can improve on it
Nov 03 '09 at 03:27 PM ScotHauder_
You have time. Deadline is set to November 16.
Nov 03 '09 at 04:54 PM Peso
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 23 '09 at 03:56 PM

Seen: 27004 times

Last Updated: Nov 14 '09 at 01:55 PM