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.
Author |
Topic |
parrot
Posting Yak Master
132 Posts |
Posted - 2012-08-21 : 17:54:47
|
I am doing a mass updating on my sql 2005 database using dynamic queries generated by my program. The programs creates the table and then inserts new records. This runs very quickly. However, at the end of the INSERT routine the program then alters the table to add a new field and then does an UPDATE to the table for thousands of records. It took the program about 10 minutes to insert 290,000 records into a new table. However, it is taking hours to do the updates to the 290,000 records. Are UPDATES that much more inefficient or is there some option I can set to speed up updates? I have the Recovery model set to Simple to keep the size of the log file down. The UPDATE instruction is very simple such as UPDATE mytable SET myfield = 'xxxx' WHERE mycontrol = '123"Does anyone have an answer as to why the UPDATE takes so long or do they actually run slower than INSERTS?Dave |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-21 : 17:56:56
|
why is column added in between? why not create table in beginning itself and then do insert/update following italso have you added any index on table. it might be worth adding index on search column------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2012-08-21 : 18:13:35
|
The column is added after all inserts have been made. I am doing a dynamic creation from input that is variable. That is why I create the table on the fly - I don't know ahead of time what's in the input stream. So I create the table, do the inserts and then do the updates. I have no choice other than to do it this way. However, I could try to create a primary key for the table to see if that speeds things up. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-21 : 18:26:55
|
Create an index on mycontrol-Chad |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2012-08-21 : 18:29:38
|
As soon as my program finishes I will add an index to my control field and see if that speeds up the process. Thanks to all who replied. |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-22 : 01:46:53
|
Have you tried adding a CLUSTERED INDEX, update statistics and then run the UPDATE?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2012-08-22 : 18:17:33
|
I added an index to the control fields and the update went much faster. Thanks to everyone for their help. I have one more problem now in that I am trying to add primary keys to a table but it will not allow it because of duplicate records. So I tried to delete one of the duplicate records and I receive an error message saying removing one of the rows will not make the row unique or may alter mutiple rows. So how can I delete a duplicate record from a table with no primary keys? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-22 : 18:45:40
|
DELETE TOP 1-Chad |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2012-08-22 : 20:36:26
|
I went ahead and deleted the whole file using DELETE FROM TABLE since I have to recreate it anyhow. I still don't understand why I can't delete using right control click and delete on the table record itself. |
|
|
|
|
|