FireDAC: Bulk-Insert pomocou Array DML a robustným spracovaním chýb na úrovni riadku

Jedna BDE-Ablosung mit nativer Anbindung Bulk-Insert mit Array DML je často najrýchlejší spôsob, ako dostať veľa záznamov do databázy: namiesto tisícov jednotlivých INSERTov sa viaže pole parametrov a odosiela naraz na server. V praxi sa však rýchlo ukáže problém: jeden záznam porušuje unikátny index, pole označené NOT NULL je prázdne, cudzí kľúč nesedí – a zrazu nie je jasné, ktorý riadok ten batch zabil, či sa niečo už zapísalo a ako bezpečne pokračovať, bez vytvorenia dátových nekonzistencií.

Presne o to tu ide: Ako použiť Array DML tak, aby si pre každý riadok dostal spoľahlivé informácie o chybách, udržal transakciu pod kontrolou a v prevádzke vedel dohľadať, čo sa stalo. Zameranie nie je na akademické štúdium API, ale na hraničný prípad, ktorý sa v reálnych importoch pravidelne objavuje: veľký batch, pár poškodených riadkov, ale chceš napriek tomu rýchlosť.

FireDAC Bulk-Insert pomocou Array DML: Prečo sa Array DML pri Bulk-Insert vôbec oplatí

Array DML (Data Manipulation Language) v FireDAC znamená: viažeš parametre nie ako jednotlivé hodnoty, ale ako pole. FireDAC potom (v závislosti od ovládača/DB) odošle menej roundtripov, môže efektívnejšie pracovať na serveri a dramaticky zníži overhead na klientskej strane. To je obzvlášť relevantné v troch situáciách:

• ETL a importné procesy: CSV/XML/JSON dnu, normalizácia/mapovanie, potom do stagingovej alebo cieľovej tabuľky.
• Bufre rozhraní: REST- alebo MQ-payloady sa zhromažďujú a periodicky ukladajú.
• Protokolové-/eventové tabuľky: veľa malých INSERTov, pri ktorých dominuje latencia.

Zisk však nie je zadarmo. S Array DML presunieš komplexitu z „mnoho jednotlivých príkazov“ na „jeden príkaz s mnohými riadkami“. To je dobré pre výkon, ale náročnejšie pre diagnostiku chýb, transakčnú logiku a opätovné spustenie.

Typický hraničný prípad: Jeden batch, jeden chybný riadok

Klasika v prevádzke: importuješ 50 000 riadkov. Zvolíš ArraySize 1 000, pretože nechceš pre každý riadok roundtrip. Batch 17 zlyhá. DB hlási len „duplicate key“ alebo „violates foreign key constraint“. V UI alebo v servisnom logu potom často stojí len: „ExecSQL failed“.

Bez spoľahlivého spracovania chýb sa potom zvyčajne stanú dve zlé veci:

• Zahodíš celý batch, hoci 999 z 1 000 riadkov by boli v poriadku.
• Vrátiš sa k jednotlivým INSERTom a stratíš výhodu výkonu trvale.

Cieľom je tretia cesta: zachovať výkon dávkového spracovania, ale presne protokolovať chyby (index riadka, kľúčové hodnoty, text chyby DB) a voliteľne potvrdiť „good rows“ – v závislosti od toho, ako kritická je konzistencia a idempotencia (opakované spustenie bez dvojitého efektu) vo vašom procese.

FireDAC Array DML: Rozhodujúce nastavenia (bez mýtov)

Pre Bulk-Insert s Array DML sú v praxi vždy tie isté nastaviteľné parametre rozhodujúce:

1) ArraySize und Batch-Größe

ArraySize (pri TFDQuery/TFDCommand) určuje, koľko „riadkov“ FireDAC sa spracuje v jednom volaní. Väčšie nie je automaticky lepšie. Príliš veľké znamená: viac pamäte na klientovi, väčší payload na linke, väčšie zámky/log‑zaťaženie na serveri a v prípade chyby väčší „Blast Radius“. Pre robustné importy je často dobrý počiatočný rozsah veľkosti dávky medzi 200 a 2 000, v závislosti od počtu stĺpcov, BLOBs a latencie.

