FireDAC: Masinis įterpimas naudojant Array DML ir eilutės lygmens klaidų apdorojimas

Vienas BDE-pakeitimas su natyviu prijungimu Bulk-Insert su Array DML dažnai yra greičiausias būdas perkelti daug įrašų į duomenų bazę: vietoje tūkstančio atskirų insertų pririšamas parametrų masyvas ir vienu metu išsiunčiamas į serverį. Tačiau praktikoje problema paaiškėja greitai: vienas įrašas pažeidžia unique indeksą, NOT NULL laukas yra tuščias, Foreign Key netinka – ir staiga neaišku, kuri eilutė sunaikino batchą, ar dalis jau buvo įrašyta ir kaip toliau tęsti tvarkingai, neįvedant duomenų nekonsistencijų.

Būtent apie tai ir yra šis tekstas: kaip naudoti Array DML taip, kad gautum po eilutę patikimą klaidų informaciją, išlaikytum transakcijos kontrolę ir galėtum operatyviai atsekti, kas įvyko. Fokusas nėra akademinė API dokumentacija, o tas kraštutinis atvejis, kuris tikruose importuose pasikartoja reguliariai: didelis batchas, kelios sugadintos eilutės, bet vis tiek nori greičio.

FireDAC Bulk-Insert su Array DML: kodėl Array DML apskritai apsimoka

Array DML (Data Manipulation Language) reiškia FireDAC: parametrus pririši ne kaip vienkartinę reikšmę, o kaip masyvą. FireDAC tada (priklausomai nuo draiverio/DB) sumažina roundtripų skaičių, serverio pusėje gali veikti efektyviau ir ženkliai sumažina kliento overheadą. Tai ypač aktualu trijose situacijose:

• ETL- ir importo srautai: CSV/XML/JSON įkėlimas, normalizavimas/mappinimas, tada į staging ar tikslinę lentelę.
• Sąsajų buferis: REST arba MQ payload’ai yra sukaupiami ir periodiškai persistinami.
• Protokolo-/event-lentelės: daug mažų insertų, kur dominuoja latencija.

Nauda neateina už dyką. Naudodamas Array DML perkeliate sudėtingumą nuo „daugelio atskirų sakinių“ į „vieną sakinį su daug eilučių“. Tai gerai dėl našumo, bet reikalauja didesnės atsakomybės dėl klaidų diagnostikos, transakcinės logikos ir pakartotinio vykdymo.

Tipinis kraštutinis atvejis: vienas batchas, viena sugadinta eilutė

Tipinis scenarijus eksploatacijoje: importuoji 50 000 eilučių. Pasirenki ArraySize = 1 000, nes nenori roundtripinti kiekvienos eilutės. 17-as batchas žlunga. DB grąžina tik „duplicate key“ arba „violates foreign key constraint“. UI ar service loge dažnai lieka tik: „ExecSQL failed“.

Be tvarkingo klaidų valdymo dažniausiai nutinka du blogi dalykai:

• Išmetate visą batchą, nors 999 iš 1 000 eilučių būtų buvusios tvarkingos.
• Grįžtate prie atskirų insertų ir prarandate našumo pranašumą ilguoju laikotarpiu.

Tikslas yra trečias kelias: išlaikyti paketų našumą, bet fiksuoti defektus preciziškai (eilutės indeksas, raktinės reikšmės, DB klaidos tekstas) ir pasirinktinai patvirtinti „geras eilutes“ – priklausomai nuo to, kiek kritiškas nuoseklumas ir idempotentiškumas (kartotinis vykdymas be dublikatų) tavo procese.

FireDAC Array DML: Svarbiausi parametrai (be mitų)

Praktikoje masiniam įterpimui su Array DML visada lemiami tie patys parametrai:

1) ArraySize ir partijų dydis

ArraySize (bei TFDQuery/TFDCommand) nusako, kiek „eilutės“ FireDAC apdorojama viename kvietime. Didesnis nebūtinai geriau. Per didelis reiškia: daugiau atminties kliente, didesnį payload tinkle, platesnius užraktus/žurnalo apkrovą serveryje ir klaidos atveju didesnį poveikio spindulį. Patikimiems importams dažnai partijų dydis tarp 200 ir 2.000 yra geras pradinio taško pasirinkimas, priklausomai nuo stulpelių skaičiaus, BLOBs ir latencijos.

2) Transakcijų riba

Reikia aiškaus sprendimo: Commit už partiją arba Commit visam importui. Tai nėra skonio reikalas, o operacijų sprendimas:

• Commit už partiją: riboja užraktus ir transakcijų žurnalą, palengvina pakartotinį paleidimą, bet tarpinės būsenos matomos (priklausomai nuo izoliacijos lygio). Klaida partijoje 17 palieka partijas 1–16 sistemoje.
• Commit pabaigoje: „viskas arba nieko“, semantiškai nuoseklesnis sprendimas, bet didelėms apimtims gresia ilgi užraktai, didelis rollback ir klaidos atveju visi duomenys prarandami.

Daugeliui sąsajų ir importavimo procesų „Commit už partiją“ yra realistiškesnė eksploatacijos strategija – tačiau tik jei idempotentiškumas ir dublikavimo strategija yra tvarkingai sureguliuoti (pvz., per natūralius raktus, Upserts arba importo ID).

3) UpdateOptions ir paruoštos užklausos

Kartojamų partijų atveju verta palikti užklausą paruoštą. „Prepare“ reiškia: FireDAC leidžia DB užklausą parsykuoti/kompiliuoti ir pakartotinai naudoti. Priklausomai nuo DB tai gali turėti pastebimą poveikį, ypač didelio dažnio vykdymuose. Čia svarbiau ne „trikų rinkinys“, o: nuoseklus to paties Query objekto (ar to paties TFDCommand) pakartotinis naudojimas ir stabilūs parametrų tipai.

Tvarkingas klaidų tvarkymas po eilutę: ko tau iš tikrųjų reikia

Jei nori tvarkyti klaidas „po eilutę“, tau reikia trijų dalykų:

1. Priskyrimas: kuris masyvo indeksas (0..N-1) sukėlė klaidą?
2. Kontekstas: kokias verslo raktines reikšmes turi ta eilutė (pvz., išorinė ID, kliento numeris, laiko žyma)?
3. Valdymas: ką darysi toliau? Nutraukti, praleisti tik blogas eilutes ar padalinti partiją?

FireDAC gali, priklausomai nuo tvarkyklės, grąžinti klaidas pagal masyvo elementą. Tačiau praktiškai tai nėra „visada užtikrinta“. Reikia tikėtis, kad kai kurios duomenų bazės/teikėjai praneš tik apie pirmą klaidą arba kad klaida partijoje visai nebeleis vykdyti likusių eilučių. Būtent dėl to patikimas modelis dažniausiai yra dviejų lygių:

• Lygis A: pabandyk vykdyti partiją kaip Array DML.
• Lygis B: jei partija nepavyksta, padalink ją (per pusę) arba valdomai pereik prie vienos eilutės vykdymo – bet tik šiai partijai – ir kruopščiai žurnaluok.

Skamba kaip papildomas darbas, tačiau importo srautuose tai dažnai yra skirtumas tarp „naktį 02:00 viskas sustoja“ ir „importas vyksta toliau, 7 eilutės pateko į klaidų sąrašą“.

Praktiškas modelis: pirmiausia partijos vykdymas, paskui tikslinė izoliacija

Šis modelis pasiteisino proceso artimoje programinėje įrangoje, kur duomenų kokybė yra mišri:

1 žingsnis: duomenis susidėlioti į batch struktūrą (įskaitant klaidos kontekstą)

Saugok importuojamus duomenis ne tik kaip žaliąsias reikšmes, bet ir su minimaliu kontekstu: išoriniu ID, šaltinio eilutės numeriu, galbūt hash/patikros suma. Tai nėra „Nice to have“: klaidos atveju nenorėsi iš naujo parsuoti CSV, kad sužinotum, kas sugadinta.

2 žingsnis: vykdyti Array DML

