SQL Server: сокращаем время загрузки хранилища данных. Часть I

Повышаем производительность — изменение пакета SSIS
Чтобы увеличить производительность, я предпринял попытку оптимизировать пакет SSIS. На тестах с небольшим подмножеством данных (1000 строк данных) пакет в его первоначальном состоянии был выполнен за 3,5 минуты. Предписав задаче DataFlow использовать не OLE DB Destination, a SQL Destination, я сократил время выполнения пакета до 1,5 минут. Затем я рассмотрел различные методы параллельного выполнения пакета SS1S. Я изменил пакет для одновременной обработки различных пакетов файлов (в виде нескольких задач DataFlow), создал главный пакет для параллельного выполнения базовых пакетов (см. скриншот ниже) и проверил некоторые комбинации их взаимодействия. К сожалению, мне не удалось добиться повышения производительности. На моем компьютере не замечено перегрузки процессора, памяти или диска, но пропускная способность SSIS уменьшилась пропорционально числу одновременно обрабатываемых файлов.


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

Команда BULK INSERT
Команда BULK INSERT появилась в версии SQL Server 7.0 и используется для загрузки данных из файла в таблицу или представление. Она не столь гибкая, как пакет SSIS, но достаточно хорошо поддается настройке и обеспечивает возможность загрузки как из локальных, так и из удаленных файлов в нескольких форматах. С помощью аргументов BULK INSERT можно управлять размером транзакции, перенаправлять ошибки (и указывать максимально разрешенное число ошибок), а также изменять блокировку поведения и условия срабатывания триггера для таблицы.


Как показано в выше, я подготовил сценарий T-SQL с курсором для захвата пути к файлу для каждой последовательности (обратите внимание, что в пакете SSIS выполнялась итерация по путям к файлам через циклическую задачу ForEach). Внутри курсора я вызывал команду BULK INSERT для загрузки каждой последовательности в промежуточные таблицы; после завершения курсора я выполнял хранимую процедуру ([dbo].[spl_SeriesValue]) для объединения промежуточных результатов с таблицей назначения.

При первом запуске этого сценария в среде Management Studio мне показалось, что ничего не случилось. Я был в растерянности: в течение нескольких секунд в окне результатов запроса виднелась пустая сетка. Затем, как будто проснувшись, SQL Server начал лихорадочно выдавать данные. Я не поверил своим глазам и запустил сценарий повторно. Во второй раз результат был получен даже немного быстрее — через 5 секунд! Это было решение, с помощью которого всю последовательность можно было потенциально перезагрузить за 25 минут. Однако после тестирования с большим числом рядов выяснилось, что масштабирование сценария происходило нелинейно (например, время обработки 5-тысячной последовательности составило около 32 секунд— уменьшение производительности примерно на 20%). Однако мне еще хотелось выяснить, можно ли исключить некоторое число операций записи в файлы данных или журнала, связанных с использованием промежуточных таблиц.