FireDAC: Bulk-insert met Array DML en nette foutafhandeling per rij

Een BDE-vervanging met native aansluiting Bulk-Insert met Array DML is vaak de snelste manier om veel records in een database te krijgen: in plaats van duizend afzonderlijke inserts wordt een parameter-array gebonden en in één keer naar de server gestuurd. In de praktijk ontstaat het knelpunt echter snel: een record overtreedt een unique-index, een NOT NULL-veld is leeg, een foreign key klopt niet – en plots is onduidelijk, welke regel de batch heeft laten falen, of een deel al is weggeschreven en hoe je netjes verdergaat zonder dataconsistentie te veroorzaken.

Daarom gaat het hier precies om: hoe je Array DML zo inzet dat je per regel betrouwbare foutinformatie krijgt, de transactie onder controle houdt en in de productie kunt achterhalen wat er gebeurd is. De focus ligt niet op academische API-leesstof, maar op het randgeval dat bij echte imports regelmatig voorkomt: een grote batch, enkele kapotte regels, maar je wilt toch tempo houden.

FireDAC Bulk-Insert met Array DML: waarom Array DML bij Bulk-Insert de moeite waard is

Array DML (Data Manipulation Language) betekent in FireDAC: je bindt parameters niet als enkele waarde, maar als array. FireDAC stuurt dan (afhankelijk van driver/DB) minder roundtrips, kan serverzijdig efficiënter werken en vermindert de overhead in de client drastisch. Dat is in drie situaties bijzonder relevant:

• ETL- en importpaden: CSV/XML/JSON erin, normalisatie/mapping, daarna in staging- of doeltabel.
• Interfacebuffers: REST- of MQ-payloads worden verzameld en periodiek opgeslagen.
• Log-/eventtabellen: veel kleine inserts waarbij latentie domineert.

Het voordeel komt echter niet gratis. Met Array DML verschuif je complexiteit van „veel afzonderlijke statements“ naar „één statement met veel regels“. Dat is goed voor performance, maar veeleisender voor foutdiagnose, transactielogica en herstart.

Het typische randgeval: een batch, één kapotte regel

De klassieker in de praktijk: je importeert 50.000 regels. Je kiest een ArraySize van 1.000, omdat je niet voor elke regel een roundtrip wilt. Batch 17 faalt. De DB meldt alleen „duplicate key“ of „violates foreign key constraint“. In de UI of in het servicelog staat dan vaak alleen: „ExecSQL failed“.

Zonder goede foutafhandeling gebeuren dan meestal twee vervelende dingen:

• Je gooit de hele batch weg, hoewel 999 van de 1.000 regels ok zouden zijn.
• Je valt terug op individuele inserts en verliest het performancevoordeel permanent.

Ziel ist ein dritter Weg: Batch-Performance behalten, aber defekt-genau (rijindex, sleutelwaarden, DB-foutmelding) protokollieren und optional „goede rijen“ committen – afhankelijk davon, wie kritisch konsistentie und idempotentie (meerdere keren uitvoeren zonder dubbele werking) in deinem Prozess sind.

FireDAC Array DML: Die relevanten Stellschrauben (ohne Mythen)

Für Bulk-Insert mit Array DML sind in der Praxis immer dieselben Stellschrauben entscheidend:

1) ArraySize und Batch-Größe

ArraySize (bei TFDQuery/TFDCommand) bestimmt, wie viele „Zeilen“ FireDAC in einem Aufruf abarbeitet. Größer ist nicht automatisch besser. Zu groß bedeutet: mehr Speicher im Client, mehr Payload auf der Leitung, größere Locks/Log-Last auf dem Server und im Fehlerfall mehr „Blast Radius“. Für robuste Imports ist häufig eine Batch-Größe zwischen 200 und 2.000 ein guter Startpunkt, abhängig von Spaltenanzahl, BLOBs und Latenz.

2) Transaktionsgrenze

Du brauchst eine klare Entscheidung: Commit pro Batch oder Commit für den gesamten Import. Das ist keine Geschmackssache, sondern eine Betriebsentscheidung:

• Commit pro Batch: begrenzt Sperren und Transaktionslog, einfacher Wiederanlauf, aber Zwischenstände sind sichtbar (je nach Isolatieniveau). Fehler in Batch 17 lassen Batch 1–16 im System.
• Commit am Ende: „Alles oder nichts“, konsistenter in einem fachlichen Sinne, aber bei großen Mengen riskierst du lange Locks, großes Rollback und im Fehlerfall ist alles weg.

Für viele Schnittstellen- und Importprozesse ist „Commit pro Batch“ die realistischere Betriebsstrategie – aber nur, wenn du idempotentie und duplicaatstrategie sauber geregelt hast (z. B. über natürliche Schlüssel, Upserts oder eine Import-ID).

3) UpdateOptions und Prepared Statements

Bei wiederholten Batches lohnt es sich, das Statement vorbereitet zu lassen. „Prepare“ bedeutet: FireDAC lässt die DB das Statement parsen/kompilieren und wiederverwendet es. Je nach DB kann das einen spürbaren Effekt haben, vor allem bei hoher Frequenz. Wichtig ist hier weniger „Trick 17“, sondern: Konsequente Wiederverwendung desselben Query-Objekts (oder desselben TFDCommand) und stabile Parametertypen.

Sauberes Error-Handling pro Zeile: Was du wirklich brauchst

Wenn du „pro Zeile“ Fehler behandeln willst, brauchst du drei Dinge:

1. Zuordnung: Welcher Array-Index (0..N-1) hat versagt?
2. Kontext: Welche fachlichen Schlüsselwerte hat diese Zeile (z. B. externe ID, Kundennummer, Zeitstempel)?
3. Steuerung: Was tust du danach? Abbrechen, nur schlechte Zeilen skippen, oder Batch splitten?

FireDAC kann je nach Treiber Fehler je Array-Element zurückliefern. Praktisch ist das aber nicht „einfach immer da“. Du musst damit rechnen, dass manche Datenbanken/Provider nur den ersten Fehler melden oder dass im Batch ein Fehler den REST gar nicht mehr ausführt. Genau deswegen ist ein robustes Muster meist zweistufig:

• Stufe A: Versuche den Batch als Array DML.
• Stufe B: Wenn der Batch scheitert, splitte ihn (halbieren) oder falle kontrolliert auf Einzelzeilen zurück – aber nur für diesen Batch – und logge sauber.

Das klingt nach Mehrarbeit, ist aber in Importstrecken der Unterschied zwischen „nachts um 02:00 Uhr bleibt alles stehen“ und „Import läuft durch, 7 Zeilen landen in der Fehlerliste“.

Ein praxistaugliches Muster: Batch zuerst, dann gezielt isolieren

Het volgende patroon heeft zich bewezen voor procesnabije softwareoplossingen waarin de datakwaliteit gemengd is:

Stap 1: Gegevens in een batchstructuur plaatsen (incl. foutcontext)

Sla de te importeren gegevens niet alleen op als ruwe waarden, maar met minimaal context: externe ID, regelnnummer uit de bron, eventueel hash/checksum. Dat is geen „Nice to have“: in geval van fouten wil je niet eerst de CSV opnieuw moeten parsen om te achterhalen wat er kapot is.

Stap 2: Array DML uitvoeren

Je stelt ArraySize in op de batchlengte, bindt parameters als arrays en voert ExecSQL uit. Belangrijk: houd parametertypen stabiel (bijv. voor numerieke velden niet de ene keer als string en de andere keer als integer binden), anders veroorzaakt de DB impliciete casts of FireDAC moet per element omzetten.

Stap 3: Foutgeval – batch afbakenen in plaats van blind herhalen

Als ExecSQL faalt, heb je twee robuuste opties:

• Binary Split (halveren): verdeel het batch in twee helften, probeer elke helft opnieuw als Array DML. Herhaal dit totdat je bij een kleine hoeveelheid komt die je individueel kunt controleren. Voordeel: je behoudt veel performance als slechts enkele rijen fout zijn. Nadeel: meer logica, en bij systematische fouten (bijv. verkeerd datatype) levert het weinig op.
• Fallback op individuele rijen voor dit batch: je zet ArraySize=1 (of bindt individuele waarden) en voert regel voor regel uit, logt fouten en gaat door. Voordeel: eenvoudig, gegarandeerd per regel. Nadeel: in dit batch verlies je snelheid.

In de praktijk combineer ik beide: eerst 1–2 keer splitsen (om „goede blokken“ snel door te krijgen), daarna bij kleine RESThoeveelheden overschakelen op individuele rijen om eenduidige foutinformatie te loggen.

Foutobjecten en meldingen: wat je uit FireDAC zou moeten halen

FireDAC kapselt DB-fouten in exceptions (typisch EFDDBEngineException) met detailinformatie. Voor het operationeel beheer zijn drie niveaus belangrijk:

• DB-foutcode (db-specifiek): bijv. SQLSTATE bij PostgreSQL, Error Number bij SQL Server.
• Constraint-/objectnaam: vaak opgenomen in de fouttekst (unique index, FK-constraint).
• Statementcontext: tabel, bewerking, eventueel parameterwaarden (voorzichtig met persoonsgegevens).

Als je per regel wilt loggen, moet je in geval van fout bovendien de regel identificeren. FireDAC kan in sommige gevallen de array-index leveren. Vertrouw er echter niet uitsluitend op. Bouw altijd aanvullend een eigen index (positie in het batch) en log bij die positie ten minste één zakelijke sleutel.

Valkuilen die bij echte imports tijd kosten

1) ‚Het was toch maar één regel‘ – maar de transactie is al ‚dirty‘

Afhankelijk van DB en driver kan een fout ertoe leiden dat de hele uitvoering van het statement als mislukt wordt beschouwd en de transactie in een toestand verkeert waarin je ofwel expliciet moet rollen, of waarin verdere statements falen. Juist bij sommige drivers is ’na een fout gewoon doorgaan‘ geen veilige veronderstelling.

Gevolg: Als je binnen een transactie werkt en een batch faalt, is het standaardpad: rollback van de huidige batchcontext (of van de hele transactie) en daarna opnieuw beginnen. Dat sluit goed aan bij ‚commit per batch‘.

2) Autocommit vs. expliciete transactie

Als je geen expliciete transactie start, bepaalt vaak de driver/provider hoe statements gecommit worden. Voor bulk-imports is dat zelden wat je wilt. Expliciete transacties geven je controle over:

• Lockduur
• Rollback-gedrag
• Herstartpunten

En: Expliciet betekent niet „een enorme transactie“. Het betekent „bewust“.

3) Triggers, Constraints en bijwerkingen

Array DML versnelt de overdracht, maar niet automatisch het serverzijde werk. Als je op de doeltabel triggers hebt (bijv. Audit-Logging, automatische statusberekening), dan is het knelpunt mogelijk helemaal niet de insert, maar de triggercode. Een batch kan dan wel minder roundtrips hebben, maar de CPU op de DB-server blijft de beperkende factor.

Voor admins en technische leads: bij prestatieproblemen is een blik op Wait Events/Locks en het transactielog de moeite waard. De Bulk-Insert is dan slechts de aanleiding, niet de oorzaak.

4) Datatypes en impliciete conversies

Een van de meest voorkomende „Waarom is dat traag?“-redenen: parameters worden als string gebonden, de DB cast per regel naar Integer/Date/Decimal. Dat is onzichtbaar, maar duur. Voor stabiele performance:

• Parameter-datatypes correct instellen (datum als datum, getal als getal).
• Bij Decimals op locale-valkuilen letten (komma vs. punt). FireDAC is hier meestal correct, maar gemengde bronnen niet.
• Tijdzone/UTC-strategie vooraf afstemmen (Timestamps zijn bij imports een klassieker).

5) Foutmeldingen zijn voor mensen, maar niet voor automatisering

Het is verleidelijk om de fouttekst te parsen („duplicate key value violates unique constraint …“). Doe dat alleen als laatste optie. Beter zijn gestructureerde codes (SQLSTATE, Error Number). Helaas leveren niet alle Treiber alles even goed. Plan daarom beide in: code en tekst, plus optioneel „Constraint-Name uit tekst“, maar zonder harde afhankelijkheid.

Debugging-tips: Zo vind je snel de kapotte regel

Batch reproduceerbaar maken

Als een import sporadisch faalt, heb je reproduceerbaarheid nodig. Sla per batch een klein diagnosebestand of een logregel op die het bevat:

• Batchnummer en tijd
• ArraySize en transactiemodus
• de lijst van domeinsleutels (bijv. externe IDs) in de batch

Dat is vaak genoeg om achteraf gericht een mini-import alleen voor deze IDs te starten.

De uiteindelijke SQL zichtbaar maken (maar zonder datalekken)

Tijdens debugging wil je weten: is de SQL correct? Zijn de parameters goed? FireDAC biedt Monitoring/Tracing over FDMoni-componenten en Treiber-Logging. In productienabije omgevingen is het belangrijk:

• Tracing doelgericht en tijdelijk inschakelen (performance en gegevensbescherming).
• Parameterwaarden alleen in een veilige omgeving of gemaskeerd loggen.
• Bij persoonsgegevens: in de log alleen technische sleutels (IDs) en geen leesbare inhoud.

Als je split-tests uitvoert: stopcriteria definiëren

Bij een binary split wil je niet eindeloos blijven splitsen. Stel een ondergrens in, bijv. „onder 20 regels schakelen naar enkelvoudige modus“. En stel een limiet in hoeveel fouten je in totaal tolereert voordat je de import afbreekt (bijv. bij systematische mapping-problemen). Anders krijg je eindeloze foutlijsten en blokkeer je vervolgverwerking.

Wanneer de inspanning echt de moeite waard is (en wanneer niet)

Array DML met per-regel foutafhandeling is vooral de moeite waard wanneer:

• Veel regels verwerkt worden (duizenden tot miljoenen).
• Weinig regels fout zijn, maar je toch door wilt laten lopen.
• Import in productie stabiel moet draaien (bijv. nachtverwerking, service zonder UI).
• Je een foutlijst terug moet spelen naar de business/bron (met regelreferentie).

Het loont minder als:

• je maar een paar tientallen regels schrijft (enkele inserts zijn ok),
• de datakwaliteit zo slecht is dat 30–50% van de regels faalt (dan is een staging-strategie zinvoller),
• je toch al een DB-native bulk-load-methode gebruikt (bijv. COPY in PostgreSQL, BCP/BULK INSERT in SQL Server) – dan is Array DML niet het juiste gereedschap.

Alternatieve architectuur: staging-tabel in plaats van „direct naar doel“

Als je regelmatig met gemengde datakwaliteit worstelt, is een puur „direct in de doeltabel inserten“ vaak de verkeerde keuze. Een staging-tabel (voorfase) is een tabel waarin je gegevens eerst technisch correct opslaat (indien nodig met zachte/ruimere typen), en ze pas daarna valideert en naar de doeltabel overzet.

Voordelen in de operatie:

• Foutieve records blijven herleidbaar opgeslagen (incl. ruwe data).
• Je kunt validatie gescheiden en herhaalbaar uitvoeren.
• Je koppelt de interface-acceptatie los van de vakinhoudelijke verwerking.

Array DML is dan vaak de snelle weg naar de staging-tabel, terwijl de overzetting naar de doeltabel als set-gebaseerde SQL (of Stored Procedure) gebeurt. Dat verschuift foutafhandeling meer naar de DB-zijde, wat afhankelijk van de organisatie (DBA-rollen, deployment) wenselijk of onwenselijk kan zijn.

Operatie en administratie: wat IT-Leads en admins daarover moeten weten

Monitoring: foutpercentage en doorvoer zijn de kernmetriek

Voor de stabiele operatie van een bulk-import zijn twee metrieken zinvoller dan alleen de uitvoeringstijd:

• Doorvoer: regels per minuut (of per batch) incl. piek/mediaan.
• Foutpercentage: foutieve regels per run, idealiter gegroepeerd naar foutklassen (Unique, FK, NOT NULL, typeconflict).

Als je deze twee waarden regelmatig ziet, herken je vroeg of er iets aan de bron veranderd is (bijv. nieuw formaat) of dat het doelsysteem (bijv. nieuwe constraints) strenger is geworden.

Vergrendelingen en lastvensters

Bulk-inserts kunnen locking en IO-belasting veroorzaken. Als gebruikers parallel op dezelfde tabellen werken, moet je nadenken over isolatieniveau, indexen en eventueel partitionering. Praktisch betekent dit: ofwel imports in lastvensters plannen, of de datastroom zo opzetten dat hij met lopende operatie kan coexisteren (bijv. via staging + asynchrone overname).

Concreet checklist voor een robuuste bulk-insert met Array DML

• Batchgrootte vastleggen (startwaarde 500–1.000) en meetbaar tunen.
• Expliete transactie: commit per batch als default, „commit aan het einde“ alleen bewust toepassen.
• Parametertypen stabiel instellen, geen impliciete casts afdwingen.
• Foutcontext per record meenemen (externe ID, bronregel).
• Foutstrategie: eerst batch, daarna split/fallback, per regel loggen.
• Logging: codes + tekst, maar privacyconform; Batch-ID en run-ID vastleggen.
• Herstart: idempotentie garanderen (sleutel/Upsert/Import-ID).

Conclusie: Array DML is snel – robuust wordt het door proces en foutstrategie

Een FireDAC Bulk-Insert met Array DML is een krachtig hulpmiddel, zolang je niet doet alsof er geen fouten zijn. In echte datastromen zijn er altijd uitschieters: duplicaten, ontbrekende referenties, kapotte datumwaarden. De juiste aanpak is daarom: Array DML voor de performance, gecombineerd met een gecontroleerde isolatiestrategie (Split of Fallback) en een per regel traceerbare foutenlijst. Daarmee combineer je snelheid en bedrijfsszekerheid – en dat is precies wat telt wanneer imports niet alleen in het lab draaien, maar elke nacht betrouwbaar moeten lopen.

Als jullie een bestaand import- of interfaceproces in Delphi/FireDAC willen stabiliseren (Performance, transacties, herstart, Logging), bespreken we dat graag structureel in een technisch gesprek:

Voor dit onderwerp zijn ook Delphi Bulk Insert en Bulk Insert Delphi FireDAC belangrijk. Het artikel behandelt deze aspecten op een begrijpelijke manier en toont waar het in de dagelijkse praktijk om gaat.

Project of moderniseringsopdracht met Net-Base bespreken.