2) Transaktionsgrenze

Potrebujete jasné rozhodnutie: Commit za dávku alebo Commit pre celý import. Nie je to otázka vkusu, ale prevádzkové rozhodnutie:

• Commit za dávku: obmedzuje zámky a transakčný log, jednoduchší reštart, ale medzistavy sú viditeľné (podľa úrovne izolácie). Chyby v dávke 17 nechajú dávky 1–16 v systéme.
• Commit na konci: „všetko alebo nič“, konzistentnejšie z fachového hľadiska, ale pri veľkých objemoch riskujete dlhé zámky, veľké rollbacky a v prípade chyby je všetko preč.

Pre mnoho rozhraní a importných procesov je „Commit za dávku“ realistickejšia prevádzková stratégia – ale iba, ak máte idempotenciu a stratégiu duplikátov čistým spôsobom vyriešené (napr. cez prirodzené kľúče, Upserts alebo importné ID).

3) UpdateOptions und Prepared Statements

Pri opakovaných dávkach sa oplatí ponechať statement pripravený. „Prepare“ znamená: FireDAC nechá DB statement parsovať/kompilovať a znovu ho používa. Podľa DB môže mať to citeľný efekt, najmä pri vysokej frekvencii. Dôležitejšie tu nie je nejaký „trik 17“, ale: konzekventné opätovné použitie toho istého Query‑objektu (alebo toho istého TFDCommand) a stabilné typy parametrov.

Korektné spracovanie chýb po riadku: čo naozaj potrebujete

Ak chcete riešiť chyby „po riadku“, potrebujete tri veci:

1. Priradenie: Ktorý index poľa (0..N-1) zlyhal?
2. Kontext: Aké obchodné kľúčové hodnoty má tento riadok (napr. externé ID, číslo zákazníka, časová pečiatka)?
3. Riadenie: Čo urobíte potom? Zrušíte, vynecháte len chybné riadky, alebo dávku rozdelíte?

FireDAC môže podľa ovládača vrátiť chyby po jednotlivých prvkoch poľa. V praxi to však nie je „automaticky k dispozícii“. Musíte rátať s tým, že niektoré databázy/poskytovatelia nahlásia len prvú chybu alebo že jedna chyba v dávke zabrzdí vykonanie zvyšku. Práve preto je robustný vzor často dvojstupňový:

• Stupeň A: Pokúste sa spracovať dávku ako Array DML.
• Stupeň B: Ak dávka zlyhá, rozdeľte ju (na polovicu) alebo kontrolovane prejdite na spracovanie po jednotlivých riadkoch – ale len pre túto dávku – a dôkladne zaznamenajte chyby.

Znie to ako viac práce, ale v importných tokoch je to rozdiel medzi „o 02:00 v noci všetko stojí“ a „import prebehne, 7 riadkov skončí v zozname chýb“.

Praktický vzor: Najprv dávka, potom cielene izolovať

Následujúci vzor sa osvedčil pri procesne orientovaných softvérových riešeniach, v ktorých je kvalita údajov zmiešaná:

Krok 1: Uložiť údaje do batch-štruktúry (vrátane kontextu chýb)

Ukladaj importované údaje nielen ako surové hodnoty, ale s minimálnym kontextom: externé ID, číslo riadku zo zdroja, prípadne Hash/Checksumme. To nie je „Nice to have“: v prípade chyby nechceš najprv znovu parsovať CSV, aby si zistil, čo je poškodené.

Krok 2: Array DML vykonať

Nastavíš ArraySize na dĺžku batchu, viažeš parametre ako polia a spustíš ExecSQL. Dôležité: udržiavaj stabilné typy parametrov (napr. pre numerické polia ich neviaž striedavo ako String a ako Integer), inak DB vykoná implicitné casty alebo FireDAC bude musieť prevádzať každý prvok.

Krok 3: Pri chybe – zúžiť batch namiesto slepého opakovania

Ak ExecSQL zlyhá, máš dve robustné možnosti:

• Binary Split (rozdeliť na polovice): rozdeliť batch na dve polovice, skúsiť každú polovicu znova ako Array DML. Tento postup opakuješ, kým nedospeješ k malej množine, ktorú môžeš skontrolovať jednotlivo. Výhoda: zachovávaš veľkú časť výkonu, ak je poškodených len niekoľko riadkov. Nevýhoda: viac logiky a pri systematických chybách (napr. nesprávny dátový typ) to prináša málo.
• Fallback na jednotlivé riadky pre tento batch: nastavíš ArraySize=1 (alebo viažeš jednotlivé hodnoty) a vykonávaš riadok po riadku, loguješ chyby a pokračuješ. Výhoda: jednoduché, garantované pre každý riadok. Nevýhoda: v tomto batchu stratíš rýchlosť.

V praxi to kombinujem: najprv 1–2‑krát rozdeliť (aby sa rýchlo prešli „dobré bloky“), potom pri malých zostávajúcich množinách prejsť na jednotlivé riadky, aby sa zaznamenali jednoznačné informácie o chybách.

Objekty chýb a hlásenia: was du aus FireDAC herausziehen solltest

FireDAC uzatvára DB-činy do Exceptions (typicky EFDDBEngineException) s detailnými informáciami. Pre prevádzku sú dôležité tri úrovne:

• DB-Fehlercode (db-spezifisch): napr. SQLSTATE pri PostgreSQL, Error Number pri SQL Serveri.
• Constraint-/Objektname: často obsiahnutý v texte chyby (Unique-Index, FK-Constraint).
• Statement-Kontext: tabuľka, operácia, prípadne hodnoty parametrov (opatrne pri osobných údajoch).

Ak chceš logovať po riadku, musíš v prípade chyby navyše identifikovať riadok. FireDAC môže v niektorých prípadoch poskytnúť index v poli. Nespoliehaj sa však výhradne na to. Vždy si vytvor vlastný index (pozícia v batchi) a k tejto pozícii zaloguj aspoň jeden obchodný kľúč.

Úskalia, ktoré pri reálnych importoch stoja čas

1) „Bol to predsa len iba jeden riadok“ – aber die Transaktion ist schon „dirty“

Podľa DB a ovládača môže chyba viesť k tomu, že sa celé vykonanie príkazu považuje za zlyhané a transakcia je v stave, v ktorom musíš buď explicitne vykonať rollback, alebo v ktorom ďalšie príkazy zlyhávajú. Najmä pri niektorých ovládačoch nie je „po chybe jednoducho pokračovať“ bezpečné predpokladať.

Dôsledok: Ak pracuješ v transakcii a batch zlyhá, štandardná cesta je: Rollback aktuálneho batch-Kontexts (alebo celej transakcie) a následné opätovné spustenie. To dobre zapadá do „Commit na batch“.

2) Autocommit vs. explizite Transaktion

Ak nespustíš explicitnú transakciu, často ovládač/provider rozhodne, ako bude committovať príkazy. Pre hromadné importy to zriedka zodpovedá tomu, čo potrebuješ. Explicitné transakcie ti dávajú kontrolu nad:

• doba trvania zámkov
• Správanie pri rollbacku
• reštartovacie body

A: Explizit neznamená „obrovská transakcia“. Znamená to „vedome“.

3) Trigger, Constraints a vedľajšie účinky

Array DML urýchľuje odovzdanie, ale automaticky neurychľuje prácu na strane servera. Ak máš na cieľovej tabuľke triggery (napr. audit-logging, automatické výpočty stavu), potom úzke miesto nemusí byť INSERT, ale samotný trigger-kód. Batch tak môže mať síce menej roundtripov, no CPU na DB-serveri zostáva limitujúcim faktorom.

Pre adminov a technických leadov: Pri problémoch s výkonom sa oplatí pozrieť na wait events/locks a transakčný log. Bulk-INSERT je často len spúšťač, nie príčina.

4) Dátové typy a implicitné konverzie