Nustatai ArraySize į batch ilgį, prijungi parametrus kaip masyvus ir vykdai ExecSQL. Svarbu: laikyk parametrų tipus stabiliais (pvz. skaitiniams laukams nesijungti kartais kaip String, kartais kaip Integer), priešingu atveju DB darys implicitinius konvertavimus arba FireDAC turės kiekvienam elementui konvertuoti.

3 žingsnis: klaidos atvejis – batch susiaurinti, o ne aklai kartoti

Jei ExecSQL nepavyksta, turi dvi tvirtas galimybes:

• Binary Split (padalyti per pusę): padalinki batch į dvi dalis ir kiekvieną dalį vėl bandom vykdyti kaip Array DML. Tai kartoji tol, kol lieka nedidelis kiekis, kurį gali patikrinti po vieną. Privalumas: išlaikai daug našumo, jei sugadintų eilučių yra nedaug. Trūkumas: daugiau logikos, o sisteminių klaidų (pvz. neteisingas duomenų tipas) atveju tai mažai padeda.
• Atsarginis variantas — pereiti prie vienos eilutės vykdymo šiam batch: nustatai ArraySize=1 (arba bindini atskiras reikšmes) ir vykdai eilutę po eilutės, logini klaidas ir tęsi. Privalumas: paprasta, garantuota po eilutę. Trūkumas: šio batch metu prarandi spartą.

Praktikoje aš derinu abu metodus: pirmiausia 1–2 kartus padalinti (kad „gerus blokus“ greitai praleistum), tada prie mažų likučių pereiti prie atskiros eilutės, kad gautum aiškią klaidos informaciją.

Klaidos objektai ir pranešimai: ką turėtum iš FireDAC išsiurbti

FireDAC apgaubia DB klaidas Exceptions (įprastai EFDDBEngineException) su detalėmis. Eksploatacijai svarbios trys lygtys:

• DB klaidų kodas (DB-specifinis): pvz. SQLSTATE PostgreSQL atveju, Error Number SQL Server atveju.
• Apribojimo/objekto pavadinimas: dažnai įtrauktas į klaidos tekstą (Unique-Index, FK-Constraint).
• Statement kontekstas: lentelė, operacija, jei reikia – parametro reikšmės (atsargiai su asmens duomenimis).

Jei nori loginti po eilutę, klaidos atveju taip pat turi identifikuoti pačią eilutę. FireDAC tam tikromis aplinkybėmis gali pateikti masyvo indeksą. Tačiau nepasikliauk vien tik tuo. Visada sukurk papildomą savo indeksą (pozicija batch’e) ir prie šios pozicijos užfiksuok bent vieną verslo raktą.

Kliūtys, kurios tikruose importuose kainuoja laiką

1) „Tai gi tik viena eilutė“ – bet transakcija jau „dirty“

Priklausomai nuo DB ir draiverio, klaida gali lemti, kad visa statement vykdymo seka bus laikoma nepavykusia ir transakcija pateks į būseną, kurioje turi explicitiai atlikti rollback arba tolesni statements nepavyks. Ypač kai kurių draiverių atveju „po klaidos tiesiog tęsti“ nėra saugi prielaida.

Konsekvencija: jei dirbi transakcijoje ir batch nepavyksta, įprastas kelias yra: Rollback dabartinio batch konteksto (arba visos transakcijos) ir tada pradėti iš naujo. Tai gerai dera su „Commit po batch“.

2) Autocommit vs. explicit transakcija

Jei nepradedies explicit transakcijos, dažnai draiveris/provider nusprendžia, kaip jis commit’ina statements. Bulk importams tai retai yra pageidaujama elgsena. Explicit transakcijos suteikia kontrolę dėl:

• Užrakinimo trukmė
• Rollback elgsena
• Atkūrimo taškai

Ir: „eksplizitiškai“ nereiškia „milžiniška transakcija“. Reiškia „sąmoningai“.

3) Trigeriai, apribojimai ir šalutinis poveikis

Array DML pagreitina perdavimą, bet ne automatiškai serverio pusės darbą. Jei tikslineje lentelėje yra trigeriai (pvz., audito žurnalas, automatinis būsenos skaičiavimas), tada siaurasis kaklas gali būti ne INSERT, o trigerio kodas. Tuomet partija gali turėti mažiau roundtrip’ų, bet DB serverio CPU lieka ribojantis veiksnys.

