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,
Select @ModifiedDate = getdate()
Select @rowcount = 1
Set rowcount 15000
While @rowcount > 0
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
WAITFOR DELAY '00:00:02'
--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.