FireDAC: Bulk-Insert sa Array DML i pouzdanim rukovanjem greškama po retku

Jedan BDE-zamjena s izvornom vezom Bulk-Insert s Array DML često je najbrži način da mnogo zapisa stigne u bazu podataka: umjesto tisuću pojedinačnih INSERT-ova vežeš parametre kao niz i u jednom potezu šalješ serveru. U praksi se problem brzo pojavi: neki zapis krši Unique-index, polje označeno kao NOT NULL je prazno, strani ključ ne odgovara – i odjednom nije jasno koja je tačno linija ubila batch, je li dio već zapisan i kako nastaviti na čist način bez stvaranja nekonzistentnosti podataka.

Upravo o tome se radi ovdje: kako koristiš Array DML tako da dobiješ po redu pouzdane informacije o greškama, zadržiš kontrolu nad transakcijom i u produkciji možeš rekonstruirati šta se dogodilo. Fokus nije na akademskom proučavanju API-ja, nego na rubnom slučaju koji se u stvarnim importima redovno javlja: veliki batch, nekoliko neispravnih redova, ali želiš zadržati brzinu.

FireDAC Bulk-Insert mit Array DML: Warum Array DML beim Bulk-Insert überhaupt lohnt

Array DML (Data Manipulation Language) u FireDAC znači: parametre ne vežeš kao pojedinačne vrijednosti, nego kao niz. FireDAC tada (ovisno o drajveru/DB) šalje manje roundtrip-ova, može na serverskoj strani efikasnije raditi i drastično smanjuje overhead na klijentu. To je posebno relevantno u tri situacije:

• ETL- i import-putanje: CSV/XML/JSON ulazi, normalizacija/mapiranje, pa u staging ili ciljnu tabelu.
• Skladište poruka / bafer interfejsa: MQ-payloads ili poruke se skupljaju i periodično spremaju.
• Tablice protokola/događaja: mnogo malih INSERT-ova gde dominira latencija.

Dobit se, međutim, ne dobija besplatno. Sa Array DML pomjeraš kompleksnost sa „mnogo pojedinačnih naredbi“ na „jedna naredba sa mnogo redova“. To je dobro za performanse, ali zahtijeva više u pogledu diagnostike grešaka, logike transakcija i ponovnog pokretanja.

Tipičan rubni slučaj: jedan batch, jedan neispravan red

Klasičan scenarij u radu: importuješ 50.000 redova. Postaviš ArraySize na 1.000 jer ne želiš roundtrip za svaki red. Batch 17 zakaže. Baza prijavljuje samo „duplicate key“ ili „violates foreign key constraint“. U UI-ju ili u service-logu često stoji samo: „ExecSQL failed“.

Bez urednog rukovanja greškama obično se dogode dvije loše stvari:

• odbaciš cijeli batch, iako je 999 od 1.000 redova bilo ispravno.
• vratiš se na pojedinačne INSERT-e i trajno izgubiš prednost u performansama.

Cilj je treći put: zadržati Batch-performansu, ali precizno evidentirati kvarove (indeks reda, vrijednosti ključeva, tekst DB greške) i opcionalno „dobrih redova“ commit-ovati – ovisno o tome koliko su konzistentnost i idempotencija (višestruko izvršavanje bez duplog efekta) kritični u tvom procesu.

FireDAC Array DML: Die relevanten Stellschrauben (ohne Mythen)

Za Bulk-Insert s Array DML u praksi su uvijek isti parametri odlučujući:

1) ArraySize und Batch-Größe

ArraySize (bei TFDQuery/TFDCommand) određuje koliko „redova“ FireDAC se obrađuje u jednom pozivu. Veće nije automatski bolje. Preveliko znači: više memorije na klijentu, veći payload na mreži, veća zaključavanja/transakcioni log na serveru i u slučaju greške veći „Blast Radius“. Za robusne importe često je veličina batcha između 200 und 2.000 dobar polazni položaj, ovisno o broju kolona, BLOBs i latenciji.

2) Transaktionsgrenze

Trebaš jasnu odluku: Commit pro Batch ili Commit für den gesamten Import. To nije stvar ukusa, već operativna odluka:

• Commit pro Batch: ograničava zaključavanja i transakcioni log, omogućava jednostavniji ponovni pokušaj, ali međurezultati su vidljivi (ovisno o nivou izolacije). Greška u Batch 17 ostavlja Batch 1–16 u sistemu.
• Commit am Ende: „Sve ili ništa“, konzistentnije u poslovnom smislu, ali kod velikih količina riskiraš duge zaključke (Locks), veliko vraćanje transakcije (Rollback) i u slučaju greške sve može biti izgubljeno.

Za mnoge interfejse i import procese je „Commit pro Batch“ realističnija operativna strategija – ali samo ako si idempotenciju i strategiju za duplikate jasno riješio (npr. preko prirodnih ključeva, Upserts ili import-ID).

3) UpdateOptions und Prepared Statements

Kod ponavljanih batchova isplati se držati statement pripremljenim. „Prepare“ znači: FireDAC dopušta DB da parsira/kompajlira statement i ponovno ga koristi. Ovisno o DB to može imati opipljiv efekt, posebno pri visokoj frekvenciji. Važno nije tražiti „trik 17“, već: dosljedna ponovna upotreba istog Query-objekta (ili istog TFDCommand) i stabilni tipovi parametara.

Sauberes Error-Handling pro Zeile: Was du wirklich brauchst

Ako želiš rukovati greškama „po redu“, trebaš tri stvari:

1. Identifikacija: Koji Array-indeks (0..N-1) je otkazao?
2. Kontext: Koje poslovne ključne vrijednosti ima taj red (npr. eksterni ID, broj klijenta, vremenski pečat)?
3. Steuerung: Šta radiš nakon toga? Prekidaš, preskačeš samo loše redove, ili razbijaš batch?

FireDAC može, ovisno o driveru, vratiti greške po elementu niza. U praksi to nije uvijek dostupno. Moraš računati da neke baze/provjajderi prijavljuju samo prvu grešku ili da greška u batchu uopće ne izvrši ostatak. Upravo zato je robustan obrazac obično dvostupanjski:

• Stufe A: Pokušaj batch kao Array DML.
• Stufe B: Ako batch zakaže, razdijeli ga (na pola) ili kontrolisano padni na pojedinačne redove – ali samo za taj batch – i uredno loguj.

Zvuci kao dodatni rad, ali u import tokovima to je razlika između „u 02:00 sve stane“ i „import prođe kroz, 7 redova završi na listi grešaka“.

Ein praxistaugliches Muster: Batch zuerst, dann gezielt isolieren

Sljedeći obrazac se pokazao pouzdanim za softverska rješenja bliska procesu, u kojima je kvaliteta podataka mješovita:

Korak 1: Podatke smjestiti u batch-strukturu (uključujući kontekst greške)

Spremite podatke za uvoz ne samo kao sirove vrijednosti, već s minimalnim kontekstom: eksterni ID, broj reda iz izvora, eventualno Hash/Checksumme. Ovo nije „Nice to have“: u slučaju greške ne želite prvo ponovo parsirati CSV da biste saznali šta je pokvareno.

Korak 2: Izvršiti Array DML

Postavite ArraySize na dužinu batcha, vežete parametre kao nizove i izvršavate ExecSQL. Važno: održavajte stabilne tipove parametara (npr. za numerička polja ne vezivati ponekad kao String, ponekad kao Integer), inače DB proizvodi implicitne konverzije ili FireDAC mora svakoj stavci pretvarati.

Korak 3: U slučaju greške – suzite batch umjesto da ga slijepo ponavljate

Ako ExecSQL zakaže, imaš dvije robusne opcije:

• Binary Split (prepoloviti): podijeliti batch na dvije polovice, svaku polovicu ponovo pokušati kao Array DML. To ponavljate dok ne dođete do male količine koju možete provjeriti pojedinačno. Prednost: zadržavate većinu performansi ako je pokvareno samo nekoliko redova. Nedostatak: više logike, i kod sistemskih grešaka (npr. pogrešan tip podataka) to malo pomaže.
• Fallback auf Einzelzeilen za ovaj batch: postavite ArraySize=1 (ili vežete pojedinačne vrijednosti) i izvršavate red po red, logujete greške i nastavljate. Prednost: jednostavno, garantovano po redu. Nedostatak: u ovom batchu gubite brzinu.

U praksi kombinujem oboje: prvo 1–2 puta podijeliti (da brzo prođete „dobre blokove“), zatim kod malih ostataka preći na pojedinačne redove kako biste zabilježili jasne informacije o greškama.

Objekti grešaka i poruke: šta treba izvući iz FireDAC

FireDAC kapsulira DB- greške u Exceptions (tipično EFDDBEngineException) sa detaljnim informacijama. Za rad su važne tri razine:

• DB-kod greške (specifičan za DB): npr. SQLSTATE kod PostgreSQL, Error Number kod SQL Servera.
• Naziv constrainta/objekta: često sadržan u tekstu greške (Unique-Index, FK-Constraint).
• Kontekst upita: tabela, operacija, eventualno vrijednosti parametara (pažljivo sa ličnim podacima).

Ako želiš logovati po redu, u slučaju greške moraš dodatno identifikovati red. FireDAC može u nekim slučajevima vratiti indeks niza. Međutim, nemoj se isključivo na to oslanjati. Uvijek dodaj svoj vlastiti indeks (pozicija u batchu) i za tu poziciju zabilježi najmanje jedan poslovni ključ.

