FireDAC: Bulk-Insert med Array DML og robust feilhandsaming per rad

Ei BDE-avløysing med nativ tilkopling Bulk-Insert med Array DML er ofte den raskaste måten å få mange datarader inn i ei database på: i staden for tusen enkeltinnsettingar blir eit parameter-array bunde og sendt til serveren i éin operasjon. I praksis oppstår knutepunktet likevel raskt: ei rad bryt ein Unique-Index, eit NOT NULL-felt er tomt, ein Foreign Key passar ikkje – og plutseleg er det uklart kva for ei rad som slo ut batchen, om delar allereie er skrivne og korleis du ryddig kan fortsetje utan å skapa datainkonsekvensar.

Nettopp dette handlar denne teksten om: Korleis du brukar Array DML slik at du per rad får pålitelege feilinformasjonar, held transaksjonen under kontroll og i drift kan etterprøve kva som skjedde. Fokuset ligg ikkje på akademisk API-lesing, men på randtilfellet som i reelle importar jamleg dukkar opp: ein stor batch, få øydelagde rader, men du vil framleis ha høgt tempo.

FireDAC Bulk-Insert med Array DML: Kvifor Array DML ved Bulk-Insert er nyttig

Array DML (Data Manipulation Language) betyr i FireDAC at du bind parameterar ikkje som enkeltverdiar, men som eit array. FireDAC sender då (avhengig av driver/DB) færre roundtrips, kan jobbe meir effektivt på serversida og reduserer overhead i klienten drastisk. Dette er særleg relevant i tre situasjonar:

• ETL- og importløp: CSV/XML/JSON inn, normalisering/mapping, deretter i staging- eller måltabell.
• Grensesnittbuffer: REST- eller MQ-payloads blir samla og periodisk lagra.
• Protokoll-/event-tabellar: mange små inserts der latens dominerer.

Gevinsten kjem ikkje gratis. Med Array DML flyttar du kompleksiteten frå «mange enkeltstatement» til «eit statement med mange rader». Det er bra for ytelse, men meir krevjande for feildiagnostikk, transaksjonslogikk og gjentaking.

Det typiske randtilfellet: ein batch, ein øydelagd rad

Klassikaren i drift: Du importerer 50.000 rader. Du vel ein ArraySize på 1.000 fordi du ikkje vil ha ein roundtrip per rad. Batch 17 feilar. DB-en gir som regel berre «duplicate key» eller «violates foreign key constraint». I UIen eller i service-loga står det då ofte berre: «ExecSQL failed».

Utan ordentleg feilhandtering skjer som regel to dårlege ting:

• Du kastar heile batchen, sjølv om 999 av 1 000 rader var ok.
• Du fell tilbake til enkeltinnsettingar og mistar ytelsesfordelen varig.

Målet er ein tredje veg: behalde Batch-Performance, men loggføre defekt-nøyaktig (radindeks, nøkkelverdiar, DB-feilmelding) og valfritt committe „good rows“ – avhengig av kor kritisk konsistens og idempotens (å køyre fleire gonger utan dobbel verknad) er i prosessen din.

FireDAC Array DML: Dei relevante parameterane (utan myter)

For Bulk-Insert med Array DML er i praksis alltid dei same parameterane avgjerande:

1) ArraySize og Batch-Größe

ArraySize (ved TFDQuery/TFDCommand) bestemmer kor mange „rader“ FireDAC som blir handtert i eit kall. Større er ikkje automatisk betre. For stort betyr: meir minne i klienten, større payload på linja, auka lås-/logg-belastning på serveren og ved feil større „blast radius“. For robuste import er ofte ein batch-storleik mellom 200 und 2.000 eit godt utgangspunkt, avhengig av kolonneantall, BLOBs og latens.

2) Transaktionsgrenze

Du treng ei klår avgjerd: Commit per batch eller Commit for heile importen. Dette er ikkje smakssak, men ei driftsavgjerd:

• Commit per batch: avgrensar lås og transaksjonslogg, enklare å RESTarta, men delvise mellomresultat er synlege (avhengig av isolasjonsnivå). Feil i batch 17 let batch 1–16 liggje i systemet.
• Commit am Ende: «alt eller ingenting», fagleg meir konsistent, men ved store volum risikerer du lange lås, stort rollback og at alt går tapt ved feil.

For mange grensesnitt- og importprosessar er «Commit per batch» den mest realistiske driftsstrategien – men berre dersom du har rydda opp i idempotens og duplikatstrategi (t.d. via naturlige nøklar, Upserts eller ei Import-ID).