Administratoriams ir techniniams vadovams: esant našumo problemoms verta pažvelgti į laukimo įvykius / užraktus ir transakcijų žurnalą. Bulk-INSERT tada yra tik iššaukėjas, ne priežastis.

4) Duomenų tipai ir implicitinės konvertacijos

Viena iš dažniausių priežasčių „kodėl tai lėtai?“: parametrai susiejami kaip eilutės, DB per eilutę atlieka cast į Integer/Date/Decimal. Tai nematoma, bet brangu. Dėl stabilių rezultatų:

• Nustatykite parametrų duomenų tipus teisingai (datą kaip datą, skaičių kaip skaičių).
• Dėl decimalų atkreipkite dėmesį į lokalės spąstus (kablelis vs taškas). FireDAC čia dažniausiai teisingas, bet mišrūs šaltiniai — ne.
• Iš anksto susitarkite dėl laiko juostų / UTC strategijos (laiko žymos importuose – dažna problema).

5) Klaidos tekstai skirti žmonėms, bet ne automatizacijai

Gundanti yra parsinti klaidos tekstą („duplicate key value violates unique constraint …“). Darykite tai tik kaip paskutinę išeitį. Geriau naudoti struktūrizuotus kodus (SQLSTATE, klaidos numeris). Deja, ne visi tvarkyklės tiekia viską vienodai gerai. Todėl planuokite abi galimybes: kodą ir tekstą, plius opcionaliai „constraint pavadinimą iš teksto“, bet be griežtos priklausomybės.

Derinimo patarimai: kaip greitai rasti klaidingą eilutę

Partiją padaryti reprodukuojamą

Jei importas sporadiškai žlunga, reikia reproduciruojamumo. Išsaugokite kiekvienai partijai mažą diagnostinį failą arba log įrašą, kuris turi:

• partijos numerį ir laiką
• ArraySize ir transakcijos režimą
• partijoje esančių verslo raktų sąrašą (pvz., išoriniai ID)

To dažnai pakanka, kad vėliau tiksliai paleisti mini-importą tik toms ID.

Daryti galutinę SQL matomą (bet be duomenų nutekėjimo)

Derinimo metu nori sužinoti: ar SQL teisinga? ar parametrai tinkami? FireDAC siūlo stebėjimą / trasavimą per FDMoni komponentus ir tvarkyklės logginimą. Produkcijai artimose aplinkose svarbu:

• trasavimą aktyvuoti tikslingai ir tik laikinai (veikimas ir duomenų apsauga).
• parametrų reikšmes loginti tik saugioje aplinkoje arba užmaskuotas.
• asmens duomenų atveju: loge tik techniniai raktai (ID) ir jokio aiškaus teksto.

Jei atliekate split-testą: apibrėžkite nutraukimo kriterijus

Atliekant binarinį dalijimą nenorite dalinti be galo. Nustatykite apatininę ribą, pvz., „kai mažiau nei 20 eilučių – pereiti į vieneto režimą“. Ir nustatykite limitą, kiek klaidų iš viso toleruosite prieš nutraukdami importą (pvz., esant sistemingoms mapingo klaidoms). Priešingu atveju susidursite su begalinėmis klaidų sąrašais ir blokuosite tolimesnį apdorojimą.

Kada pastangos tikrai atsiperka (ir kada ne)

Array DML su eilutės lygiu klaidų valdymu ypač atsiperka, kai:

• Daug eilučių apdorojama (tūkstančiai iki milijonų).
• Kelių eilučių klaidos yra, bet vis tiek norite, kad procesas tęstųsi.
• Importas gamyboje turi vykti stabiliai (pvz., naktinis apdorojimas, paslauga be UI).
• Turi grąžinti klaidų sąrašą verslo padaliniui / šaltiniui (su eilučių nuoroda).

Mažiau apsimoka, jei:

• rašote tik kelias dešimtis eilučių (vienetiniai insertai yra priimtini),
• duomenų kokybė tokia prasta, kad 30–50 % eilučių nepraeina (tuomet staging strategija yra tikslingesnė),
• jau naudojate DB-gimtą masinį įkėlimo metodą (pvz. COPY in PostgreSQL, BCP/BULK INSERT in SQL Server) – tuomet Array DML nėra tinkamas įrankis.

Alternatyvi architektūra: Staging lentelė vietoje „tiesiogiai į tikslinę lentelę“

Jei reguliariai susiduriate su mišria duomenų kokybe, grynas „INSERT tiesiai į tikslinę lentelę“ dažnai būna neteisingas sprendimas. Staging-Tabelle (parengiamoji stadija) yra lentelė, kurioje duomenis iš pradžių saugote techniškai teisingai (galbūt su laisvesniais tipais), o tik vėliau validuojate ir perkeliat į tikslinę lentelę.

Eksploatacijos privalumai:

• Neteisingi įrašai lieka saugomi ir atsekami (įskaitant neapdorotus duomenis).
• Validavimą galite vykdyti atskirai ir pakartotinai.
• Atskiriate sąsajos priėmimą nuo funkcininio/dalykinio apdorojimo.

Array DML dažnai yra greitas kelias į staging lentelę, tuo tarpu perkėlimas į tikslinę lentelę vykdomas kaip rinkiniais pagrįstos SQL užklausos (arba saugotos procedūros). Tai perkelia klaidų tvarkymą labiau į DB pusę, kas priklausomai nuo organizacijos (DBA vaidmenys, diegimas) gali būti prasminga arba nepageidautina.

Eksploatacija ir administravimas: Ką IT vadovai ir administratoriai turėtų žinoti

Monitoravimas: klaidų dalis ir pralaidumas yra pagrindinės metrikos

Stabiliam masinio importo veikimui dvi metrikos yra informatyvesnės nei vien „vykdymo laikas“:

• Pralaidumas: eilučių per minutę (arba per partiją), įskaitant piką/medianą.
• Klaidų rodiklis: klaidingos eilutės per vykdymą, idealu grupuoti pagal klaidų klases (Unique, FK, NOT NULL, tipų konfliktas).

Reguliariai stebėdami šiuos du rodiklius, anksti pastebėsite, ar šaltinyje kas nors pasikeitė (pvz. naujas formatas) arba ar tikslinė sistema (pvz. nauji constraint’ai) tapo griežtesnė.

Užrakinimai ir apkrovos langai

Bulk-Inserts gali sukelti užrakinimus ir I/O apkrovą. Jei tuo pačiu metu vartotojai dirba su tomis pačiomis lentelėmis, turite apsvarstyti izoliacijos lygius, indeksus ir, jei reikia, partitioning’ą. Praktika: arba vykdyti importus apkrovos languose, arba sukurti duomenų srautą taip, kad jis koegzistuotų su veikiančia sistema (pvz. per Staging + asinchroninį perėmimą).

Konkreti patikros sąrašas robustiam masiniam įrašymui su Array DML

• Partijos dydis– nustatyti (pradinis vertės intervalas 500–1 000) ir tuninti remiantis matavimais.
• Aiški transakcija: commitinti po kiekvienos partijos kaip numatytą elgseną; „Commit am Ende“ taikyti tik sąmoningai.
• Parametrų tipai stabilūs – nenaudoti implicitinių cast’ų.
• Klaidos kontekstas kiekvienam įrašui saugomas (išorinė ID, šaltinio eilutė).
• Klaidos strategija: pirmiausia vertinti partiją, tuomet Split/Fallback, loguoti po eilute.
• Logging: kodai + tekstas, bet laikytis duomenų apsaugos; fiksuoti Batch-ID ir vykdymo ID.
• Perkrovimas: užtikrinti idempotenciją (raktas/Upsert/Import-ID).

Išvada: Array DML yra greitas – patikimas jis tampa per procesus ir klaidų strategiją

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.

Jei norite stabilizuoti esamą importo ar sąsajos procesą Delphi/FireDAC (našumas, transakcijos, pakartotinis paleidimas, žurnavimas), mes tai mielai struktūrizuotai aptarsime techniniame pokalbyje:

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.

Aptarkite projektą ar modernizacijos užmojį su Net-Base.