Jeden z najčastejších dôvodov „prečo je to pomalé?“: parametre sú viazané ako string, DB potom pre každý riadok konvertuje na Integer/Date/Decimal. To je neviditeľné, no drahé. Pre stabilný výkon:

• Nastav vhodné dátové typy parametrov (dátum ako dátum, číslo ako číslo).
• Pri decimaloch dávaj pozor na locale pasce (čiarka vs. bodka). FireDAC je tu zvyčajne správne, ale zmiešané zdroje nie.
• Vopred si ujasni stratégiu časových pásiem/UTC (timestamps sú pri importoch klasika).

5) Chybové texty sú pre ľudí, nie pre automatizáciu

Je lákavé parsovať chybový text („duplicate key value violates unique constraint …“). Rob to len ako poslednú možnosť. Lepšie sú štruktúrované kódy (SQLSTATE, Error Number). Bohužiaľ nie všetky ovládače poskytujú všetko rovnako dobre. Naplánuj preto oboje: kód a text, plus voliteľne „constraint-name z textu“, ale bez tvrdej závislosti.

Tipy na ladenie: Ako rýchlo nájsť chybný riadok

Urobiť batch reprodukovateľným

Ak import sporadicky zlyháva, potrebuješ reprodukovateľnosť. Ulož pre každý batch malý diagnostický súbor alebo log-záznam, ktorý obsahuje:

• číslo batchu a čas
• ArraySize a režim transakcie
• zoznam biznisových kľúčov (napr. externé ID) v batche

To často stačí, aby si následne cielene spustil mini-import len pre tieto ID.

Zviditeľniť finálne SQL (ale bez únikov dát)

Pri ladení chceš vedieť: Je SQL správne? Sú parametre v poriadku? FireDAC poskytuje monitoring/tracing cez FDMoni-Komponenten a logovanie ovládačov. V produkčne blízkych prostrediach je dôležité:

• zapínať tracing cielene a len dočasne (výkon a ochrana údajov)
• logovať hodnoty parametrov len v bezpečnom prostredí alebo maskované
• pri osobných údajoch: v logu len technické kľúče (ID) a žiadne údaje v čitateľnom texte

Ak testuješ split (binárne delenie): definuj kritériá prerušenia

Pri binárnom delení nechceš deliť do nekonečna. Nastav spodnú hranicu, napr. „pod 20 riadkov prepni na jednotlivý režim“. A nastav limit, koľko chýb celkovo toleruješ, než import prerušíš (napr. pri systémových problémoch s mapovaním). Inak skončíš s nekonečnými zoznamami chýb a zablokuješ následné spracovanie.

Kedy sa snaha naozaj oplatí (a kedy nie)

Array DML s error-handlingom na riadok sa oplatí najmä, ak:

• sa spracováva veľké množstvo riadkov (tisíce až milióny).
• len málo riadkov je chybových, ale napriek tomu chceš pokračovať.
• import musí bežať stabilne v prevádzke (napr. nočné spracovanie, služba bez UI).
• musíš vrátiť zoznam chýb príslušnému oddeleniu/zdroju (s odkazom na riadky).

To sa menej oplatí, ak:

• zapíšeš len niekoľko desiatok riadkov (jednotlivé INSERTy sú v poriadku),
• kvalita dát je taká slabá, že 30–50 % riadkov zlyhá (vtedy je rozumnejšia staging stratégia),
• používaš natívny DB bulk-load postup (z. B. COPY v PostgreSQL, BCP/BULK INSERT v SQL Server) – v tom prípade nie je Array DML nástrojom.

Alternatívna architektúra: Staging tabuľka namiesto „priamo do cieľa“

Ak pravidelne bojuješ s rôznou kvalitou dát, čisté „INSERT priamo do cieľovej tabuľky“ často nie je správne rozhodnutie. Staging-Tabelle (predstupeň) je tabuľka, do ktorej najprv uložíš dáta technicky korektne (prípadne s mäkšími typmi), a až potom ich validuješ a prevádzaš do cieľovej tabuľky.

