Simple update stament takes too long to finish

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 2000 Forums

Transact-SQL (2000)

Simple update stament takes too long to finish

Author

Topic

ttran
Starting Member

23 Posts

Posted - 2006-10-31 : 12:08:50

Hi,

I have a very simple update statement on a table to update about 3/4 of million rows at the end of the day before going home. It took about 15 hours, and hasn't finish yet. So I have to cancel it. Please help !!!! We desperately need to update the table !

SQL Server 2005
MS SQL Server Management Studio, v 9.00.1399.00 Operating System 5.0.2195

My update statement:
update tst
set del = 1, dts = getdate()
where del = 0 and id = 'mlt'

TST table:
have 1,746,065 rows
have 22 columns
Primary key: pid + sq

Thank you in advance

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-31 : 12:28:33

Try adding an index to "id". Indexing "del" may or may not yield an improvement, since it appears to be boolean data.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

Kristen
Test

22859 Posts

Posted - 2006-10-31 : 14:20:35

"It took about 15 hours ... So I have to cancel it"

It may take 15 hours to cancel :-(

Can you update in batches?


DECLARE @intRowCount int
SET @intRowCount = 1 -- Force first iteration
WHILE @intRowCount > 0
BEGIN
SET ROWCOUNT 10000  -- Limit to 10,000 rows - pick a sensible batch size
update tst
set del = 1, dts = getdate()
where del = 0 and id = 'mlt' 
SELECT @intRowCount = @@ROWCOUNT
SET ROWCOUNT 0 -- Cancel the LIMIT - this is very important!
WAITFOR DELAY '000:00:05' -- Waitfor 5 seconds to allow other processes to run - optional!
END

If neither del = 0 nor id = 'mlt' are indexed then it will take a long time to SCAN for the data to be updated. If that is the case it might be better to FIRST put the KEYs for the records to be deleted into a temporary table, properly indexed, and then JOIN that to the delete statement

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72804

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 15:17:10

Sometime, I don't know why, it helps to write

update tst
set del = 1, dts = getdate()
where del = 0 and id = 'mlt' and pid >= 0-- or whatever value primary key (identity) started with

Peter Larsson
Helsingborg, Sweden

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-31 : 16:09:35

quote:
Originally posted by Peso

Sometime, I don't know why, it helps to write

update tst
set del = 1, dts = getdate()
where del = 0 and id = 'mlt' and pid >= 0-- or whatever value primary key (identity) started with

Peter Larsson
Helsingborg, Sweden

Well that's one I've never heard of before. Does it change the execution plan?

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-10-31 : 16:33:38

>>Well that's one I've never heard of before. Does it change the execution plan?

It can...

http://weblogs.sqlteam.com/davidm/archive/2005/04/07/4291.aspx

DavidM

Production is just another testing cycle

Kristen
Test

22859 Posts

Posted - 2006-10-31 : 17:28:57

Got to go polish my mirror for the new smoke that is being delivered tomorrow!

ttran
Starting Member

23 Posts

Posted - 2006-10-31 : 17:30:04

Trying anything now does take a long time...
But I'll keep you informed.
Thank you for all suggestions and ideas.

ttran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources