Скорочуємо час завантаження сховища даних, windows it pro

Надіслати заявку на отримання матеріалів

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

Для цього я використовував:

Давайте пройдемо по всіх етапах реалізації цього рішення.

загальні зауваження

Екран 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%). Однак мені ще хотілося з'ясувати, чи можна виключити деякий число операцій записи в файли даних або журналу, пов'язаних з використанням проміжних таблиць.

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

Я змінив програмний код в лістингу 1. щоб використовувати оптимізовані для розміщення в пам'яті, короткочасні, проміжні таблиці. На тесті однотисячний послідовності продуктивність сценарію була майже такою ж, як раніше (

5 секунд). При повторному виконанні більшого набору (5? Тисячна послідовність) сценарій був завершений за 22 секунди.

Тепер у мене з'явилося лінійно масштабується, зі скороченим числом операцій записи в файли даних або журналів (запис на диск відсутній, за винятком останньої команди об'єднання). Як завершальний кроку я вирішив з'ясувати, чи можу я виконати кілька версій цього сценарію паралельно, зберігаючи при цьому лінійне масштабування.

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

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

В останньому рішенні вдається обробити всі ряди даних менш ніж за 15 хвилин. Цікаво, що мій «сервер» (насправді віртуальна машина на ноутбуці) своєму розпорядженні значні ресурси процесора і пам'яті, тому, ймовірно, мені вдасться паралельно запускати додаткові завдання, щоб додатково зменшити час обробки.

Треба сказати, що виконується щоночі перезавантаження більш ніж 250 тис. Файлів з роздільниками - не самий типовий випадок. Але ви зможете використовувати один (або більше) з трьох описаних мною прийомів (BULK INSERT, таблиці, оптимізовані для пам'яті, і асинхронне виконання T-SQL) в своєму середовищі.

Лістинг 1. Сценарій T-SQL Script з командою BULK INSERT

Лістинг 2. Створення короткочасної таблиці, оптимізованої для розміщення в пам'яті

Лістинг 3. Виклик завдання агента SQL Server з T-SQL для організації асинхронних операцій

Поділіться матеріалом з колегами і друзями