Пакетные обновления SQL для большой таблицы с множеством индексов
У нас есть таблица с 18 столбцами, 7 из которых битовые столбцы, с более чем 100 миллионами строк. Он имеет 6 некластеризованных индексов, в 5 из которых есть столбец, который мне нужно обновить.
Первичный ключ (кластеризованный) - это уникальный идентификатор, называемый EntityID.
Мне нужно обновить один из битовых флагов в этой таблице, используя другую таблицу, содержащую значения, которые мне нужно синхронизировать. Мой менеджер попросил меня написать обновление, которое запускалось бы партиями, поскольку даже самые маленькие обновления требуют времени из-за всех индексов и большого количества строк в таблице. Он также попросил, чтобы обновление запускалось на основе отсортированного по EntityID ASC, он упомянул кое-что о сокращении количества читаемых страниц.
Я написал, вероятно, 5 разных версий отсортированного пакетного обновления, и они работают, но мне интересно узнать, есть ли уже хорошо отлаженный шаблон, который я мог бы использовать для этого.
Ответы 2
select 1
while(@@rowcount > 0)
begin
update top (100000) t
set t.bit = s.bit
from table t
join tbls s
on s.EntityID = t.EntityID
and t.bit != s.bit
end
Я бы не советовал сортировать. Пусть оптимизатор запросов сделает свое дело.
Если у вас есть t.bit null, я бы сделал это отдельно, поскольку or замедляет обновление.
Я предлагаю вам отключить все индексы, обновить, а затем включить в индексах.
@chadwin Вы можете спросить человека, у которого есть доступ? Отключение и последующее восстановление индексов должно иметь значение. Объясните им, зачем вам это нужно.
Этот процесс обновления просто не имеет достаточно высокого значения, чтобы заслужить отключение 5 индексов в таблице из 100 миллионов записей, которую пользователи и приложения тысячи раз в день подвергают воздействию, а затем восстановление индексов. Это процесс, который будет выполняться каждый день.
@chadwin Вы не знаете, что отключение и включение нецелесообразно, пока вы не протестируете. Это фрагментирует индексы всех этих индексов. Я буду придерживаться не сортировать - оптимизатор запросов, вероятно, умнее вас.
Я всего лишь говорю, чтобы отсортировать порядок исходных значений перед созданием пакета записей для обновления, это то, что меня просили сделать. Итак, если у меня есть 250 000 записей, которые мне нужно обновить ... затем отсортируйте 250 000 по EntityID и возьмите ТОП 10 000 записей, присоединитесь к entityid, обновите их, затем возьмите следующие 10 000 записей, присоединитесь к entityid, обновите их. Вот как меня попросил сделать мой менеджер / системный архитектор. Я ищу только хорошо проработанный сценарий, который это покрывает. В противном случае я просто использую свой собственный код.
Если у вас был код, который вы хотели отшлифовать, то почему вы его не опубликовали? Бьюсь об заклад, мой более простой ответ будет быстрее. PK - это физический порядок таблицы. Оптимизатор запросов не собирается тупить и плескаться.
Вам нужно будет провести некоторое тестирование, и это действительно зависит от того, сможете ли вы остановить другие запросы в это время, но довольно часто много быстрее
- сбросить индексы
- сделать вставки / обновления
- воссоздать индексы
У меня нет возможности отключить индексы. Это очень часто используемая таблица в производственной базе данных. И под сортировкой я имел в виду ... отсортируйте исходные значения по первичному ключу, а затем возьмите свои пакеты в этом порядке, таким образом вы уменьшите общее количество посещаемых страниц при выполнении обновлений.