3) UpdateOptions und Prepared Statements

Ved gjentekne batchar lønner det seg å halde statementet forberedt. „Prepare“ betyr: FireDAC lar DB-en parse/kompilere statementet og gjenbruke det. Avhengig av DB kan dette gi merkbar effekt, særleg ved høg frekvens. Viktigare enn noko «knep» her er: konsekvent gjenbruk av same query-objektet (eller same TFDCommand) og stabile parametertypar.

Ryddig feilhandtering per rad: Kva du verkeleg treng

Om du vil handsame feil «per rad», treng du tre ting:

1. Tilknyting: Kva array-indeks (0..N-1) feila?
2. Kontekst: Kva faglege nøkkelverdiar har denne raden (t.d. ekstern ID, kundenummer, tidsstempel)?
3. Styring: Kva gjer du etterpå? Avbryt, hopp over berre dei dårlege radene, eller del batchen?

FireDAC kan, avhengig av driveren, returnere feilmelding per array-element. I praksis er dette likevel ikkje alltid tilgjengeleg. Du må rekne med at nokre databasar/providerar berre rapporterer første feilen, eller at ei feil i batchen forhindrar RESTen frå å kjøre. Difor er eit robust mønster som regel to-trinna:

• Trinn A: Prøv batchen som Array DML.
• Trinn B: Om batchen feilar, del han (halver) eller fall kontrollert tilbake til einskilde rader – men berre for denne batchen – og loggfør nøye.

Det høyREST ut som meirarbeid, men i importløp er dette skilnaden mellom «klokka 02:00 om natta står alt» og «importen køyrer vidare, 7 rader hamnar i feillista».

Eit praksisreelt mønster: Batch først, så målretta isolering

Følgjande mønster har vist seg å vere nyttig for prosessnære programvareløysingar der datakvaliteten er variabel:

Steg 1: Legg data i ei batch-struktur (inkl. feileskontext)

Lag dei data som skal importerast ikkje berre som rå verdiar, men med minste mogleg kontekst: ekstern ID, rekkjenummer frå kjelda, eventuelt hash/checksum. Dette er ikkje «Nice to have»: ved feil vil du ikkje først måtte parse CSV-en igjen for å finne ut kva som er øydelagt.

Steg 2: Kjør Array DML

Set ArraySize til batch-lengda, bind parameter som array, og kall ExecSQL. Viktig: hald parametertypane stabile (t.d. for numeriske felt ikkje somme tider binde som String og somme tider som Integer), elles vil databasen lage implisitte casts eller FireDAC må omforme per element.

Steg 3: Feiltilfelle – avgrens batchen i staden for å gjenta blindt

Om ExecSQL feilar, har du to robuste alternativ:

• Binary Split (halvere): Del batchen i to, prøv kvart halve som Array DML igjen. Gjenta til du kjem ned til ei lita mengd som du kan kontrollere enkeltvis. Fordel: Du beheld mykje av ytelsen når berre få rader er «dårlege». Ulempe: Meir logikk, og ved systematiske feil (t.d. feil datatyp) er gevinsten liten.
• Fallback auf Einzelzeilen for denne batchen: Set ArraySize=1 (eller bind enkeltdatar) og køyr rad for rad, loggfør feil og fortsett. Fordel: Enkelt, gir garantisert handtering per rad. Ulempe: Du mista tempo i denne batchen.

I praksis kombinerer eg begge: Først splitte 1–2 gonger (for å få «gode blokker» raskt gjennom), deretter ved små RESTmengder gå over til enkeltrader for å loggføre tydelege feildetaljar.

Feilobjekt og meldingar: Kva du bør hente frå FireDAC

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

• DB-feilkode (db-spesifikk): t.d. SQLSTATE i PostgreSQL, Error Number i SQL Server.
• Constraint-/objektnamn: ofte inkludert i feilmeldinga (Unique-Index, FK-Constraint).
• Statement-kontekst: tabell, operasjon, eventuelt parametervalid (vorsichtig bei personenbezogenen Daten).

Om du vil loggføre per rad, må du ved feil i tillegg identifisere raden. FireDAC kan i nokre tilfelle gi array-indeksen. Stol likevel ikkje berre på dette. Lag alltid ein eigne indeks (posisjon i batchen) og loggfør for denne posisjonen minst ein fagleg nøkkel.

Fallgruver som kostar tid i reelle importar

1) «Det var jo berre eine rad» – men transaksjonen er allereie «dirty»

