FireDAC: Bulk-Insert з Array DML та ретельною обробкою помилок на рівні рядка

Одне BDE-заміна з нативним підключенням Bulk-Insert із Array DML часто є найшвидшим способом записати багато записів у базу даних: замість тисячі окремих операцій INSERT прив’язується масив параметрів і відправляється одним разом на сервер. На практиці проте швидко виникає критична деталь: один запис порушує Unique-Index, поле NOT NULL порожнє, зовнішній ключ не підходить – і раптом незрозуміло, який саме рядок вбив батч, чи частина вже записана і як ти коректно продовжиш, не створюючи невідповідностей у даних.

Саме про це йдеться тут: як використовувати Array DML так, щоб ти отримував по кожному рядку надійні дані про помилки, тримав транзакцію під контролем і у експлуатації міг простежити, що сталося. Фокус не на академічному вивченні API, а на граничному випадку, який регулярно виникає в реальних імпортах: великий батч, кілька пошкоджених рядків, але ти все одно хочеш швидкість.

FireDAC Bulk-Insert з Array DML: Warum Array DML beim Bulk-Insert überhaupt lohnt

Array DML (Data Manipulation Language) означає в FireDAC: ти прив’язуєш параметри не як одиночні значення, а як масив. FireDAC надсилає тоді (je nach Treiber/DB) менше Roundtrips, може працювати ефективніше на стороні сервера і радикально зменшує накладні витрати на клієнті. Це особливо релевантно в трьох ситуаціях:

• ETL- und Importstrecken: CSV/XML/JSON на вхід, нормалізація/мапінг, потім у Staging- або цільову таблицю.
• Schnittstellen-Puffer: REST- oder MQ-Payloads збираються і періодично фіксуються.
• Protokoll-/Event-Tabellen: багато малих вставок, де домінує Latenz.

Виграш при цьому не безкоштовний. З Array DML ти переміщаєш складність від «багато окремих Statements» до «одного Statement з багатьма рядками». Це добре для продуктивності, але більш вимогливе щодо діагностики помилок, логіки транзакцій і повторного запуску.

Типовий Randfall: Ein Batch, eine kaputte Zeile

Класика в експлуатації: ти імпортуєш 50.000 рядків. Ти вибираєш ArraySize 1.000, бо не хочеш для кожного рядка робити Roundtrip. Batch 17 зазнає невдачі. Die DB meldet nur „duplicate key“ oder „violates foreign key constraint“. У UI або в сервісному логу часто стоїть лише: „ExecSQL failed“.

Без коректного Error-Handling зазвичай трапляються дві погані речі:

• Ти викидаєш увесь батч, хоча 999 із 1.000 рядків були б ок.
• Ти повертаєшся до одиночних вставок і втрачаєш перевагу в продуктивності надовго.

Мета — третій шлях: зберегти продуктивність пакетної обробки, але протоколювати з точністю до дефекту (індекс рядка, ключові значення, текст помилки БД) і опціонально зафіксувати коректні рядки (commit) — залежно від того, наскільки критичні для вашого процесу консистентність та ідемпотентність (кілька виконань без подвійного ефекту).

FireDAC Array DML: Die relevanten Stellschrauben (ohne Mythen)

Для Bulk-Insert з Array DML на практиці завжди вирішальними є одні й ті ж налаштування:

1) ArraySize und Batch-Größe

ArraySize (bei TFDQuery/TFDCommand) визначає, скільки «рядків» FireDAC опрацьовується в одному виклику. Більше не завжди краще. Занадто велике значення означає: більше пам’яті на клієнті, більший payload по каналу, більші блокування/навантаження на журнал на сервері і в разі помилки — більший «радіус ураження». Для надійного імпорту часто оптимальна розмірність пакета в межах 200–2000, залежно від кількості стовпців, BLOBs і затримок мережі.

2) Transaktionsgrenze

Потрібне чітке рішення: Commit pro Batch або Commit für den gesamten Import. Це не питання смаку, а експлуатаційне рішення:

• Commit pro Batch: обмежує блокування та журнал транзакцій, полегшує відновлення, але проміжні результати стають видимі (залежно від Isolation Level). Помилка в пакеті 17 залишить у системі пакети 1–16.
• Commit am Ende: «все або нічого», більш консистентно з погляду предметної області, але при великих обсягах ризикуєте довгими блокуваннями, великим відкатом і в разі помилки — втратою всього.

Для багатьох інтерфейсних і імпортних процесів «Commit pro Batch» є більш реалістичною експлуатаційною стратегією — але тільки, якщо ви коректно забезпечили ідемпотентність і стратегію обробки дубліката даних (наприклад за природними ключами, через upserts або import-ID).

3) UpdateOptions und Prepared Statements

При багаторазових пакетах має сенс тримати statement підготовленим. «Prepare» означає: FireDAC дає БД можливість розібрати/скомпілювати statement і використовувати його повторно. Залежно від СУБД це може давати відчутний ефект, особливо при високій частоті. Тут важливіше не якийсь «Trick 17», а: послідовне повторне використання того самого об’єкта Query (або того самого TFDCommand) і стабільні типи параметрів.

Sauberes Error-Handling pro Zeile: Was du wirklich brauchst

Якщо ви хочете обробляти помилки «покажчикам рядка», вам потрібно три речі:

1. Zuordnung: Який індекс масиву (0..N-1) зазнав збою?
2. Kontext: Які предметні ключові значення має цей рядок (напр., зовнішній ID, номер клієнта, мітка часу)?
3. Steuerung: Що ви робите далі? Перервати, пропустити лише погані рядки чи розбити пакет?

FireDAC може в залежності від драйвера повертати помилку для кожного елемента масиву. На практиці це не завжди «мається на увазі». Потрібно очікувати, що деякі бази/провайдери повідомляють лише першу помилку або що помилка в пакеті взагалі перерве виконання решти. Саме тому надійний підхід зазвичай двоступеневий:

• Stufe A: Спробуйте пакет як Array DML.
• Stufe B: Якщо пакет зазнав невдачі, розбийте його (наприклад наполовину) або контрольовано перейдіть на обробку по-рядково — але лише для цього пакета — і зафіксуйте детальний лог.

Це звучить як додаткова робота, але в імпортних потоках це різниця між «о 02:00 ночі все зупинилося» і «імпорт пройшов, 7 рядків потрапили в список помилок».

Ein praxistaugliches Muster: Batch zuerst, dann gezielt isolieren

Наведену нижче схему зарекомендувала себе для процесно-орієнтованих програмних рішень, у яких якість даних різна:

Крок 1: Помістити дані в пакетну структуру (включно з контекстом помилки)

Зберігайте дані, що підлягають імпорту, не лише як сирі значення, а з мінімальним контекстом: зовнішній ID, номер рядка у джерелі, за потреби хеш/контрольна сума. Це не «nice to have»: у разі помилки ви не хочете знову парсити CSV, щоб виявити, що зламалося.

Крок 2: Виконати Array DML

Ви встановлюєте ArraySize на довжину пакета, прив’язуєте параметри як масиви і виконуєте ExecSQL. Важливо: зберігати стабільні типи параметрів (наприклад, для числових полів не прив’язувати інколи як String, інколи як Integer), інакше БД робитиме неявні приведення типів або FireDAC змушений буде перетворювати кожен елемент окремо.

Крок 3: У разі помилки — звужувати пакет замість сліпого повторення

Якщо ExecSQL зазнає невдачі, у вас є дві надійні опції:

• Binary Split (поділ навпіл): розділити пакет на дві половини і для кожної половини знову спробувати Array DML. Повторюєте це, поки не дійдете до невеликої кількості рядків, які можна перевіряти поодинці. Перевага: зберігається велика частина продуктивності, якщо пошкоджені лише одиничні рядки. Недолік: більше логіки, і при системних помилках (наприклад, невірний тип даних) користі мало.
• Відкат на одиночні рядки для цього пакета: встановити ArraySize=1 (або прив’язувати одиночні значення) і виконувати рядок за рядком, логуючи помилки і продовжуючи. Перевага: просто, гарантує обробку на рівні рядка. Недолік: у цьому пакеті втрачається швидкість.

На практиці я комбіную обидва підходи: спочатку 1–2 рази розділити (щоб швидко пропустити «хороші блоки»), потім при невеликих залишках перейти на одиночні рядки, щоб зафіксувати однозначну інформацію про помилки.

Об’єкти помилок і повідомлення: що ви маєте витягти з FireDAC

FireDAC інкапсулює помилки БД у Exceptions (зазвичай EFDDBEngineException) з детальною інформацією. Для експлуатації важливі три рівні:

• Код помилки БД (специфічний для СУБД): наприклад SQLSTATE у PostgreSQL, Error Number у SQL Server.
• Назва обмеження/об’єкта: часто міститься в тексті помилки (Unique-Index, FK-Constraint).
• Контекст запиту: таблиця, операція, за потреби значення параметрів (обережно з персональними даними).

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

