FireDAC: Bulk-Insert z Array DML in dosledno obravnavanje napak po vrstici

Ena BDE-zamenjava z nativno vezavo Bulk-Insert mit Array DML je pogosto najhitrejša pot, da veliko zapisov vnesete v podatkovno bazo: namesto tisoč posameznih Inserts se veže parameter-Array in pošlje strežniku naenkrat. V praksi pa hitro pride do težave: en zapis krši Unique-Index, polje NOT NULL je prazno, tuji ključ ne ustreza – in nenadoma ni jasno, katera vrstica je paket ustavila, ali je del že zapisan in kako naprej, brez ustvarjanja podatkovnih inkonzistenc.

Tukaj gre ravno za to: kako uporabljaš Array DML tako, da dobiš za vsako vrstico zanesljive informacije o napakah, ohraniš nadzor nad transakcijo in v obratovanju lahko slediš, kaj se je zgodilo. Osrednje ni akademsko branje API-jev, temveč robni primer, ki se v resničnih uvozih pogosto pojavi: velik paket, nekaj pokvarjenih vrstic, vendar želiš kljub temu hitrost.

FireDAC Bulk-Insert z Array DML: Zakaj se Array DML sploh izplača pri Bulk-Insertu

Array DML (Data Manipulation Language) pomeni v FireDAC: parametre vežeš ne kot posamezno vrednost, temveč kot polje. FireDAC nato (odvisno od Treiber/DB) pošlje manj Roundtrips, lahko strežniško dela bolj učinkovito in drastično zmanjša overhead na klientu. To je v treh situacijah še posebej relevantno:

• ETL- in uvozne poti: CSV/XML/JSON noter, normalizacija/mapping, nato v staging- ali ciljno tabelo.
• Predpomnilnik vmesnikov: REST- oder MQ-Payloads se zbirajo in jih periodično trajno zapišeš.
• Protokolne-/Event-tabele: veliko majhnih Inserts, kjer dominira latenca.

Prihranek pa ni brez cene. Z Array DML premakneš kompleksnost iz „mnogih posameznih stavkov“ v „en stavek z mnogimi vrsticami“. To je dobro za Performance, vendar zahtevnejše za Fehlerdiagnose, Transaktionslogik in Wiederanlauf.

Der typische Randfall: Ein Batch, eine kaputte Zeile

Klasični primer v obratovanju: uvažaš 50.000 vrstic. Izbereš ArraySize von 1.000, ker ne želiš za vsako vrstico enega Roundtripa. Batch 17 spodleti. Die DB meldet nur „duplicate key“ oder „violates foreign key constraint“. V UI ali v servisnem dnevniku pogosto piše le: „ExecSQL failed“.

Brez urejenega Error-Handlinga se potem običajno zgodita dve slabi stvari:

• Zavržeš celoten Batch, čeprav bi bilo 999 od 1.000 vrstic v redu.
• Vrneš se na Einzel-Inserts in trajno izgubiš Performancevorteil.

Cilj je tretja pot: ohraniti zmogljivost paketne obdelave, hkrati pa natančno beležiti napake (indeks vrstice, ključne vrednosti, besedilo DB-napake) in po potrebi izvesti commit za „dobre vrstice“ – odvisno od tega, kako kritični sta doslednost in idempotentnost (večkratno izvajanje brez dvojnega učinka) v tvojem procesu.

FireDAC Array DML: relevantne nastavitve (brez mitov)

Pri množičnem vnašanju (Bulk-Insert) z Array DML so v praksi vedno iste ključne nastavitve odločilne:

1) ArraySize in velikost batcha

ArraySize (pri TFDQuery/TFDCommand) določa, koliko „vrst“ FireDAC se obdela v enem klicu. Več ni nujno bolje. Preveliko pomeni: več pomnilnika na klientu, večji promet po omrežju, večje locke/log-obremenitev na strežniku in v primeru napake večji „blast radius“. Za robustne importe je pogosto dobra začetna velikost batcha med 200 in 2.000, odvisno od števila stolpcev, BLOB-ov in latence.