Avhengig av database og driver kan ein feil føre til at heile statement-køyringa vert rekna som mislykka, og transaksjonen hamnar i ein tilstand der du anten må rulle tilbake eksplisitt eller der vidare statements feilar. Særleg med enkelte drivarar er «etter feil berre fortsetje» ikkje ei trygg føresetnad.

Konsekvens: Om du arbeider i ein transaksjon og ein batch feilar, er standardvegen: rollback av den aktuelle batch-konteksten (eller heile transaksjonen) og så starte på nytt. Det passar godt med «Commit per Batch».

2) Autocommit vs. eksplisitt transaksjon

Om du ikkje startar ei eksplisitt transaksjon, avgjer ofte driveren/Provider korleis statements blir committa. For Bulk-Imports er det sjeldan det du ønskjer. Eksplisitte transaksjonar gir deg kontroll over:

• Låsetid
• Rollback-oppførsel
• Gjenopptakingspunkter

Og: Eksplisitt tyder ikkje «ein enorm transaksjon». Det tyder «medvite».

3) Triggarar, Constraints og bieffektar

Array DML akselererer overføringa, men ikkje automatisk arbeidet på serversida. Dersom du har triggarar på måltabellen (t.d. audit-logging, automatisk statusberekning), kan flaskehalsen vere ikkje INSERT-en, men triggerkoden. Då kan eit batch ha færre roundtrips, men CPU-en på DB-serveren blir framleis den limiterande faktoren.

For adminar og tekniske leiarar: Ved ytelsesproblem løner det seg å sjå på wait events/locks og transaksjonsloggen. Bulk-Insert er då ofte berre utløysaren, ikkje årsaka.

4) Datatypar og implisitte konverteringar

Eit av dei vanlegaste «kvifor går det tregt?»-åska: Parameter blir bundne som strengar, og databasen kastar per rad til Integer/Date/Decimal. Det er usynleg, men dyrt. For stabil ytelse:

• Set parameterdatatypane korrekt (dato som dato, tal som tal).
• Ver merksam på locale-feller ved decimals (komma vs. punkt). FireDAC er som regel korrekt her, men blandingskjelder er det ikkje.
• Avklar tidssone/UTC-strategi på førehand (tidsstempel er ein klassikar ved importar).

5) Feilmeldingar er for menneske, ikkje for automatisering

Det kan vere freistande å parse feilmeldingstekst («duplicate key value violates unique constraint …»). Gjer det berre som siste utveg. betre er strukturerte koder (SQLSTATE, Error Number). Dessverre leverer ikkje alle drivarar alt likt godt. Planlegg derfor for begge delar: Kode og tekst, pluss valfritt «constraint-name frå tekst», men utan hard avhengnad.

Debugging-råd: Slik finn du raskt den feilaktige raden

Gjer batchen reproduserbar

Dersom ein import feilar sporadisk, treng du reproduserbarheit. Lagre per batch ei lita diagnosefil eller ein loggpost som inneheld:

• Batch-nummer og tid
• ArraySize og transaksjonsmodus
• lista over dei faglege nøklane (t.d. eksterne ID-ar) i batchen

Det er ofte nok til at du i ettertid målretta kan starte ein mini-import berre for desse ID-ane.

Gjer endeleg SQL synleg (men utan datalekkasje)

I debugging vil du vite: Er SQL-en korrekt? Er parameterane riktige? FireDAC tilbyr monitoring/tracing over FDMoni-Komponenten og driver-logging. I produksjonsnære miljø er følgjande viktig:

• Aktiver tracing målretta og berre midlertidig (ytelse og personvern).
• Loggfør parameterverdiar berre i eit sikkert miljø eller maskerte.
• For personopplysningar: i loggen berre tekniske nøklar (ID-ar) og inga klartekstinnhald.

Når du testar split: Definer avbrotskriteria

Ved binary split ønskjer du ikkje å dele i det uendelege. Set ein nedre grense, t.d. «under 20 rader: skift til enkeltmodus». Og set eit tak for kor mange feil du tolererer totalt før du avbryt importen (t.d. ved systematiske mappingproblem). Elles riskerar du å hamne i uendelege feil-lister og blokkere etterfølgjande behandling.

Når innsatsen verkeleg lønner seg (og når ikkje)

Array DML med feilhandtering per rad lønner seg særleg når:

• Mange rader blir handsama (tusenar til millionar).
• Få rader er feilaktige, men du likevel ønskjer å prosessere resten.
• Importen må køyre stabilt i drift (t.d. nattkøyring, teneste utan UI).
• Du må sende ei feiliste attende til fagavdeling/kjelde (med radreferanse).

Det er mindre hensiktsmessig når:

• du berre skriv nokre få dusin rader (enkeltinnsettingar er ok),
• datakvaliteten er så dårleg at 30–50 % av radene mislykkast (då er ei staging-strategi meir hensiktsmessig),
• du uansett brukar ein DB-nativ bulk-load-metode (t.d. COPY i PostgreSQL, BCP/BULK INSERT i SQL Server) – då er ikkje Array DML reiskapen.

Alternativ arkitektur: Staging-tabell i staden for «direkte til målet»

Når du jamleg handsamar blandet datakvalitet, er eit reint «Insert direkte i måltabellen» ofte feil val. Ein Staging-Tabelle (forstad) er ei tabell der du først lagrar data teknisk korrekt (eventuelt med fleksible typar), og først etterpå validerer og overfører til måltabellen.

Fordelar i drift:

• Feilaktige dataposter blir lagra på ein etterprøvbar måte (inkl. rådata).
• Du kan utføre validering separat og gjentakbart.
• Du skil grensesnittmottak frå fagleg prosessering.

Array DML er då ofte den raske vegen inn i Staging-Tabelle, medan overføringa til måltabellen skjer som settbasert SQL (eller Stored Procedure). Det flyttar feilhandsaming meir til DB-sida, noko som avhengig av organisasjon (DBA-roller, Deployment) kan vere fornuftig eller uønskt.

Drift og administrasjon: Det IT-leiarar og administratorar bør vite

Overvaking: Feilrate og gjennomstrøyming er kjernemetrikane

For stabil drift av ein bulk-import er to metrikar meir talande enn «køyringstid» aleine:

• Gjennomstrøyming: rader per minutt (eller per batch) inkl. topp/median.
• Feilrate: feilaktige rader per kjøring, ideelt gruppert etter feilklassar (Unique, FK, NOT NULL, Typkonflikt).

Når du ser desse to verdiane regelmessig, oppdagar du tidleg om noko i kjelda har endra seg (t.d. nytt format) eller om målsystemet (t.d. nye Constraints) har blitt strengare.

Låsing og lastvindauge

Bulk-inserts kan føre til låsing og IO-last. Når brukarar arbeidar parallelt på same tabellar, må du tenkje på Isolation Level, indeksar og eventuelt partisjonering. Praktisk betyr det: anten legg du importar i lastvindauge, eller byggjer dataløpet slik at det kan koeksistere med løpande drift (t.d. over Staging + asynkron overføring).

Konkrett sjekkliste for eit robust bulk-insert med Array DML

• Batch-storleik fastsetje (startverdi 500–1 000) og målbart finjustere.
• Eksplisitt transaksjon: commit per batch som standard; «commit ved slutt» berre med overlegg.
• Parametertypar setje stabilt; ikkje tvinge fram implisitte kast.
• Feilkontext per datapost ta med (ekstern ID, kjelderad).
• Feilstrategi: batch først, deretter split/fallback, logg per rad.
• Logging: kode + tekst, men personvernmessig forsvarleg; registrer Batch-ID og Lauf-ID.
• Gjenopptak: sikre idempotens (Schlüssel/Upsert/Import-ID).

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

Eit FireDAC Bulk-Insert med Array DML er eit kraftig verktøy, så lenge du ikkje late som om det ikkje finst feil. I ekte datastrømmer finst det alltid avvik: duplikatar, manglande referansar, ugyldige datoverdiar. Den ryddige tilnærminga er difor: Array DML for ytelse, kombinert med ei kontrollert isolasjonsstrategi (Split eller Fallback) og ei feilliste per rad som kan etterprøvast. Slik får du både ytelse og driftssikkerheit – og nettopp det tel når importar ikkje berre køyrer i laboratoriet, men må køyre påliteleg kvar natt.

Dersom de vil stabilisere ein eksisterande import- eller grensesnittprosess i Delphi/FireDAC (ytelse, transaksjonar, gjenopptak, logging), avklarar vi det gjerne strukturert i ein teknisk samtale:

For dette temaet er også Delphi Bulk Insert og Bulk Insert Delphi FireDAC viktige. Innlegget set desse aspekta inn i ein forståeleg samanheng og viser kva som er viktig i praksis.

Drøft prosjekt eller moderniseringsprosjekt med Net-Base.