Когда речь идет о загрузке данных из стейджинговой таблицы в таблицу назначения и таблицу истории изменений в SQL Server, существует несколько способов выполнить эту задачу. В этой статье мы рассмотрим различные методы и сравним их производительность.
Таблицы
Прежде чем мы перейдем к различным методам, давайте взглянем на таблицы, участвующие в нашем сценарии. У нас есть стейджинговая таблица, таблица назначения и таблица истории. Стейджинговая таблица содержит n записей, и нашей целью является загрузка n записей в таблицу назначения и n записей в таблицу истории.
Таблица назначения имеет две версии, “Base” и “Trig”, чтобы вместить триггер, который будет добавлен позже. Таблица истории также имеет две версии, чтобы позволить внешний ключ к таблице назначения.
Метод 1: Множественная вставка с использованием OUTPUT
В первом методе мы используем выражение OUTPUT для выполнения множественной вставки. Этот метод включает вставку записей из стейджинговой таблицы в таблицу назначения и захват вставленных записей с помощью выражения OUTPUT. Затем мы вставляем эти захваченные записи в таблицу истории.
Метод 2: Множественная вставка с использованием триггера на вставку
Второй метод включает добавление триггера на вставку в таблицу назначения. Этот триггер автоматически вставляет записи в таблицу истории на основе данных, вставленных в таблицу назначения. Затем мы выполняем множественную вставку из стейджинговой таблицы в таблицу назначения.
Метод 3: Итеративная вставка с использованием прямого SQL
В третьем методе мы используем более простой подход, используя счетчик и цикл WHILE для перебора записей в стейджинговой таблице. Мы выполняем итеративную вставку в таблицу назначения, а затем вставляем соответствующие записи в таблицу истории.
Метод 4: Итеративная вставка с использованием триггера на вставку
Подобно третьему методу, четвертый метод также включает итеративную вставку. Однако, вместо использования прямого SQL, мы используем триггер на вставку в таблицу назначения для обработки вставки записей в таблицу истории.
Метод 5: Множественная вставка с использованием OUTPUT в хранимой процедуре
В пятом методе мы инкапсулируем множественную вставку с использованием выражения OUTPUT внутри хранимой процедуры. Это позволяет нам легко выполнить операторы INSERT как единую рабочую единицу.
Результаты
Мы провели тесты с различным количеством записей в стейджинговой таблице, чтобы измерить производительность каждого метода. Записывалось время выполнения каждого теста.
| Метод | Транзакция | Записи: 512 | Записи: 32,768 | Записи: 1,048,576 |
|---|---|---|---|---|
| Метод 1a | Нет | 16 мс | 820 мс | 35,953 мс |
| Метод 1b | Да | 16 мс | 793 мс | 36,306 мс |
| Метод 2 | Неявная | 30 мс | 593 мс | 19,923 мс |
| Метод 3 | Нет | 190 мс | 12,426 мс | 376,663 мс |
| Метод 4 | Неявная | 140 мс | 8,016 мс | 244,296 мс |
| Метод 5a | Нет | 16 мс | 1,056 мс | 29,370 мс |
| Метод 5b | Да | 13 мс | 730 мс | 27,830 мс |
Вывод
На основе наших тестов, множественные решения с использованием выражения OUTPUT или триггеров показали лучшую производительность по сравнению с итеративными решениями, работающими с записями по одной. Кроме того, использование явной транзакции, независимо от того, напрямую ли она написана или обернута в хранимую процедуру, улучшает производительность.
Хотя триггеры часто считаются “злом” многими разработчиками SQL Server, они могут быть уместны в определенных сценариях, таких как создание записей истории или заполнение таблиц для целей хранения данных. В нашем случае триггер показал лучший результат.
Стоит отметить, что выражение OUTPUT может сохранять записи в переменной таблицы между вызовами, поэтому может потребоваться очистить переменную таблицы перед каждой операцией INSERT INTO … OUTPUT … INTO
В целом, выбор метода зависит от конкретных требований и соображений производительности вашего сценария. Не стесняйтесь экспериментировать и оптимизировать выражение OUTPUT или триггеры, чтобы достичь наилучших результатов для вашего случая использования.