2) Transakcijska meja

Potrebna je jasna odločitev: commit na paket ali commit za celoten uvoz. To ni stvar okusa, temveč operativna odločitev:

• Commit na paket: omeji zaklepe in log transakcij, poenostavi ponovno zaganjanje, vendar so vmesni rezultati vidni (odvisno od Isolation Level). Napake v paketu 17 pustijo pakete 1–16 v sistemu.
• Commit na koncu: „vse ali nič“, bolj konsistentno z vidika poslovne logike, vendar pri velikih količinah tvegaš dolge zaklepe, velik rollback in v primeru napake izgubiš vse.

Za mnoge vmesnike in uvozne procese je „commit na paket“ bolj realistična operativna strategija – a le, če imaš idempotentnost in strategijo za dvojčke (npr. preko naravnih ključev, upsert-ov ali import-ID) urejeno.

3) UpdateOptions in pripravljeni stavki

Pri ponavljajočih se paketih se splača pustiti stavek pripravljen. „Prepare“ pomeni: FireDAC pusti DB, da stavek parsira/kompilira in ga ponovno uporabi. Glede na DB ima to lahko občuten učinek, predvsem pri visoki frekvenci. Tukaj ni govor o „triku 17“, ampak o: dosledni ponovni uporabi istega Query-objekta (ali istega TFDCommand) in stabilnih tipih parametrov.

Dosledno upravljanje napak po vrstici: kaj res potrebuješ

Če želiš napake obravnavati „po vrstici“, potrebuješ tri stvari:

1. Dodelitev: kateri indeks v polju (0..N-1) je spodletel?
2. Kontekst: katere poslovne/ključno-identifikacijske vrednosti ima ta vrstica (npr. zunanja ID, številka stranke, časovni žig)?
3. Nadzor: kaj narediš potem? Prekineš, preskočiš samo slabe vrstice ali razdeliš batch?

FireDAC lahko glede na gonilnik vrne napake po posameznem elementu polja. V praksi pa to ni vedno na voljo. Moraš računati, da nekatere baze/driverji poročajo le prvo napako ali da napaka v batchu prepreči izvedbo preostalih elementov. Ravno zato je robusten vzorec ponavadi dvostopenjski:

• Stopnja A: Poskusi batch kot Array DML.
• Stopnja B: Če batch spodleti, ga razdeli (na pol) ali preide kontrolirano na obdelavo posameznih vrstic – vendar le za ta batch – in natančno zabeleži napake.

Zveni kot dodatno delo, vendar je v uvoznih tokovih razlika med „ob 02:00 zjutraj se vse ustavi“ in „uvoz teče naprej, 7 vrstic pristane na seznamu napak“.

Praktičen vzorec: najprej batch, potem ciljno izoliranje

Naslednji vzorec se je izkazal za uporaben pri procesno povezanih programskih rešitvah, kjer je kakovost podatkov mešana:

Korak 1: Pakiranje podatkov v Batch-Struktur (inkl. Fehlerkontext)

Shrani podatke, ki jih boš uvozil, ne le kot surove vrednosti, ampak z minimalnim kontekstom: zunanja ID, številka vrstice iz vira, po potrebi hash/checksum. To ni „Nice to have“: v primeru napake nočeš najprej ponovno parsirati CSV, da bi ugotovil, kaj je pokvarjeno.

Korak 2: Array DML ausführen

Nastaviš ArraySize na dolžino batcha, bindas parametre kot polja in izvedeš ExecSQL. Pomembno: ohrani stabilne tipe parametrov (npr. za numerična polja jih ne veži včasih kot String, včasih kot Integer), sicer baza ustvari implicitne caste ali pa FireDAC mora pretvarjati po elementu.

Korak 3: Fehlerfall – Batch eingrenzen statt blind wiederholen

