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

 

[tlbroadbent]

The following query may work for you.

Update kStoreQty
Set ItemID =
(Select Top 1 ItemID
From kStoreQty k
Where k.id < kStoreQty.id
And ItemID Is Not Null
Order By ID Desc)
Where itemid is null

Your script would likely run faster if you used correct data types. The ID and ItemID appear to be integers in the table. You created the variables as varchar(255). That means SQL must convert the variable or the column on every pass through the loop. Then when the ItemId is inserted it must be converted. All of this conversion will add greatly to the time required. Not only that but SQL may not use the indexes (if any exist) because you used the wrong data types.

Is the table indexed? If not, make sure you have an index on the ID column. Remove additional indexes during the update as tthey will impeded performance. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.