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

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

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

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

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

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

11.12.2008 16:19:01
5 ОТВЕТОВ
РЕШЕНИЕ

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

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

3
11.12.2008 17:21:03

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

3
11.12.2008 16:23:03
Предложение Брайана Ноблауха сначала убрать индексы, а затем перестроить их должно очень помочь. Просто не забудьте всегда удалять кластерный индекс последним и добавлять его обратно первым.
Tom H 11.12.2008 17:25:38

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

3
11.12.2008 16:32:04
Я попробовал это однажды, и у меня сложилось впечатление, что SQL создает внутреннюю временную таблицу, выталкивая данные за кулисы. В целом, казалось бы, все пойдет быстрее, если я сам сделаю то же самое.
Ron Harlev 11.12.2008 16:36:40
Также мне нужно преобразовать некоторые данные в пути. Я думаю, что объединение двух столбцов в один может быть показательным примером.
Ron Harlev 11.12.2008 16:38:21
Alter table не делает этого, если правильно написан сценарий (если вы не используете графический интерфейс, который использует). Я просто добавил столбец со значением по умолчанию в 11-миллионный тестовый файл записи за 10 минут. Импорт BCP будет работать быстрее, чем ваш метод. Просто не забудьте заполнить все индексы, текущие
HLGEM 11.12.2008 18:35:37

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

0
11.12.2008 17:22:25

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

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

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

  • создать новую таблицу с новой структурой
  • сделать выбор в эту таблицу
  • брось оригинал
  • переименуйте новую таблицу в имя старой
0
11.12.2008 17:37:16