UPDATE или MERGE очень больших таблиц в SQL Server

Мне нужно выполнить ежедневное обновление очень большого (300M записей) и широкого TABLE1. Исходные данные для обновлений расположены в другой таблице UTABLE, которая составляет 10% -25% строк TABLE1, но является узкой. Обе таблицы имеют record_id в качестве первичного ключа.

В настоящее время я воссоздаю TABLE1, используя следующий подход:

<!-- language: sql -->
    1) SELECT (required columns) INTO TMP_TABLE1 
    FROM TABLE1 T join UTABLE U on T.record_id=U.record_id  
    2) DROP TABLE TABLE1  
    3) sp_rename 'TMP_TABLE1', 'TABLE1'

Однако это занимает почти 40 минут на моем сервере (60 ГБ оперативной памяти для SQL Server). Я хочу добиться 50% производительности - какие еще варианты я могу попробовать?

  • MERGE и UPDATE - что-то вроде кода ниже работает быстрее только для очень маленькой таблицы UTABLE - в полном размере, все просто зависает:

    <!-- language: SQL -->
MERGE TABLE1 as target  
USING UTABLE as source  
ON target.record_id = source.record_id   
  WHEN MATCHED THEN   
    UPDATE SET Target.columns=source.columns

  • Я слышал, что я могу выполнять пакетный MERGE с помощью ROWCOUNT, но я не думаю, что он может быть достаточно быстрым для таблицы строк 300M.

  • Любые подсказки SQL-запросов, которые могут быть полезны?

Ответы

Ответ 1

На самом деле, я нашел общие рекомендации для таких запросов: Идея использовать SQL Merge или Update очень умна, но она терпит неудачу, когда нам нужно обновить многие записи (т.е. 75M) в большой и широкий таблицы (т.е. 240M).

Рассматривая план запроса ниже, мы можем сказать, что TABLE SCAN из TABLE1 и final MERGE занимают 90% времени.

MERGE TABLE1 as Target  
USING UTABLE as source  
ON Target.record_id = source.record_id   
WHEN MATCHED AND (condition) THEN   
    UPDATE SET Target.columns=source.columns

Итак, чтобы использовать MERGE, нам нужно:

  • Уменьшите количество строк, которые нам нужно обновить и правильно передать эту информацию на SQL Server. Это можно сделать, уменьшив UTABLE или указав дополнительный condition, который сужает часть, подлежащую объединению.
  • Убедитесь, что часть, подлежащая объединению, вписывается в память, иначе запрос работает медленнее. Создание TABLE1 в два раза меньше сократило мое реальное время запроса с 11 часов до 40 минут.

Как упоминалось выше, вы можете использовать синтаксис UPDATE и использовать предложение WHERE для сужения части, подлежащей объединению - это даст такие же результаты. Также избегайте индексации TABLE1, так как это приведет к дополнительной работе по восстановлению индекса во время MERGE

Ответ 2

Прежде всего, я узнаю, где ваше узкое место - ваш процессор привязан или простаивает? Другими словами - ваша подсистема IO может правильно обрабатывать нагрузку?

Воспроизведение полной таблицы - это много нагрузки ввода-вывода, не говоря уже о том, что для ее размещения потребуется много места, чтобы в два раза временно хранить таблицу.

Вам нужно выполнить MERGE - из того, что я вижу, достаточно простого обновления. Пример:

UPDATE
    TABLE1
SET
    ColumnX = UTABLE.ColumnX
    ...
FROM
    TABLE1
INNER JOIN
    UTABLE ON TABLE1.record_id = UTABLE.record_id

Вы можете загружать обновления с помощью ROWCOUNT, но это не ускорит выполнение, это поможет только уменьшить общую блокировку.

Также - какие индексы у вас есть на столе? Возможно, быстрее отключить индексы перед обновлением, а затем перестроить их с нуля (только некластеризованные).