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 2005 Forums
 Transact-SQL (2005)
 UPDATE query runs real slow

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 it

also have you added any index on table. it might be worth adding index on search column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-21 : 18:26:55
Create an index on mycontrol

-Chad
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-22 : 18:45:40
DELETE TOP 1


-Chad
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -