FireDAC: Масово вмъкване с Array DML и прецизна обработка на грешки на ниво ред

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

Точно това е темата тук: как да използваш Array DML така, че да получаваш за всеки запис надеждна информация за грешките, да държиш транзакцията под контрол и в експлоатация да можеш да проследиш какво се е случило. Фокусът не е върху академичното изучаване на API, а върху крайния случай, който при реални импорти редовно се проявява: голям батч, няколко невалидни записа, но въпреки това искаш скорост.

FireDAC Bulk-Insert с Array DML: Защо Array DML е изгоден при Bulk-Insert

Array DML (Data Manipulation Language) означава в FireDAC: ти привързваш параметрите не като единична стойност, а като масив. FireDAC изпраща тогава (в зависимост от драйвър/DB) по-малко roundtrips, може да работи по-ефективно от страна на сървъра и драстично намалява overhead-а на клиента. Това е особено релевантно в три ситуации:

• ETL- и импортни потоци: CSV/XML/JSON вход, нормализация/съпоставяне, после в staging- или целева таблица.
• Буфер за интерфейси: REST- или MQ-payloads се акумулират и периодично се записват.
• Таблици за протоколи/събития: много малки INSERT-и, при които латентността доминира.

Печалбата обаче не идва даром. С Array DML премествaш комплексността от „много отделни заявки“ към „една заявка с много редове“. Това е добре за производителността, но повишава изискванията за диагностика на грешки, логика на транзакциите и повторно изпълнение.

Типичният граничен случай: Един батч, един невалиден запис

Класическият случай в експлоатация: ти импортираш 50 000 реда. Избираш ArraySize 1 000, защото не искаш за всеки ред отделен roundtrip. Батч 17 се проваля. DB-то съобщава само „duplicate key“ или „violates foreign key constraint“. В UI-то или в логовете на услугата често стои само: „ExecSQL failed“.

Без чисто error-handling обикновено се случват две лоши неща:

• Изхвърляш целия батч, въпреки че 999 от 1 000 реда биха били наред.
• Връщаш се към единични INSERT-и и губиш предимството в производителността за постоянно.

Целта е трети път: запазване на производителността на пакетната обработка, но прецизно протоколиране на дефектите (индекс на реда, стойности на ключове, текст на грешката от БД) и опционно комитване на „валидни редове“ – в зависимост от това колко критични са консистенцията и идемпотентността (повтарящо се изпълнение без двойствен ефект) в твоя процес.

FireDAC Array DML: Релевантните настройки (без митове)

За Bulk-Insert с Array DML на практика винаги същите настройки са решаващи:

1) ArraySize und Batch-Größe

ArraySize (при TFDQuery/TFDCommand) определя колко „реда“ FireDAC се обработват в едно извикване. По-голямото не е автоматично по-добро. Твърде голямо означава: повече памет в клиента, по-голям payload по линията, по-големи заключвания/натоварване на логовете на сървъра и при грешка по‑голям „blast radius“. За стабилни импорти често добра начална Batch‑глоба е между 200 и 2 000, в зависимост от брой колони, BLOBs и латентност.

2) Transaktionsgrenze

Трябва ти ясно решение: Commit за всеки пакет или Commit за целия импорт. Това не е въпрос на вкус, а оперативно решение:

• Commit за всеки пакет: ограничава заключванията и транзакционния лог, улеснява рестарта, но междинните състояния са видими (в зависимост от нивото на изолация). Грешка в пакет 17 оставя пакетите 1–16 в системата.
• Commit в края: „всичко или нищо“, по-съгласувано в бизнес смисъл, но при големи обеми рискуваш дълги заключвания, голям rollback и при грешка всичко се губи.

За много интерфейсни и импортни процеси „Commit за всеки пакет“ е по-реалистичната оперативна стратегия – но само ако идемпотентността и стратегията за дублирани записи са ясно разрешени (напр. чрез естествени ключове, Upserts или Import-ID).

3) UpdateOptions und Prepared Statements

При повторни пакети има смисъл да оставиш заявката подготвена. „Prepare“ означава: FireDAC позволява на БД да парсне/компилира заявката и да я преизползва. В зависимост от БД това може да има осезаем ефект, особено при висока честота. По-важното тук не е някакъв „трик 17“, а: последователно преизползване на един и същи Query-обект (или същия TFDCommand) и стабилни типове на параметрите.

Коректно обработване на грешки за всеки ред: Какво наистина ти трябва

Ако искаш да обработваш грешки „за всеки ред“, имаш нужда от три неща:

1. Отчитане: Кой Array-индекс (0..N-1) е провалил операцията?
2. Контекст: Кои са бизнес ключовите стойности за този ред (напр. външно ID, номер на клиент, времеви печат)?
3. Управление: Какво правиш след това? Прекратяваш, пропускаш само лошите редове или разделяш пакета?

FireDAC може в зависимост от драйвъра да върне грешка по елемент на масива. На практика това не е „винаги налично“. Трябва да предвидиш, че някои бази/доставчици докладват само първата грешка или че при грешка в пакета останалата част може изобщо да не се изпълни. Точно затова едно устойчиво решение обикновено е двустепенно:

• Стъпка A: Опитай пакета като Array DML.
• Стъпка B: Ако пакетът се провали, раздели го (на половина) или премини контролирано към единични редове – но само за този пакет – и логни подробно.

Звучи като повече работа, но в импорти това често е разликата между „в 02:00 през нощта всичко спира“ и „импортът минава, 7 реда отиват в списъка с грешки“.

Практически приложим модел: първо пакетно изпълнение, после целенасочено изолиране

Следният модел се е доказал за процесно-ориентирани софтуерни решения, при които качеството на данните е смесено:

Стъпка 1: Поставяне на данните в пакетна структура (вкл. контекст при грешки)

Запазвай данните за импорт не само като сурови стойности, а с минимален контекст: външен ID, номер на реда от източника, евентуално хеш/контролна сума. Това не е „Nice to have“: при грешка не искаш първо да парсваш CSV, за да разбереш какво е счупено.

Стъпка 2: Array DML ausführen

Задаваш ArraySize на дължината на пакета, свързваш параметрите като масиви и изпълняваш ExecSQL. Важно: поддържай типовете на параметрите стабилни (напр. за числови полета да не ги свързваш понякога като String, понякога като Integer), в противен случай базата данни прави имплицитни кастове или FireDAC ще трябва да преобразува всеки елемент.

Стъпка 3: При грешка – стесни пакета вместо да повтаряш на сляпо

Ако ExecSQL се провали, имаш две надеждни опции:

• Binary Split (разполовяване): разделяш пакета на две половини и опитваш всяка половина отново като Array DML. Повтаряш това, докато не останеш с малко количество, което можеш да провериш по единично. Предимство: запазваш голяма част от производителността, ако само няколко реда са повредени. Недостатък: повече логика и при системни грешки (напр. грешен тип данни) ефектът е малък.
• Fallback към единични редове за този пакет: задаваш ArraySize=1 (или свързваш единични стойности) и изпълняваш ред по ред, логваш грешките и продължаваш. Предимство: просто, гарантирано за всеки ред. Недостатък: в този пакет губиш скорост.

На практика комбинирам и двете: първо разполовявам 1–2 пъти (за да премина бързо „добри блокове“), след това при малки остатъци преминавам към единични редове, за да логвам точни данни за грешките.

Fehlerobjekte und Meldungen: Was du aus FireDAC herausziehen solltest

FireDAC капсулира DB-Fehler в Exceptions (типично EFDDBEngineException) с подробна информация. За експлоатация са важни три нива:

• DB-Fehlercode (db-spezifisch): напр. SQLSTATE при PostgreSQL, Error Number при SQL Server.
• Constraint-/Objektname: често се съдържа в текста на грешката (Unique-Index, FK-Constraint).
• Statement-Kontext: таблица, операция, евентуално стойности на параметри (пазете се при лични данни).

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

Капани, които отнемат време в реални импорти

1) „Беше само един ред“ – но транзакцията вече е „dirty“

В зависимост от БД и драйвъра грешка може да причини цялото изпълнение на statement-а да се счита за неуспешно и транзакцията да остане в състояние, в което трябва явно да направиш rollback или в което последващи statement-и ще се провалят. Особено при някои драйвъри „да продължиш просто така след грешка“ не е безопасно предположение.

Последствие: ако работиш в транзакция и един пакет се провали, стандартният път е: Rollback на текущия пакетен контекст (или на цялата транзакция) и след това рестарт. Това пасва добре на „Commit на пакет“.

2) Autocommit vs. explizite Transaktion

Ако не стартираш експлицитна транзакция, често драйвърът/провайдърът решава как се комитват statement-ите. За bulk-imports това рядко е желаното поведение. Експлицитните транзакции ти дават контрол върху:

• продължителност на заключването
• Поведение при откат
• Точки за повторно стартиране

И: Експлицитно не означава „една голяма транзакция“. Означава „осъзнато“.

3) Trigger, Constraints und Nebenwirkungen

Array DML ускори предаването, но не автоматично работата на сървъра. Ако на целевата таблица имаш тригери (напр. Audit-Logging, автоматично изчисляване на статус), тясното място може да не е самият Insert, а кодът на тригера. В такъв случай един batch може да има по-малко roundtrips, но CPU-то на DB-сървъра остава ограничаващият фактор.

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

4) Datentypen und implizite Konvertierungen

Една от най-честите причини за „Защо е бавно?“: параметрите са свързани като string и базата данни прави каст на всяки ред към Integer/Date/Decimal. Това е невидимо, но скъпо. За стабилна производителност:

• Задавайте подходящи типове за параметрите (дата като дата, число като число).
• При Decimals внимавайте с локалните капани (запетая срещу точка). FireDAC тук в повечето случаи е правилният избор, но смесени източници не са.
• Уточнете предварително стратегията за часови зони/UTC (timestamp-ите при импорти са класически проблем).

5) Fehlertexte sind für Menschen, aber nicht für Automatisierung

Изкушаващо е да парснеш текста на грешката („duplicate key value violates unique constraint …“). Прави това само като последна опция. По-добри са структурирани кодове (SQLSTATE, Error Number). За съжаление не всички драйвери предоставят всичко еднакво добре. Планирай и двете: код и текст, плюс опционално „Constraint-Name от текста“, но без твърда зависимост.

Debugging-Hinweise: So findest du schnell die kaputte Zeile

Batch reproduzierbar machen

Ако един импорт се проваля спорадично, ти трябва възпроизводимост. Запази за всеки batch малък диагностичен файл или лог запис, който съдържа:

• Номер на batch и време
• ArraySize и режим на транзакция
• списъка с бизнес ключове (напр. външни IDs) в batch-а

Често това е достатъчно, за да стартиш впоследствие целенасочено мини-импорт само за тези IDs.

Finale SQL sichtbar machen (aber ohne Datenleaks)

При дебъг искаш да знаеш: коректен ли е SQL-ът? правилни ли са параметрите? FireDAC предлага Monitoring/Tracing чрез FDMoni-компоненти и логване на драйвера. В продукционно близки среди е важно:

• активирай tracing селективно и само временно (въздействие върху производителността и защита на данните)
• логвай стойностите на параметрите само в сигурна среда или маскирани
• при лични данни: в лога само технически ключове (IDs) и никакви данни в ясно текстово съдържание

Wenn du split-testest: Abbruchkriterien definieren

При Binary Split не искаш да делиш вечно. Задай долна граница, напр. „под 20 реда превключи на единичен режим“. И определи лимит колко грешки общо толерираш, преди да прекратиш импорта (напр. при системни проблеми с мапинга). В противен случай ще се озовеш с безкрайни списъци с грешки и ще блокираш последваща обработка.

Wann sich der Aufwand wirklich lohnt (und wann nicht)

Array DML с обработка на грешки per ред си заслужава особено когато:

• Много редове се обработват (хиляди до милиони).
• Няколко реда са с грешки, но въпреки това искаш процесът да продължи.
• Импортът трябва да работи стабилно в продукция (напр. нощна обработка, услуга без UI).
• Трябва да върнеш списък с грешки към бизнес звеното/източника (с референция към редовете).

По-малко е подходящо, когато:

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

Алтернативна архитектура: Staging-Tabelle вместо „direkt ins Ziel“

Ако редовно се сблъскваш с несъвместимо качество на данните, чистото „insert директно в целевата таблица“ често е погрешно решение. Staging-Tabelle (предварителна таблица) е таблица, в която първоначално записваш данните технически коректно (при нужда с по-меки типове), а едва след това ги валидираш и прехвърляш в целевата таблица.

Предимства в експлоатация:

• Грешните записи остават проследими и съхранени (включително суровите данни).
• Можеш да изпълняваш валидацията отделно и повторяемо.
• Развързваш приемането на интерфейса от бизнес обработката.

Array DML често е бързият път към Staging-Tabelle, докато прехвърлянето в целевата таблица се прави като set-базиран SQL (или Stored Procedure). Това премества обработката на грешки повече към DB страната, което в зависимост от организацията (DBA роли, деплоймент) може да е желателно или нежелано.

Експлоатация и администриране: какво трябва да знаят IT-леадите и администраторите

Мониторинг: процент на грешки и пропусквателна способност са основните метрики

За стабилна експлоатация на масов внос две метрики казват повече от самото „време на изпълнение“:

• Пропусквателна способност: редове в минута (или на партида) включително пик/медиана.
• Процент грешки: брой грешни редове на изпълнение, идеално групирани по класове грешки (Unique, FK, NOT NULL, конфликт на типове).

Ако наблюдаваш тези две стойности редовно, ще разбереш рано дали източникът се е променил (напр. нов формат) или целевата система (напр. нови constraints) е станала по-строга.

Блокировки и времеви прозорци за натоварване

Bulk-Inserts могат да предизвикат блокировки и IO натоварване. Ако паралелно потребители работят върху същите таблици, трябва да обмислиш ниво на изолация, индекси и евентуално партициониране. Практически това означава: или правиш импорти в дефинирани прозорци за натоварване, или изграждаш поток на данни, който да съжителства с работеща система (напр. чрез Staging + асинхронно прехвърляне).

Конкретен контролен списък за надежден Bulk-Insert с Array DML

• Размер на партидата да се определи (начална стойност 500–1 000) и да се тюне измеримо.
• Експлицитна транзакция: Commit на партида по подразбиране; „Commit в края“ само като съзнателно решение.
• Стабилни типове параметри да се зададат, да не се налагат имплицитни cast-ове.
• Контекст на грешката за всеки запис да се носи (външен ID, изходен ред).
• Стратегия при грешки: първо обработка на ниво партида, после split/fallback и логване на ниво ред.
• Логване: кодове + текст, но в съответствие с изискванията за защита на данните; записвай Batch-ID и Lauf-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.

Wenn ihr einen bestehenden Import- oder Schnittstellenprozess in Delphi/FireDAC stabilisieren wollt (Performance, Transaktionen, Wiederanlauf, Logging), klären wir das gern strukturiert im technischen Gespräch:

Für dieses Thema sind auch Delphi Bulk Insert und Bulk Insert Delphi FireDAC wichtig. Der Beitrag ordnet diese Aspekte verständlich ein und zeigt, worauf es im Alltag ankommt.

Projekt oder Modernisierungsvorhaben mit Net-Base besprechen.