Предложения по оптимизации, необходимые для статуса SQL UPDATE. Используются две таблицы записей ~5 миллионов

Все ответы до сих пор твердо ориентированы на часть SQL вашего вопроса, но в некоторой степени пренебрегают частью SAS. Я настоятельно рекомендую попробовать обновление/изменение шага данных/ merge вместо proc sql для такого рода обновлений. Должна быть возможность отсортировать обе таблицы и применить аналогичную логику из вашего SQL, чтобы гарантировать обновление правильных строк/столбцов.

Я видел, как подобные обновления выполнялись за считанные минуты для 20 или более миллионов строк.

Кроме того, посетите http://runsubmit.com , сайт в стиле stackoverflow для SAS, чтобы получить дополнительные ответы по SAS.

Раскрытие информации: я сотрудник SAS. Я не имею ничего общего с runsubmit, который запускается независимо.

Я не знаком с вариантом SQL, который вы используете. Однако независимо от того, добьётесь ли вы большей производительности или нет, вы должны использовать синтаксис соединения ANSI. Вот как это будет выглядеть в T-SQL, измените его для своей системы:

UPDATE N
SET N.2ND_ID = O.2ND_ID
FROM
   NEW_TABLE AS N
   INNER JOIN OLD_TABLE AS O ON N.PK_ID = O.PK_ID
WHERE
   N.2ND_ID <> O.2ND_ID
   AND N.3RD_ID IS NOT NULL  
   AND O.3RD_ID IS NULL  
   AND N.CODE IS NOT NULL  
   AND O.CODE IS NULL

Обратите внимание, что дополнительные условия, которые я удалил, не являются необходимыми, например, N.2ND_ID <> O.2ND_ID уже гарантирует, что эти два столбца не равны нулю.

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

1. Разбейте обновление на пакеты (небольшие кусочки, перебирающие весь набор). Хотя это звучит как противоречие обычному совету базы данных «не зацикливаться, используйте наборы», на самом деле это не так: вы просто используете меньшие наборы, а не зацикливаетесь на уровне строк. Лучший способ пакетного обновления, подобного этому, — «пройтись по кластеризованному индексу». Я не уверен, что этот термин имеет смысл в используемой вами СУБД, но, по сути, он означает выбор блоков, которые вы обновляете во время каждого цикла, на основе порядка, в котором они будут найдены в объекте таблицы, который вы обновляете. PK_ID звучит так, как будто это кандидат на использование, но если необработанные данные таблицы не упорядочены по этому столбцу, это станет более сложным. В T-SQL цикл пакетной обработки может выглядеть так:

   DECLARE
      @ID int,
      @Count int
   
   SET @ID = 1
   SET @Count = 1
   
   WHILE @Count > 0 BEGIN
      UPDATE N
      SET N.2ND_ID = O.2ND_ID
      FROM
         NEW_TABLE AS N
         INNER JOIN OLD_TABLE AS O ON N.PK_ID = O.PK_ID
      WHERE
         N.2ND_ID <> O.2ND_ID
         AND N.3RD_ID IS NOT NULL  
         AND O.3RD_ID IS NULL  
         AND N.CODE IS NOT NULL  
         AND O.CODE IS NULL
         AND N.PK_ID BETWEEN @ID AND @ID + 4999
      SET @Count = @@RowCount
      SET @ID = @ID + 5000
   END
   

   В этом примере предполагается, что ваш столбец PK_ID плотно упакован, что каждое обновление действительно достигает 5000 строк. Если это не так, то переключитесь на метод с использованием TOP 5000 и либо выведите обновленные PK_ID в таблицу, либо найдите @StartID и @EndID для следующего обновления за один шаг, а затем выполните его.

   По моему опыту, хорошие размеры пакетов, как правило, составляют от 1000 до 20000 строк. На сервере MS-SQL оптимальная точка находится чуть ниже числа, которое вызывает переключение с поиска на сканирование (поскольку в конечном итоге механизм базы данных предполагает, что одно сканирование дешевле, чем множество операций поиска, хотя часто это бывает не так). неправильно при работе с таблицами с 5 миллионами строк).

2. Сначала выберите идентификаторы и данные для обновления в рабочей/временной таблице, а затем присоединитесь к ней. Идея состоит в том, чтобы справиться с масштабным сканированием с помощью простого оператора INSERT, затем добавить индексы во временную таблицу и выполнить обновление без использования сложного предложения WHERE. Как только таблица содержит только строки, которые нужно обновить, и необходимые столбцы, предложение WHERE может не только потерять большинство своих условий, но и временная таблица будет иметь намного меньше строк и гораздо больше строк на странице (поскольку в ней нет лишних столбцов). что значительно повысит производительность. Это можно сделать даже поэтапно, когда создается «тень» новой таблицы, затем «тень» старой таблицы, затем соединение между ними и, наконец, обратное соединение с новой таблицей для ее обновления. Хотя это звучит как много работы, я думаю, вы будете удивлены совершенно сумасшедшей скоростью завершения, которую это может предложить.

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

Не используйте обновление, создайте аналогичную новую таблицу и используйте вставку в (поля) для выбора полей из обеих таблиц.

• Отбросьте индексы перед выполнением запроса.
• Отбросьте триггеры перед выполнением запроса.

Что-то типа:

insert into NEW_TABLE (field1,field2,NEW_2ND_ID)  
select field1, field2, (SELECT 2ND_ID FROM OLD_TABLE....) from NEW_TABLE

• Пересоздайте индексы после завершения запроса.
• Повторное создание триггеров после завершения запроса.

(В конце концов вы замените этой новой таблицей существующую таблицу)

Я думаю, что сейчас это вложенный подзапрос, поэтому оператор Select будет запущен для количества записей, соответствующих условию where.

Однако я бы порекомендовал вам перейти на SQL - обновление с присоединением. Объяснение можно найти здесь: http://bytes.com/topic/oracle/answers/65819-sql-update-join-syntax.

Когда у вас есть обновление с JOIN, примените соответствующие индексы.

Также не стоит использовать те индексы, которые включают 2nd_id, их нужно отключать и затем перестраивать после обновления, так как это может быть массовое обновление данных.

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