Сокращаем время загрузки хранилища данных

Недавно мне потребовалось оптимизировать выполняемый каждую ночь процесс пакетной обработки для хранилища данных SQL Server, связанный с загрузкой более 250 тыс. неструктурированных файлов. После ряда экспериментов мне удалось уменьшить общее время загрузки с 14 часов до 15 минут.

Для этого я использовал:

• команду T-SQL BULK INSERT;
• таблицы, оптимизированные для размещения в памяти в SQL Server 2014;
• асинхронное и параллельное выполнение команд T-SQL.

Давайте пройдем по всем этапам реализации этого решения.

Общие замечания

Читателям, знакомым с моими предыдущими статьями, известно мое пристрастие к экономике. Федеральный резервный банк Сент-Луиса (http://research.stlouisfed.org/) располагает большим набором экономических данных, более 250 тыс. строк данных из 79 источников в США и других странах, содержащих более 38 млн строк. Некоторые последовательности обновляются ежедневно, а старые значения изменяются. Хранение этих данных в реляционной базе данных представляет собой довольно сложную задачу, так как каждую последовательность необходимо перезагружать или объединять целиком. Моя первая попытка перезагрузить данные, в которой использовался простой пакет SQL Server Integration Services (SSIS) (см. экран 1) заняла 14 часов — результат, который нельзя признать удовлетворительным.

Экран 1. Перезагрузка данных с помощью пакета SQL Server Integration Services

Здесь я хочу пояснить, зачем нужно загружать значения строк данных в базу данных. На первый взгляд сохранение набора данных в строке (состоящей всего из двух полей — дата и значение) в базе данных необязательно. Я подумал о том, чтобы сохранить их в первоначальном формате (как отдельные файлы) или использовать базу данных, отличную от SQL (например, Hadoop). Но одним из главных моих требований было вычисление корреляции строк данных со всей базой данных. Благодаря возможностям базы данных SQL (и запросов на основе набора) такой запрос удавалось выполнить примерно за минуту на сервере средней мощности.

Повышаем производительность — изменение пакета SSIS

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

Затем я рассмотрел различные методы параллельного выполнения пакета SSIS. Я изменил пакет для одновременной обработки различных пакетов файлов (в виде нескольких задач DataFlow), создал главный пакет для параллельного выполнения базовых пакетов (см. экран 2) и проверил некоторые комбинации их взаимодействия. К сожалению, мне не удалось добиться повышения производительности. На моем компьютере не замечено перегрузки процессора, памяти или диска, но пропускная способность SSIS уменьшилась пропорционально числу одновременно обрабатываемых файлов.

Экран 2. Параллельное выполнение базовых пакетов

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

Команда BULK INSERT

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

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

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

Однако после тестирования с большим числом рядов выяснилось, что масштабирование сценария происходило нелинейно (например, время обработки 5?тысячной последовательности составило около 32 секунд — уменьшение производительности примерно на 20%). Однако мне еще хотелось выяснить, можно ли исключить некоторое число операций записи в файлы данных или журнала, связанных с использованием промежуточных таблиц.

Таблицы, оптимизированные для размещения в памяти

Большинство новых возможностей SQL Server 2014 относится к транзакциям; одно из новшеств, первоначально известное как Hekaton, обеспечивает сохранение таблицы в памяти. Эти таблицы, оптимизированные для памяти, предназначены для OLTP-приложений, в которых активно используемые таблицы могут выиграть от преимуществ в быстродействии памяти перед диском. Эти таблицы могут быть определены как долговременные (вставки и изменения данных, помимо сохранения в памяти, записываются на диск и доступны после перезапуска системы) или кратковременные (вставки и изменения данных не записываются на диск; таблица пуста, хотя схема по-прежнему доступна после перезапуска системы). В листинге 2 показан пример формирования кратковременной таблицы.

Я изменил программный код в листинге 1, чтобы использовать оптимизированные для размещения в памяти, кратковременные, промежуточные таблицы. На тесте однотысячной последовательности производительность сценария была почти такой же, как прежде (~5 секунд). При повторном выполнении более крупного набора (5?тысячная последовательность) сценарий был завершен за 22 секунды.

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

Асинхронное выполнение команд T-SQL

Я изменил сценарий, чтобы ввести начало или конец диапазона последовательности данных на последовательности с пакетным значением для выполнения периодических пересылок из промежуточной таблицы в таблицу назначения, а затем сохранил изменение как хранимую процедуру. Можно было бы вернуться к пакету SSIS (с несколькими задачами Execute SQL, вызывающими хранимую процедуру), чтобы обеспечить параллельную обработку, но я хотел найти подход T-SQL, вызываемый или выполняемый из сервера базы данных. По умолчанию хранимая процедура или сценарий T-SQL выполняет каждую команду синхронно. Поиск в Интернете с ключевыми словами asynchronous T-SQL дает несколько возможностей, в том числе компонент SQL Service Broker, хранимые процедуры на основе CLR и динамическое создание или выполнение заданий агента SQL Server.

В конечном итоге я использовал агент SQL Server, назначив четыре задания, три из которых асинхронно вызываются из четвертого. Код T-SQL для четвертого задания показан в листинге 3 (обратите внимание, что после выполнения этого сценария вы можете отслеживать ход с использованием типичных средств агента SQL Server, таких как монитор активности заданий). Я обнаружил и заимствовал этот прием из интернет-публикации Антонина Фоллера. Только учтите, что фрагмент кода Антонина формирует задание агента SQL Server «на ходу», выполняет задание, а затем удаляет его. Пока я избрал более жесткий подход.

В последнем решении удается обработать все ряды данных менее чем за 15 минут. Интересно, что мой «сервер» (в действительности виртуальная машина на ноутбуке) располагает значительными ресурсами процессора и памяти, поэтому, вероятно, мне удастся параллельно запускать дополнительные задания, чтобы дополнительно уменьшить время обработки.

Надо сказать, что выполняемая каждую ночь перезагрузка более чем 250 тыс. файлов с разделителями — не самый типичный случай. Но вы сможете использовать один (или больше) из трех описанных мною приемов (BULK INSERT, таблицы, оптимизированные для памяти, и асинхронное исполнение T-SQL) в своей среде.

-- Очистка предшествующего выполнения
TRUNCATE TABLE dbo.stgSeriesValueNonMemoryOpt;
-- Track Performance of this script
DECLARE @tStart DATETIME2
, @tEnd DATETIME2;
SET @tStart = GETDATE();
-- Объявить курсор для получения пути файла последовательности,
    определить начало и конец последовательности
DECLARE @Start INT = 0
, @Finish INT = 5000;
DECLARE @FilePath VARCHAR(255)
, @SeriesName VARCHAR(50)
, @SeriesId INT;
DECLARE @Path VARCHAR(100);
-- BULK INSERT может извлечь из локального местоположения
    наш удаленный (UNC) путь
SET @Path = '\\132.132.0.5\c$\downloadedfiles\fred\FRED2_csv_2\data\';
-- Мы построим T-SQL команду BULK INSERT динамически,
    а затем вызовем через команду EXEC
DECLARE @sql NVARCHAR(4000);
-- Временная промежуточная таблица
IF OBJECT_ID('tempdb..#stgSeriesValue') IS NOT NULL
   TRUNCATE TABLE #stgSeriesValue;
ELSE
   CREATE TABLE #stgSeriesValue
      (
       [Date] DATE NOT NULL
     , Value DECIMAL(28, 10) NULL
      );
DECLARE db_cursor 
FOR
   SELECT @Path + [File]
       , SeriesName
       , SeriesId
      FROM dbo.Series
      WHERE SeriesId BETWEEN @Start AND @Finish
      ORDER BY SeriesId;
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @FilePath, @SeriesName, @SeriesId
WHILE @@FETCH_STATUS = 0
   BEGIN 
      PRINT @FilePath;
-- Сначала BulkLoad ряд в промежуточную таблицу, содержащую
    Date и Value
      SET @sql = 'BULK INSERT #stgSeriesValue FROM ''' + @FilePath
         + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR ='','',
         ROWTERMINATOR =''\n'', MAXERRORS = 500 )';
      EXEC(@sql);
