Update process slow - 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 2000 Forums

Transact-SQL (2000)

Update process slow

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2006-05-09 : 16:59:56

I have a table that is around 4mm records. I have a field in that table that needs to be updated with values from another table. This process is taking hours, when it should be minutes. Here is the code:

Create index ix_cptcodemodall on Tbl_Txn_Temp (TxnCptCde,TxnModAll)
go
--run time 00:44:57
update Tbl_Txn_Temp
set CPT_Mod = Tbl_Txn_Temp.TxnCptCde + Tbl_Txn_Temp.TxnModAll
go
--run time 00:06:14
Create index ix_CPT_ModActual on Tbl_Txn_Temp (CPT_Mod)
go
Create index ix_cpt_cw_CPT_Mod on cpt_cw (CPT_Mod)

go

--Run time:
UPDATE Tbl_Txn_Temp
SET CPT_26_TC = cpt.CPT_26_TC
FROM Tbl_Txn_Temp as txn
join cpt_cw as cpt
on txn.CPT_Mod = cpt.CPT_Mod

You can see where I've created the indexes in hope that this would speed up the process but nothing so far. I've looked at the tempdb and the log file and neither are ballooning.

Any help is apreciated..

Job

harrisw48
Starting Member

34 Posts

Posted - 2006-05-10 : 02:42:45

Is your tempdb and your data on the same drive.?
If so try separating them.

Kristen
Test

22859 Posts

Posted - 2006-05-10 : 02:57:09

I may be being thick here, but is that the right syntax? Shouldn't the Alias be used?

UPDATE ~~Tbl_Txn_Temp~~ txn
SET CPT_26_TC = cpt.CPT_26_TC
FROM Tbl_Txn_Temp as txn
join cpt_cw as cpt
on txn.CPT_Mod = cpt.CPT_Mod

I presume that txn.CPT_Mod and cpt.CPT_Mod are unique within the table (otherwise the JOIN is ambiguous for this update), therefore adding UNIQUE to the CREATE INDEX may give the optimiser a further help.

Other possibility is to do the update in batches, so that the transaction log is not huge - but I don't know if that improves runtime overall.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-10 : 03:09:57

And also to be specific on the update column's table.

UPDATE txn
SET    txn.CPT_26_TC = cpt.CPT_26_TC
FROM   Tbl_Txn_Temp as txn
join   cpt_cw as cpt
on     txn.CPT_Mod = cpt.CPT_Mod

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-10 : 03:20:23

quote:
Originally posted by khtan

And also to be specific on the update column's table.
UPDATE txn
SET txn.CPT_26_TC = cpt.CPT_26_TC
FROM Tbl_Txn_Temp as txn
join cpt_cw as cpt
on txn.CPT_Mod = cpt.CPT_Mod

KH

Does it make any difference on performance if you specify the tablename or alias name. column on update statement??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Kristen
Test

22859 Posts

Posted - 2006-05-10 : 05:40:18

"Does it make any difference on performance if you specify the tablename or alias name. column on update statement??"

I've never done it - I figure SQL can only update the table specified in the UPDATE phrase! But that doesn't mean its Best Practice of course ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-10 : 06:59:46

It wont make any difference. It is just for clarity

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-10 : 07:57:18

Yes. It is just for clarity

Job
Yak Posting Veteran

69 Posts

Posted - 2006-05-10 : 11:44:47

quote:
Originally posted by Kristen

I presume that txn.CPT_Mod and cpt.CPT_Mod are unique within the table (otherwise the JOIN is ambiguous for this update), therefore adding UNIQUE to the CREATE INDEX may give the optimiser a further help.

Other possibility is to do the update in batches, so that the transaction log is not huge - but I don't know if that improves runtime overall.

Kristen

The cpt.CPT_Mod is unique for that table, but it is not unique in the txn table. I tried in batches and it wasn't any faster. Also, I do have the tempdb and this database on the same hd, I really don't have any other place to put other than on another network drive or something...

Kristen
Test

22859 Posts

Posted - 2006-05-10 : 14:23:40

Ah, OK. What's the average number of txn.CPT_Mod for each cpt.CPT_Mod?

Its probably irrelevant, but maybe its using a table scan instead of a bookmark lookup ...

Might be worth looking at the Query plan to see what SQL has decided to do.

Kristen

Job
Yak Posting Veteran

69 Posts

Posted - 2006-05-10 : 14:58:46

Not sure how to look at the Query Plan unless that is the 'Display Estimated Execution Plan'. Btw, if I try the different syntax referring to the alias it gives the 'The multi-part identifier' error, but works fine the original way...not sure..is one way better than the other?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources