Large Updates on SQL 6.5 - suggestions???
Hi,
I am new here and am hoping someone might have performance tuning suggestions for a large update I have to do.
I am updating 350,000 records on a 6.5 database and I am incrimenting them out 5,000 at a time. It took about 17 hours to complete the update!!!
Does this sound really off? Is there anything I can do to speed this up? I am doing a straight update. I am not specifying any cursor and am not using any temp tables... but I am doing it in a stored procedure. Any suggestions about what I can do better would be very much appreciated.
Thanks again.
Large Number of Updates in 6.5 - Reply
Hi skhanal,
Thanks so much for your reply!
The database is a test database that I am working with in preparation for updating the real db next month. When I run the stored procedure I am the only one in the database so there are no other connections that could cause additional locks. I am incrementing 5000 at a time because when I tried to run it on the whole table I got errors on syslogs and tempdb and transaction logs even though trucate was turned on.
The procedure that I run checks the system_id (PK) and I have 3 variables. Hi, Low, Cur. I update field: "Name" by 5000 system_id's at a time and that seems to have solved my problem with filling up the logs and causing errors.
1 - I'm not sure if this db uses clustered index's but my query is based on the primary key, but I am not updating a primary or foreing key field, just a field in the table.
If I were, what could I do differently to help?
2 - Yes there are foreign key's in the table and there are several tables that reference this table.
3 - Tempdb and the log files are located on the same partition.
4 - I have not run the performance monitor to see what the back up could be. Not sure I would know what to do if I found a back up because it's one table and one field I have to do? Maybe a temporary table???
Would a dynamic cursor be good? It's my understanding that cursors are not recommended in stored procedures?
Maybe I should do an explicit transaction? Would it make any difference?
Hope this helps some? I just don't know where to go with this? The timing of this is important and I have to find a way to make this go as fast as it possibly can!
Thanks again so much for taking the time.
TedBud
SQL Updates 6.5 - update script
Hi,
Thanks for the post!!! Here is what
I'm putting in a stored procedure.
Any thoughts are most appreciated.
declare @high int, @lowest int, @current int
--initialize the loop
select @high = 350000, @lowest = 128, @current = 5000
--declare the loop conditions
while (@current < @high) begin
--the loop
update <table>(tblCusInfo)
set <field>(names)
where <pk>(system_id) between @lowest and @current
--update the loop
select @lowest = @current, @current = @current + 5000
end
Thanks