FireDAC: Bulk-Insert med Array DML och konsekvent felhantering per rad

En BDE-Ablosung mit nativer Anbindung Bulk-insert med Array DML är ofta det snabbaste sättet att få många poster in i en databas: istället för tusen enskilda inserts binds en parameter-array och skickas i ett svep till servern. I praktiken uppstår dock snabbt knäckpunkten: en post bryter mot ett Unique-Index, ett NOT NULL-fält är tomt, en Foreign Key stämmer inte – och plötsligt är det oklart vilken rad som dödade batchen, om en del redan har skrivits och hur du fortsätter på ett rent sätt utan att skapa datainkonsekvenser.

Det är precis det här det handlar om: hur du använder Array DML så att du per rad får belastbar felinformation, håller transaktionen under kontroll och i drift kan följa vad som hänt. Fokus ligger inte på akademisk API-läsning, utan på det hörnfall som regelbundet dyker upp i verkliga importer: en stor batch, några få trasiga rader, men du vill ändå behålla tempot.

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- och importer: CSV/XML/JSON in, normalisering/mappning, sedan till staging- eller måltabell.
• Gränssnitts-Puffer: REST- eller MQ-payloads samlas och sparas periodiskt.
• Protokoll-/händelsetabeller: många små inserts där latenstid dominerar.

Vinsten kommer dock inte gratis. Mit Array DML förflyttar du komplexitet från „många enstaka statements“ till „ett statement med många rader“. Det är bra för prestanda, men mer krävande för felsökning, transaktionslogik och återkörning.

Det typiska hörnfallet: Ett batch, en trasig rad

Klassikern i drift: Du importerar 50.000 rader. Du väljer en ArraySize von 1.000, weil du inte vill ha en roundtrip per rad. Batch 17 misslyckas. DB:n rapporterar bara „duplicate key“ eller „violates foreign key constraint“. I gränssnittet eller i serviceloggen står det då ofta bara: „ExecSQL failed“.

Utan ordentlig felhantering händer då ofta två dåliga saker:

• Du slänger hela batchen, trots att 999 av 1.000 rader skulle vara ok.
• Du faller tillbaka på enstaka inserts och förlorar prestandafördelen permanent.

Målet är en tredje väg: behålla batchpRESTanda, men logga defekt-exakt (radindex, nyckelvärden, DB-feltext) och valfritt committa „giltiga rader“ – beroende på hur kritisk konsistens och idempotens (upprepade körningar utan dubbel effekt) är i din process.

FireDAC Array DML: De relevanta parametrarna (utan myter)

För bulk-insert med Array DML är i praktiken alltid samma parametrar avgörande:

1) ArraySize och batchstorlek

ArraySize (i TFDQuery/TFDCommand) bestämmer hur många „rader“ FireDAC som bearbetas i ett anrop. Större är inte automatiskt bättre. För stort innebär: mer minne i klienten, mer payload på länken, större lås/ökat loggtryck på servern och vid fel större „blast radius“. För robusta imports är ofta en batchstorlek mellan 200 och 2 000 en bra utgångspunkt, beroende på antal kolumner, BLOBs och latens.

2) Transaktionsgräns

Du behöver ett klart beslut: commit per batch eller commit för hela importen. Det är ingen smaksak, utan ett driftbeslut:

• Commit per batch: begränsar lås och transaktionsloggen, enklare återstart, men mellanliggande tillstånd är synliga (beroende på isoleringsnivå). Fel i batch 17 lämnar batch 1–16 i systemet.
• Commit i slutet: „allt eller inget“, mer konsekvent i ett verksamhetsmässigt avseende, men vid stora volymer riskerar du långa lås, stora rollbacks och vid fel förloras allt.

För många gränssnitts- och importprocesser är „commit per batch“ den mer realistiska driftsstrategin – men bara om du har idempotens och dubbletthantering tydligt reglerat (t.ex. via naturliga nycklar, Upserts eller en import-ID).

3) UpdateOptions och Prepared Statements

Vid upprepade batchar lönar det sig att ha statementet förberett. „Prepare“ betyder: FireDAC låter DB:n parsa/kompilera uttalandet och återanvända det. Beroende på DB kan det ge en märkbar effekt, särskilt vid hög frekvens. Viktigt här är mindre „trick 17“ och mer: konsekvent återanvändning av samma query-objekt (eller samma TFDCommand) och stabila parametertyper.

