FireDAC: Bulk-innsetting med Array DML og robust feilhåndtering per rad

Et BDE-erstatning med native tilkobling Bulk-Insert med Array DML er ofte den raskeste måten å få mange poster inn i en database på: i stedet for tusen separate INSERTs bindes et parameterarray og sendes samlet til serveren. I praksis oppstår knyttet raskt: En rad bryter en Unique-Index, et NOT NULL-felt er tomt, en Foreign Key stemmer ikke – og plutselig er det uklart hvilken rad som slo ut batchen, om deler allerede er skrevet og hvordan du trygt fortsetter uten å skape datainkonsistenser.

Det er nettopp dette artikkelen handler om: Hvordan du bruker Array DML slik at du får per rad pålitelige feildetaljer, beholder kontrollen over transaksjonen og i drift kan spore hva som skjedde. Fokuset ligger ikke på akademisk API-lesning, men på kanttilfellet som i reelle importer dukker opp jevnlig: Et stort batch, noen få ødelagte rader, men du vil fortsatt ha tempo.

FireDAC Bulk-Insert med Array DML: Hvorfor Array DML i det hele tatt lønner seg

Array DML (Data Manipulation Language) betyr i FireDAC: Du binder parametere ikke som enkeltverdier, men som et array. FireDAC sender da (avhengig av driver/DB) færre roundtrips, kan kjøre mer effektivt på serversiden og reduserer klientens overhead drastisk. Dette er spesielt relevant i tre situasjoner:

• ETL- og importløp: CSV/XML/JSON inn, normalisering/mapping, så til staging- eller måltabell.
• Grensesnittbuffer: REST- eller MQ-payloads samles og persisteres periodisk.
• Protokoll-/hendelsestabeller: mange små INSERTs hvor latenstid dominerer.

Gevinsten kommer imidlertid ikke gratis. Med Array DML flytter du kompleksiteten fra «mange enkeltstående statements» til «ett statement med mange rader». Det er bra for ytelsen, men stiller høyere krav til feildiagnostikk, transaksjonslogikk og gjenkjøring.

Det typiske kanttilfellet: Et batch, en ødelagt rad

Klassikeren i drift: Du importerer 50 000 rader. Du velger en ArraySize på 1 000 fordi du ikke vil ha en roundtrip per rad. Batch 17 feiler. DB-en rapporterer bare «duplicate key» eller «violates foreign key constraint». I UI-en eller i serviceloggen står det da ofte bare: «ExecSQL failed».

Uten ordentlig error-håndtering skjer som regel to dårlige ting:

• Du kaster hele batchen, selv om 999 av 1 000 rader var ok.
• Du faller tilbake på enkeltinnsettinger og mister ytelsesfordelen permanent.

Målet er en tredje vei: beholde batch-ytelsen, men loggføre defekt-nøyaktig (radindeks, nøkkelverdier, DB-feilmelding) og valgfritt committe «gode rader» – avhengig av hvor kritisk konsistens og idempotens (å kjøre flere ganger uten dobbel effekt) er i prosessen din.

FireDAC Array DML: De relevante justeringsparametrene (uten myter)

For Bulk-Insert med Array DML er i praksis alltid de samme innstillingsparametrene avgjørende:

1) ArraySize og batch-størrelse

ArraySize (i TFDQuery/TFDCommand) bestemmer hvor mange «rader» FireDAC som behandles i ett kall. Større er ikke automatisk bedre. For stort betyr: mer minne i klienten, mer payload på linjen, større låser/logg-belastning på serveren og ved feil større «blast radius». For robuste importer er ofte en batch-størrelse mellom 200 og 2.000 et godt utgangspunkt, avhengig av antall kolonner, BLOBs og latens.

2) Transaksjonsgrense

Du trenger et klart valg: Commit per batch eller Commit for hele importen. Dette er ikke et spørsmål om preferanse, men en driftsbeslutning:

• Commit per batch: begrenser låser og transaksjonslogg, enklere gjenopptak, men delresultater er synlige (avhengig av isolasjonsnivå). Feil i batch 17 lar batch 1–16 være i systemet.
• Commit ved slutten: «alt eller intet», mer konsistent i faglig forstand, men ved store mengder risikerer du lange låser, stort rollback og i tilfelle feil blir alt borte.

For mange grensesnitt- og importprosesser er «Commit per batch» den mer realistiske driftsstrategien – men bare hvis du har idempotens og dublettstrategi ryddig regulert (f.eks. via naturlige nøkler, Upserts eller en Import-ID).

3) UpdateOptions og Prepared Statements

Ved gjentatte batches lønner det seg å holde statementet forberedt. «Prepare» betyr at FireDAC lar DB-en parse/kompilere statementet og gjenbruke det. Avhengig av DB kan det gi merkbar effekt, spesielt ved høy frekvens. Viktig her er ikke «snarveier», men: konsekvent gjenbruk av samme Query-objekt (eller samme TFDCommand) og stabile parametertyper.

Ryddig feilbehandling per rad: Hva du virkelig trenger

Hvis du vil håndtere feil «per rad», trenger du tre ting:

1. Tilordning: Hvilket array-indeks (0..N-1) feilet?
2. Kontext: Hvilke faglige nøkkelverdier har denne raden (f.eks. ekstern ID, kundenummer, tidsstempel)?
3. Styring: Hva gjør du deretter? Avbryte, hoppe over kun dårlige rader, eller splitte batchen?

FireDAC kan avhengig av driver returnere feil per array-element. I praksis er dette imidlertid ikke «automatisk tilgjengelig». Du må regne med at noen databaser/leverandører bare rapporterer den første feilen, eller at en feil i batchen gjør at RESTen ikke blir utført. Nøyaktig derfor er et robust mønster vanligvis to-trinns:

• Trinn A: Prøv batchen som Array DML.
• Trinn B: Hvis batchen feiler, splitt den (halvere) eller fall kontrollert tilbake til enkeltlinjer – men bare for denne batchen – og loggfør grundig.

Det høres ut som merarbeid, men i importløp er dette forskjellen mellom «kl. 02:00 om natten står alt stille» og «importen kjører videre, 7 rader ender opp i feillisten».

Et praktisk mønster: Batch først, så målrettet isolering

Følgende mønster har vist seg nyttig i prosessnær programvare hvor datakvaliteten er blandet:

Trinn 1: Pakk dataene i en batch-struktur (inkl. feilkontekst)

Ikke bare lagre de dataene som skal importeres som råverdier, men med minst mulig kontekst: ekstern ID, linjenummer fra kilden, eventuelt hash/sjekksum. Dette er ikke „Nice to have“: Ved feil vil du ikke først måtte parse CSV-en igjen for å finne ut hva som er ødelagt.

Trinn 2: Utfør Array DML

Sett ArraySize til batch-lengde, bind parametere som arrays, og kjør ExecSQL. Viktig: hold parametertypene stabile (f.eks. ikke bind et numerisk felt noen ganger som String og andre ganger som Integer), ellers vil databasen produsere implisitte casts eller FireDAC må konvertere per element.

Trinn 3: Ved feil – innsnevre batchen i stedet for å gjenta blindt

Hvis ExecSQL mislykkes, har du to robuste alternativer:

• Binærsplitting (halvere): Del batchen i to halvdeler, og prøv hver halvdel igjen som Array DML. Gjenta til du kommer ned på en liten mengde som du kan sjekke individuelt. Fordel: Du bevarer mye ytelse når bare noen få rader er ødelagte. Ulempe: Mer logikk, og ved systematiske feil (f.eks. feil datatyper) hjelper det lite.
• Fallback til enkeltlinjer for denne batchen: Sett ArraySize=1 (eller bind enkelverdier) og kjør rad for rad, logg feil og fortsett. Fordel: Enkelt, garantert per rad. Ulempe: Du mister fart i denne batchen.

I praksis kombinerer jeg begge: Først dele 1–2 ganger (for raskt å få igjennom „gode blokker“), deretter ved små RESTmengder gå over til enkeltlinjer for å logge entydig feilinformasjon.

Feilobjekter og meldinger: Hva du bør trekke ut av FireDAC

FireDAC kapsler inn DB-feil i unntak (typisk EFDDBEngineException) med detaljinformasjon. For drift er tre nivåer viktige:

• DB-feilkode (DB-spesifikk): f.eks. SQLSTATE for PostgreSQL, Error Number for SQL Server.
• Constraint-/objektnavn: ofte inkludert i feilteksten (Unique-index, FK-constraint).
• Statement-kontekst: tabell, operasjon, eventuelt parameterverdier (vær forsiktig med personopplysninger).

Hvis du vil logge per rad, må du ved feil også identifisere raden. FireDAC kan i noen tilfeller gi array-indeksen. Ikke stol utelukkende på det. Lag alltid i tillegg en egen indeks (posisjon i batchen) og logg til denne posisjonen minst én faglig nøkkel.

Fallgruver som koster tid i reelle importer

1) „Det var jo bare én rad“ – men transaksjonen er allerede „dirty“

Avhengig av database og driver kan en feil føre til at hele statement-kjøringen regnes som mislykket og at transaksjonen er i en tilstand hvor du enten må gjøre eksplisitt rollback eller hvor etterfølgende statements feiler. Særlig med enkelte drivere er „bare fortsett etter feil“ ingen sikker antakelse.

Konsekvens: Hvis du arbeider i en transaksjon og en batch feiler, er standardveien: Rollback av gjeldende batch-kontekst (eller hele transaksjonen) og så starte på nytt. Dette passer godt med „Commit per batch“.

2) Autocommit vs. eksplisitt transaksjon

Hvis du ikke starter en eksplisitt transaksjon, bestemmer ofte driveren/provider hvordan statements committes. For bulk-importer er det sjelden det du ønsker. Eksplisitte transaksjoner gir deg kontroll over:

• låsetid
• Rollback-atferd
• Gjenopptakelsespunkter

Og: Eksplisitt betyr ikke «en gigantisk transaksjon». Det betyr «bevisst».

3) Triggere, Constraints og bivirkninger

Array DML akselererer overføringen, men ikke automatisk arbeidet på serversiden. Hvis du har triggere på måltabellen (f.eks. audit-logging, automatisk statusberegning), kan flaskehalsen faktisk være triggerkoden. Da kan et batch ha færre roundtrips, men CPU-en på DB-serveren forblir begrensende faktor.

For admins og tekniske ledere: Ved ytelsesproblemer lønner det seg å se på Wait Events/Locks og transaksjonsloggen. Bulk-insertet er da ofte bare utløseren, ikke årsaken.

4) Datatyper og implisitte konverteringer

En av de vanligste «Hvorfor er det tregt?»‑årsakene: Parametere bindes som streng, og DB caster per rad til Integer/Date/Decimal. Det er usynlig, men kostbart. For stabil ytelse:

• Sett parameter-datatyper riktig (dato som dato, tall som tall).
• Ved desimaler: pass på locale-feller (komma vs. punkt). FireDAC er vanligvis korrekt her, men blandede kilder er det ikke.
• Avklar tidssone/UTC‑strategi på forhånd (tidsstempler er en klassiker ved importer).

5) Feilmeldinger er for mennesker, men ikke for automatisering

Det er fristende å parse feilmeldingen («duplicate key value violates unique constraint …»). Gjør det kun som siste utvei. Bedre er strukturerte koder (SQLSTATE, Error Number). Dessverre gir ikke alle drivere alt like godt. Planlegg derfor begge deler: kode og tekst, pluss valgfritt «constraint-navn fra tekst», men uten hard avhengighet.

Feilsøkingstips: Slik finner du raskt den feilede raden

Gjør batch reproduserbar

Hvis en import sporadisk feiler, trenger du reproducerbarhet. Lagre for hvert batch en liten diagnosefil eller en loggoppføring som inneholder:

• Batchnummer og tidspunkt
• ArraySize og transaksjonsmodus
• listen over faglige nøkler (f.eks. eksterne ID-er) i batchen

Det er ofte nok til i etterkant målrettet å kjøre en mini‑import bare for disse ID‑ene.

Gjør endelig SQL synlig (men uten datalekkasjer)

I feilsøking vil du vite: Er SQL korrekt? Er parameterne riktige? FireDAC tilbyr monitoring/tracing via FDMoni‑komponenter og driver‑logging. I produksjonsnære miljøer er det viktig:

• Aktiver tracing målrettet og kun midlertidig (ytelse og personvern).
• Logg parameterverdier kun i et sikkert miljø eller maskert.
• For personopplysninger: i logg kun tekniske nøkler (ID‑er) og ingen klartekstinnhold.

Hvis du gjør split‑testing: Definer avbruddskriterier

Ved binær splitting vil du ikke dele i det uendelige. Sett en nedre grense, f.eks. «under 20 rader: bytt til enkelmodus». Og sett en grense for hvor mange feil du totalt tolererer før du avbryter importen (f.eks. ved systematiske mapping‑problemer). Ellers ender du opp med uendelige feilister og blokkerer etterfølgende behandling.

Når arbeidet virkelig lønner seg (og når ikke)

Array DML med feilhåndtering per rad lønner seg særlig når:

