Hi guys,

I know this has been discussed before but I don't think it was answered completely. I got a st. proc. which does a fairly simple update to 1 single table. I am NOT using cursors (knowing how bad it is), just 1 single update. The statements is required to update 700,000 rows And it takes 1 hr 28 mins to complete. The table being updated is indexed. I've already created a #temp table to 1st determine which rows to be updated (which only takes 3.163 secs to do) then I simply join the #temp table with the real table for the update. Code as below:

declare @tmp_CurrentDate datetime
select @tmp_CurrentDate = getdate()
create table #temp_of_Table1
(Col1 varchar(10),
Col2 varchar(10),
Col3 varchar(10),
Col4 varchar(10),
NeedsToBeUpdated tinyint)

-- ok, the select for this was a bit more complicated then this
-- but this is just a simplified version of it for arguments sack
-- I know if I am just filtering by datetime then I don't need this temp table
-- and I don't need a join. But this is just an example.
insert into #temp_of_Table1
select Col1,Col2,Col3,Col4,1 from Table1
where Col5 < "20080101"
-- where Col5 is the Last Updated date time stamp of the row in Table1
-- and Col1,Col2,Col3 is a long key of Table1
-- up to this point it only takes a few secs to run
-- and #temp_of_Table1 is about 700,000 rows in size.

update Table1
set Col5 = @tmp_CurrentDate
from Table1 a, #temp_of_Table1 b
where b.NeedsToBeUpdated = 1
and a.Col1 = b.Col1
and a.Col2 = b.Col2
and a.Col3 = b.Col3

-- ok

Now, this update took 1 hr 28 mins. Help please??? any one?

Thank You in Advance.