Zamke koje u stvarnim importima oduzimaju vrijeme

1) „Pa bio je to samo jedan red“ – ali transakcija je već „dirty“

Ovisno o DB-u i driveru, greška može dovesti do toga da se cijelo izvršavanje upita smatra neuspjelim i da je transakcija u stanju u kojem moraš eksplicitno izvršiti rollback ili u kojem dalji upiti neće proći. Posebno kod nekih drivera, „nakon greške jednostavno nastaviti“ nije sigurna pretpostavka.

Posljedica: Ako radiš unutar transakcije i batch zakaže, standardni put je: Rollback trenutnog batch-konteksta (ili cijele transakcije) i potom ponovo pokrenuti. To se dobro uklapa sa „Commit po batchu“.

2) Autocommit vs. eksplicitna transakcija

Ako ne pokrećeš eksplicitnu transakciju, često driver/provider odlučuje kako će se upiti commitovati. Za bulk-importe to rijetko odgovara onome što želiš. Eksplicitne transakcije ti daju kontrolu nad:

• trajanje zaključavanja
• Ponašanje rollbacka
• Tačke ponovnog pokretanja

I: Eksplizitno ne znači „ogromna transakcija“. Znači „svjesno“.

3) Trigger, Constraints i nuspojave

Array DML ubrzava prijenos, ali ne nužno i server-side rad. Ako na ciljnoj tablici imaš triggere (npr. audit-logging, automatski izračun statusa), onda usko grlo možda nije Insert, već kod u triggeru. Tada batch može imati manje Roundtrips, ali CPU na DB-Serveru ostaje limitirajući faktor.

Za administratore i tehničke leadove: Pri problemima s performansama vrijedi pogledati Wait Events/Locks i transakcijski log. Bulk-Insert je tada samo okidač, ne i uzrok.

4) Tipovi podataka i implicitne konverzije

Jedan od najčešćih razloga za pitanje „Zašto je sporo?“: parametri se vežu kao stringovi, a DB per red castuje u Integer/Date/Decimal. To je nevidljivo, ali skupo. Za stabilne performanse:

• Postavi odgovarajuće tipove podataka za parametre (datum kao datum, broj kao broj).
• Kod decimala pazi na zamke vezane uz locale (zarez vs. tačka). FireDAC je ovdje najčešće ispravno, ali mješoviti izvori nisu.
• Unaprijed razjasni strategiju vremenskih zona/UTC (timestampi su pri importima klasičan izvor problema).

5) Tekstovi grešaka su za ljude, ali ne i za automatizaciju

Primamljivo je parsirati tekst greške („duplicate key value violates unique constraint …“). Radi to samo kao posljednju opciju. Bolje su strukturirani kodovi (SQLSTATE, Error Number). Nažalost, ne daju svi drajveri sve jednako dobro. Planiraj stoga oba: kod i tekst, plus opcionalno „naziv ograničenja iz teksta“, ali bez čvrste ovisnosti.

Savjeti za debugiranje: Kako brzo pronaći neispravni red

Učiniti batch reproducibilnim

Ako import povremeno zakaže, treba ti reprodukcija. Sačuvaj za svaki batch malu dijagnostičku datoteku ili log-unos koji sadrži:

• Broj batcha i vremensku oznaku
• ArraySize i mod transakcije
• listu poslovnih ključeva (npr. eksterni ID-evi) u batchu

To često dovoljno da naknadno ciljano pokreneš mini-import samo za te ID-ove.

Učiniti finalni SQL vidljivim (ali bez curenja podataka)

Pri debugiranju želiš znati: Je li SQL ispravan? Jesu li parametri točni? FireDAC pruža monitoring/tracing preko FDMoni-Komponenten i drajver-logginga. U produkcijski bliskim okruženjima važno je:

• Tracing ciljano uključiti samo privremeno (performanse i zaštita podataka).
• Vrijednosti parametara logovati samo u sigurnom okruženju ili maskirane.
• Za osobne podatke: u logu samo tehnički ključevi (ID-evi) i nikakvi podaci u čistom tekstu.

Ako radiš split-test: definiraj kriterije prekida

Pri binary splitu ne želiš beskonačno dijeliti. Postavi donju granicu, npr. „ispod 20 redova switch na pojedinačni modus“. I postavi limit koliko grešaka ukupno toleriraš prije nego prekineš import (npr. kod sistemskih mapping-problema). Inače ćeš završiti s beskonačnim listama grešaka i blokirati dalju obradu.

Kada se trud stvarno isplati (i kada ne)

Array DML s rukovanjem greškama po redu posebno se isplati kada:

• Velik broj redova se obrađuje (hiljade do miliona).
• Samo manji broj redova je grešan, ali želiš da proces nastavi.
• Import mora u produkciji raditi stabilno (npr. noćna obrada, servis bez UI).
• Trebaš vratiti listu grešaka poslovnom odjelu/izvoru (s referencom na redove).

Manje se isplati ako:

• ti pišeš samo nekoliko desetaka redova (pojedinačni inserti su u redu),
• kvalitet podataka je toliko loš da 30–50% redova ne uspije (tada je staging-strategija smislenija),
• ionako koristiš DB-native bulk-load postupak (npr. COPY in PostgreSQL, BCP/BULK INSERT in SQL Server) – tada Array DML nije odgovarajuće sredstvo.

Alternative Architektur: staging-tabela statt „Direkt ins Ziel”

Ako se redovno boriš s mješovitim kvalitetom podataka, čisto „insert direktno u ciljnu tabelu“ često je pogrešna odluka. staging-tabela (predsloj) je tabela u koju podatke prvo tehnički ispravno pohraniš (po potrebi s mekšim tipovima), i tek potom validiraš i prebacuješ u ciljnu tabelu.

Prednosti u radu:

• Neispravni zapisi ostaju pregledno pohranjeni (uključujući sirove podatke).
• Validaciju možeš izvoditi odvojeno i ponovljivo.
• Razdvajate prihvat sučelja od poslovne obrade.

Array DML je često brz put u staging-tabelu, dok se prijenos u ciljnu tabelu vrši kao set-bazirani SQL (ili Stored Procedure). To pomjera obradu grešaka više na DB-razinu, što ovisno o organizaciji (DBA-uloge, deployment) može biti poželjno ili neželjeno.

Betrieb und Administration: Was IT-Leads und Admins dazu wissen sollten

Monitoring: Fehlerquote und Durchsatz sind die Kernmetriken

Za stabilan rad bulk-importa dvije metrike govore više od same „vrijeme izvođenja“:

• Propusnost: redova po minuti (ili po batchu) uključujući peak/median.
• Stopa grešaka: neispravni redovi po pokretanju, idealno grupisani po klasama grešaka (Unique, FK, NOT NULL, konflikt tipova).

Ako redovno pratiš ove dvije vrijednosti, rano ćeš uočiti da li se nešto promijenilo na izvoru (npr. novi format) ili je ciljni sistem postao stroži (npr. novi constrainti).

Sperren und Lastfenster

Bulk-inserti mogu izazvati zaključavanja i IO-opterećenje. Ako paralelno korisnici rade na istim tabelama, moraš razmotriti isolation level, indekse i eventualno particioniranje. Prakticno to znači: ili planirati importe u prozore sa slabijim opterećenjem, ili graditi tok podataka tako da koegzistira s produkcijom (npr. preko staginga + asinkronog preuzimanja).

Konkrete Checkliste für einen robusten Bulk-Insert mit Array DML

• Batch-Größe odrediti (startna vrijednost 500–1.000) i mjerljivo tunirati.
• Explizite Transaktion: Commit po batchu kao zadano; „Commit am Ende“ samo svjesno.
• Parametertypen stabil postaviti, ne forsirati implicitne konverzije.
• Fehlerkontext pro Datensatz voditi (eksterni ID, izvorni red).
• Fehlerstrategie: prvo batch, zatim split/fallback, logovanje po redu.
• Logging: kodovi + tekst, ali u skladu s zaštitom podataka; evidentirati Batch-ID i Run-ID.
• Wiederanlauf: osigurati idempotenciju (ključ/Upsert/Import-ID).

Fazit: Array DML ist schnell – robust wird es durch Prozess und Fehlerstrategie

Ein FireDAC Bulk-Insert mit Array DML ist ein starkes Werkzeug, solange du nicht so tust, als gäbe es keine Fehler. In echten Datenströmen gibt es immer Ausreißer: Dubletten, fehlende Referenzen, kaputte Datumswerte. Der saubere Ansatz ist deshalb: Array DML für die Performance, kombiniert mit einer kontrollierten Isolationsstrategie (Split oder Fallback) und einer pro Zeile nachvollziehbaren Fehlerliste. Damit bekommst du Tempo und Betriebssicherheit zusammen – und genau das zählt, wenn Imports nicht nur im Lab laufen, sondern jede Nacht zuverlässig durch müssen.

Wenn ihr einen bestehenden Import- oder Schnittstellenprozess in Delphi/FireDAC stabilisieren wollt (Performance, Transaktionen, Wiederanlauf, Logging), klären wir das gern strukturiert im technischen Gespräch:

Für dieses Thema sind auch Delphi Bulk Insert und Bulk Insert Delphi FireDAC wichtig. Der Beitrag ordnet diese Aspekte verständlich ein und zeigt, worauf es im Alltag ankommt.

Projekt oder Modernisierungsvorhaben mit Net-Base besprechen.