Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple Update Takes Hours

Status
Not open for further replies.

craving94509

Technical User
Sep 28, 2012
5
US
I have a simple update that seems to take a long time (6 hours). I am wondering if anyone can offer a way to speed it up?

350,000 rows with no null values in the source column. The source column is a text field that has values as long as 3900 characters. There are no indexes on the table.

UPDATE TABLE1 SET COLUMN1 = COLUMN2

Appreciate any help.
 
If I had to guess without any further information, I would speculate that your "table" may in reality is a view with a complicated join condition. That's one plausible explanation for an apparently straightforward operation taking an inordinately long time.

But of course there is no reason to guess. Practically the first thing you should do when faced with performance problems in your sql is to generate the execution plan for slow running statement. That will more than likely give us some clues as to what's happening with your update. Please do so and report your results here.
 
BTW how what's your table average row length?
Actually your statement needs approximately 1 GB in rollback segment at least, so it's not so simple as it looks like :).


Regards, Dima
 
look at updating that table in batches - this post has some examples of how to do it
(The CTAS example on that post needs to be considered carefully - CTAS will not create the new table with defaults and other settings from the source table)



Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
What happens if you try to emulate your update with a CTAS

i.e

create table2 as select column2 as column1, column2 as column2, ... columnN as columnN
from table1

if this method is really quick, then do it and just drop table1 afterwards and then
rename table2 to table1


In order to understand recursion, you must first understand recursion.
 
taupirho

a table created with CTAS will not keep all the original table definitions, and on those missing defaults are one of the aspects - in order to use the CTAS as the method, one would have after that to apply all those definitions that are not "copied" as part of the CTAS process.

So it is not just a process of running the CTAS, drop and rename. There is more to it

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
The OP doesn't give much context to the issue so your objections to my answer may or may not matter. In any case bringing the new table up to be the same definition as the old table would be trivial.

Let's see what the OP has to say about it


In order to understand recursion, you must first understand recursion.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top