FireDAC: Hromadný INSERT s Array DML a konzistentním ošetřením chyb na úrovni jednotlivých řádků

Ein BDE-náhrada s nativním napojením Bulk-Insert mit Array DML ist oft der schnellste Weg, um viele Datensätze in eine Datenbank zu bekommen: statt tausend einzelner Inserts wird ein Parameter-Array gebunden und in einem Rutsch an den Server geschickt. In der Praxis kommt der Knackpunkt aber schnell: Ein Datensatz verletzt einen Unique-Index, ein NOT NULL-Feld ist leer, ein Foreign Key passt nicht – und plötzlich ist unklar, welche Zeile das Batch gekillt hat, ob ein Teil schon geschrieben wurde und wie du sauber weitermachst, ohne Dateninkonsistenzen zu erzeugen.

Genau darum geht es hier: Wie du Array DML so einsetzt, dass du pro Zeile belastbare Fehlerinfos bekommst, die Transaktion im Griff behältst und im Betrieb nachvollziehen kannst, was passiert ist. Der Fokus liegt nicht auf akademischer API-Lektüre, sondern auf dem Randfall, der in echten Importen regelmäßig aufschlägt: Ein großer Batch, wenige kaputte Zeilen, aber du willst trotzdem Tempo.

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

Array DML (Data Manipulation Language) bedeutet in FireDAC: Du bindest Parameter nicht als Einzelwert, sondern als Array. FireDAC sendet dann (je nach Treiber/DB) weniger Roundtrips, kann serverseitig effizienter arbeiten und reduziert den Overhead im Client drastisch. Das ist in drei Situationen besonders relevant:

• ETL- und Importstrecken: CSV/XML/JSON rein, Normalisierung/Mapping, dann in Staging- oder Zieltabelle.
• Schnittstellen-Puffer: REST- oder MQ-Payloads werden gesammelt und periodisch persistiert.
• Protokoll-/Event-Tabellen: viele kleine Inserts, bei denen Latenz dominiert.

Der Gewinn kommt aber nicht gratis. Mit Array DML verschiebst du Komplexität von „viele einzelne Statements“ zu „ein Statement mit vielen Zeilen“. Das ist gut für Performance, aber anspruchsvoller für Fehlerdiagnose, Transaktionslogik und Wiederanlauf.

Der typische Randfall: Ein Batch, eine kaputte Zeile

Der Klassiker im Betrieb: Du importierst 50 000 Zeilen. Du wählst eine ArraySize von 1 000, weil du nicht für jede Zeile einen Roundtrip willst. Batch 17 scheitert. Die DB meldet nur „duplicate key“ oder „violates foreign key constraint“. In der UI oder im Service-Log steht dann oft nur: „ExecSQL failed“.

Ohne sauberes Error-Handling passieren dann meist zwei schlechte Dinge:

• Du wirfst den ganzen Batch weg, obwohl 999 von 1 000 Zeilen ok wären.
• Du fällst auf Einzel-Inserts zurück und verlierst den Performancevorteil dauerhaft.

Cílem je třetí cesta: zachovat výkon dávky, ale zaznamenávat chyby přesně (index řádku, hodnoty klíčů, text chyby DB) a volitelně commitovat „good rows“ – v závislosti na tom, jak kritická je konzistence a idempotence (opakované spuštění bez zdvojeného efektu) ve vašem procesu.

FireDAC Array DML: relevantní nastavitelné parametry (bez mýtů)

Pro bulk-insert pomocí Array DML jsou v praxi vždy rozhodující stejné nastavitelné parametry:

1) ArraySize und Batch-Größe

ArraySize (u TFDQuery/TFDCommand) určuje, kolik „řádků“ FireDAC se zpracuje v jednom volání. Větší není automaticky lepší. Příliš velké znamená: více paměti na klientu, větší payload na síti, větší zámky/zátěž logu na serveru a v případě chyby větší „Blast Radius“. Pro robustní importy je často velikost dávky mezi 200 und 2.000 dobrým výchozím bodem, v závislosti na počtu sloupců, BLOBs a latenci.

2) Transaktionsgrenze

Potřebujete jasné rozhodnutí: Commit pro Batch nebo Commit für den gesamten Import. To není otázka vkusu, ale provozní rozhodnutí:

• Commit pro Batch: omezuje zámky a transakční log, usnadňuje RESTart, ale mezistavy jsou viditelné (v závislosti na úrovni izolace). Chyba v dávce 17 nechá dávky 1–16 v systému.
• Commit am Ende: „vše nebo nic“, z hlediska doménové konzistence konzistentnější, ale u velkých objemů riskujete dlouhé zámky, rozsáhlé rollbacky a v případě chyby je vše pryč.

Pro mnoho rozhraní a importních procesů je „Commit pro Batch“ realistickější provozní strategií – ale pouze, pokud máte idempotenci a strategii duplicit čistě ošetřenou (např. pomocí přirozených klíčů, Upserts nebo importní ID).

3) UpdateOptions und Prepared Statements

Při opakovaných dávkách se vyplatí nechat statement připravený. „Prepare“ znamená: FireDAC nechá DB statement parsovat/kompilovat a znovu použít. V závislosti na DB to může mít znatelný efekt, zejména při vysoké frekvenci. Důležité zde není žádný „trik 17“, ale: konsekventní opakované použití téhož Query-objektu (nebo téhož TFDCommand) a stabilní typy parametrů.

Čisté zpracování chyb po řádku: co opravdu potřebujete

Pokud chcete zpracovávat chyby „po řádku“, potřebujete tři věci:

1. Přiřazení: Který index pole (0..N-1) selhal?
2. Kontext: Jaké doménové klíčové hodnoty má tento řádek (např. externí ID, číslo zákazníka, časová značka)?
3. Řízení: Co s tím uděláte dál? Zrušit, přeskočit pouze špatné řádky, nebo dávku rozdělit?

FireDAC může podle ovladače vracet chyby za jednotlivé prvky pole. V praxi to ale není „vždy automaticky k dispozici“. Musíte počítat s tím, že některé databáze/poskytovatelé hlásí pouze první chybu, nebo že v dávce jedna chyba znemožní provedení zbytku. Právě proto je robustní vzor obvykle dvoustupňový:

• Stupeň A: Zkuste dávku jako Array DML.
• Stupeň B: Pokud dávka selže, rozdělte ji (napůl) nebo přejděte kontrolovaně na jednotlivé řádky – ale pouze pro tuto dávku – a logujte pečlivě.

Zní to jako více práce, ale v importních tocích to znamená rozdíl mezi „v 02:00 v noci vše stojí“ a „import proběhne, 7 řádků skončí v chybovém seznamu“.

Ein praxistaugliches Muster: Batch zuerst, dann gezielt isolieren

Následující vzor se osvědčil u procesně blízkých softwarových řešení, kde je kvalita dat různorodá:

Krok 1: Uložit data do struktury dávky (včetně kontextu chyby)

Ukládejte importovaná data nejen jako surové hodnoty, ale s minimálním kontextem: externí ID, číslo řádku ze zdroje, případně hash/kontrolní součet. Není to „nice to have“: v případě chyby nechcete znovu parsovat CSV, abyste přišli na to, co je špatně.

Krok 2: Provést Array DML

Nastavíte ArraySize na délku dávky, svážete parametry jako pole a zavoláte ExecSQL. Důležité: udržujte typy parametrů stabilní (např. u číselných polí je nevázat někdy jako String, jindy jako Integer), jinak DB provede implicitní přetypování nebo FireDAC bude muset převádět po jednotlivých prvcích.

Krok 3: Chybný případ – omezit dávku místo slepého opakování

Pokud ExecSQL selže, máte dvě robustní možnosti:

• Binary Split (půlení): Rozdělte dávku na dvě poloviny a obě poloviny znovu vyzkoušejte jako Array DML. Opakujte to, dokud nedosáhnete malé množiny, kterou můžete zkontrolovat jednotlivě. Výhoda: zachováte velkou část výkonu, pokud jsou poškozené jen jednotlivé řádky. Nevýhoda: větší složitost logiky a při systémových chybách (např. špatný datový typ) to moc nepomůže.
• Fallback na jednotlivé řádky pro danou dávku: Nastavíte ArraySize=1 (nebo svážete jednotlivé hodnoty) a provádíte řádek po řádku, logujete chyby a pokračujete. Výhoda: jednoduché, zaručeně per-řádek. Nevýhoda: v této dávce ztratíte výkon.

V praxi kombinuji obě metody: nejprve jednou až dvakrát rozdělit (aby se rychle prošly „dobré bloky“), pak při malých zbytcích přejít na jednotlivé řádky, abyste získali jednoznačné informace o chybách.

Objekty chyb a hlášení: co byste měli získat z FireDAC

FireDAC zapouzdřuje DB chyby do výjimek (typicky EFDDBEngineException) s detailními informacemi. Pro provoz jsou důležité tři úrovně:

• DB chybový kód (specifický pro DB): např. SQLSTATE u PostgreSQL, Error Number u SQL Serveru.
• Jméno constraintu/objektu: často obsažené v textu chyby (unique index, FK constraint).
• Kontext příkazu: tabulka, operace, případně hodnoty parametrů (opatrně u osobních údajů).

Pokud chcete logovat per řádek, musíte v případě chyby také identifikovat řádek. FireDAC může za určitých okolností dodat index v poli. Na něj se ale nespoléhejte úplně. Vždy si vytvářejte vlastní index (pozice v dávce) a k této pozici logujte alespoň jeden doménový klíč.

Úskalí, která při reálných importechašet čas

1) „Byl to přece jen jeden řádek“ – ale transakce je už „dirty“

V závislosti na DB a ovladači může chyba způsobit, že celé provedení příkazu je považováno za selhané a transakce je v takovém stavu, že ji buď musíte explicitně rollbackovat, nebo v ní další příkazy selhávají. Zejména u některých ovladačů není bezpečné předpokládat „po chybě prostě pokračovat”.

Důsledek: pokud pracujete v transakci a dávka selže, standardní postup je: rollback aktuálního kontextu dávky (nebo celé transakce) a pak začít znovu. To dobře sedí k „Commit na dávku“.

2) Autocommit vs. explicitní transakce

Pokud nespustíte explicitní transakci, často rozhoduje ovladač/provider o tom, jak se příkazy commitují. Pro hromadné importy to zřídka bývá to, co chcete. Explicitní transakce vám dávají kontrolu nad:

• doba zámku
• Chování rollbacku
• Body pro opětovné spuštění

A: „Výslovně“ neznamená „obrovská transakce“. Znamená „uvědoměle“.

3) Trigger, Constraints und Nebenwirkungen

Array DML zrychlí předání, ale ne automaticky práci na straně serveru. Pokud máš na cílové tabulce triggery (např. auditní logování, automatické výpočty stavu), může být úzké hrdlo nikoli INSERT, ale kód triggeru. Pak může batch sice mít méně roundtripů, ale CPU na DB serveru zůstává limitujícím faktorem.

Pro administrátory a technické vedoucí: při problémech s výkonem se vyplatí nahlédnout na Wait Events/Locks a transakční log. Hromadný INSERT je pak často jen spouštěč, ne příčina.

4) Datentypen und implizite Konvertierungen

Jeden z nejčastějších důvodů „Proč je to pomalé?“: parametry jsou vázány jako stringy a DB per řádek přetypovává na Integer/Date/Decimal. To je neviditelné, ale nákladné. Pro stabilní výkon:

• Nastavit datové typy parametrů správně (datum jako datum, číslo jako číslo).
• Při desetinných číslech dávat pozor na lokalizační pasti (čárka vs. tečka). FireDAC je zde většinou správné, ale smíšené zdroje nikoli.
• Ujasnit strategii časových pásem/UTC předem (timestampy jsou při importe klasika).

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

Je svůdné parsovat chybový text („duplicate key value violates unique constraint …“). Dělej to jen jako krajní možnost. Lepší jsou strukturované kódy (SQLSTATE, Error Number). Bohužel ne všechny ovladače poskytují vše stejně kvalitně. Plánuj tedy obojí: kód a text, plus volitelně „název constraintu z textu“, ale bez tvrdé závislosti.

Debugging-Hinweise: So findest du schnell die kaputte Zeile

Batch reproduzierbar machen

Když import občas selhává, potřebuješ reprodukovatelnost. Ulož pro každý batch malý diagnostický soubor nebo záznam v logu, který obsahuje:

• Číslo batchu a čas
• ArraySize a režim transakce
• seznam obchodních klíčů (např. externí ID) v batchi

To často stačí k tomu, abys následně cíleně spustil mini-import jen pro tato ID.

Finale SQL sichtbar machen (aber ohne Datenleaks)

Při ladění chceš vědět: Je SQL korektní? Jsou parametry správné? FireDAC nabízí monitoring/tracing přes FDMoni-komponenty a logování ovladače. V produkčně blízkém prostředí je důležité:

• Tracing aktivovat cíleně pouze dočasně (výkon a ochrana dat).
• Hodnoty parametrů logovat jen v bezpečném prostředí nebo maskované.
• U osobních údajů: v logu pouze technické klíče (ID) a žádné záznamy v prostém textu.

Wenn du split-testest: Abbruchkriterien definieren

Při binárním dělení nechceš dělit donekonečna. Nastav dolní hranici, např. „pod 20 řádků přepnout na režim po jednotlivých záznamech“. A definuj limit, kolik chyb celkem toleruješ, než import přerušíš (např. při systematických problémech mapování). Jinak skončíš s nekonečnými seznamy chyb a zablokuješ následné zpracování.

Wann sich der Aufwand wirklich lohnt (und wann nicht)

Array DML s chybovým zpracováním po řádcích se vyplatí zejména, pokud:

