FireDAC: Bulk-Insert med Array DML og robust fejlhåndtering pr. række

Et BDE-afløsning med native tilslutning Bulk-Insert med Array DML er ofte den hurtigste måde at få mange poster ind i en database på: i stedet for tusind enkeltindsættelser bindes et parameter-array og sendes på én gang til serveren. I praksis opstår dog hurtigt knasten: En post bryder en unik indeks, et NOT NULL-felt er tomt, en fremmednøgle passer ikke – og pludselig er det uklart, hvilken række der slog batchen ihjel, om en del allerede er skrevet, og hvordan du fortsætter ordentligt uden at skabe datainkonsistenser.

Netop det handler dette om: Hvordan du bruger Array DML, så du får pr. række pålidelige fejloplysninger, holder transaktionen under kontrol og i drift kan spore, hvad der er sket. Fokus ligger ikke på akademisk API-læsning, men på kanttilfældet, der i reelle importer regelmæssigt opstår: En stor batch, få ødelagte rækker, men du vil alligevel have fart.

FireDAC Bulk-Insert med Array DML: Hvorfor Array DML overhovedet betaler sig ved Bulk-Insert

Array DML (Data Manipulation Language) betyder i FireDAC: Du binder parametre ikke som enkeltværdier, men som arrays. FireDAC sender derefter (afhængigt af driver/DB) færre roundtrips, kan arbejde mere effektivt på serversiden og reducerer overhead i klienten dramatisk. Det er særligt relevant i tre situationer:

• ETL- og importforløb: CSV/XML/JSON ind, normalisering/mapping, derefter i staging- eller måltabel.
• Schnittstellen-Puffer: REST- eller MQ-payloads samles og persistres periodisk.
• Protokol-/Event-Tabellen: mange små inserts, hvor latenstid dominerer.

Gevinsten kommer dog ikke gratis. Med Array DML flytter du kompleksitet fra „mange enkelte statements“ til „et statement med mange rækker“. Det er godt for ydeevnen, men mere krævende for fejldiagnostik, transaktionslogik og genkørsel.

Det typiske kanttilfælde: En batch, en ødelagt række

Klassikeren i drift: Du importerer 50.000 rækker. Du vælger en ArraySize på 1.000, fordi du ikke vil have en roundtrip per række. Batch 17 fejler. DB’en melder kun „duplicate key“ eller „violates foreign key constraint“. I UI’en eller i serviceloggen står der ofte kun: „ExecSQL failed“.

Uden ordentlig fejlhåndtering sker der typisk to dårlige ting:

• Du kasserer hele batchen, selvom 999 af 1.000 rækker var ok.
• Du falder tilbage til enkelt-inserts og mister performancefordelen permanent.

Målet er en tredje vej: bevare batch-ydeevne, men fejl-præcist (rækkeindeks, nøgleværdier, DB-fejltekst) logge og valgfrit committe „gode rækker“ – afhængigt af, hvor kritisk konsistens og idempotens (gentagen kørsel uden dobbelt effekt) er i din proces.

FireDAC Array DML: De relevante parametre (uden myter)

Til Bulk-Insert med Array DML er det i praksis altid de samme parametre, der er afgørende:

1) ArraySize und Batch-Größe

ArraySize (ved TFDQuery/TFDCommand) bestemmer, hvor mange „rækker“ FireDAC der behandles i et kald. Større er ikke automatisk bedre. For stort betyder: mere hukommelse på klienten, mere payload på linjen, større locks/log-belastning på serveren og i fejltilfælde større „blast radius“. For robuste importprocesser er en Batch-Größe mellem 200 und 2.000 ofte et godt udgangspunkt, afhængigt af antal kolonner, BLOBs og latenstid.

2) Transaktionsgrenze

Du har brug for en klar beslutning: Commit pr. batch eller Commit for hele importen. Det er ikke et spørgsmål om smag, men en driftsbeslutning:

• Commit pr. batch: begrænser låsninger og transaktionslog, gør genstart lettere, men mellemliggende tilstande er synlige (afhængig af Isolation Level). Fejl i Batch 17 lader Batch 1–16 forblive i systemet.
• Commit ved slut: „alt eller intet“, mere konsistent i en faglig forstand, men ved store mængder risikerer du lange låsninger, stort rollback og i fejltilfælde er alt væk.

For mange integrations- og importprocesser er „Commit pr. batch“ den mere realistiske driftsstrategi – men kun, hvis du har idempotens og en duplikatstrategi klart på plads (f.eks. via naturlige nøgler, Upserts eller en import-ID).

3) UpdateOptions und Prepared Statements

Ved gentagne batches lønner det sig at lade statement være forberedt. „Prepare“ betyder: FireDAC lader DB’en parse/kompilere statementet og genbruge det. Afhængigt af DB kan det have en mærkbar effekt, især ved høj frekvens. Vigtigt her er mindre „Trick 17“, og mere: konsekvent genbrug af det samme query-objekt (eller det samme TFDCommand) og stabile parametertyper.

Sauberes Error-Handling pro Zeile: Was du wirklich brauchst

Hvis du vil håndtere fejl „pr. række“, har du brug for tre ting:

1. Identifikation: Hvilket array-index (0..N-1) fejlede?
2. Kontekst: Hvilke faglige nøgleværdier har denne række (f.eks. ekstern ID, kundenummer, tidsstempel)?
3. Styring: Hvad gør du derefter? Afbryde, kun springe over dårlige rækker, eller splitte batchen?

FireDAC kan afhængigt af driver returnere fejl pr. array-element. I praksis er det dog ikke „bare altid til stede“. Du må regne med, at nogle databaser/udbydere kun rapporterer den første fejl, eller at en fejl i batchen forhindrer RESTen i at blive udført. Netop derfor er et robust mønster som regel to-trins:

• Trin A: Forsøg batchen som Array DML.
• Trin B: Hvis batchen fejler, split den (halver) eller fald kontrolleret tilbage til enkeltvise rækker – men kun for denne batch – og log grundigt.

Det lyder som ekstra arbejde, men i importprocesser er det forskellen mellem „kl. 02:00 om natten står alt stille“ og „Importen gennemføres, 7 rækker ender i fejllisten“.

Et praksisorienteret mønster: Batch først, derefter målrettet isolering

Følgende mønster har vist sig nyttigt til processnære softwareløsninger, hvor datakvaliteten er varieret:

Trin 1: Pak data i en batch-struktur (inkl. fejlkontekst)

Gem de data, der skal importeres, ikke kun som rå værdier, men med minimalt kontekst: ekstern ID, rækkenummer fra kilden, eventuelt hash/checksum. Det er ikke „Nice to have“: i fejltilfælde vil du ikke først parse CSV’en igen for at finde ud af, hvad der er gået i stykker.

Trin 2: Array DML udføre

Sæt ArraySize til batch-længden, bind parametre som arrays, og kør ExecSQL. Vigtigt: hold parametertyperne stabile (f.eks. bind ikke for numeriske felter nogle gange som String og andre gange som Integer), ellers producerer DB’en implicitte casts eller FireDAC skal konvertere hvert element.

Trin 3: Fejlsituation – indsnævr batch i stedet for blind gentagelse

Hvis ExecSQL fejler, har du to robuste muligheder:

• Binary Split (halvere): Del batch’en i to halvdele, prøv hver halvdel igen som Array DML. Gentag, indtil du har en lille mængde, som du enkeltvis kan kontrollere. Fordel: du bevarer meget af ydeevnen, når kun få rækker er defekte. Ulempe: mere logik, og ved systematiske fejl (f.eks. forkert datatype) hjælper det ikke meget.
• Fallback på enkeltlinjer for denne batch: Sæt ArraySize=1 (eller bind enkeltværdier) og udfør linje for linje, log fejl og fortsæt. Fordel: enkelt, garanteret per linje. Ulempe: i denne batch mister du tempo.

I praksis kombinerer jeg begge: først splitte 1–2 gange (for hurtigt at komme igennem „gode blokke“), derefter ved små RESTer skifte til enkeltlinjer for at logge entydige fejloplysninger.

Fejlobjekter og meddelelser: Hvad du bør udtrække fra FireDAC

FireDAC indkapsler DB-fejl i exceptions (typisk EFDDBEngineException) med detaljerede oplysninger. For driften er tre niveauer vigtige:

• DB-fejlkode (DB-specifik): f.eks. SQLSTATE i PostgreSQL, Error Number i SQL Server.
• Constraint-/objektnavn: ofte indeholdt i fejlteksten (Unique-index, FK-constraint).
• Statement-kontekst: tabel, operation, evt. parameterværdier (vær varsom med personoplysninger).

Hvis du vil logge per linje, skal du i fejlsituationen desuden identificere linjen. FireDAC kan under visse omstændigheder levere array-indekset. Stol dog ikke udelukkende på det. Byg altid et ekstra internt indeks (position i batch’en) og log ved denne position mindst én faglig nøgle.

Faldgruber, der koster tid i reelle importer

1) „Det var jo kun én linje“ – men transaktionen er allerede „dirty“

Afhængigt af DB og driver kan en fejl få hele statement-udførelsen til at blive betragtet som mislykket, og transaktionen kan være i en tilstand, hvor du enten eksplicit skal rulle tilbage eller hvor efterfølgende statements fejler. Især med visse drivere er „efter fejl bare fortsæt“ ikke en sikker antagelse.

Konsekvens: Hvis du arbejder i en transaktion og et batch fejler, er standardvejen: rollback af den aktuelle batch-kontekst (eller af hele transaktionen) og derefter starte igen. Det passer godt til „commit per batch“.

2) Autocommit vs. eksplicit transaktion

Hvis du ikke starter en eksplicit transaktion, afgør driveren/provider ofte, hvordan den committer statements. Til bulk-importer er det sjældent det, du ønsker. Eksplicitte transaktioner giver dig kontrol over:

• låsetid
• Rollback-adfærd
• Genstartspunkter

Og: „Eksplizit“ betyder ikke „en kæmpestor transaktion“. Det betyder „bevidst“.

3) Trigger, Constraints og sideeffekter

Array DML fremskynder overførslen, men ikke automatisk det serverside arbejde. Hvis du har triggere på måltabellen (fx audit-logging, automatisk statusberegning), så er flaskehalsen muligvis ikke INSERT-operationen, men triggerkoden. Et batch kan dermed reducere antal roundtrips, men CPU’en på DB-serveren forbliver den begrænsende faktor.

For admins og tekniske leads: Ved performanceproblemer er det værd at kigge på Wait Events/Locks og transaktionsloggen. Bulk-Insert er ofte kun udløseren, ikke årsagen.

4) Datatyper og implicitte konverteringer

En af de hyppigste „Hvorfor er det langsomt?“-årsager: Parametre bindes som strenge, og databasen caster per række til Integer/Date/Decimal. Det er usynligt, men dyrt. For stabil performance:

• Sæt parameter-datatypen korrekt (dato som dato, tal som tal).
• Vær opmærksom på locale-fælder for decimals (komma vs. punkt). FireDAC er her som regel korrekt, men blandede kilder er det ikke.
• Aftal tidszone/UTC-strategi på forhånd (timestamps er en klassiker ved imports).

5) Fejltekster er for mennesker, men ikke til automatisering

Det er fristende at parse fejlteksten („duplicate key value violates unique constraint …“). Gør det kun som sidste udvej. Bedre er strukturerede koder (SQLSTATE, Error Number). Desværre leverer ikke alle drivere alt lige godt. Planlæg derfor begge dele: kode og tekst, plus eventuelt „constraint-navn fra teksten“, men uden hård afhængighed.

Debugging-anvisninger: Sådan finder du hurtigt den fejlbehæftede række

Gør batchen reproducerbar

Hvis et importjob fejler sporadisk, har du brug for reproducerbarhed. Gem per batch en lille diagnosefil eller en logindgang, der indeholder:

• Batchnummer og tidspunkt
• ArraySize og transaktionsmodus
• listen over faglige nøgler (fx eksterne IDs) i batchen

Det er ofte nok til bagefter målrettet at starte en mini-import kun for disse IDs.

Gør den endelige SQL synlig (men uden datalæk)

Under debugging vil du vide: Er SQL korrekt? Er parametrene rigtige? FireDAC tilbyder monitoring/tracing via FDMoni-komponenter og driver-logging. I produktionsnære miljøer er det vigtigt:

• Aktivér tracing målrettet og kun midlertidigt (performance og databeskyttelse).
• Log parameterværdier kun i et sikkert miljø eller maskér dem.
• Ved personoplysninger: kun tekniske nøgler (IDs) i loggen og ingen klartekstdata.

Hvis du split-tester: Definér afbrudskriterier

Ved binary split vil du ikke dele i det uendelige. Sæt en nedre grænse, fx „under 20 rækker skift til enkeltmodus“. Og sæt et loft for, hvor mange fejl du samlet tolererer, før du afbryder importen (fx ved systematiske mapping-problemer). Ellers ender du i endeløse fejlister og blokerer efterfølgende behandling.

Hvornår indsatsen virkelig er umagen værd (og hvornår ikke)

Array DML med fejlhåndtering per række er særlig indsatsberettiget, når:

• Mange rækker behandles (tusinder til millioner).
• Få rækker er fejlbehæftede, men du alligevel vil gennemføre.
• Importen skal køre stabilt i drift (fx natkørsel, service uden UI).
• Du skal kunne returnere en fejl-liste til fagafdeling/kilde (med rækkehenvisning).

Det er mindre værd at gøre, hvis:

• du kun skriver et par dusin linjer (enkeltindsættelser er ok),
• datakvaliteten er så dårlig, at 30–50% af rækkerne fejler (så er en staging-strategi mere fornuftig),
• du under alle omstændigheder bruger en DB-native bulk-load-metode (f.eks. COPY i PostgreSQL, BCP/BULK INSERT i SQL Server) – så er Array DML ikke værktøjet.

Alternativ arkitektur: Staging-tabel i stedet for „Direkte til målet“

Hvis du regelmæssigt kæmper med blandet datakvalitet, er et rent „insert direkte i måltabellen“ ofte det forkerte valg. En staging-tabel (forstadie) er en tabel, hvor du først gemmer data teknisk korrekt (evt. med bløde typer), og først derefter validerer og overfører til måltabellen.

Fordele i drift:

• Fejlbehæftede dataposter forbliver gemt og kan spores (inkl. rådata).
• Du kan køre validering separat og gentageligt.
• Du adskiller antagelser om grænseflader fra den faglige behandling.

Array DML er ofte den hurtige vej ind i staging-tabellen, mens overførelsen til måltabellen sker som set-baseret SQL (eller Stored Procedure). Det flytter fejlbehandlingen mere over på DB-siden, hvilket afhængigt af organisationen (DBA-roller, Deployment) kan være fornuftigt eller uønsket.

Drift og administration: Hvad IT-leads og admins bør vide

Monitoring: Fejlprocent og gennemstrømning er kernemetrikkerne

For stabil drift af et bulk-import er to metrikker mere sigende end „kørselstid“ alene:

• Gennemstrømning: rækker pr. minut (eller pr. batch) inkl. peak/median.
• Fejlprocent: fejlbehæftede rækker pr. kørsel, ideelt grupperet efter fejlkategorier (Unique, FK, NOT NULL, typekonflikt).

Når du ser disse to værdier regelmæssigt, opdager du tidligt, om noget er ændret ved kilden (f.eks. nyt format) eller om målsystemet (f.eks. nye constraints) er blevet strengere.

Låsning og belastningsvinduer

Bulk-inserts kan skabe locking og IO-belastning. Hvis brugere arbejder parallelt på de samme tabeller, skal du overveje isolation level, indekser og eventuelt partitionering. I praksis betyder det: enten placere imports i belastningsvinduer, eller bygge dataflowet, så det kan sameksistere med løbende drift (f.eks. via staging + asynkron overtagelse).

Konkrete tjekliste for et robust bulk-insert med Array DML

• Batch-størrelse fastsætte (startværdi 500–1.000) og justere målbart.
• Eksplicit transaktion: commit pr. batch som standard; „commit ved slut“ kun med omtanke.
• Parametertyper stabile sætte, undgå at tvinge implicitte casts.
• Fejlkontekst pr. datapost medføre (ekstern ID, kildelinje).
• Fejlstrategi: batch først, derefter split/fallback, log per række.
• Logging: koder + tekst, men databeskyttelseskompatibelt; registrér batch-ID og kørsel-ID.
• Genstart: sikre idempotens (nøgle/Upsert/Import-ID).

Konklusion: Array DML er hurtigt – robust bliver det gennem proces og fejlstrategi

Et FireDAC Bulk-Insert med Array DML er et stærkt værktøj, så længe du ikke lader som om, der ikke findes fejl. I virkelige datastrømme optræder der altid afvigelser: Dubletten, manglende referencer, ødelagte datoværdier. Den rigtige fremgangsmåde er derfor: Array DML for ydeevne, kombineret med en kontrolleret isolationsstrategi (Split eller Fallback) og en fejlliste, der er sporbar pr. række. Så får du både tempo og driftssikkerhed — og det er præcis dét, der tæller, når importer ikke kun kører i laboratoriet, men skal køre pålideligt hver nat.

Hvis I vil stabilisere en eksisterende import- eller grænsefladeproces i Delphi/FireDAC (ydeevne, transaktioner, genstart, logning), afklarer vi det gerne struktureret i en teknisk samtale:

For dette emne er også Delphi Bulk Insert og Bulk Insert Delphi FireDAC vigtige. Artiklen sætter disse aspekter i en klart forståelig kontekst og viser, hvad der er afgørende i praksis.

Drøft projekt eller moderniseringsforløb med Net-Base.