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.

Your problem is the join to the temp table with no index
But I think you don't need a temp table. How many rows in Table1?

Hi,

As i was stating in the code comments, the reason for the #temp table was because of a more complicated preprocessing to choose which rows in Table1 to update with. The select into #temp was ONLY a sample but NOT the real code for building the #temp table. Of course if I am simply updating using the date on Col5 then I don't need the #temp table.

Ok ok. The #temp table was actually created using bcp in of a set of new data from data files coming in from 3rd parties. And these 80% of data in these files are already in Table1 from previous imports. So for these 80% of rows, instead of reinserting them into Table1, I just want to update the date time that their are reupdated. And this is the update statement for.

Table1 got 2.5 Million rows in it.

So you think I should index Col1, Col2, Col3 of the #temp table? Table1 already indexed with Col1, Col2, Col3 in 1 index.

Thx for ur help.

Yes I think so

OK. Let me try. I'll be back.....

Thx

How many rows are there in the temp table with
NeedsToBeUpdated != 1
When last was statistics updated
When last did you do a reorg
or recreate the index on Table1
What is the output from a showplan and optdiag

Can't help but think there is something you are not telling us.
Quote "The #temp table was actually created using bcp in"
It is not possible to bcp in to a #temp table

hi,
ok, tried to create index on #temp. But optimiser says the #temp table doesn't exist. And I had to try the wrapper st proc approach instead. So I made a wrapper st proc to create the #temp table and populated it and then created the index on it. And then tried to create the 2nd st proc with recompile which has the join and update on Table1 in it with recompile. But it won't let me create the st proc, saying #temp table not declared. So stuck.

Ok, u r right, I didn't bcp directly into the #temp. I bcp'ed into a permanent table without index on it 1st. Then I insert into the #temp after I created it in the st proc. Sorry, because that part only took 7.7secs to complete, I didn't think that was the problem and didn't need to include that in the post.

So I insert into #temp table select *,0 from bcp_table
the 0 was for the NeedToBeUpdated column.
then I did a join (without any indexes on #temp) with Table1 using Col1, Col2, Col3 like this:

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

And that took only 9.4 secs to run.
Out of the 700,000 rows in the #temp table, 668,000 rows were updated to 1. And the rest are 0.

Stats are updated automatically on Table1 every night at 2am.
Reorg? umm about 5 yrs ago
Recreate index on Table1? ummm ago 12 months ago.

Sigh, u r going to ask me to do reorg now, aint ya?
ok, then worth a try, I'll be back in a few days then.....
Thx

Hi pdreyer,
ok, done. Stats update had no effect on the update. But you've nailed it with the reorg! took 3 hrs 45 mins to reorg but now the update takes 6 mins 59 secs instead of 1.5 hrs. Well Done!!
Thank You Very Much for your help.