ОБНОВЛЕНИЕ или MERGE очень больших таблиц в SQL Server

Question

Мне нужно выполнять ежедневное обновление очень большого (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% - какие еще варианты я могу попробовать?

1. 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
   

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

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

Answer 1

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

Глядя на план запроса ниже, мы можем сказать, что TABLE SCAN ТАБЛИЦЫ 1 и финал 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, нам нужно:

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

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

Answer 2

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

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

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

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

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

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