Підводні камені, які відбирають час у реальних імпортах

1) «То була лише одна рядка» — але транзакція вже в стані «dirty»

Залежно від СУБД та драйвера помилка може призвести до того, що виконання всього оператору вважається невдалим, а транзакція переходить у стан, коли потрібно або явно відкотити, або подальші оператори зазнають невдачі. Особливо для деяких драйверів припущення «після помилки просто продовжити» не є безпечним.

Наслідок: якщо ви працюєте в межах транзакції і пакет зазнає невдачі, стандартний шлях: відкат поточного контексту пакета (або всієї транзакції) і потім починати заново. Це добре поєднується з «commit за пакет».

2) Autocommit vs. явна транзакція

Якщо ви не запускаєте явну транзакцію, часто драйвер/провайдер вирішує, як він фіксує виконання операторів. Для масових імпортів це рідко те, що вам потрібно. Явні транзакції дають вам контроль над:

• Тривалістю блокувань
• Поведінка при rollback
• Пункти повторного запуску

І ще: «експліцитно» не означає «величезна транзакція». Це означає «усвідомлено».

3) Тригери, обмеження та побічні ефекти

Array DML пришвидшує передачу даних, але не автоматично роботу на сервері. Якщо на цільовій таблиці є тригери (наприклад, аудит-логування, автоматичний розрахунок статусу), то вузьким місцем може бути не INSERT, а код тригера. У такому випадку пакет може зменшити кількість roundtrip’ів, але завантаження CPU на DB-сервері й надалі залишатиметься фактором обмеження.

Для адміністраторів і технічних лідерів: при проблемах з продуктивністю варто подивитися Wait Events/Locks та журнал транзакцій. Bulk-Insert у такому випадку часто лише тригер, а не корінна причина.

4) Типи даних та неявні конвертації

Одна з найпоширеніших причин «чому це повільно»: параметри зв’язуються як рядки, і БД при кожному рядку кастить до Integer/Date/Decimal. Це непомітно, але дорого коштує. Для стабільної продуктивності:

• Задавати відповідні типи параметрів (дата як дата, число як число).
• Для Decimal враховувати підводні камені локалі (кома проти крапки). FireDAC тут зазвичай коректний, але змішані джерела — ні.
• Заздалегідь узгодити стратегію часових поясів/UTC (таймстемпи — класика для імпортів).

5) Тексти помилок — для людей, але не для автоматизації

Спокуса парсити текст помилки («duplicate key value violates unique constraint …») велика. Робити це варто тільки як останню інстанцію. Краще використовувати структуровані коди (SQLSTATE, Error Number). На жаль, не всі драйвери однаково добре їх віддають. Плануй тому обидва підходи: код і текст, плюс опційно «ім’я constraint з тексту», але без жорсткої залежності.

Поради з відладки: як швидко знайти проблемний рядок

Зробити batch відтворюваним

Якщо імпорт спорадично падає, потрібна відтворюваність. Зберігай для кожного батчу невеликий файл діагностики або запис у лог, який містить:

• Номер батчу та час
• ArraySize і режим транзакції
• список предметних ключів (наприклад, зовнішні ID) у батчі

Цього часто достатньо, щоб пізніше цілеспрямовано запустити міні-імпорт лише для цих ID.

Зробити фінальний SQL видимим (але без витоків даних)

Під час відладки ви хочете знати: чи коректний SQL? чи правильні параметри? FireDAC забезпечує моніторинг/трейсинг через FDMoni-компоненти та логування драйвера. У наближених до продакшну середовищах важливо:

• увімкнути трейсинг прицільно й тимчасово (через вплив на продуктивність і приватність даних).
• логувати значення параметрів лише в безпечному середовищі або в замаскованому вигляді.
• щодо персональних даних: у логах лише технічні ключі (ID), без даних у відкритому вигляді.

Якщо ти робиш split-тест: визначити критерії зупинки

При бінарному розділенні ти не хочеш ділити вічно. Встанови нижню межу, наприклад «якщо менше 20 рядків — переключитися на одиночний режим». І зафіксуй ліміт того, скільки помилок ти готовий терпіти загалом, перш ніж скасувати імпорт (наприклад при системних проблемах мапінгу). Інакше отримаєш нескінченні списки помилок і заблокуєш подальшу обробку.

Коли зусилля справді виправдані (і коли ні)

Array DML з обробкою помилок по рядку особливо доцільний, коли:

