Help speeding up a Complex Query

[Niyoto]

I am receiving data in the following format:

ID ItemID Description Qty Store
1 1 White Ski 0 1
2 1 2
3 9 3
4 2 Blue Ski 3 1
5 4 2
6 0 3

I want to fix the table so it looks like this:

ID ItemID Description Qty Store
1 1 White Ski 0 1
2 1 1 2
3 1 9 3
4 2 Blue Ski 3 1
5 2 4 2
6 2 0 3

I wrote this query, but need to speed it up as it takes around 2.5 hours to run. (there are around 170,000 rows).
Here is the query I use, can anyone help me speed it up?

DECLARE @ID varchar(255), @INV varchar(255), @nINV varchar(255)
DECLARE kirk_cursor SCROLL CURSOR FOR
SELECT [ID], ItemID FROM kStoreQty order by [ID]

OPEN kirk_cursor

FETCH NEXT FROM kirk_cursor
INTO @ID, @INV

WHILE @@FETCH_STATUS = 0
BEGIN
IF @INV IS NULL
BEGIN
UPDATE kStoreQty SET
ItemID = @nINV
WHERE [ID] = @ID

FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
END
ELSE
BEGIN
SELECT @nINV = @INV

FETCH NEXT FROM kirk_cursor
INTO @ID, @INV
END
END

CLOSE kirk_cursor
DEALLOCATE kirk_cursor

 

[r937]

you might try asking this question in the microsoft sql/server forum
forum183

rudy

http://rudy.ca/

 

[CD12]

I don't know if this will work foryour data base, it did work on my Oracle data base.
Insted of a cursor and loop I just used an update statement:

UPDATE tablename t1
SET t1.ItemID = DECODE( t1.ItemID, 
                  null, ( SELECT MAX(t2.ItemID) 
                          FROM tablename t2 
                          WHERE t2.ID < t1.ID ), 
                  t1.ItemID );

This statement updates the ItemID with the maximum ItemId of the entries with a smaller ID, else it updates it with itself.

 

[CD12]

Do you have an index on ID? If not it would also help to fasten your query.