FireDAC: Bulk-Insert s Array DML-om i preciznim rukovanjem pogreškama po retku

Jedno BDE-Ablosung mit nativer Anbindung Bulk-Insert s Array DML često je najbrži način da se veliki broj zapisa ubaci u bazu podataka: umjesto tisuću pojedinačnih Inserts veže se niz parametara i sve se pošalje serveru odjednom. U praksi se ključni problem brzo pojavi: jedan zapis krši Unique-Index, polje NOT NULL je prazno, Foreign Key ne odgovara – i iznenada nije jasno koji redak je zaustavio batch, je li dio već upisan i kako nastaviti uredno, a da se ne stvore nekonzistentnosti podataka.

Upravo o tome je riječ: kako primijeniti Array DML tako da dobiješ po retku pouzdane informacije o pogreškama, zadržiš transakciju pod kontrolom i u radu možeš rekonstruirati što se dogodilo. Fokus nije na akademskom proučavanju API-ja, nego na rubnom slučaju koji se u stvarnim importima redovito iskaže: veliki batch, nekoliko oštećenih redaka, ali želiš ipak brzinu.

FireDAC Bulk-Insert s Array DML: Zašto se Array DML uopće isplati pri Bulk-Insertu

Array DML (Data Manipulation Language) u FireDAC znači: parametre vežeš ne kao pojedinačnu vrijednost, nego kao niz. FireDAC potom šalje (ovisno o driveru/DB) manje roundtripova, može raditi efikasnije na strani servera i drastično smanjuje overhead na klijentu. To je posebno relevantno u tri situacije:

• ETL i tokovi uvoza: CSV/XML/JSON unutra, normalizacija/mapiranje, zatim u staging- ili ciljnu tablicu.
• Puferski sloj sučelja: REST- ili MQ-payloadovi se skupljaju i periodično perzistiraju.
• Tablice zapisa/događaja: mnoga mala Inserts kod kojih dominira latencija.

Dobitak međutim nije besplatan. S Array DML premještaš složenost s „mnogo pojedinačnih statementa“ na „jedan statement s mnogo redaka“. To je dobro za performanse, ali zahtjevnije za dijagnostiku pogrešaka, logiku transakcija i ponovni pokušaj.

Tipičan rubni slučaj: jedan neispravan redak u batchu

Klasik u radu: uvoziš 50.000 redaka. Odabereš ArraySize od 1.000 jer ne želiš za svaki redak roundtrip. Batch 17 zakaže. DB prijavi samo „duplicate key“ ili „violates foreign key constraint“. U UI-ju ili u service-logu često piše samo: „ExecSQL failed“.

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

• Baciš cijeli batch, iako bi 999 od 1.000 redaka bilo u redu.
• Vratiš se na pojedinačna umetanja i trajno izgubiš prednost u performansama.

Cilj je treći put: zadržati Batch-performanse, ali precizno evidentirati greške (indeks retka, ključne vrijednosti, tekst DB-pogreške) i opcionalno „good rows“ commitati – ovisno o tome koliko su dosljednost i idempotencija (više izvršavanja bez dvostrukog učinka) u tvom procesu kritični.

FireDAC Array DML: Ključne postavke (bez mitova)

Za Bulk-Insert s Array DML u praksi su uvijek iste ključne postavke odlučujuće:

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ći lockovi/log-opterećenje 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 početni izbor, ovisno o broju stupaca, BLOB-ovima i latenciji.

2) Transaktionsgrenze

Trebaš jasnu odluku: Commit po Batch ili Commit za cijeli Import. To nije stvar ukusa, nego operativna odluka:

• Commit po Batch: ograničava zaključavanja i transakcijski log, olakšava ponovno pokretanje, ali međurezultati su vidljivi (ovisno o razini izolacije). Greška u batchu 17 ostavlja batcheve 1–16 u sustavu.
• Commit na kraju: „sve ili ništa“, konzistentnije u funkcionalnom smislu, ali pri velikim količinama riskiraš duge lockove, velik rollback i u slučaju greške sve je izgubljeno.

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

3) UpdateOptions und Prepared Statements

Kod ponavljanih batcheva isplati se ostaviti statement pripremljenim. „Prepare“ znači: FireDAC dopušta DB-u da statement parsira/kompilira i ponovno koristi. Ovisno o DB-u to može imati primjetan učinak, osobito pri visokoj frekvenciji. Važno je ovdje manje „trik 17“, a više: konzistentno ponovno korištenje istog Query-objekta (ili istog TFDCommand) i stabilni tipovi parametara.

Sauberes Error-Handling pro Zeile: Was du wirklich brauchst

Ako želiš rukovati pogreškama „po retku“, trebaš tri stvari:

1. Zuordnung: koji array-indeks (0..N-1) je zakazao?
2. Kontext: koje poslovne ključne vrijednosti ima taj red (npr. eksterni ID, broj klijenta, vremenski žig)?
3. Steuerung: što radiš nakon toga? Prekinuti, samo loše retke preskočiti, ili batch razdijeliti?

FireDAC može, ovisno o driveru, vratiti pogreške po elementu niza. U praksi to ipak nije „jednostavno uvijek dostupno“. Moraš računati s tim da neke baze/pružatelji samo prijave prvu pogrešku ili da jedna pogreška u batchu uopće ne izvrši ostatak. Upravo zato je robusni obrazac obično dvostupanjski:

• Stufe A: Pokušaj batch kao Array DML.
• Stufe B: Ako batch zakaže, razdijeli ga (na pola) ili kontrolirano prijeđi na pojedinačne retke – ali samo za taj batch – i uredno logiraj.

To zvuči kao dodatni posao, ali u import-stazama je razlika između „u 02:00 sva obrada stane“ i „import prođe, 7 redova završi na listi pogrešaka“.

Praktičan obrazac: Batch zuerst, dann gezielt isolieren

Sljedeći obrazac se pokazao učinkovit za procesno bliske softverske rješenja u kojima je kvaliteta podataka mješovita:

Korak 1: Spremiti podatke u batch-strukturu (uključujući kontekst pogrešaka)

Spremi podatke za uvoz ne samo kao sirove vrijednosti, nego s minimalnim kontekstom: eksterni ID, redni broj retka iz izvora, eventualno hash/kontrolna suma. To nije ’nice to have‘: u slučaju pogreške nećeš ponovno morati parsirati CSV da bi otkrio što je pokvareno.

Korak 2: Izvršiti Array DML

Postavi ArraySize na duljinu batcha, veži parametre kao nizove i pokreni ExecSQL. Važno: održavaj stabilne tipove parametara (npr. za numerička polja ne vezuj ponekad kao String, ponekad kao Integer), inače DB proizvodi implicitne caste ili FireDAC mora za svaki element izvršavati konverziju.

Korak 3: U slučaju pogreške – suziti batch umjesto ponavljati naslijepo

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

• Binary Split (prepoloviti): podijeli batch na dvije polovice, pokušaj svaku polovicu ponovno kao Array DML. Ponavljaš to dok ne dođeš do male količine koju možeš provjeriti pojedinačno. Prednost: zadržavaš većinu performansi ako je pokvareno samo nekoliko redaka. Nedostatak: više logike, i pri sistemskim pogreškama (npr. pogrešan tip podataka) malo pomaže.
• Fallback na pojedinačne retke za ovaj batch: postavi ArraySize=1 (ili veži pojedinačne vrijednosti) i izvršavaj red po red, logiraj pogreške i nastavi. Prednost: jednostavno, garantirano po retku. Nedostatak: u ovom batchu gubiš brzinu.

U praksi kombiniram oboje: prvo 1–2 puta prepoloviti (da brzo prođeš ‚dobre blokove‘), zatim kod malih ostataka prijeći na pojedinačne retke kako bi zabilježio jasne informacije o pogreškama.

Objekti pogrešaka i poruke: što bi trebao izvući iz FireDAC

FireDAC kapsulira DB-pogreške u Exceptione (tipično EFDDBEngineException) s detaljnim informacijama. Za operativni rad važne su tri razine:

• DB-kôd pogreške (ovisno o DB): npr. SQLSTATE kod PostgreSQL, Error Number kod SQL Servera.
• Ime constrainta/objekta: često je sadržano u tekstu pogreške (Unique-Index, FK-Constraint).
• Kontekst upita: tablica, operacija, eventualno vrijednosti parametara (pažljivo s osobnim podacima).

Ako želiš logirati po retku, u slučaju pogreške osim toga moraš identificirati red. FireDAC može ponekad dati Array-Index. Međutim, ne osloni se isključivo na to. Uvijek si izgradi dodatni vlastiti indeks (pozicija u batchu) i za tu poziciju zabilježi najmanje jedan poslovni ključ.

Zamke koje u stvarnim importima oduzimaju vrijeme

1) ‚Pa samo je jedan red‘ – ali transakcija je već ‚dirty‘

Ovisno o DB-u i driveru, pogreška može uzrokovati da se cijelo izvršavanje izraza smatra neuspješnim i da je transakcija u stanju u kojem moraš eksplicitno izvršiti rollback ili u kojem daljnji upiti ne uspijevaju. Posebno kod nekih drivera ’nastaviti jednostavno nakon pogreške‘ nije sigurna pretpostavka.

Posljedica: Ako radiš unutar transakcije i batch zakaže, standardni put je: rollback trenutnog batch-konteksta (ili cijele transakcije) i potom ponovno krenuti. To se dobro uklapa s ‚commit po batchu‘.

2) Autocommit vs. eksplicitna transakcija

Ako ne pokreneš eksplicitnu transakciju, često driver/provider odlučuje kako committati statemente. Za bulk-importe to rijetko odgovara onome što želiš. Eksplicitne transakcije daju ti kontrolu nad:

• Trajanje zaključavanja
• Ponašanje rollbacka
• Točke ponovnog pokretanja

Napomena: „Izričito“ ne znači „ogromna transakcija“. Znači „svjesno“.

3) Triggeri, ograničenja i nuspojave

Array DML ubrzava prijenos, ali ne automatski rad na strani servera. Ako na ciljnoj tablici imaš triggere (npr. Audit-Logging, automatski izračun statusa), usko grlo možda uopće nije INSERT, nego kod triggera. Tada batch može imati manje roundtripa, ali CPU na DB-serveru ostaje ograničavajući faktor.

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

4) Tipovi podataka i implicitne konverzije

Jedan od najčešćih razloga „Zašto je to sporo?“: parametri se vežu kao stringovi, DB radi cast po retku u Integer/Date/Decimal. To je nevidljivo, ali skupo. Za stabilne performanse:

• Postaviti odgovarajuće tipove podataka parametara (datum kao datum, broj kao broj).
• Kod decimalnih brojeva paziti na zamke lokalizacije (zarez vs. točka). FireDAC je ovdje obično ispravno, ali miješani izvori nisu.
• Rano dogovoriti strategiju vremenskih zona/UTC (Timestamps su pri importima klasika).

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

Primamljivo je parsirati tekst pogreške („duplicate key value violates unique constraint …“). Radite to samo kao posljednju opciju. Bolji su strukturirani kodovi (SQLSTATE, Error Number). Nažalost, ne svi drajveri sve isporučuju jednako. Planiraj zato oboje: kod i tekst, plus opcionalno „naziv constrainta iz teksta“, ali bez čvrste ovisnosti.

Smjernice za otklanjanje pogrešaka: Kako brzo pronaći neispravan red

Učiniti batch reproducibilnim

Ako import povremeno zakaže, trebaš reprodukciju. Spremi po batchu malu dijagnostičku datoteku ili log-zapis koji sadrži:

• Broj batcha i vrijeme
• ArraySize i mod transakcije
• popis poslovnih ključeva (npr. vanjski ID-evi) u batchu

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

Učiniti konačni SQL vidljivim (ali bez curenja podataka)

Pri debugiranju želiš znati: Je li SQL ispravan? Jesu li parametri točni? FireDAC nudi monitoring/tracing preko FDMoni-komponenti i logiranja drajvera. U produkcijski bliskim okruženjima važno je:

• omogućiti tracing ciljano i privremeno (performanse i zaštita podataka).
• vrednosti parametara logirati samo u sigurnom okruženju ili maskirane.
• za osobne podatke: u logu samo tehnički ključevi (ID-evi) i nijedan sadržaj u čitljivom obliku.

Ako split-testiraš: definiraj kriterije za prekid

Pri binarnom splitu ne želiš beskonačno dijeliti. Postavi donju granicu, npr. „ispod 20 redova prebaciti na pojedinačni način“. I postavi limit koliko pogrešaka ukupno toleriraš prije nego prekineš import (npr. kod sistemskih problema mapiranja). Inače ćeš završiti s beskonačnim listama pogrešaka i blokirati daljnju obradu.

Kada se trud zaista isplati (a kada ne)

Array DML s rukovanjem pogrešaka po retku posebno se isplati kada:

• Mnogi redovi trebaju biti obrađeni (tisuće do milijuna).
• Mali broj redova je neispravan, ali ih ipak želiš obraditi bez prekida.
• Import mora stabilno raditi u produkciji (npr. noćna obrada, servis bez UI).
• moraš vratiti listu pogrešaka poslovnoj jedinici/izvoru (s referencijom na redove).

Manje se isplati ako:

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

Alternativna arhitektura: Staging-tablica umjesto „Direktno u cilj“

Ako se redovito boriš s miješanom kvalitetom podataka, čisti „insert direktno u ciljnu tablicu“ često je pogrešna odluka. Staging-tablica (predsloj) je tablica u koju podatke prvo tehnički ispravno pohranjuješ (po potrebi s mekšim tipovima), a tek potom validiraš i prebacuješ u ciljnu tablicu.

Prednosti u radu:

• Neispravni zapisi ostaju dostupno pohranjeni (uključujući sirove podatke).
• Validaciju možeš izvoditi odvojeno i ponovljivo.
• Odvajaš prihvat podataka na sučelju od stručne obrade.

Array DML je često brz put u staging-tablicu, dok se prijenos u ciljnu tablicu radi kao set-bazirani SQL (ili Stored Procedure). To pomiče većinu obrade pogrešaka na razinu DB-a, što ovisno o organizaciji (DBA-rolle, deploy) može biti smisleno ili neželjeno.

Rukovanje i administracija: što IT-vođe i administratori trebaju znati

Monitoring: stopa pogrešaka i propusnost su ključne metrike

Za stabilan rad bulk-importa dvije metrike govore više od same „trajnosti“:

• Propusnost: redaka po minuti (ili po batchu) uključujući vršne vrijednosti i medijan.
• Postotak pogrešaka: neispravni redci po izvođenju, idealno grupirani po klasama pogrešaka (Unique, FK, NOT NULL, konflikt tipa).

Ako redovito pratiš ove dvije vrijednosti, rano uočiš promjene u izvoru (npr. novi format) ili zaoštravanje u ciljnom sustavu (npr. novi constrainti).

Zaključavanja i vremenski prozori za opterećenje

Bulk-inserti mogu uzrokovati zaključavanja i IO-opterećenje. Ako korisnici paralelno rade na istim tablicama, moraš razmotriti razinu izolacije, indekse i eventualno particioniranje. U praksi to znači: ili stavljati importe u vremenske prozore s nižim opterećenjem, ili graditi tok podataka tako da koegzistira s radom u produkciji (npr. preko staginga + asinkrone preuzimanja).

Konkretni kontrolni popis za robustan Bulk-Insert s Array DML

• Veličina batcha postavljena (početna vrijednost 500–1.000) i mjerljivo dorađivana.
• Eksplicitna transakcija: Commit po batchu kao zadano; „Commit na kraju“ samo svjesno.
• Stabilni tipovi parametara postavljeni, bez prisiljavanja implicitnih konverzija.
• Kontekst pogreške po zapisu voditi uz podatke (vanjski ID, izvorni redak).
• Strategija pogrešaka: prvo batch, potom split/fallback, log po retku.
• Logging: kodovi + tekst, ali u skladu s GDPR; bilježiti Batch-ID i Run-ID.
• Ponovni pokušaj: osigurati idempotentnost (ključ/Upsert/Import-ID).

Zaključak: Array DML je brz – robustan postaje kroz proces i strategiju pogrešaka

Jedan FireDAC Bulk-Insert pomoću Array DML snažan je alat, sve dok se ne praviš da pogrešaka nema. U stvarnim podatkovnim tokovima uvijek ima odstupanja: duplikati, nedostajuće reference, oštećene vrijednosti datuma. Zato je čist pristup: Array DML za performanse, kombiniran s kontroliranom strategijom izolacije (Split ili Fallback) i listom pogrešaka koja se može pratiti po retku. Na taj način dobivaš brzinu i operativnu pouzdanost zajedno – i upravo je to bitno kad importi ne rade samo u laboratoriju, nego moraju svake noći pouzdano proći.

Ako želite stabilizirati postojeći proces uvoza ili sučelja u Delphi/FireDAC (performanse, transakcije, ponovno pokretanje, logiranje), razjasnit ćemo to rado strukturirano u tehničkom razgovoru:

Za ovu temu su također važni Delphi Bulk Insert i Bulk Insert Delphi FireDAC. Članak razumljivo stavlja te aspekte u kontekst i pokazuje na što treba paziti u svakodnevnom radu.

Razgovarajte o projektu ili planu modernizacije s Net-Base.