Če ExecSQL spodleti, imaš dve robustni možnosti:

• Binary Split (halbieren): razdeliš batch na dve polovici in poskusiš vsako polovico znova z Array DML. To ponavljaš, dokler ne prideš do majhne množine, ki jo lahko preveriš posamezno. Prednost: ohraniš veliko zmogljivost, če je poškodovanih le nekaj vrstic. Slabost: več logike, in pri sistemskih napakah (npr. napačen tip podatkov) prinaša malo koristi.
• Fallback auf Einzelzeilen za ta batch: nastaviš ArraySize=1 (ali vežeš posamezne vrednosti) in izvajaš vrstico za vrstico, beležiš napake in nadaljuješ. Prednost: enostavno, zagotavlja obravnavo po vrstici. Slabost: v tem batchu izgubiš hitrost.

V praksi kombiniram oboje: najprej 1–2‑krat razdelim (da hitro obdelam „dobre bloke“), nato pri majhnih ostankih preidem na posamezne vrstice, da zabeležim jasne informacije o napakah.

Fehlerobjekte und Meldungen: Was du aus FireDAC herausziehen solltest

FireDAC zapakira napake DB v izjeme (tipično EFDDBEngineException) z dodatnimi podrobnostmi. Za obratovanje so pomembne tri ravni:

• DB-Fehlercode (db-spezifisch): npr. SQLSTATE pri PostgreSQL, Error Number pri SQL Server.
• Constraint-/Objektname: pogosto vključen v besedilo napake (Unique-Index, FK-Constraint).
• Statement-Kontext: tabela, operacija, po potrebi vrednosti parametrov (pri osebnih podatkih previdno).

Če želiš beležiti po vrstici, moraš v primeru napake dodatno identificirati vrstico. FireDAC lahko v določenih primerih vrne indeks v polju. Vendar se ne zanašaj izključno na to. Vedno si dodaj lasten indeks (pozicija v batchu) in k tej poziciji zabeleži vsaj en poslovni ključ.

Fallstricke, die in echten Imports Zeit kosten

1) „Es war doch nur eine Zeile“ – aber die Transaktion ist schon „dirty“

Glede na bazo in gonilnik lahko napaka povzroči, da se celotna izvedba stavka obravnava kot neuspešna in transakcija ostane v stanju, v katerem jo moraš bodisi eksplicitno razveljaviti ali pa bodo nadaljnji stavki neuspešni. Pri nekaterih gonilnikih torej ni varno predpostavljati, da se lahko „po napaki preprosto nadaljuje“.

Posledica: če delaš v transakciji in batch spodleti, je standardni postopek: Rollback des aktuellen Batch-Kontexts (ali celotne transakcije) in nato ponovno začeti. To se dobro ujema s „Commit pro Batch“.

2) Autocommit vs. explizite Transaktion

Če ne zaženeš eksplicitne transakcije, pogosto gonilnik/provider odloča, kako committati stavke. Za Bulk-Imports je to redko tisto, kar želiš. Eksplicitne transakcije ti dajejo nadzor nad:

• Lock-Dauer
• Vedenje rollbacka
• Točke ponovnega zagona

In: „Izrecno“ ne pomeni „ogromna transakcija“. Pomeni „zavestno“.

3) Trigger, Constraints und Nebenwirkungen

Array DML pospeši prenos, vendar ne avtomatično delo na strani strežnika. Če imaš na ciljni tabeli triggerje (npr. audit-logging, samodejno izračunavanje statusa), potem ozko grlo morda sploh ni INSERT, temveč koda triggerja. V takem primeru ima batch lahko manj Roundtrips, a CPU na DB-strežniku ostane omejujoči dejavnik.

Za admin-e in tehnične leade: pri težavah z zmogljivostjo se splača pogledati Wait Events/Locks in transakcijski log. Bulk-Insert je pogosto le sprožilec, ne vzrok.

4) Datentypen und implizite Konvertierungen

