FireDAC: Hulgisisestus Array-DML-iga ja korralik rea-põhine veakäsitlus

Üks BDE-asendamine natiivse ühendusega Bulk-Insert Array DML-iga on sageli kiireim viis suur hulk kirjeid andmebaasi saada: ühe tuhande eraldi INSERTi asemel seotakse parameetrid massivina ja saadetakse korraga serverile. Praktikas tekib aga kiiresti kitsaskoht: üks kirje rikub unikaalindeksi, NOT NULL-väli on tühi, välisvõti ei sobi – ja äkki pole selge, milline rida partii katkestas, kas osa on juba kirjutatud ja kuidas edasi minna ilma andmete ebakõlasid tekitamata.

Just sellepärast on see artikkel: kuidas kasutada Array DML-i nii, et sa saad rida kohta usaldusväärse veateabe, hoiad tehingu kontrolli all ja suudad operatiivses keskkonnas taastada, mis juhtus. Fookus ei ole akadeemilisel API-lugemisel, vaid äärejuhtumil, mis päris importide juures regulaarselt esineb: suur partii, mõned katkised read, aga sa tahad siiski kiirust.

FireDAC Bulk-Insert Array DML-iga: miks Array DML Bulk-Insert’i puhul üldse tasub

Array DML (Data Manipulation Language) tähendab FireDAC kontekstis seda, et sa sidud parameetrid mitte üksikväärtustena, vaid massiivina. FireDAC saadab siis (sõltuvalt draiverist/DB-st) vähem ringkäike, võib serveripoolselt efektiivsemalt töötada ja vähendab kliendipoolset ülekoormust märkimisväärselt. See on eriti oluline kolmes olukorras:

• ETL- ja importiteid: CSV/XML/JSON sisse, normaliseerimine/mäppimine, siis staging- või sihttabelisse.
• Liidese puhver: REST- või MQ-payloadid kogutakse ja salvestatakse perioodiliselt.
• Protokolli-/sündmustabelid: palju väikseid INSERTe, kus latentsus domineerib.

Kasu ei tule tasuta. Array DMLiga nihutad keerukust „palju eraldiseisvat statementi“ pealt „üks statement paljude ridadega“. See on hea jõudluse jaoks, aga nõuab rohkem tööd veadiagnostika, tehingulogiika ja taastuskäigu osas.

Tüüpiline äärejuhtum: üks partii, üks rikutud rida

Tööalane klassika: importid 50 000 rida. Sa valid ArraySize’i 1000, et vältida iga rea jaoks ringkäiku. Partii 17 ebaõnnestub. Andmebaas teatab vaid „duplicate key“ või „violates foreign key constraint“. UI-s või teenuse logis on tihti vaid: „ExecSQL failed“.

Ilma korraliku veakäsitluse jahtimisel juhtuvad tavaliselt kaks kehva asja:

• Sa viskad kogu partii ära, kuigi 999-st 1000-st real oleks kõik korras.
• Sa langeb tagasi üksik-insertitele ja kaotad jõudliseeelise püsivalt.

Eesmärk on kolmas tee: s säilitada Batch-töötluse jõudlus, kuid logida defektid täpselt (realine indeks, võtmeväärtused, DB- veatekst) ja valikuliselt „korrektsed read“ commitida – sõltuvalt sellest, kui kriitiline on sinu protsessis järjepidevus ja idempotentsus (sama toimingu korduval käivitamisel ilma topelttulemuse tekketa).

FireDAC Array DML: olulised reguleerimisparameetrid (ilma müütideta)

Array DML-iga masssisestustel on praktikas alati samad reguleerimisparameetrid otsustava tähtsusega:

1) ArraySize ja partii suurus

ArraySize (bei TFDQuery/TFDCommand) määrab, mitu „rida“ FireDAC ühes kutses töödeldakse. Suurem ei ole automaatselt parem. Liiga suur tähendab: rohkem mälu kliendis, suurem payload üle võrgu, suuremad lukustused ja logikoormus serveris ning vea korral suurem mõjuulatus. Tugevate importide puhul on sageli partii suurus vahel 200 ja 2 000 hea lähtepunkt, sõltuvalt veergude arvust, BLOBide mahust ja latentsusest.

2) Tehingu piir

Sa vajad selget otsust: Commit pro Batch või Commit für den gesamten Import. See ei ole maitse asi, vaid käitusotsus:

• Commit pro Batch: piirab lukustusi ja transaktsioonilogi, lihtsam taaskäivitus, kuid vahetulemused on nähtavad (sõltuvalt isolatsioonitasemest). Viga partii 17 puhul jätab partii 1–16 süsteemi.
• Commit am Ende: „kõik või mitte midagi“, ärilises mõttes konsistentsem, kuid suurte koguste puhul riskid pikkade lukustustega, suure tagasikerimise ja vea korral kaob kõik.

Paljude liideste- ja impordiprotsesside puhul on „Commit pro Batch“ realistlikum käitusstrateegia – kuid ainult siis, kui oled idempotentsuse ja dubleettide strateegia korrektselt lahendanud (nt loomulike võtmete, upsertide või impordi-ID kaudu).

3) UpdateOptions und Prepared Statements

Korduvate partiide puhul tasub hoida statement ettevalmistatuna. „Prepare“ tähendab: FireDAC laseb andmebaasil lauset parsida/kompileerida ja seda taaskasutada. Sõltuvalt andmebaasist võib sellel olla tuntav efekt, eriti suure sageduse juures. Oluline ei ole siin mingi „Trick 17“, vaid: sama Query-objekti (või sama TFDCommand) järjepidev taaskasutus ja parameetritüüpide stabiilsus.

Puhas rea-kaupa veakäsitlus: mida sa tegelikult vajad

Kui soovid „rea kaupa“ vigu käsitleda, vajad kolme asja:

1. Seostamine: milline massiiviindeks (0..N-1) ebaõnnestus?
2. Kontext: millised ärilised võtmeväärtused sellele reale kuuluvad (nt väline ID, kliendi number, ajatempli)?
3. Juhtimine: mida sa peale seda teed? Katkestad, jätad halvad read vahele või jagad partii?

FireDAC võib sõltuvalt draiverist tagastada vigasid iga massiivielemendi kohta. Praktikas ei ole see aga „lihtsalt alati olemas“. Pead arvestama, et mõned andmebaasid/teenusepakkujad teatavad ainult esimesest veast või et partii viga peatab ülejäänu. Täpselt sellepärast on robustne muster enamasti kaheastmeline:

• Stufe A: proovi partii töödelda Array DML-ina.
• Stufe B: kui partii ebaõnnestub, jaga see (poolita) või lange kontrollitult üksikrealisele töötlemisele – aga ainult selle partii jaoks – ja logi korrektselt.

Tundub nagu rohkem tööd, kuid impordiradadel on see sageli vahe „kell 02:00 öösel jääb kõik seisma“ ja „import läbib protsessi, 7 rida sattusid vigade nimekirja“.

Praktiline muster: esmalt partii, siis sihipärane isoleerimine

Järgmine mustrikäik on osutunud töökindlaks protsessipõhiste tarkvaralahenduste puhul, kus andmete kvaliteet on varieeruv:

Samm 1: andmed pakkida partiistruktuuri (k.a. veakontekst)

Säilita imporditavad andmed mitte ainult toorväärtustena, vaid koos minimaalse kontekstiga: väline ID, rea number allikast, vajadusel hash/kontrollsumma. See ei ole „Nice to have“: veajuhul ei taha sa esmalt uuesti CSV-d parsida, et välja selgitada, mis katki on.

Samm 2: Array DML-i käivitamine

Seadista ArraySize partiipikkuseks, seo parameetrid massiividena ja käivita ExecSQL. Oluline: hoia parameetritüüpide määratus stabiilsena (nt numbrivälju ära seo kord Stringina, kord Integer’ina), vastasel juhul tekitab andmebaas implitsiitseid caste või FireDAC peab iga elementi teisendama.

Samm 3: veajuhtum – partii kitsendamine, mitte pimesi kordamine

