Really slow update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Really slow update

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2009-12-15 : 14:16:02

I created a simple Int field in a table with 26 million rows. Tried to populate the field incrementally:
Update Table
Set Field = @i + field + @i + 1
Let it run for 9 hours, still didn't finish. I then tried the same thing, but updated a field with NULL. Same thing.
Question is, is there something I am doing wrong or is there something wrong with the database?

Thank you

Robert R. Barnes

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-15 : 14:35:39

First, what are you trying to accomplish? Number the records from 1 to 26 million...? If so, make the field an identity column


ALTER TABLE yourTable
ADD id int identity(1, 1) not null

Also, your actual script would be helpful, instead of only a part of it

Lopaka
Starting Member

48 Posts

Posted - 2009-12-15 : 14:56:05

here is the actual script:
ALTER TABLE table ADD PK INT NULL
go

-- Populate the new Primary Key
DECLARE @i INT
SET @i = 0
UPDATE table
SET @i = PK = @i + 1

Changed the int to an identity, however i think it will result in the same duration...:(

Robert R. Barnes

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-15 : 15:05:03

I doubt it. But make sure nothing is blocking it by executing sp_who2 in a seperate query window.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources