如何提高約 50.000.000 個條目的 UPDATE 性能

我們有一個查詢使用臨時表來更新數據庫中的某些列：

CREATE TABLE #NEW_OLD ( 
   NEW nvarchar(20), 
   OLD nvarchar(20) 
); 
INSERT INTO #NEW_OLD
VALUES
('DOMAIN\KEY', 'NEW_KEY'),
('DOMAIN\KEY2', 'NEW_KEY2')
(...) 
-- (~ 5.000 keys are inserted)

BEGIN TRANSACTION
BEGIN TRY

   DECLARE @TABLES_COLUMNS TABLE(TABLE_NAME varchar(100), COLUMN_NAME varchar(100))

   INSERT INTO @TABLES_COLUMNS (TABLE_NAME, COLUMN_NAME)
   VALUES
   ('[dbo].[MyTable1]', '[MyColumn1]'),
   ('[dbo].[MyTable2]', '[MyColumn2]')
   (...)
   -- ~ 20 columns are included. The tables have ~ 10.000.000 rows.

   -- Looping over table and column name variables with cursor
   DECLARE TABLES_COLUMNS_CURSOR CURSOR LOCAL FAST_FORWARD FOR
       SELECT TABLE_NAME, COLUMN_NAME 
       FROM @TABLES_COLUMNS

   -- Variables to hold table and column name
   DECLARE @TABLE_NAME varchar(30), @COLUMN_NAME varchar(30)

   OPEN TABLES_COLUMNS_CURSOR

   -- Instead of fetching twice, we set up no-exit loop
   WHILE 1 = 1
   BEGIN

       -- And then fetch
       FETCH NEXT FROM TABLES_COLUMNS_CURSOR 
       INTO @TABLE_NAME, @COLUMN_NAME

       -- And stop if nothing is fetched
       IF @@FETCH_STATUS <> 0
       BEGIN
           BREAK
       END

       -- Now, update each field with the new key that is associated with that key in the temporary table
       EXECUTE
       (
           'UPDATE ' + @TABLE_NAME + ' ' + 
           'SET ' + @COLUMN_NAME + ' = new_old.NEW ' + 
           'FROM ' + @TABLE_NAME + 'AS t ' + 
           'INNER JOIN #NEW_OLD AS new_old ' + 
           'ON t.' + @COLUMN_NAME + 'COLLATE DATABASE_DEFAULT = new_old.OLD COLLATE DATABASE_DEFAULT'
       )

   END

   CLOSE TABLES_COLUMNS_CURSOR
   DEALLOCATE TABLES_COLUMNS_CURSOR

END TRY
BEGIN CATCH

   IF @@TRANCOUNT > 0
       ROLLBACK TRANSACTION;
END CATCH;


IF @@TRANCOUNT > 0
   COMMIT TRANSACTION;

此查詢需要數小時才能執行。在 SQL Server 2008 中，動態部分 ( EXECUTE) 不應該對性能影響太大。

如何提高性能？

還有一件事。如果這些表非常大，即使你有很好的索引，你也應該循環更新這些表的行，因為事務日誌每次都在增長，大事務需要更多的時間。

例子：

CREATE TABLE UpdateTable(id int IDENTITY(1,1), name varchar(200), namenew varchar(200))

INSERT INTO UpdateTable VALUES ('Old1','New2');
INSERT INTO UpdateTable VALUES ('Old2','New2');
go

SET NOCOUNT ON;
DECLARE @cnt INT = -1;

WHILE (@cnt<> 0)
BEGIN
   UPDATE TOP (1) UpdateTable
   SET NAME = namenew
   WHERE name <> namenew

   SET @cnt = @@ROWCOUNT;
END    

您應該將頂部 (1) 更改為可接受的值，例如 100000 或更大，您可以在您的環境中測量它

引用自：https://dba.stackexchange.com/questions/126089