Výhody pri prevádzke:

• Chybné záznamy zostávajú uložené s možnosťou auditovania (vrátane surových dát).
• Validáciu môžeš vykonávať samostatne a opakovane.
• Oddelíš príjem rozhrania od doménového spracovania.

Array DML je potom často rýchla cesta do Staging-Tabelle, zatiaľ čo prevod do cieľovej tabuľky prebieha ako set‑based SQL (alebo Stored Procedure). Tým sa chyba spracovania presunie viac na DB stranu, čo môže byť v závislosti od organizácie (DBA role, deployment) žiaduce alebo neželané.

Prevádzka a administrácia: čo by mali vedieť vedúci IT a administrátori

Monitoring: miera chýb a priepustnosť sú kľúčové metriky

Pre stabilnú prevádzku bulk importu sú dve metriky výpovednejšie než samotný „čas behu“:

• Priepustnosť: riadky za minútu (alebo za dávku) vrátane peak/mediánu.
• Miera chýb: chybné riadky na beh, ideálne zoskupené podľa triedy chyby (Unique, FK, NOT NULL, konflikt typu).

Ak tieto dve hodnoty sleduješ pravidelne, spoznáš včas, či sa na zdroji niečo zmenilo (napr. nový formát) alebo či cieľový systém (napr. nové constraints) sprísnil požiadavky.

Uzamykanie a časové okná so zníženým zaťažením

Bulk‑inserty môžu spôsobiť locking a IO zaťaženie. Ak súčasne používateľia pracujú na rovnakých tabuľkách, musíš uvažovať o izolačnom stupni, indexoch a prípadne partiocionovaní. Prakticky to znamená: buď umiestniť importy do časových okien so zníženou záťažou, alebo navrhnúť dátový tok tak, aby koexistoval s prevádzkou (napr. cez Staging + asynchrónne prevzatie).

Konkrétny kontrolný zoznam pre robustný Bulk-Insert s Array DML

• Veľkosť dávky nastaviť (počiatočná hodnota 500–1 000) a merateľne doladiť.
• Explicitná transakcia: commit za dávku ako predvolené, „commit na konci“ iba so zámerným rozhodnutím.
• Stabilné typy parametrov nastaviť, nevyžadovať implicitné konverzie.
• Viesť kontext chyby pre každý záznam (externé ID, zdrojový riadok).
• Stratégia chýb: najskôr prístup na úrovni dávky, potom rozdelenie/fallback, logovať po riadku.
• Logovanie: kódy + text, ale v súlade s ochranou údajov; zaznamenávať Batch-ID a Lauf-ID.
• Opätovné spustenie: zabezpečiť idempotenciu (kľúč/UPSERT/Import-ID).

Záver: Array DML je rýchly – robustný sa stane vďaka procesu a stratégii chýb

Jedno FireDAC Bulk-Insert s Array DML je silný nástroj, pokiaľ sa nesprávaš, akoby chyby neexistovali. V reálnych dátových tokoch sa vždy vyskytujú výkyvy: duplicitné záznamy, chýbajúce referencie, poškodené dátumové hodnoty. Čistý prístup je preto: Array DML pre výkon, skombinované s kontrolovanou izolačnou stratégiou (Split alebo Fallback) a zoznamom chýb vysledovateľným pre každý riadok. Tak dosiahneš rýchlosť aj prevádzkovú spoľahlivosť zároveň – a presne na tom záleží, keď importy nesmú bežať len v testovacom prostredí, ale musia každú noc spoľahlivo prebehnúť.

Ak chcete stabilizovať existujúci importný alebo rozhraniový proces v Delphi/FireDAC (výkon, transakcie, opätovné spustenie, logovanie), radi to preberieme štruktúrovane v technickom rozhovore:

Pre túto tému sú dôležité aj Delphi Bulk Insert a Bulk Insert Delphi FireDAC. Článok tieto aspekty jasne zaradí a ukáže, na čo záleží v bežnej prevádzke.

Prejednať projekt alebo modernizačný zámer s Net-Base.