• Mange rader behandles (tusenvis til millioner).
• Få rader feiler, men du likevel ønsker at prosessen skal fullføres.
• Importen må kjøre stabilt i drift (f.eks. nattkjøring, tjeneste uten UI).
• Du må kunne returnere en feilliste til fagavdeling/kilde (med radreferanser).

Det lønner seg mindre når:

• du bare skriver noen få dusin rader (enkeltinserts er ok),
• datakvaliteten er så dårlig at 30–50 % av radene feiler (da er en staging-strategi mer fornuftig),
• du uansett bruker et DB-native bulk-load-prosedyre (f.eks. COPY i PostgreSQL, BCP/BULK INSERT i SQL Server) – da er Array DML ikke verktøyet.

Alternativ arkitektur: Staging-tabell i stedet for «direkte til mål»

Hvis du jevnlig håndterer blandet datakvalitet, er et rent «insert direkte i måltabellen» ofte feil valg. En Staging-tabell (forstadium) er en tabell hvor du først lagrer data teknisk korrekt (eventuelt med myke typer), og først deretter validerer og overfører til måltabellen.

Fordeler i drift:

• Feilaktige poster blir lagret og kan spores (inkl. rådata).
• Du kan kjøre validering separat og repeterbart.
• Du løsner grensesnittmottak fra faglig behandling.

Array DML er da ofte den raske veien inn i Staging-tabellen, mens overføringen til måltabellen skjer som sett-baserte SQL-operasjoner (eller lagrede prosedyrer). Det flytter feilhåndtering mer mot databasesiden, noe som avhengig av organisasjon (DBA-roller, utrulling) kan være fornuftig eller uønsket.

Drift og administrasjon: Hva IT-ledere og administratorer bør vite

Overvåkning: Feilrate og gjennomstrømning er kjerne-metrikker

For stabil drift av et bulk-import er to metrikker mer talende enn «kjøretid» alene:

• Gjennomstrømning: rader per minutt (eller per batch) inkl. peak/median.
• Feilrate: feilaktige rader per kjøring, ideelt gruppert etter feilklasser (Unique, FK, NOT NULL, typekonflikt).

Hvis du ser disse to verdiene regelmessig, oppdager du tidlig om noe har endret seg i kilden (f.eks. nytt format) eller om målsystemet (f.eks. nye constraints) har blitt strengere.

Lås og lastvinduer

Bulk-inserts kan skape låsing og IO-belastning. Hvis brukere jobber parallelt på de samme tabellene må du vurdere isolasjonsnivå, indekser og eventuelt partisjonering. Praktisk betyr det: enten legge imports i lastvinduer, eller bygge datastrømmen slik at den kan sameksistere med løpende drift (f.eks. via Staging + asynkron overtakelse).

Konkrete sjekkpunkter for et robust bulk-insert med Array DML

• Batch-størrelse fastsette (startverdi 500–1.000) og målrettet tunge.
• Eksplisitt transaksjon: commit per batch som standard, «commit på slutten» kun bevisst.
• Stabile parametertyper sette, ikke tvinge implisitte konverteringer.
• Feilkontekst per post føre med (ekstern ID, kilde-rad).
• Feilstrategi: batch først, deretter split/fallback, logg per rad.
• Logging: koder + tekst, men i samsvar med personvern; registrer batch-ID og kjørings-ID.
• Gjenkjørbarhet: sikre idempotens (nøkkel/upsert/import-ID).

Konklusjon: Array DML er raskt – robust blir det gjennom prosess og feilstrategi

Et FireDAC Bulk-Insert med Array DML er et kraftig verktøy, så lenge du ikke later som om det ikke finnes feil. I reelle datastrømmer finnes det alltid uteliggere: duplikater, manglende referanser, ødelagte datoverdier. Den rene tilnærmingen er derfor: Array DML for ytelsen, kombinert med en kontrollert isolasjonsstrategi (Split eller Fallback) og en feilliste som er etterprøvbar per rad. Slik får du fart og driftssikkerhet sammen – og nettopp det teller når importer ikke bare kjøres i laben, men må fullføres pålitelig hver natt.

Hvis dere vil stabilisere en eksisterende import- eller grensesnittprosess i Delphi/FireDAC (ytelse, transaksjoner, gjenopptak, logging), avklarer vi det gjerne strukturert i en teknisk samtale:

For dette temaet er også Delphi Bulk Insert og Bulk Insert Delphi FireDAC viktige. Innlegget setter disse aspektene i en forståelig sammenheng og viser hva som er avgjørende i praksis.

Diskuter prosjekt eller moderniseringsprosjekt med Net-Base.