-- Обновить вторую промежуточную таблицу идентификатором
    последовательности
-- BULK INSERT не позволяет присоединять дополнительные столбцы
    к исходному файлу, так что это лучший известный мне обходной
    прием
      INSERT INTO dbo.stgSeriesValueNonMemoryOpt
            (
             SeriesId
           , Date
           , Value
            )
            SELECT @SeriesId
                , Date
                , Value
               FROM #stgSeriesValue;
-- Удаление из первой промежуточной таблицы для подготовки
    к следующей последовательности
      DELETE FROM #stgSeriesValue;
      FETCH NEXT FROM db_cursor INTO @FilePath, @SeriesName, @SeriesId;
   END 
CLOSE db_cursor; 
DEALLOCATE db_cursor;
-- Данные загружаются в промежуточную таблицу; теперь вызываем
    хранимую процедуру для объединения всех элементов в таблице
    назначения
-- Примечание: в этой хранимой процедуре используется команда
    MERGE, появившаяся в SQL Server 2008.
EXECUTE [dbo].[spI_SeriesValueNonMemoryOpt] @Start, @Finish;
-- Очистка промежуточных таблиц
DROP TABLE #stgSeriesValue;
TRUNCATE TABLE dbo.stgSeriesValueNonMemoryOpt;
-- Get Performance Results
SET @tEnd = GETDATE();
SELECT DATEDIFF(SECOND, @tStart, @tEnd) AS elapsed_seconds;

CREATE TABLE [dbo].[stgSeriesValue]
   (
    [SeriesId] [INT] NOT NULL
  , [Date] [DATE] NOT NULL
  , [Value] [DECIMAL](28, 10) NULL
  , CONSTRAINT [stgSeriesValue_primaryKey] PRIMARY KEY NONCLUSTERED
      ([SeriesId] ASC, [Date] ASC)
   )
   WITH (
        MEMORY_OPTIMIZED =
        ON ,
        DURABILITY =
        SCHEMA_ONLY )
GO

EXEC msdb.dbo.sp_start_job N'Process_SeriesValue_01';

EXEC msdb.dbo.sp_start_job N'Process_SeriesValue_02';

EXEC msdb.dbo.sp_start_job N'Process_SeriesValue_03';