Robust felhantering per rad: Vad du verkligen behöver

Om du vill hantera fel „per rad“ behöver du tre saker:

1. Tilldelning: Vilket arrayindex (0..N-1) misslyckades?
2. Kontext: Vilka affärsnyckelvärden har denna rad (t.ex. extern ID, kundnummer, tidsstämpel)?
3. Styrning: Vad gör du efteråt? Avbryter, hoppar över bara de dåliga raderna, eller delar upp batchen?

FireDAC kan beroende på drivrutin returnera fel per arrayelement. Praktiskt är det dock inte „alltid tillgängligt“. Du måste räkna med att vissa databaser/leverantörer bara rapporterar det första felet eller att ett fel i batchen gör att RESTen inte körs alls. Precis därför är ett robust mönster oftast tvåstegat:

• Steg A: Försök köra batchen som Array DML.
• Steg B: Om batchen misslyckas, dela den (halvera) eller fall kontrollerat tillbaka på enstaka rader – men bara för den här batchen – och logga noggrant.

Det låter som merarbete, men i importflöden är det skillnaden mellan „klockan 02:00 på natten står allt still“ och „importen körs igenom, 7 rader hamnar i felistan“.

Ett praktiskt mönster: Batch först, sedan isolera målmedvetet

Följande mönster har visat sig fungera för processnära mjukvarulösningar där datakvaliteten är blandad:

Steg 1: Packa data i en batchstruktur (inkl. felkontext)

Spara de data som ska importeras inte bara som råa värden, utan med minimalt kontext: extern ID, radnummer från källan, eventuellt hash/checksumma. Det är inte „nice to have“: vid fel vill du inte behöva parsa CSV:n igen för att ta reda på vad som är trasigt.

Steg 2: Utför Array DML

Du sätter ArraySize till batchlängd, binder parametrar som arrayer och kör ExecSQL. Viktigt: håll parametertyper stabila (t.ex. bind inte numeriska fält ibland som sträng och ibland som Integer), annars producerar DB:n implicita casts eller FireDAC måste konvertera per element.

Steg 3: Felhantering – avgränsa batchen istället för att köra blindt igen

Om ExecSQL misslyckas har du två robusta alternativ:

• Binary Split (halvera): dela batchen i två halvor och försök varje halva igen som Array DML. Upprepa tills du når en liten mängd som du kan kontrollera individuellt. Fördel: du behåller mycket pRESTanda när bara få rader är skadade. Nackdel: mer logik, och vid systematiska fel (t.ex. fel datatyp) hjälper det lite.
• Fallback till enstaka rader för den här batchen: sätt ArraySize=1 (eller bind enskilda värden) och kör rad för rad, logga fel och fortsätt. Fördel: enkelt, garanterat per rad. Nackdel: du förlorar tempo i denna batch.

I praktiken kombinerar jag båda: först dela 1–2 gånger (för att snabbt få igenom „goda block“) och sedan gå över till enstaka rader för små RESTmängder för att logga entydig felinformation.

Felobjekt och meddelanden: Vad du bör dra ur FireDAC

FireDAC inkapslar DB-fel i Exceptions (typiskt EFDDBEngineException) med detaljinformation. För drift är tre nivåer viktiga:

• DB-felkod (DB-specifik): t.ex. SQLSTATE för PostgreSQL, Error Number för SQL Server.
• Constraint-/objektnamn: ofta inkluderat i feltexten (unik-index, FK-constraint).
• Statement-kontext: tabell, operation, eventuellt parametervärden (var försiktig med personuppgifter).

Om du vill logga per rad måste du vid fel dessutom identifiera raden. FireDAC kan under vissa omständigheter leverera array-indexet. Lita dock inte enbart på det. Bygg alltid dessutom en egen indexering (position i batchen) och logga för denna position åtminstone en affärsmässig nyckel.

Fallgropar som kostar tid i verkliga importer

1) „Det var ju bara en rad“ – men transaktionen är redan „dirty“

Beroende på DB och drivrutin kan ett fel leda till att hela statement-exekveringen betraktas som misslyckad och att transaktionen hamnar i ett tillstånd där du antingen måste göra explicit rollback eller där efterföljande statements misslyckas. Särskilt för vissa drivrutiner är „efter fel, fortsätt bara“ inte en säker antagning.

Konsekvens: Om du arbetar i en transaktion och en batch misslyckas är standardvägen: Rollback av aktuell batchkontext (eller hela transaktionen) och starta om. Det passar väl med „Commit per batch“.

2) Autocommit vs. explicit transaktion

Om du inte startar en explicit transaktion bestämmer ofta drivrutinen/provider hur den committar statements. För bulk-importer är det sällan vad du vill. Explicit transaktion ger dig kontroll över:

• Låsningstid
• Rollback-beteende
• Återstartspunkter

Och: Uttryckligen betyder inte „en jättestor transaktion“. Det betyder „medvetet“.

3) Trigger, Constraints und Nebenwirkungen

Array DML snabbar upp överföringen, men inte automatiskt det server-side arbetet. Om du har triggers på mål-tabellen (t.ex. audit-logging, automatisk statusberäkning) kan flaskhalsen ibland inte vara INSERT:en utan triggerkoden. Ett batch kan visserligen minska antalet roundtrips, men CPU:n på DB-servern förblir den begränsande faktorn.

För administratörer och tekniska leadar: Vid prestandaproblem är det värt att titta på Wait Events/Locks och transaktionsloggen. Bulk-Insert är då bara utlösaren, inte orsaken.

4) Datatyper och implicita konverteringar

En av de vanligaste „Varför är det långsamt?“-orsakerna: parametrar binds som strängar, och DB:n castar per rad till Integer/Date/Decimal. Det är osynligt men kostsamt. För stabil prestanda:

• Sätt parameterdatatyperna korrekt (datum som datum, tal som tal).
• Vid decimaltal, uppmärksamma locale-fällor (komma vs punkt). FireDAC är här oftast korrekt, men blandade källor är det inte.
• Klargör tidszoner/UTC-strategi i förväg (tidsstämplar är en klassiker vid importer).

5) Felmeddelanden är för människor, men inte för automatisering

Det är frestande att parsa feltexten („duplicate key value violates unique constraint …“). Gör det bara som sista utväg. Bättre är strukturerade koder (SQLSTATE, Error Number). Tyvärr levererar inte alla drivrutiner allt på samma sätt. Planera därför för båda: kod och text, plus eventuellt „constraint-namn ur texten“, men utan hårt beroende.

Debugging-anvisningar: Så hittar du snabbt den felaktiga raden

Gör batchen reproducerbar

Om en import sporadiskt misslyckas behöver du reproducerbarhet. Spara per batch en liten diagnostikfil eller en loggpost som innehåller:

• Batchnummer och tid
• ArraySize och transaktionsläge
• listan över verksamhetsnycklar (t.ex. externa ID:er) i batchen

Det räcker ofta för att i efterhand köra en riktad mini-import endast för dessa ID:n.

Gör den slutgiltiga SQL synlig (men utan dataläckor)

Vid debugging vill du veta: Är SQL korrekt? Är parametrarna rätt? FireDAC erbjuder monitoring/tracing via FDMoni-komponenter och drivrutinsloggning. I produktionsnära miljöer är det viktigt:

• Aktivera tracing selektivt och endast temporärt (prestanda och dataskydd).
• Logga parametervärden bara i en säker miljö eller maskerat.
• För personuppgifter: endast tekniska nycklar (ID:n) i loggen och inga klartextvärden.

När du split-testar: definiera avbrottskriterier

Vid Binary Split vill du inte dela i oändlighet. Sätt en nedre gräns, t.ex. „under 20 rader växla till enkelmodus“. Och sätt en gräns för hur många fel du totalt tolererar innan du avbryter importen (t.ex. vid systematiska mappningsproblem). Annars riskerar du oändliga fellistor och blockerad efterbearbetning.

När ansträngningen verkligen lönar sig (och när inte)

Array DML med per-rad felhantering är särskilt motiverat när:

• Många rader ska bearbetas (tusentals till miljoner).
• Få rader är felaktiga, men du ändå vill köra igenom allt.
• Importen måste köras stabilt i drift (t.ex. nattkörning, tjänst utan UI).
• Du behöver återföra en felrapport till verksamheten/källan (med radreferenser).

