August 30, 2006 at 8:12 pm
I have a very large table (about 200,000 records), but there are only 2 fields in the table only one populated with data. I need to update the 2nd field with the first field's data.
UPDATE Table1
SET field2 = field1
This is taking a really long time to run about 3.5 minutes.
Is this normal? Can I create an index? What can I do to shorten the run time?
Thanks,
Ninel
August 30, 2006 at 9:09 pm
First of all - the task is really stupid.
Why anybody would need 2 columns with exactly the same data?
If you mean to make some modifications to the data later you better do it within this UPDATE, because it gonna take time for further updates, and it's gonna be longer than this update.
Second - don't create index on field2.
I would suggest to remove all indexes existing on this table and recreate it after UPDATE is completed.
_____________
Code for TallyGenerator
August 31, 2006 at 2:14 am
There are 2 problems here, excluding the why:
What existing indexes do you have on the table?
The system will take time to update the table and update the index's if it exists.
Since you are updating the whole table it would not make any differance if you have a index or not, except for the point above, as SQL will do a table scan(clustered indexes excluded).
Depending on how big your table is and how much memory you have avaiable, you could pin the table to memory and do your update and then unpin it...
Lukas Botha
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply