FireDAC: Bulk-Insert со Array DML и прецизно ракување со грешки за секој ред

Токму за тоа станува збор тука: како да ја користиш Array DML така што ќе добиваш по редица проверливи информации за грешки, ќе ја држиш транзакцијата под контрола и ќе можеш во оперативната работа да разбереш што се случило. Фокусот не е на академско читање на API, туку на работниот сценарио кој во реални импорти редовно се појавува: Голем батч, неколку оштетени редови, но сѐ уште сакаш брзина.

FireDAC Bulk-Insert со Array DML: Зошто Array DML воопшто има смисла при Bulk-Insert

Array DML (Data Manipulation Language) во рамките на FireDAC значи: параметрите не ги поврзуваш како поединечни вредности, туку како низ. FireDAC потоа (во зависност од драјвер/DB) праќа помалку Roundtrips, може серверски да работи поефикасно и значително го намалува overhead-от на клиентот. Ова е посебно релевантно во три ситуации:

• ETL- и import-процеси: CSV/XML/JSON влегуваат, нормализација/мапирање, па потоа во staging- или целната табела.
• Schnittstellen-Puffer: REST- или MQ-payloads се собираат и периодично се персистираат.
• Протоколни/евент-табели: многу мали Inserts каде латенцијата доминира.

Добивката не доаѓа бесплатно. Со Array DML ја поместуваш комплексноста од „многу поединечни statements“ кон „едно statement со многу редови“. Тоа е добро за перформанси, но побарува повеќе при дијагноза на грешки, логика на транзакции и ре-ран.

Типичниот граничен случај: Еден батч, еден скршен ред

Класика во оперативната работа: увезуваш 50.000 редови. Ги поставуваш ArraySize на 1.000 бидејќи не сакаш roundtrip за секоја редица. Батч 17 не успева. Базата пријавува само „duplicate key“ или „violates foreign key constraint“. Во UI-то или во service-log-от често пишува само: „ExecSQL failed“.

Без чисто Error-Handling обично се случуваат две лоши работи:

• Ја фрлаш целата батч, иако 999 од 1.000 редови биле во ред.
• Се враќаш на поединечни Inserts и трајно го губиш перформансниот предност.

Целта е трета патека: да се задржат перформансите на пакетите, но да се логира дефектно-точно (индекс на ред, вредности на клучеви, DB-текст на грешка) и опционално да се commit-ираат „добри редови“ – во зависност од тоа колку критична е конзистентноста и идемпотентноста (повторно извршување без двојно дејство) во твојот процес.

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

За Bulk-Insert со Array DML во пракса секогаш истите параметри се пресудни:

1) ArraySize и големина на Batch

ArraySize (кај TFDQuery/TFDCommand) одредува колку „реди“ FireDAC се обработуваат во едно повикување. Поголемо не значи автоматски подобро. Превелика вредност значи: повеќе меморија кај клиентот, поголем payload на линијата, поголеми заклучувања/оптоварување на логот на серверот и при грешка поголем „blast radius“. За робусни увози често големина на batch помеѓу 200 и 2.000 е добар почеток, зависно од бројот на колони, BLOBs и латенцијата.

2) Transaktionsgrenze

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

• Commit по пакет: ги ограничува заклучувањата и транзакциониот лог, поедноставен повторен старт, но меѓупринтовите состојби се видливи (во зависност од нивото на изолација). Грешки во Batch 17 оставаат Batch 1–16 во системот.
• Commit на крајот: „сè или ништо“, поконзистентно во стручна смисла, но кај големи обеми ризикуваш долги заклучувања, големо rollback и при грешка сè е изгубено.

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

3) UpdateOptions и Prepared Statements

При повторувачки batch-ови исплати се да се остави Statement подготвен. „Prepare“ значи: FireDAC ја тера DB-то да го парс-ира/компилира Statement-от и да го реупотреби. Во зависност од DB, тоа може да има забележлив ефект, особено при висока фреквенција. Повеќе важно отколку некој „трик 17“ е: последователна реупотреба на истиот Query-објект (или истиот TFDCommand) и стабилни типови на параметрите.

Чисто ракување со грешки по ред: Што навистина ти треба

Ако сакаш да ракуваш со грешки „по ред“, ти требаат три работи:

1. Идентификација: кој Array-индекс (0..N-1) не успеа?
2. Контекст: кои бизнис-клучни вредности ги има тој ред (на пр. екстерна ID, број на клиент, временска ознака)?
3. Контрола: што правиш понатаму? Прекинуваш, само ги прескокнуваш лошите редови или го делиш пакетот?

FireDAC може, во зависност од драјверот, да враќа грешки по елемент на Array. Практично тоа не е секогаш достапно. Мораш да сметаш дека некои бази/провајдери пријавуваат само првата грешка или дека една грешка во пакетот го спречува извршувањето на остатокот. Точно поради ова, робусен шаблон обично е двостепен:

• Степен A: Обиди се да го извршиш пакетот како Array DML.
• Степен B: Ако пакетот не успее, подели го (наполовина) или контролирано врати се на извршување поединечно по редови — но само за тој пакет — и логирај прецизно.

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

Праксично применлив шаблон: прво пакетот, па потоа целенасочено изолирање

Следниот модел се покажа како успешен за процесно-блиски софтверски решенија каде што квалитетот на податоците е мешан:

Чекор 1: Сложете ги податоците во batch-структура (вкл. контекст на грешки)

Зачувајте ги податоците кои треба да се импортират не само како сурови вредности, туку со минимален контекст: екстерен ID, број на ред од изворот, евентуално Hash/Checksumme. Ова не е „Nice to have“: во случај на грешка не сакате прво повторно да парсирате CSV за да откриете што е расипано.

Чекор 2: Извршете Array DML

Поставете ArraySize на должината на batch-от, поврзете параметри како низи и извршете ExecSQL. Важно: задржете ги типовите на параметрите стабилни (н.пр. за нумерички полиња не поврзувајте понекогаш како String, понекогаш како Integer), инаку DB ќе произведе имплицитни cast-ирања или FireDAC ќе мора да преобразува по елемент.

Чекор 3: Во случај на грешка – сузете го batch-от наместо да го повторувате слепо

Ако ExecSQL не успее, имате две робустни опции:

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

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

Објекти на грешки и пораки: Што треба да извлечете од FireDAC

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

• DB-код на грешка (специфичен за DB): н.пр. SQLSTATE кај PostgreSQL, Error Number кај SQL Server.
• Име на constraint/објект: често присутно во текстот на грешката (Unique-Index, FK-Constraint).
• Контекст на statement: табела, операција, евентуално вредности на параметри (внимателно со лични податоци).

Ако сакате да логирaте по ред, во случај на грешка мора дополнително да ја идентификувате редот. FireDAC може во некои случаи да врати Array-индекс. Не се потпирајте исклучиво на тоа. Секогаш направете сопствен индекс (позиција во batch-от) и за таа позиција логирајте најмалку еден бизнис-клуч.

Замки кои во реални импорти одземаат време

1) „Тоа беше само еден ред“ – но трансакцијата е веќе „dirty“

Во зависност од DB и драјверот, една грешка може да направи целото извршување на statement-от да се смета за неуспешно и трансакцијата да влезе во состојба во која или мора експлицитно да правите rollback или во која понатамошните statement-и ќе не успеваат. Особено кај некои драјвери, „по грешка само продолжи“ не е безбедно предпоставување.

Последица: Ако работите во рамки на трансакција и еден batch не успее, стандардниот пат е: Rollback на тековниот batch-контекст (или на целата трансакција) и потоа повторен почеток. Тоа добро се вклопува со „Commit по batch“.

2) Autocommit vs. explizite Transaktion

Ако не стартувате експлицитна трансакција, често драјверот/провајдерот одлучува како ќе се прават commit-ови на statement-ите. За bulk-импорти тоа ретко е она што го сакате. Експлицитните транзакции ви даваат контрола над:

• Времетраење на заклучувањето
• Понашање при Rollback
• Точки за повторно стартување

И: Експлицитно не значи „огромна трансакција“. Значи „свесно“.

3) Trigger, Constraints и несакани последици

Array DML го забрзува преносот, но не автоматски работата на серверската страна. Ако на целната табела имаш Trigger-и (н.п. Audit-Logging, автоматско пресметување на статус), тогаш тесното грло можеби воопшто не е INSERT-от, туку кодот на тригерот. Тогаш е можно еден batch да има помалку Roundtrips, но CPU-то на DB-серверот останува ограничувачкиот фактор.

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

4) Типови податоци и имплицитни конверзии

Една од најчестите причини „Зошто е тоа бавно?“: параметрите се биндaт како String, базата по ред конвертира во Integer/Date/Decimal. Тоа е невидливо, но скапо. За стабилни перформанси:

• Постави ги типовите на параметрите соодветно (датум како датум, број како број).
• За Decimal-типови внимавај на замките на локалот (запирка vs. точка). FireDAC е тука најчесто правилно, но мешаните извори не се.
• Појасни ја стратегијата за временски зони/UTC однапред (Timestamps се класика при импорти).

5) Текстовите за грешки се за луѓе, не за автоматизација

Примамливо е да се парсира текстот на грешката („duplicate key value violates unique constraint …“). Направи го тоа само како последна опција. Подобро се структурирани кодови (SQLSTATE, Error Number). За жал, не сите драјвери доставуваат сé еднакво добро. Затоа планирај и двете: код и текст, плус опционално „Constraint-Name aus Text“, но без цврста зависност.

Совети за дебагирање: Како брзо да го најдеш редот што предизвикува грешка

Направи batch-от репродуцибилен

Ако еден импорт повремено не успева, ти треба репродуцибилност. Зачувај за секој Batch мала дијагностичка датотека или лог-влез што содржи:

• Број на Batch и време
• ArraySize и режим на трансакција
• листата на стручни клучеви (н.п. екстерни IDs) во Batch-от

Тоа често е доволно за подоцна селективно да се стартува мини-импорт само за тие ID.

Направи ја финалната SQL видлива (но без истек на податоци)

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

• Трасирањето целенасочено активирај само привремено (перформанси и заштита на податоци).
• Вредностите на параметрите логирај само во безбедно окружување или маскирано.
• За лични податоци: во лог само технички клучеви (IDs) и никаков јасен текст.

Ако split-тестираш: дефинирај критериуми за прекин

При бинарен split не сакаш бесконечно да делиш. Постави долна граница, н.п. „под 20 редови префрли на поединечен режим“. И постави лимит колку грешки вкупно ќе ги толерираш пред да го прекинеш импортот (н.п. при систематски проблеми со мапирање). Иначе ќе застанеш во бесконечни листи со грешки и ќе ја блокираш следната обработка.

Кога навистина се исплати трудот (и кога не)

Array DML со Error-Handling по ред се исплати особено кога:

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

Не се исплати толку ако:

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

Alternative Architektur: Staging-Tabelle statt „Direkt ins Ziel“

Ако редовно се справуваш со мешан квалитет на податоци, чисто „Insert директно во табелата-цел“ често е погрешна одлука. Staging-табела (предфаза) е табела во која податоците прво ги чуваш технички коректно (евентуално со помеки типови), и дури потоа ги валидираш и ги пренесуваш во табелата-цел.

Предности во оперативата:

• Неисправните записи остануваат следливо зачувани (вкл. сурови податоци).
• Валидацијата можеш да ја извршиш посебно и повторливо.
• Го одвојуваш прифаќањето на интерфејсот од функционалната обработка.

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

Betrieb und Administration: Was IT-Leads und Admins dazu wissen sollten

Monitoring: Fehlerquote und Durchsatz sind die Kernmetriken

За стабилен оперативен рад на bulk-import, две метрики се поинформативни од самото „време на извршување“:

• Durchsatz: редови по минута (или по батч) вкл. пик/медијана.
• Fehlerquote: редови со грешки по извршување, идеално групирано по класи на грешки (Unique, FK, NOT NULL, конфликт на типови).

Ако ги следиш овие две вредности редовно, рано ќе забележиш дали нешто се променило на изворот (на пр. нов формат) или дали целниот систем (на пр. нови constraints) станал построг.

Sperren und Lastfenster

Bulk-Insert-ите можат да предизвикаат заклучувања и IO-оптоварување. Ако паралелно корисници работат на истите табели, мораш да размислиш за Isolation Level, индекси и евентуално партиционирање. Практично тоа значи: или постави импорти во прозори за оптоварување, или изгради проток на податоци кој коегзистира со тековниот оперативен рад (на пр. преку Staging + асинхроно преземање).

Konkrete Checkliste für einen robusten Bulk-Insert mit Array DML

• Batch-Größe постави (почетна вредност 500–1.000) и ја оптимизирај мерливо.
• Explizite Transaktion: Commit по батч како подразбирно; „Commit am Ende“ само со свесна одлука.
• Parametertypen stabil постави, не принудувај имплицитни кастови.
• Fehlerkontext pro Datensatz носи (екстерно ID, изворен ред).
• Fehlerstrategie: најпрво батч, потоа Split/Fallback, логирање по ред.
• Logging: кодови + текст, но во согласност со заштитата на податоци; регистрирај Batch-ID и Lauf-ID.
• Wiederanlauf: обезбеди идемпотентност (клуч/Upsert/Import-ID).

Fazit: Array DML ist schnell – robust wird es durch Prozess und Fehlerstrategie

Еден FireDAC Bulk-Insert со Array DML е моќен алат, сè додека ти не се правиш дека грешки не постојат. Во вистинските податочни текови секогаш има исклучоци: дупликати, недостасувачки референци, оштетени датумски вредности. Затоа чистиот пристап е следниов: Array DML за перформанси, комбинирано со контролирана стратегија за изолација (Split или Fallback) и листа на грешки која може да се следи за секој ред. На тој начин добиваш брзина и оперативна сигурност заедно – и токму тоа е важно кога импортите не работат само во лабораторија, туку мораат да се извршуваат сигурно секоја ноќ.

Ако сакате да го стабилизирате постоечкиот процес на импорт или интерфејс во Delphi/FireDAC (перформанси, трансакции, опоравување, логирање), ќе го разјасниме тоа структуирано во технички разговор:

За оваа тема важни се и Delphi Bulk Insert и Bulk Insert Delphi FireDAC. Статијата ги поставува овие аспекти на јасен начин и ја објаснува практичната примена во секојдневната работа.

Разговарајте за проект или модернизациски потфат со Net-Base.