Update records in batches

Last Post 19 Mar 2008 11:27 AM by trans53. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
19 Mar 2008 08:58 AM
Hi guys, i need your suggestion please:
We have this 11m rows table and i need to update 300K rows in that table.
The problem is that this is production database and i don't have any downtime at all.
This table is also replicated so i am trying to do some kind of WAITFOR DELAY here.
Another problem is that we have a timeouts set to 30 sec when customers trying to access the webpage.

Now i think where the problem is:

i have this script to update this table in batches but the script seems not to be very sufficient.
Every time the loop is executed the number of logical read increasing and the elapsed time is also going up so i can see the loop can execute more than 30sec. if i undesrtood correctly this means a timeouts from application.

This table have a primary clustered index based on the customerid and i was windering if there is a better way to do updates in batches.

Can you please suggest how to do this better? Thanks

Declare @rowcount INT,
@ModifiedDate DATETIME

Select @ModifiedDate = getdate()
Select @rowcount = 1
Set rowcount 15000

While @rowcount > 0

Begin

UPDATE dbo.Customer
SET Address1 = 'address1',
Address2 = 'address2',
phone1 = 'phone1',
phone2 = 'phone2',
email = 'email',
ModifiedDate = @ModifiedDate,
ModifiedBy = 'Contact_Info'
WHERE LabelID = 3
AND ModifiedDate <> @ModifiedDate

Select @rowcount = @@rowcount

print @rowcount

WAITFOR DELAY '00:00:02'

End

--Ouput from the fisrt loop

Table 'Customer'. Scan count 30001, logical reads 388036, physical reads 43, read-ahead reads 1587.
Table 'Worktable'. Scan count 2, logical reads 30715, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 2984 ms, elapsed time = 3075 ms.

--Ouput from the last loop

Table 'Customer'. Scan count 26905, logical reads 651550, physical reads 460, read-ahead reads 187746.
Table 'Worktable'. Scan count 2, logical reads 27547, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 5437 ms, elapsed time = 215031 ms.
trans53
New Member
New Member

--
19 Mar 2008 11:27 AM
Thank you, this helps a lot
SQLUSA
New Member
New Member

--
19 Mar 2008 10:41 PM
15000 updates still may be too high if you have performance issues.

You can go down to 1000 for example.

WAITFOR DELAY '00:00:01'

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Performance Training http://www.sqlusa.com/order2005highperformance/
You are not authorized to post a reply.

Acceptable Use Policy