Блог

Главная - Вопросы по программированию - Есть ли способ оптимизировать запрос на пакетное обновление?

Есть ли способ оптимизировать запрос на пакетное обновление?

#sql #sql-server #query-optimization

#sql #sql-сервер #запрос-оптимизация

Вопрос:

Необходимо обновить таблицу со 100 миллионами записей. Есть ли способ оптимизировать этот запрос?

 DECLARE @rows INT = 1;

WHILE @rows > 0
BEGIN
    UPDATE TOP(2000) T1
    SET T1.SomeUuid = T2.SomeUuid
    FROM dbo.Table1 T1
    INNER JOIN Table2 T2 ON T1.SomeId = T2.SomeId
                         AND T1.AnotherId = T2.AnotherId
                         AND T1.SomeField = T2.SomeField
    WHERE T1.SomeUuid IS NULL

    SET @rows = @@ROWCOUNT
END

Для объединения таблиц должен использоваться тройной ключ.

Комментарии:

1. Вы могли бы попробовать большие пакеты, например 10000 или 50000.

2. Зачем вам для этого нужен цикл?

3. Часто быстрее восстановить таблицу, создав новую таблицу с измененными значениями, а затем выполнить массовую вставку всех строк.

4. Вы могли бы добавить отфильтрованный индекс с условием where — Index Table1 ДЛЯ SomeId, ГДЕ SomeUuid = null и т.д. Убедитесь, что в Table2 есть индекс для SomeId, AnotherID и someField

Ответ №1:

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

 CREATE TABLE dbo.#data (SomeUuid bigint, AnotherId bigint);
CREATE TABLE dbo.#data_for_iteration (SomeUuid bigint, AnotherId bigint);

-- Get the Ids (Pk or Unique )
INSERT dbo.#data(SomeUuid, AnotherId)
SELECT SomeUuid, AnotherId
FROM dbo.Table1 T1
INNER JOIN Table2 T2 ON T1.SomeId = T2.SomeId
                        AND T1.AnotherId = T2.AnotherId
                        AND T1.SomeField = T2.SomeField
WHERE T1.SomeUuid IS NULL;


WHILE 1=1
BEGIN;
    DELETE TOP (2000) #data OUTPUT Deleted .SomeUuid INTO #data_for_iteration ( SomeUuid );
        -- Exite here when done
        IF @@ROWCOUNT = 0 BEGIN; PRINT  'No more data to process. Terminating'; BREAK; END;

    UPDATE T1
        SET T1.SomeUuid = T2.SomeUuid
    FROM dbo.Table1 T1
    INNER JOIN #data_for_iteration T2 ON T1.SomeId = T2.SomeId             
    
    TRUNCATE TABLE #data_for_iteration;           
END;