Kui ExecSQL ebaõnnestub, on sul kaks töökindlat valikut:

• Binary Split (halbieren): partii jagada kaheks osaks ja proovida iga osa uuesti Array DML-iga. Korda seda, kuni jõuad väikese hulga juurde, mida saad üksikult kontrollida. Eelis: säilitad suure osa jõudlusest, kui vaid mõned read on vigased. Puudus: rohkem loogikat ja süsteemsete vigade (nt vale andmetüüp) korral on sellest vähe kasu.
• Varuvariant — üksikread selle parti jaoks: seadista ArraySize=1 (või seo üksikväärtused) ja täida read ükshaaval, logi vead ja jätka. Eelis: lihtne, tagab ridadeülest täpsust. Puudus: selles partii kaotad kiiruse.

Praktikas kombineerin neid: esmalt jagan 1–2 korda (et kiiRESTi läbi lasta „töökorras plokid“), seejärel väikeste jääkide puhul lülitan üle üksikrealisele töötlemisele, et logida ühemõttelised veateated.

Veaobjektid ja teated: was du aus FireDAC herausziehen solltest

FireDAC kapseldab DB-vigu eranditesse (tavaliselt EFDDBEngineException) koos detailinfoga. Käitluse jaoks on olulised kolm tasandit:

• DB-veakood (andmebaasispetsiifiline): nt SQLSTATE PostgreSQL-is, Error Number SQL Serveris.
• Piirangu-/objekti nimi: sageli vigatekstis olemas (Unique-Index, FK-Constraint).
• Statement-kontekst: tabel, operatsioon, vajadusel parameetrite väärtused (isikuandmete puhul ettevaatlik).

Kui soovid logida ridade kaupa, pead veajuhul ka rea identifitseerima. FireDAC võib mõnel juhul tagastada massiivi indeksi. Ära siiski tugine üksnes sellele. Loo alati lisaks oma indeks (positsioon partis) ja logi selle positsiooni juures vähemalt üks domeenivõti.

Lõksud, mis reaalses impordis aega maksavad

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

Sõltuvalt andmebaasist ja draiverist võib viga põhjustada, et kogu päringu täitmine loetakse ebaõnnestunuks ja tehing satub sellisesse olekusse, kus pead kas eksplitsiitselt rollbackima või kus edasised päringud nurjuvad. Eriti mõnel draiveril ei ole „pärast viga lihtsalt edasi jätkata“ usaldusväärne eeldus.

Järeldus: kui töötad tehingus ja partii ebaõnnestub, on tavapärane käik: rollbacki teha jooksva partiikonteksti (või kogu tehingu) ja seejärel alustada uuesti. See sobib hästi mudeliga „Commit pro Batch“.

2) Autocommit vs. explizite Transaktion

Kui sa ei alusta eksplitsiitset tehingut, otsustab tihti draiver/provider, kuidas päringuid committitakse. Massimpordiks ei ole see tavaliselt see, mida vajad. Eksplitsiitsed tehingud annavad sulle kontrolli üle:

• lukustuse kestus
• Rollback-käitumine
• Taaskäivituspunkid

Ja: „eksplicitne“ ei tähenda „hiigelsuurt transaktsiooni“. See tähendab „teadlikult“.

3) Triggerid, Constraints ja kõrvalmõjud

Array DML kiirendab andmete üleandmist, aga ei tee automaatselt kiiremaks serveripoolset tööd. Kui sihttabelil on triggerid (nt audit-logimine, automaatne staatuse arvutus), võib kitsaskoht olla mitte insert-is, vaid trigger-koodis. Sellisel juhul võib batch küll vähendada roundtrippe, kuid DB-serveri CPU jääb piiravaks teguriks.

Adminidele ja tehnilistele juhtidele: jõudlusprobleemide korral tasub vaadata Wait Events/Locks ja tehingulog-i. Bulk-Insert on siis sageli ainult päästik, mitte põhjus.

4) Andmetüübid ja implitsiitsed konversioonid

