Crawling Performance Of Update Query 1

[OrWolf]

Hi all,
I have an update query that I'm running in a SQL task in my DTS package to fill a new field with a sequential number. The table holds 118k records and I'm using the following code. The machine it's running on isn't speedy and certianly not a server, but it took 10 minutes to process 5k records so I'm hoping for suggestions to speed this up.

declare @RowNum int
declare @NewSeq nvarchar

Set @RowNum = 100000
--Test the first 5k rows
WHILE @RowNum < 105000
BEGIN
Set @RowNum = @RowNum + 1
UPDATE Test
SET SequenceNo = 'CON' + CAST(@RowNum As nvarchar)
FROM TEST
WHERE TestID In(SELECT TOP 1 TestID FROM Test WHERE SequenceNo Is Null)
END



Thanks!

 

[MDXer]

The reason is because this code updates 1 record at a time which is not really an optimized way to run the code.

If the Column needs to be sequential numbers starting at 100000 why not make the column an identity column with a seed of 100000?


Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[OrWolf]

I didn't realize that an identity column would populate when added to an existing table. Ran in less than two minutes. THANK YOU!!!!!!!

 

[MDXer]

good to hear it you got it solved.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck