Транзакции становятся медленнее в SQL Server

Операции слияния часто можно улучшить, избегая лишних обновлений. Если обновлять нечего, поскольку целевая и исходная строки равны, не обновляйте строку. Это очень эффективно в случаях, когда большинство строк не изменились, потому что SQL Server записывает гораздо меньше информации в журнал транзакций.

Чтобы избежать лишних обновлений при операциях слияния, напишите оператор слияния следующим образом:

MERGE INTO target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED 
  AND ((t.col1 <> s.col1 
       OR t.col1 IS NULL AND s.col1 IS NOT NULL
       OR t.col1 IS NOT NULL AND s.col1 IS NULL)
  OR (t.col2 <> s.col2 
       OR t.col2 IS NULL AND s.col2 IS NOT NULL
       OR t.col2 IS NOT NULL AND s.col2 IS NULL)
  OR (t.col2 <> s.col3 
       OR t.col3 IS NULL AND s.col3 IS NOT NULL
       OR t.col3 IS NOT NULL AND s.col3 IS NULL))
THEN UPDATE SET
  col1 = s.col1, col2 = s.col2, col3 = s.col3
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (id, col1, col2, col3)
    VALUES (s.id, s.col1, s.col2, s.col3);

Что мы сделали, так это то, что вместо того, чтобы форсировать процесс CHECKPOINT, мы ввели искусственную задержку в WHILE LOOP, чтобы транзакции не регулировались.

Нам удалось решить проблему нехватки памяти из-за регулирования транзакций в среде SQL Server. У нас были миллионы строк в промежуточной таблице. Эти 10 000 пакетов и задержка позволили убедиться, что мы не перегружаем сервер. Были люди, обращающиеся к серверу.

DECLARE @RowCount INT;

SET @RowCount = (SELECT COUNT(*) FROM StagingTable);

WHILE (@RowCount > 0)
BEGIN

    BEGIN TRANSACTION

    DELETE TOP 10000 * 
    FROM <Staging Table> 
    OUTPUT deleted.* INTO @TableVariable

    MERGE INTO <Final Table> 
    USING @TableVariable

    COMMIT TRANSACTION

    WAITFOR DELAY '00:00:10'; --artificially introduce 10 seconds delay

    SET @RowCount = (SELECT COUNT(*) FROM StagingTable);

END