{"id":892,"date":"2010-05-25T00:00:00","date_gmt":"2010-05-25T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem\/"},"modified":"2021-06-03T16:44:17","modified_gmt":"2021-06-03T16:44:17","slug":"set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem\/","title":{"rendered":"FIFO Stock Inventory in SQL Server: Set-Based Solution vs Cursor-Based (with Benchmarks and the Winning Query)"},"content":{"rendered":"

FIFO (first-in, first-out) inventory accounting matches outgoing stock movements against the earliest incoming batches to determine cost of goods sold and remaining stock value – a classic problem in accounting and supply-chain software. In SQL, a naive implementation uses a cursor to walk stock movements in chronological order, matching each withdrawal against the oldest available receipts until the withdrawal is fully sourced. The set-based alternative is harder to design but typically 10-100x faster on production-scale data. This article presents the problem as a contest, compares the cursor-based solution to the winning set-based solution from Dave Ballantyne, and explains how the set-based approach works: a combination of running totals (implemented with window functions in SQL Server 2012+ or recursive CTEs on earlier versions), CTEs to attribute each receipt to the withdrawals it covers, and careful index design to support the aggregate queries. The specific problem in the article uses a Stock table tracking receipt and withdrawal transactions; the technique generalises to any running-balance-and-match accounting problem. Benchmarks show the set-based query completing in seconds where the cursor solution takes minutes on the same data.<\/b><\/p>\n

\n

Sometimes, solutions to the simplest sounding requests can be very difficult to implement. A case in point is Phil Factor’s second Speed Phreak Competition: The ‘FIFO Stock Inventory’ SQL Problem<\/a>. The competition presented a somewhat-simplified, but still realistic inventory accounting problem, where approximately 1500 different stock items are either purchased, sold, or returned. The mission was to calculate the remaining count, and value, of each item after all stock transactions are performed, using only T-SQL. This is a classic FIFO (First In, First Out) problem, or a queue, where the first items purchased or returned must be the first items sold.<\/p>\n

In my previous article, based around the Running Total Speed Phreak challenge<\/a>, I commented that, in my experience, there is usually “an easy solution and a fast solution”. The easy solution, often iterative and cursor-based, is simple to implement, easy to maintain, and straightforward. However, performance often becomes painfully unacceptable as the volume of data grows. The fast solution, usually set-based, is often harder to understand, and therefore maintain, but will perform well and scale gracefully.<\/p>\n

The motivation behind this series of articles was to dissect and analyze the cleverest and fastest SQL solutions to the challenges posed by the Speed Phreak competitions, expose and explain the core set-based techniques on which they rely, and so make these techniques more widely accessible to developers who need a faster and more scalable solution than the cursor can offer.<\/p>\n

This FIFO challenge is considerably more complex that the previous Running Total challenge, and my motto, “there’s an easy way and a fast way”, doesn’t quite hold true. Here, it turns out that there’s a “slow and difficult” way, using a cursor, and a “fast and even more difficult” way, using set-based techniques. This time it helps to not only think outside the box but to turn the box inside out.<\/p>\n

The FIFO Stock Inventory Challenge<\/h1>\n

In this challenge, we have a table, Stock<\/span>, which we use to track the track movements of stock in and out of our imaginary stock warehouse. Our warehouse is initially empty, and stock then moves into the warehouse as a result of a stock purchase (tranCode = ‘IN’<\/span>), or due to a subsequent return (tranCode = ‘RET’<\/span>), and stock moves out of the warehouse when it is sold (tranCode = ‘OUT’<\/span>). Each type of stock tem is indentified by an ArticleID<\/span>. Each movement of stock in or out of the warehouse, due to a purchase, sale or return of a given item, results in a row being added to the Stock<\/span> table, uniquely identified by the value in the StockID<\/span> identity column, and describing how many items were added or removed, the price for purchases, the date of the transaction, and so on.<\/p>\n

Stock movements always occur on a first-in, first-out (FIFO) basis, so the stock initially purchased or returned is the stock that is first to be sold, and the current stock inventory for a given ArticleID<\/span> will consist of those items that were the last to be added to the inventory. The challenge, in essence, was to find the most efficient way to calculate the value of the remaining stock for a given item, given that:<\/p>\n