• Обробляється багато рядків (тисячі до мільйонів).
• Невелика частка рядків містить помилки, але ти хочеш продовжити обробку.
• Імпорт має працювати стабільно в робочому режимі (наприклад нічна обробка, сервіс без UI).
• Потрібно повернути список помилок до фахового підрозділу/джерела (з прив’язкою до рядків).

Має менший сенс, якщо:

• ти пишеш лише кілька десятків рядків (окремі вставки прийнятні),
• якість даних настільки погана, що 30–50% рядків не проходять (у такому випадку стратегія з використанням Staging-таблиці доцільніша),
• ти й так використовуєш DB‑рідний механізм масового завантаження (наприклад COPY у PostgreSQL, BCP/BULK INSERT у SQL Server) — тоді Array DML не є потрібним інструментом.

Альтернативна архітектура: Staging-таблиця замість „безпосередньо в ціль“

Якщо ти регулярно маєш справу зі змішаною якістю даних, чисте «вставлення безпосередньо в цільову таблицю» часто є невірним рішенням. Staging-таблиця (передрівень) — це таблиця, в якій ти спочатку технічно коректно зберігаєш дані (за потреби з гнучкими типами), а вже потім валідируєш і переносиш у цільову таблицю.

Переваги в експлуатації:

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

Array DML часто є швидким шляхом у Staging-таблицю, тоді як перенесення в цільову таблицю виконується як set‑орієнтований SQL (або збережена процедура). Це переміщує обробку помилок більше на сторону БД, що залежно від організації (ролі DBA, процес деплойменту) може бути доцільно або небажано.

Експлуатація та адміністрування: що IT‑лідери й адміністратори повинні знати

Моніторинг: частка помилок і пропускна здатність — ключові метрики

Для стабільної роботи масового імпорту дві метрики говорять більше, ніж сама «тривалість виконання»:

• Пропускна здатність: рядків за хвилину (або за батч) включно з піками/медіаною.
• Частка помилок: неправильні рядки за запуск, бажано згруповані за класами помилок (Unique, FK, NOT NULL, конфлікт типів).

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

Блокування та вікна навантаження

Масові вставки можуть створювати блокування й навантаження на IO. Якщо одночасно користувачі працюють з тими самими таблицями, ти маєш продумати рівень ізоляції транзакцій, індекси й, за потреби, партиціювання. Практично це означає: або виконувати імпорти у вікна низького навантаження, або побудувати потік даних так, щоб він співіснував із робочим середовищем (наприклад через Staging + асинхронне перенесення).

Конкретний чекліст для надійного масового вставлення з Array DML

• Визначити розмір батчу (початкове значення 500–1 000) і налагодити його за результатами вимірювань.
• Явна транзакція: коміт на батч за замовчуванням, «коміт наприкінці» лише усвідомлено.
• Фіксувати типи параметрів стабільно, не примушувати імпліцитні приведення типів.
• Нести контекст помилки для кожного запису (зовнішній ID, рядок джерела).
• Стратегія обробки помилок: спочатку батч, потім розбиття/фолбек, логувати по рядку.
• Логування: коди + текст, але відповідно до захисту даних; фіксувати Batch‑ID і ID запуску.
• Повторний запуск: забезпечити ідемпотентність (ключі/Upsert/Import‑ID).

Висновок: Array DML швидкий — надійним його робить процес і стратегія обробки помилок

Ein FireDAC Bulk-Insert mit Array DML ist ein starkes Werkzeug, solange du nicht so tust, als gäbe es keine Fehler. In echten Datenströmen gibt es immer Ausreißer: Dubletten, fehlende Referenzen, kaputte Datumswerte. Der saubere Ansatz ist deshalb: Array DML für die Performance, kombiniert mit einer kontrollierten Isolationsstrategie (Split oder Fallback) und einer pro Zeile nachvollziehbaren Fehlerliste. Damit bekommst du Tempo und Betriebssicherheit zusammen – und genau das zählt, wenn Imports nicht nur im Lab laufen, sondern jede Nacht zuverlässig durch müssen.

Якщо ви хочете стабілізувати існуючий процес імпорту або інтерфейсу в Delphi/FireDAC (продуктивність, транзакції, Wiederanlauf, логування), ми із задоволенням розглянемо це структуровано у технічній розмові:

Для цієї теми також важливі Delphi Bulk Insert і Bulk Insert Delphi FireDAC. Стаття впорядковує ці аспекти зрозуміло та показує, на що слід звертати увагу в повсякденній експлуатації.

Обговорити проект або завдання з модернізації з Net-Base.