Eden pogostejših razlogov za vprašanje „Zakaj je to počasi?“: parametri so vezani kot stringi, DB pa za vsako vrstico naredi pretvorbo v Integer/Date/Decimal. To je nevidno, a drago. Za stabilno zmogljivost:

• Nastavi ustrezne tipe parametrov (datum kot datum, število kot število).
• Pri decimalnih vrednostih bodi pozoren na pasti glede locale (vejica vs. pika). FireDAC je tu praviloma pravilen, medtem ko mešani viri niso.
• Predhodno razjasni strategijo časovnih pasov/UTC (timestampi so pri uvozih klasika).

5) Fehlertexte sind für Menschen, aber nicht für Automatisierung

Vse je mamljivo: parsati tekst napake („duplicate key value violates unique constraint …“). Naredi to le kot zadnjo možnost. Boljše so strukturirane kode (SQLSTATE, številka napake). Na žalost ne vsi gonilniki zagotavljajo vse enako dobro. Zato predvidi oboje: kodo in besedilo, plus po želji „ime Constraint-a iz besedila“, vendar brez trde odvisnosti.

Debugging-Hinweise: So findest du schnell die kaputte Zeile

Batch reproduzierbar machen

Če uvoz občasno odpove, potrebuješ reproducibilnost. Shrani za vsak batch majhno diagnostično datoteko ali vnos v log, ki vsebuje:

• Številko batcha in čas
• ArraySize in način transakcije
• seznam poslovnih ključev (npr. zunanje ID-je) v batchu

To pogosto zadošča, da kasneje ciljno zaženeš mini-uvoz samo za te ID-je.

Finale SQL sichtbar machen (aber ohne Datenleaks)

Pri odpravljanju napak želiš vedeti: Je SQL pravilen? So parametri pravilni? FireDAC ponuja monitoring/tracing preko FDMoni-komponent in beleženja gonilnikov. V produkcijsko bližnjih okoljih je pomembno:

• Tracing selektivno vklopi le začasno (zmogljivost in varstvo podatkov).
• Vrednosti parametrov beleži le v varnem okolju ali z zamaskiranimi vrednostmi.
• Pri osebnih podatkih: v dnevnik le tehnični ključi (ID-ji) in brez vsebine v jasnem besedilu.

Wenn du split-testest: Abbruchkriterien definieren

Pri binarnem deljenju nočeš deliti v nedogled. Določi spodnjo mejo, npr. „pod 20 vrstic preklopi na enojni način“. In nastavi limit, koliko napak skupno toleriraš, preden prekineš uvoz (npr. pri sistematičnih mapping-problemih). Sicer se zatakneš v neskončne sezname napak in blokiraš nadaljnjo obdelavo.

Wann sich der Aufwand wirklich lohnt (und wann nicht)

Array DML z obravnavo napak po vrstici se posebej izplača, kadar:

• Veliko vrstic se obdela (tisoči do milijonov).
• Le nekaj vrstic je z napakami, a jih vseeno želiš obdelati.
• Uvoz mora v obratovanju teči stabilno (npr. nočno procesiranje, servis brez UI).
• Se moraš vrniti seznam napak oddelku/viru (s sklicem na vrstice).

Se to manj izplača, če:

• pišeš samo nekaj deset vrstic (posamezni inserti so v redu),
• je kakovost podatkov tako slaba, da 30–50 % vrstic spodleti (v tem primeru je smiselnejša staging-strategija),
• tako ali tako uporabljaš DB-nativno metodo za množične vnose (npr. COPY v PostgreSQL, BCP/BULK INSERT v SQL Server) – potem Array DML ni ustrezno orodje.

Alternativna arhitektura: Staging-tabela namesto „takojšnje v cilj“

Če se pogosto boriš z mešano kakovostjo podatkov, je neposreden „insert v ciljno tabelo“ pogosto napačna izbira. Staging-tabela (predstopnja) je tabela, v katero najprej tehnično pravilno shraniš podatke (po potrebi z mehkimi tipi) in jih šele nato validiraš ter prevedeš v ciljno tabelo.

