|
Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate large update in small batches with waitfor delay to prevent blocking.
-- T-SQL large update table
USE tempdb;
SELECT * INTO SOD
FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
--(121317 row(s) affected)
-- SQL update in batches of 10,000
WHILE (2 > 1)
BEGIN
BEGIN TRANSACTION
UPDATE TOP ( 10000 ) SOD
SET UnitPriceDiscount = 0.08,
ModifiedDate = CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112))
WHERE ModifiedDate < CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112))
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
-- 1 second delay
WAITFOR DELAY '00:00:01'
END -- WHILE
GO
/* Messages
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(10000 row(s) affected)
(1317 row(s) affected)
(0 row(s) affected)
*/
-- Cleanup
DROP TABLE SOD
GO
------------
Related articles:
What is the recommended batch size for SqlBulkCopy?
Batch Update on a very huge table
How to Use Batch Size to Speed Mass Updates, Inserts and Deletes
UPDATE (Transact-SQL)
http://www.sqlusa.com/bestpractices/largeupdate/
|