Пакетные обновления SQL для большой таблицы со многими индексами

У нас есть таблица с 18 столбцами, 7 из которых битовые столбцы, с более чем 100 миллионами строк. Он имеет 6 некластеризованных индексов, 5 из которых содержат столбец, который мне нужно обновить.

Первичный ключ (кластеризованный) представляет собой уникальный идентификатор, называемый EntityID.

Мне нужно обновить один из битовых флагов в этой таблице, используя другую таблицу, содержащую значения, которые мне нужно синхронизировать. Мой менеджер попросил меня написать обновление для запуска в пакетном режиме, поскольку даже самые маленькие обновления требуют времени из-за всех индексов и большого количества строк в таблице. Он также попросил, чтобы обновление запускалось на основе ASC, отсортированного по EntityID, он упомянул что-то об уменьшении количества читаемых страниц.

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


sql sql-server tsql sql-optimization
person Chad Baldwin    schedule 22.05.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward 

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 нулевой, я бы сделал это отдельно, так как or замедляет обновление.

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

person paparazzo    schedule 22.05.2018
comment
У меня нет возможности отключить индексы. Это очень часто используемая таблица в рабочей базе данных. И под сортировкой я имел в виду... отсортируйте свои исходные значения по первичному ключу, затем возьмите свои пакеты в этом порядке, таким образом вы уменьшите общее количество попаданий на страницы при выполнении ваших обновлений. - person Chad Baldwin; 22.05.2018
comment
@chadwin Можете ли вы спросить человека, у которого есть доступ? Отключение, а затем перестроение индексов должно иметь значение. Объясните им, почему вам нужно это сделать. - person Shawn; 22.05.2018
comment
Этот процесс обновления просто не имеет достаточной важности, чтобы заслуживать отключения 5 индексов в таблице со 100 миллионами записей, к которой пользователи и приложения обращаются тысячи раз в день, а затем перестроения индексов. Это процесс, который будет выполняться каждый день. - person Chad Baldwin; 22.05.2018
comment
@chadwin Вы не знаете, что отключить, а затем включить нецелесообразно, пока не проверите. Это будет фрагментировать индексы всех этих индексов. Я воздержусь от сортировки - оптимизатор запросов, вероятно, умнее вас. - person paparazzo; 22.05.2018
comment
Я только говорю, чтобы отсортировать порядок исходных значений перед созданием пакета записей для обновления, это то, что меня попросили сделать. Итак, если у меня есть 250 000 записей, которые мне нужно обновить... затем отсортируйте 250 000 по EntityID и возьмите ТОП 10 000 записей, присоединитесь к entityid, обновите их, затем возьмите следующие 10 000 записей, присоединитесь к entityid, обновите их. Вот как мой менеджер / системный архитектор попросил меня сделать это. Я только ищу хорошо отполированный подлинник, который покрывает это. В противном случае я просто буду использовать свой собственный код. - person Chad Baldwin; 22.05.2018
comment
Если у вас был код, который вы хотели отполировать, то почему вы его не опубликовали? Бьюсь об заклад, мой более простой ответ будет быстрее. PK — это физический порядок таблицы. Оптимизатор запросов не собирается сходить с ума и плескаться. - person paparazzo; 22.05.2018

arrow_upward
0
arrow_downward

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

  • сбросить индексы
  • делать вставки/обновления
  • пересоздать индексы
person Terry Carmen    schedule 22.05.2018