Det är mindre motiverat när:

• du bara skriver några tiotals rader (enskilda INSERTs är ok),
• datakvaliteten är så dålig att 30–50% av raderna misslyckas (då är en staging-strategi mer ändamålsenlig),
• du ändå använder ett DB-native bulk-laddningsförfarande (t.ex. COPY i PostgreSQL, BCP/BULK INSERT i SQL Server) – då är Array DML inte rätt verktyg.

Alternativ arkitektur: Staging-tabell istället för „Direkt ins Ziel“

Om du regelbundet brottas med blandad datakvalitet är ett rent „insert direkt i måltabellen“ ofta fel beslut. En staging-tabell (förstadium) är en tabell där du först lagrar data tekniskt korrekt (eventuellt med flexibla datatyper), och först därefter validerar och för över till måltabellen.

Fördelar i driften:

• Felaktiga dataposter förblir spårbart lagrade (inkl. rådata).
• Du kan utföra validering separat och upprepningsbart.
• Du separerar gränssnittets mottagande från den funktionella bearbetningen.

Array DML är då ofta det snabba sättet in i staging-tabellen, medan överföringen till måltabellen sker som set-baserad SQL (eller Stored Procedure). Det förskjuter felhanteringen mer åt DB-sidan, vilket beroende på organisation (DBA-roller, Deployment) kan vara lämpligt eller oönskat.

Drift och administration: Vad IT-ledare och administratörer bör veta

Övervakning: Felkvot och genomströmning är kärnmetrik

För stabil drift av en bulkimport är två metrikvärden mer talande än „körtid“ ensam:

• Genomströmning: rader per minut (eller per batch) inkl. Peak/Median.
• Felkvot: felaktiga rader per körning, helst grupperade efter felklasser (Unique, FK, NOT NULL, typkonflikt).

Om du ser dessa två värden regelbundet upptäcker du tidigt om något förändrats i källan (t.ex. nytt format) eller om målsystemet (t.ex. nya Constraints) blivit striktare.

Låsningar och belastningsfönster

Bulk-inserts kan orsaka låsning och IO-belastning. Om användare arbetar parallellt mot samma tabeller måste du tänka på isolation level, index och eventuellt partitionering. I praktiken betyder det: antingen schemalägga importer i belastningsfönster, eller utforma dataflödet så att det kan samexistera med pågående drift (t.ex. via staging + asynkron överföring).

Konkreta checklista för ett robust bulk-insert med Array DML

• Batchstorlek: sätt (startvärde 500–1 000) och finjustera mätbart.
• Explicit transaktion: commit per batch som standard, „Commit am Ende“ bara medvetet.
• Stabila parametertyper: ange dem; tvinga inte fram implicita casts.
• Felkontext per datapost föra med (extern ID, källrad).
• Felstrategi: batch först, sedan Split/Fallback, logga per rad.
• Logging: koder + text, men dataskyddskompatibelt; fånga Batch-ID och Lauf-ID.
• Återkörning: säkerställ idempotens (nyckel/Upsert/Import-ID).

Slutsats: Array DML är snabbt – robust blir det genom process och felstrategi

En FireDAC Bulk-Insert med Array DML är ett kraftfullt verktyg, så länge du inte låtsas att fel inte förekommer. I riktiga dataströmmar finns alltid avvikare: dubbletter, saknade referenser, korrupta datumvärden. Det korrekta förhållningssättet är därför: Array DML för prestanda, kombinerat med en kontrollerad isolationsstrategi (Split eller Fallback) och en per rad spårbar felista. På så sätt får du både tempo och driftsäkerhet – och det är vad som räknas när importer inte bara körs i labbet utan måste fungera pålitligt varje natt.

Om ni vill stabilisera en befintlig import- eller gränssnittsprocess i Delphi/FireDAC (prestanda, transaktioner, återstart, loggning), kan vi gärna reda ut det strukturerat i ett tekniskt samtal:

För detta ämne är också Delphi Bulk Insert och Bulk Insert Delphi FireDAC viktiga. Artikeln sätter in dessa aspekter i ett begripligt sammanhang och visar vad som är avgörande i praktiken.

Diskutera projekt eller moderniseringsprojekt med Net-Base.