Update Query versus Updatable cursor

by Damiaan Peeters 28. February 2009 02:02

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.

Who.I.am

Certified Umbraco, .net and Azure developer, seo lover. Magician in my spare time.

Month List