Tonight someone gave me passed me an awfull SQL Query. After a small few adaptions some the tablescans where eliminated. I still believed it would be quicker using a cursor. So, i started to create a SQL script with a cursor.
Create and populate test data
This was the Table to work against:
CREATE TABLE temptbl
(
ProductId INT primary key identity(1,1),
Period INT,
Qty INT,
BFQty INT,
CFQty INT
)
To populate the table, you can write a million of bytes or create a small loop
-- Populate the table with some data
INSERT INTO temptbl VALUES(200801,1,0,0)
INSERT INTO temptbl VALUES(200802,2,0,0)
INSERT INTO temptbl VALUES(200803,3,0,0)
DECLARE @RowCount INT, @Random INT, @Upper INT, @Lower INT, @periode INT
SET @Lower = 0
SET @Upper = 12
SET @RowCount = 0
WHILE @RowCount < 10000
BEGIN
SET @RowString = CAST(@RowCount AS VARCHAR(10))
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
INSERT INTO temptbl VALUES(200801+@random,@random,0,0)
SET @RowCount = @RowCount + 1
END
Select count(*) from temptbl
The solution with one single query
This was the – not sooo awful solution to fill the brought forward and carry forward fields
DECLARE @Value INT
UPDATE temptbl SET
@Value = (SELECT ISNULL(SUM(Qty),0) FROM temptbl AS InnerT WHERE InnerT.ProductId=temptbl.ProductId AND InnerT.Period<temptbl.Period),
BFQty = @Value,CFQty = @Value + Qty
Solution with (updatable) SQL cursor
The trick to let it run fast, is – if i understood it well – using the updateble cursor. Normally you should avoid using this type of cursor. But now we need to run through the whole table anyway and update each field using the data of the previous record. I highlighted the 2 important parts.
begin transaction
DECLARE MY_CURSOR Cursor DYNAMIC
FOR SELECT ProductId, qty FROM temptbl order by productId, period
FOR UPDATE OF BFQty, CFQty
Open My_Cursor
DECLARE @Value int, @Qty int, @prodId int, @OldProdId int
Fetch NEXT FROM MY_Cursor INTO @ProdId, @Qty
SET @OldProdId = @ProdId
SET @Value = 0
While (@@FETCH_STATUS = 0)
BEGIN
SET @OldProdId = @ProdId
UPDATE temptbl
set BFQty = @Value, CFQty = @Value + @Qty
WHERE CURRENT OF My_Cursor
SET @Value = @Value + @Qty
Fetch NEXT FROM MY_Cursor INTO @ProdId, @Qty
IF @OldProdId <> @ProdId
BEGIN
set @VALUE =0
END
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
commit transaction
Conclusion
Without the transaction it took 2 seconds for the cursor solution, and less than 1 second for the sql statement. Adding The transaction dropped the cursor solution also below a second.
BUT, it took me 1 hour and a half to create (and learn about updateble) cursor solutions. It’s 2:04 am, the other guy is in bed next to his girlfriend for some very reasonable time. And I even haven’t fully tested if the cursor solution has the correct return (i even doubt it).
| |
Normal
|
In transaction
|
Human work time
|
Total
|
|
Statement
|
< 1 sec
|
n.a.
|
10 minutes
|
winner
|
|
Cursor
|
2 seconds
|
< 1 sec
|
1,5 hour
|
loser
|
Next time, i might write a sql query right away. The optimizer of MSSQL does it’s work anyway.