Лучший способ обновить схему таблицы для огромных таблиц (SQL Server)

У меня есть несколько огромных таблиц в производственной базе данных SQL 2005, которым требуется обновление схемы. В основном это добавление столбцов со значениями по умолчанию и некоторые изменения типа столбца, требующие простого преобразования. Все это можно сделать с помощью простого «SELECT INTO», где целью является таблица с новой схемой.

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

Есть ли лучшая стратегия обновления для таких таблиц?

редактировать 1: Мы все еще экспериментируем без окончательного вывода. Что произойдет, если одно из моих преобразований в новую таблицу будет включать объединение каждых пяти строк в одну. Существует некоторый код, который должен выполняться при каждом преобразовании. Наилучшая производительность, которую мы могли получить, дала нам скорость, при которой преобразование таблицы из 30 миллионов строк займет не менее нескольких дней.

Даст ли мне в этом случае использование SQLCLR (выполнение преобразования с кодом, работающим внутри сервера) значительный прирост скорости?


sql sql-server tsql sql-server-2005
person Ron Harlev    schedule 11.12.2008    source источник

Ответы (5)


arrow_upward
3
arrow_downward

У нас похожая проблема, и я обнаружил, что самый быстрый способ сделать это - экспортировать данные в файлы с разделителями (порциями - в зависимости от размера строк - в нашем случае каждый файл имел 500 000 строк), выполняя любые преобразует во время экспорта, удалите и заново создайте таблицу с новой схемой, а затем выполните импорт bcp из файлов.

При использовании этого метода таблица с 30 миллионами строк заняла пару часов, тогда как изменение таблицы заняло более 30 часов.

person rjrapson    schedule 11.12.2008

arrow_upward
3
arrow_downward

Вы применяете индексы сразу или на вторичном этапе? Должно работать намного быстрее без индексации во время сборки.

person Brian Knoblauch    schedule 11.12.2008
comment
Предложение Брайана Кноблауха сначала удалить индексы, а затем перестроить их должно очень помочь. Просто не забывайте всегда удалять кластеризованный индекс последним и добавлять его первым. - person Tom H; 11.12.2008

arrow_upward
3
arrow_downward

Вы пытались использовать таблицу изменений, а не перемещать данные в новую таблицу? Почему бы вам не использовать Select into? Просто измените текущую структуру.

person HLGEM    schedule 11.12.2008
comment
Я пробовал это однажды, и у меня сложилось впечатление, что SQL создает внутреннюю временную таблицу, помещая данные за кулисы. В целом, казалось, что это пойдет быстрее, если я сделаю то же самое сам. - person Ron Harlev; 11.12.2008
comment
Также мне нужно преобразовать некоторые данные в пути. Я думаю, что объединение двух столбцов в один может быть значимым примером для этого. - person Ron Harlev; 11.12.2008
comment
Alter table не делает этого, если правильно написан сценарий (если только вы не используете графический интерфейс, который делает). Я просто добавил столбец со значением по умолчанию в тестовый файл с 11 миллионами записей за 10 минут. Импорт BCP будет работать быстрее, чем ваш метод. Просто не забудьте заполнить все индексы, текущие ограничения и т. д. - person HLGEM; 11.12.2008

arrow_upward
0
arrow_downward

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

person Jonas Lincoln    schedule 11.12.2008

arrow_upward
0
arrow_downward

У меня похожая проблема со звуком, которая возникает достаточно часто.

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

Эта таблица состоит из миллионов строк (а теперь до 80 полей) с парой индексов и экспериментов с таблицами #temp и т. д. (даже с использованием bcp для временных файлов); Я использую опцию выбора в новую таблицу:

  • создать новую таблицу с новой структурой
  • сделать выбор в этой таблице
  • скиньте оригинал
  • переименовать новую таблицу в имя старой
person Unsliced    schedule 11.12.2008