Лучший способ обновить схему таблиц для огромных таблиц (SQL Server)
У меня есть несколько огромных таблиц в производственной базе данных SQL 2005, которые нуждаются в обновлении схемы. В основном это добавление столбцов со значениями по умолчанию и некоторое изменение типа столбца, которое требует некоторого простого преобразования. Все это можно сделать с помощью простого «SELECT INTO», где целью является таблица с новой схемой.
Наши тесты пока что показывают, что даже эта простая операция, выполняемая полностью внутри сервера (без извлечения и отправки каких-либо данных), может занять часы, если не дни, в таблице с миллионами строк.
Есть ли лучшая стратегия обновления таких таблиц?
изменить 1: Мы все еще экспериментируем без окончательного вывода. Что произойдет, если одно из моих преобразований в новую таблицу будет включать объединение каждых пяти строк в одну. Есть некоторый код, который должен запускаться при каждом преобразовании. Наилучшая производительность, которую мы могли получить, достигла скорости, при которой преобразование таблицы из 30 миллионов строк займет не менее нескольких дней.
Будет ли использование SQLCLR в этом случае (выполнение преобразования с кодом, работающим внутри сервера) даст мне значительный прирост скорости?
Ответы 5
Вы применяете индексы немедленно или на втором этапе? Должен работать намного быстрее без индексации во время сборки.
Вы пробовали использовать alter table вместо перемещения данных в новую таблицу? Зачем вам использовать Select в? Просто измените свою текущую структуру.
Я пробовал это однажды, и у меня сложилось впечатление, что SQL создает некую временную таблицу внутри, проталкивая данные за кулисами. В целом, казалось, будет быстрее, если я сделаю то же самое сам.
Также мне нужно преобразовать некоторые данные по пути. Я думаю, что объединение двух столбцов в один может быть значимым примером этого.
Alter table не делает этого, если правильно написана (если вы не используете графический интерфейс, который делает). Я просто добавил столбец со значением по умолчанию в тестовый файл с 11 миллионами записей за 10 минут. Импорт BCP также будет работать быстрее, чем ваш метод. Просто не забудьте заполнить все индексы, текущие ограничения и т. д.
У нас есть аналогичная проблема, и я обнаружил, что самый быстрый способ сделать это - экспортировать данные в файлы с разделителями (кусками - в зависимости от размера строк - в нашем случае каждый файл имел 500000 строк), выполняя любые преобразуется во время экспорта, отбрасывает и воссоздает таблицу с новой схемой, а затем выполняет импорт bcp из файлов.
При использовании этого метода таблица с 30 миллионами строк заняла пару часов, тогда как таблица alter заняла более 30 часов.
Добавьте столбец, разрешающий null, затем выполните обновление до значения по умолчанию вручную, затем повторно измените таблицу, чтобы добавить значение по умолчанию. Таким образом, вы можете контролировать обновления и делать их небольшими порциями.
У меня похожая проблема со звуком, которая возникает довольно часто.
Наша база данных кэширует результаты удаленной хранимой процедуры, которая иногда расширяется новыми полями.
Эта таблица состоит из миллионов строк (а теперь около 80 полей) с парой индексов и экспериментов с таблицами #temp и т. д. (Даже с использованием bcp для временных файлов); Я использую опцию выбора в новую таблицу:
- создать новую таблицу с новой структурой
- сделайте выбор в этой таблице
- брось оригинал
- переименуйте новую таблицу в имя старой
Предложение Брайана Кноблауха сначала удалить индексы, а затем перестроить их, должно очень помочь. Просто не забывайте всегда удалять кластерный индекс последним и добавлять его первым.