• Mnoho řádků se zpracovává (tisíce až miliony).
• Několik chybových řádků existuje, ale přesto chceš zpracování dokončit.
• Import musí v provozu běžet stabilně (např. noční zpracování, služba bez UI).
• Potřebuješ vrátit seznam chyb zdroji/funkčnímu oddělení (s odkazem na řádky).

Méně se to vyplatí, pokud:

• když píšeš jen několik desítek řádků (jednotlivé INSERTy jsou v pořádku),
• když je kvalita dat natolik špatná, že 30–50 % řádků selže (v tom případě je smysluplnější stagingová strategie),
• když stejně používáš nativní DB postup pro hromadné nahrávání (např. COPY v PostgreSQL, BCP/BULK INSERT v SQL Serveru) – pak není Array DML nástrojem.

Alternativní architektura: stagingová tabulka místo „přímo do cíle“

Pokud se pravidelně potýkáš s proměnlivou kvalitou dat, je čistý „INSERT přímo do cílové tabulky“ často špatné rozhodnutí. Eine stagingová tabulka (předstupeň) je tabulka, do které nejprve uložíš data technicky korektně (případně s méně přísnými typy), a teprve potom je validuješ a převedeš do cílové tabulky.

Výhody v provozu:

• Chybné záznamy zůstávají uložené, sledovatelné (včetně surových dat).
• Validaci lze provádět samostatně a opakovaně.
• Oddělíš příjem rozhraní od doménového zpracování.

Array DML je pak často rychlá cesta do stagingové tabulky, zatímco převod do cílové tabulky probíhá jako set‑based SQL (nebo uložená procedura). To přesune zpracování chyb více na stranU DB, což v závislosti na organizaci (DBA role, deployment) může být smysluplné nebo nežádoucí.

Provoz a administrace: co by vedoucí IT a administrátoři měli vědět

Monitoring: míra chybovosti a propustnost jsou klíčové metriky

Pro stabilní provoz hromadného importu jsou dvě metriky vypovídající víc než samotná „doba běhu“:

• Propustnost: řádky za minutu (nebo za dávku) včetně špiček/mediánu.
• Míra chybovosti: chybné řádky na běh, ideálně seskupené podle tříd chyb (Unique, FK, NOT NULL, konflikt typů).

Pokud tyto dvě hodnoty sleduješ pravidelně, zjistíš brzy, zda se na zdroji něco změnilo (např. nový formát) nebo zda se cílový systém (např. nová omezení) zpřísnil.

Zamykání a okna zátěže

Hromadné INSERTy mohou způsobit zamykání a IO zátěž. Pokud současně uživatelé pracují se stejnými tabulkami, musíš uvažovat o izolačních úrovních, indexech a případně partikcionování. Prakticky to znamená: buď umístit importy do zátěžových oken, nebo navrhnout datový tok tak, aby koexistoval s běžným provozem (např. přes staging + asynchronní převzetí).

Konkrétní kontrolní seznam pro robustní hromadný INSERT s Array DML

• Velikost dávky nastavit (počáteční hodnota 500–1 000) a měřitelně vyladit.
• Explicitní transakce: COMMIT po dávce jako výchozí; „COMMIT na konci“ jen vědomě.
• Stabilní typy parametrů nastavit, nevyvolávat implicitní konverze.
• Chybový kontext pro každý záznam předávat (externí ID, řádek ve zdroji).
• Strategie chyb: nejprve úroveň dávky, pak rozdělení/fallback, logování po řádku.
• Logování: kódy + text, ale v souladu s ochranou osobních údajů; zachytit Batch‑ID a ID běhu.
• Opětovné spuštění: zajistit idempotenci (klíč/Upsert/Import‑ID).

Závěr: Array DML je rychlý – robustní se to stane díky procesu a strategii zpracování chyb

FireDAC Bulk-Insert s Array DML je silný nástroj, pokud se nepředstírá, že chyby neexistují. V reálných datových tocích se vždy objeví odlehlé případy: duplikáty, chybějící reference, poškozené datumové hodnoty. Správný přístup proto zní: Array DML pro výkon, v kombinaci s řízenou izolační strategií (Split nebo Fallback) a seznamem chyb dohledatelným pro každý řádek. Tím získáte výkon i provozní spolehlivost – a právě na tom záleží, když importy neběží jen v laboratoři, ale musí každou noc spolehlivě proběhnout.

Pokud chcete stabilizovat stávající importní nebo integrační proces v Delphi/FireDAC (výkon, transakce, obnovení běhu, logování), probereme to rádi strukturovaně v technické konzultaci:

Pro toto téma jsou také důležité Delphi Bulk Insert a Bulk Insert Delphi FireDAC. Článek tyto aspekty srozumitelně zařazuje a ukazuje, na co záleží v praxi.

Prodiskutovat projekt nebo modernizační záměr s Net-Base.