Prednosti v obratovanju:

• Nepravilni zapisi ostanejo sledljivo shranjeni (vključno z izvornimi podatki).
• Validacijo lahko izvedeš ločeno in večkrat.
• Ločiš sprejem vmesnikov od strokovne obdelave.

Array DML je pogosto hiter način v Staging-tabelo, medtem ko se prenos v ciljno tabelo izvede kot set‑bazirano SQL (ali shranjena procedura). To premakne obravnavo napak bolj na stran DB, kar je glede na organizacijo (DBA-vloge, deployment) lahko smiselno ali nezaželeno.

Obratovanje in administracija: kaj bi morali vedeti IT-vodje in skrbniki

Nadzor: delež napak in prepustnost sta ključni metri

Za stabilno obratovanje množičnega uvoza sta dve metrikI bolj povedni kot samo „čas izvajanja“:

• Prepustnost: vrstic na minuto (ali na batch), vključno s peak/median.
• Delež napak: napačne vrstice na zagon, idealno razvrščene po razredih napak (Unique, FK, NOT NULL, konflikt tipov).

Če redno spremljaš ti dve vrednosti, hitro zaznaš, ali se je na viru kaj spremenilo (npr. nov format) ali ali je ciljni sistem (npr. nove constraints) postal strožji.

Zaklepi in časovna okna obremenitve

Bulk-Inserti lahko povzročijo zaklepe in IO-obremenitev. Če hkrati uporabniki delajo na istih tabelah, moraš razmisliti o isolation level, indeksih in po potrebi o particioniranju. V praksi to pomeni: bodisi umestiti uvoze v časovna okna z nižjo obremenitvijo, bodisi zgraditi podatkovni tok tako, da koeksistira z obratovanjem (npr. preko staginga + asinkrone prevzeme).

Konkretni kontrolni seznam za robusten Bulk-Insert z Array DML

• Velikost batcha določiti (izhodišče 500–1.000) in jo merljivo ugašati.
• Eksplicitna transakcija: commit na batch kot privzeto, „commit na koncu“ le zavedno.
• Stabilni tipi parametrov nastaviti, ne siliti implicitnih pretvorb.
• Kontekst napake za vsak zapis voditi (zunanja ID, izvorna vrstica).
• Strategija obravnave napak: najprej batch, nato split/fallback, beleženje po vrstici.
• Logging: kode + besedilo, vendar skladno z varstvom podatkov; Batch-ID in Lauf-ID zabeležiti.
• Ponovni zagon: zagotoviti idempotentnost (ključ/Upsert/Import-ID).

Zaključek: Array DML je hiter – robusten ga naredijo proces in strategija obravnave napak

En FireDAC Bulk-Insert z Array DML je močno orodje, dokler se ne pretvarjaš, da napak ni. V pravih podatkovnih tokovih vedno pride do odstopanj: podvojitve, manjkajoče reference, okvarjene datumske vrednosti. Čist pristop je zato: Array DML za zmogljivost, kombiniran s kontrolirano strategijo izolacije (Split ali Fallback) in s sledljivim seznamom napak za vsako vrstico. Tako združiš hitrost in operativno zanesljivost – in prav to šteje, ko uvozi ne tečejo le v laboratoriju, ampak morajo vsako noč zanesljivo potekati.

Če želite stabilizirati obstoječi uvozni ali vmesniški proces v Delphi/FireDAC (Performance, Transaktionen, Wiederanlauf, Logging), to z veseljem razčlenimo strukturirano v tehničnem pogovoru:

Za to temo so pomembni tudi Delphi Bulk Insert in Bulk Insert Delphi FireDAC. Prispevek te vidike razumljivo umešča in pokaže, na kaj gre v vsakodnevni praksi.

Projekt oder Modernisierungsvorhaben mit Net-Base besprechen.