Üks levinumaid „Miks see aeglane on?“ põhjuseid: parameetrid seotakse String-ina, DB konverteerib rea kohta Integer/Date/Decimal-iks. See on nähtamatu, aga kallis. Stabiilse jõudluse jaoks:

• Määra parameetrite andmetüübid õigesti (kuupäev kuupäevana, number numbrina).
• Decimal-ite puhul arvesta lokaadi lõksudega (koma vs punkt). FireDAC on siin tavaliselt õige, kuid segatud allikad mitte.
• Ajavööndid/UTC-strateegia eelnevalt kokku leppida (timestamp-id on importide klassika).

5) Veateated on inimestele, mitte automatiseerimisele

Põrgupõnev on hakata veateksti parsima („duplicate key value violates unique constraint …“). Tee seda ainult viimase roohana. Eelistatavamad on struktureeritud koodid (SQLSTATE, Error Number). Kahjuks ei anna kõik draiverid kõiki andmeid võrdselt hästi. Planeeri seetõttu mõlemat: kood ja tekst, plus valikuline „constraint-nime tõlgendamine tekstist“, kuid ilma tugeva sõltuvuseta.

Silumisjuhised: nii leiad kiiresti vigase rea

Muuda batch reprodutseeritavaks

Kui import ebaõnnestub spontaanselt, vajad reprodutseeritavust. Salvesta iga batch-i kohta väike diagnostikafail või logikirje, mis sisaldab:

• Batchi number ja aeg
• ArraySize ja tehingurežiim
• äriliste võtmete nimekiri batchis (nt välised ID-d)

See on tihti piisav, et hiljem sihipäraselt käivitada mini-impord just nende ID-de jaoks.

Lõplik SQL nähtavaks teha (aga ilma andmeleketeta)

Silumisel tahad teada: kas SQL on korrektne? kas parameetrid on õiged? FireDAC pakub monitooringut/tracingut läbi FDMoni-komponentide ja draiverilogide. Tootmislähedastes keskkondades on oluline:

• Lülita tracing sisse sihipäraselt ja ainult ajutiselt (jõudlus ja andmekaitse).
• Logi parameetrite väärtused ainult turvalises keskkonnas või maskeeritult.
• Isikuandmete korral: logis ainult tehnilised võtmed (ID-d), mitte selgesõnalised andmed.

Kui sa split-testid: määra katkestamiskriteeriumid

Binary Split-i puhul ei taha sa lõputult jagada. Sea alampiir, nt „alla 20 rea puhul lülitu üksikrežiimile“. Ja määra maksimaalne vigade arv, mida kokku talud, enne kui import katkestad (nt süsteemsete mapimisvigade korral). Vastasel juhul tekib lõputu vealoend ja järgnevad protsessid jäävad blokeerituks.

Millal pingutus tõesti tasub (ja millal mitte)

Array DML koos rea-põhise veakäsitlusega tasub eriti, kui:

• Suurt hulka ridu töödeldakse (tuhanded kuni miljonid).
• Vähe ridu on vigased, kuid soovid siiski protsessi lõpuni läbi lasta.
• Import peab tootmises stabiilselt jooksma (nt ööpäevane töötlemine, teenus ilma UI-ta).
• Pead vigade nimekirja tagastama ärivaldkonnale/allikale (rea viitega).

See on vähem otstarbekas, kui:

• sa kirjutad vaid paarikümmend rida (üksikinsertid on ok),
• andmete kvaliteet on nii halb, et 30–50% ridadest ebaõnnestub (sel juhul on staging-strateegia otstarbekam),
• sa nagunii kasutad DB-natiivset massilaadimise meetodit (nt COPY PostgreSQL-is, BCP/BULK INSERT SQL Serveris) – siis pole Array DML õige tööriist.

Alternatiivne arhitektuur: staging-tabel „otse sihtkohta“ asemel

Kui sa regulaarselt võitled erineva andmekvaliteediga, on puhas „Insert otse sihttabelisse“ sageli vale otsus. staging-tabel (vaheaste) on tabel, kuhu salvestad andmed esmalt tehniliselt korrektselt (vajadusel pehmete tüüpidega) ja alles seejärel valideerid ning viid sihttabelisse.

Töös tekkivad eelised:

• veade read jäävad jälgitavalt salvestatuks (sh toorandmed).
• valideerimise saab sooritada eraldi ja kordusena.
• sa eraldad liidese andmete vastuvõtu ja ärilise töötlemise.

Array DML on siis sageli kiire tee staging-tabelisse, samas kui üleviimine sihttabelisse toimub setipõhise SQL-i (või Stored Procedure’i) kaudu. See nihutab vigade käsitlemise rohkem DB-küljele, mis võib sõltuvalt organisatsioonist (DBA-rollid, deployment) olla otstarbekas või soovimatu.

Töö ja haldus: mida IT-juhtidele ja administraatoritele teada tuleks

Järelevalve: veamäär ja läbilaskevõime on põhimetriteks

Masssisipordi stabiilseks opereerimiseks annavad kaks mõõdikut rohkem infot kui ainult „läbiviimise aeg“:

• Läbilaskevõime: read minutis (või batchi kohta) sh tipp / mediaan.
• Veamäär: ebaõnnestunud read jooksu kohta, ideaaljuhul grupeerituna veaklasside järgi (Unique, FK, NOT NULL, tüübikonflikt).

Kui sa neid kahte näitajat regulaarselt jälgid, märkad varakult, kas allikas on muutunud (nt uus formaat) või kas sihtsüsteem on karmistunud (nt uued piirangud).

Lukustused ja koormusaknad

Masssisipordid võivad põhjustada lukustusi ja I/O-koormust. Kui samaajal kasutajad töötavad samadel tabelitel, pead arvestama isolatsioonitasemete, indeksite ja vajadusel partitsioonimisega. Praktiliselt tähendab see: kas ajasta importid koormusaknasse või ehita andmevoog nii, et see kooseksisteerib jooksva tööga (nt läbi staging + asünkroonse ületoomise).

Konkreetne kontrollnimekiri robustseks Bulk-Insertiks koos Array DML-iga

• Batchi suurus määrata (algväärtus 500–1 000) ja mõõdetavalt häälestada.
• Eksplitsiitne tehing: commit per batch vaikimisi; „commit lõpuks“ ainult teadlikult.
• Parameetritüüpide stabiilsus: määra stabiilsed tüübid, ära sunni implitsiitseid konverteerimisi.
• Veakontekst iga andmerea kohta: kaasa kanda (väline ID, lähterida).
• Veastrateegia: esmalt batch, seejärel split/fallback; logi rea kaupa.
• Logimine: koodid + tekst, kuid andmekaitsele vastav; salvestada batch-ID ja jooksu-ID.
• Taaskäivitus: tagada idempotentsus (võtmed / upsert / import-ID).

Järeldus: Array DML on kiire – robustseks teeb selle protsess ja veastrateegia

Üks FireDAC Bulk-Insert Array DML-iga on tugev tööriist, kuid ainult seni, kuni sa ei tee nägu, et vigu ei eksisteeri. Reaalsetes andmevoogudes on alati kõrvalekaldeid: dubleedid, puuduvad viited, vigased kuupäevaväärtused. Seetõttu on puhas lähenemine järgmine: Array DML jõudluse jaoks, kombineerituna kontrollitud isolatsioonistrateegiaga (Split või Fallback) ja iga rea kohta jälgitava vigade nimekirjaga. Sellega ühildad sa kiiruse ja töökindluse – ja just see loeb, kui impordid ei toimu ainult laboris, vaid peavad iga öö usaldusväärselt lõpule jõudma.

Kui te soovite olemasolevat import- või liideseprotsessi in Delphi/FireDAC stabiliseerida (jõudlus, transaktsioonid, taaskäivitumine, logimine), selgitame seda meelsasti struktureeritult tehnilises vestluses:

Selle teema puhul on olulised ka Delphi Bulk Insert ja Bulk Insert Delphi FireDAC. Artikkel paigutab need aspektid arusaadavalt konteksti ja näitab, millele igapäevases töös tähelepanu pöörata.

Arutada projekti või moderniseerimishanke koos Net-Base.