FireDAC: Masveida ievietošana ar Array DML un rūpīga kļūdu apstrāde pa rindai

Viens BDE-Ablosung mit nativer Anbindung Bulk-Insert mit Array DML bieži ir ātrākais veids, kā iegūt daudz ierakstu datubāzē: nevis tūkstotis atsevišķu INSERT, parametrs tiek sasaistīts kā masīvs un nosūtīts uz serveri vienā piegājienā. Taču praksē problēma parādās ātri: viens ieraksts pārkāpj unikālo indeksu, NOT NULL lauks ir tukšs, ārējais atslēgas ieraksts neder — un pēkšņi nav skaidrs, kura rinda iznīcināja batch, vai daļa jau ir ierakstīta un kā tu vari turpināt tīri, neveidojot datu nekonsekvenci.

Tieši par to šeit ir runa: kā izmantot Array DML tā, lai tu pa rindai saņemtu uzticamu kļūdu informāciju, saglabātu kontroli pār transakciju un ekspluatācijā varētu atsekot, kas notika. Fokusā nav akadēmiska API-lasīšana, bet maldīšanās gadījums, kas reālos importos regulāri uznāk: liels batch, dažas bojātas rindas, bet tu tomēr gribi ātrumu.

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

Array DML (Data Manipulation Language) FireDAC nozīmē: parametrus sasaista nevis kā atsevišķas vērtības, bet kā masīvu. FireDAC tad (atkarībā no draivera/DB) sūta mazāk Roundtripu, var servera pusē darboties efektīvāk un būtiski samazina klienta pieslodzi. Tas ir īpaši nozīmīgi trīs situācijās:

• ETL- un importa ceļi: CSV/XML/JSON iekšā, normalizācija/mapping, pēc tam staging vai mērķtabulā.
• Saskarnes buferis: REST- vai MQ-payloadi tiek uzkrāti un periodiski persistēti.
• Protokolu-/event-tabulas: daudz mazu INSERTu, kur dominē latentums.

Ieguvums tomēr nenāk bez cenas. Ar Array DML tu pārvieto sarežģītību no “daudzi atsevišķi statementi” uz “viens statement ar daudzām rindām”. Tas ir labs veiktspējai, taču prasīgāks attiecībā uz kļūdu diagnostiku, transakciju loģiku un atjaunošanos.

Tipiskais maldīgais gadījums: viens batch, viena salauzta rinda

Dienesta klasika: tu importē 50 000 rindu. Izvēlies ArraySize 1 000, jo nevēlies par katru rindu veikt roundtrip. 17. batch izgāžas. DB ziņo tikai “duplicate key” vai “violates foreign key constraint”. UI vai servisa žurnālā bieži vien redz tikai: „ExecSQL failed“.

Bez sakarīga kļūdu apstrādes parasti notiek divas sliktas lietas:

• Tu iznīcini visu batch, lai gan 999 no 1 000 rindu būtu kārtībā.
• Tu atgriezies pie atsevišķiem INSERTiem un uz ilgu laiku zaudē veiktspējas priekšrocību.

Mērķis ir trešais ceļš: saglabāt batch-veiktspēju, bet reģistrēt kļūdas precīzi (rindas indekss, atslēgas vērtības, DB kļūdas teksts) un pēc izvēles izpildīt „derīgās rindas” — atkarībā no tā, cik kritiska ir konsekvence un idempotence (vairākkārtēja izpilde bez dubultas ietekmes) tavā procesā.

FireDAC Array DML: Svarīgākie iestatījumi (bez mītu)

Bulk-Insert ar Array DML praksē vienmēr izšķiroši ir šādi iestatījumi:

1) ArraySize und Batch-Größe

ArraySize (bei TFDQuery/TFDCommand) nosaka, cik daudz „rindu“ FireDAC tiek apstrādāts vienā izsaukumā. Lielāks ne vienmēr ir labāks. Pārāk liels nozīmē: vairāk atmiņas klientā, lielāku pārraides slodzi tīklā, lielākas bloķēšanas/žurnāla slodzes serverī un kļūmes gadījumā lielāku „Blast Radius“. Robustiem importiem bieži kā labs sākumpunkts der partijas izmērs starp 200 un 2.000, atkarībā no kolonnu skaita, BLOBu un latentēm.

2) Transaktionsgrenze

Tev nepieciešams skaidrs lēmums: Commit par partiju vai Commit par visu importu. Tas nav gaumes jautājums, bet ekspluatācijas lēmums:

• Commit par partiju: ierobežo bloķēšanas un transakciju žurnāla apjomu, vienkāršāka atkārtota palaišana, bet starpposmi var būt redzami (atkarībā no izolācijas līmeņa). Kļūme partijā 17 atstās partijas 1–16 sistēmā.
• Commit beigās: „viss vai nekas“, loģiski konsekventāk no biznesa viedokļa, bet lielos apjomos tu riskē ar ilgām bloķēm, lielu rollback apjomu, un kļūmes gadījumā viss var pazust.

Daudziem saskarnes un importa procesiem „Commit par partiju“ ir reālāka ekspluatācijas stratēģija — bet tikai, ja tu skaidri esi noregulējis idempotenci un dubletu stratēģiju (piem., ar dabiskajām atslēgām, Upserts vai Import-ID).

3) UpdateOptions und Prepared Statements

Atkārtotām partijām atmaksājas atstāt vaicājumu sagatavotu. „Prepare“ nozīmē: FireDAC ļauj DB parsēt/kompilēt vaicājumu un izmantot to atkārtoti. Atkarībā no DB tas var dot jūtamu efektu, it īpaši pie lielas frekvences. Šeit mazāk svarīgs ir „knifs 17“, vairāk: konsekventa viena un tā paša Query-objekta (vai viena un tā paša TFDCommand) atkārtota izmantošana un stabilas parametru datu tipi.

Skaidra kļūdu apstrāde pa rindu: kas tev patiesi nepieciešams

Ja tu vēlies apstrādāt kļūdas „pa rindu“, tev vajag trīs lietas:

1. Saskaņošana: Kurš Array-indekss (0..N-1) ir izgāzies?
2. Konteksts: Kādas biznesa atslēgas vērtības ir šai rindai (piem., ārējā ID, klienta numurs, laika zīmogs)?
3. Vadība: Ko tu dari pēc tam? Pārtraukt, izlaist tikai sliktās rindas vai sadalīt partiju?

FireDAC var, atkarībā no draivera, atgriezt kļūdas pa katru array-elementu. Tomēr praktiski tas nav „vienmēr klāt“. Jārēķinās, ka dažas datu bāzes/piegādātāji ziņo tikai par pirmo kļūdu vai ka partijā viena kļūda vispār neizpilda pārējo. Tieši tāpēc robusts modelis parasti ir divpakāpju:

• Pakāpe A: Mēģini izpildīt partiju kā Array DML.
• Pakāpe B: Ja partija neizdodas, sadali to (uz pusēm) vai kontrolēti pārej uz atsevišķām rindām — bet tikai šai partijai — un rūpīgi žurnālo.

Tas izklausās pēc papilddarba, taču importa plūsmās tas ir atšķirība starp „naktī plkst. 02:00 viss apstājas“ un „imports norit, 7 rindas nonāk kļūdu sarakstā“.

Praktiski lietojams modelis: vispirms partija, pēc tam mērķtiecīga izolācija

Šāda pieeja ir sevi attaisnojusi procesiem tuvos programmatūras risinājumos, kuros datu kvalitāte ir jaukta:

Solis 1: Datus iepakot partijas struktūrā (iekļ. kļūdu konteksts)

Saglabā importējamās rindas ne tikai kā neapstrādātas vērtības, bet ar minimālu kontekstu: ārējais ID, rindu numurs no avota, iespējams hash/kontrolsumma. Tas nav „Nice to have“: kļūdas gadījumā tu negribi atkārtoti parsēt CSV, lai noskaidrotu, kas ir bojāts.

Solis 2: Array DML izpilde

Tu iestati ArraySize uz partijas garumu, saisti parametrus kā masīvus un izpildi ExecSQL. Svarīgi: parametrtipus uzturēt stabilus (piem., skaitliskos laukus nebindēt reizēm kā String, reizēm kā Integer), citādi DB radīs implicitus castus vai FireDAC būs jākonvertē katram elementam.

Solis 3: Kļūdas gadījums – ierobežo partiju, nevis atkārto akli

Ja ExecSQL neizdodas, tev ir divas robustas iespējas:

• Binary Split (sadalīt pa pusei): partiju sadali divās daļās, katru daļu mēģini vēlreiz izpildīt kā Array DML. To atkārto, līdz nonāc pie maza apjoma, ko vari pārbaudīt pa vienai rindai. Priekšrocība: saglabā daudz veiktspējas, ja tikai dažas rindas ir bojātas. Trūkums: vairāk loģikas, un pie sistemātiskām kļūdām (piem., nepareizs datu tips) tas maz palīdz.
• Fallback uz atsevišķām rindām šai partijai: iestati ArraySize=1 (vai saisti atsevišķas vērtības) un izpildi rindai pa rindai, žurnāli kļūdas un turpini. Priekšrocība: vienkārši, garantēti pa rindai. Trūkums: šajā partijā zaudēsi ātrumu.

Praksē es apvienoju abus: vispirms 1–2 reizes splitu (lai ātri izietu „labos blokus“), tad pie maziem atlikumiem pāreju uz atsevišķām rindām, lai žurnālā iegūtu viennozīmīgu kļūdu informāciju.

Kļūdu objekti un ziņojumi: ko tu vari izvilkt no FireDAC

FireDAC kapsulē DB-kļūdas Exceptions (parasti EFDDBEngineException) ar detalizētu informāciju. Operatīvai darbībai svarīgi ir trīs līmeņi:

• DB-Fehlercode (db-specifiski): piem., SQLSTATE PostgreSQL gadījumā, Error Number SQL Server.
• Constraint-/Objektname: bieži iekļauts kļūdas tekstā (Unique-Index, FK-Constraint).
• Statement-Kontext: tabula, operācija, iespējams parametru vērtības (rūpīgi ar personas datiem).

Ja vēlies žurnālā ierakstīt pa rindai, kļūdas gadījumā tev jāidentificē arī rinda. FireDAC var, atkarībā no situācijas, nodrošināt masīva indeksu. Tomēr nepaļaujies tikai uz to. Vienmēr izveido papildus savu indeksu (pozīcija partijā) un pie šīs pozīcijas žurnāli vismaz vienu funkcionālu atslēgu.

Klupšanas punkti, kas īstos importos prasīs laiku

1) „Es war doch nur eine Zeile“ – bet transakcija jau ir „dirty“

Atkarībā no DB un draivera kļūda var novest pie tā, ka visa Statement izpilde tiek uzskatīta par neveiksmīgu un transakcija nonāk stāvoklī, kurā tev vai nu jāveic Rollback vai kurā turpmāki Statements izgāžas. Īpaši pie dažiem draiveriem „nach Fehler einfach weitermachen“ nav droša pieņēmums.

Secinājums: ja tu strādā transakcijā un partija neizdodas, standarta ceļš ir: Rollback des aktuellen Batch-Kontexts (vai visas transakcijas) un tad sākt no jauna. Tas labi saskan ar „Commit pro Batch“.

2) Autocommit vs. explizite Transaktion

Ja tu neuzsāc explizītu transakciju, bieži draiveris/provider nosaka, kā tiek commitoti Statements. Lielapjoma importiem tas reti ir tas, ko tu vēlies. Explizītas transakcijas dod tev kontroli pār:

• Lock-Dauer
• Rollback-uzvedība
• Atsākšanas punkti

Un: „Eksplīcīts“ nenozīmē „milzīga transakcija“. Tas nozīmē „apzināts“.

3) Triggeri, ierobežojumi un blakusparādības

Array DML paātrina nodošanu, bet ne automātiski servera puses darbu. Ja mērķtabulā ir triggeri (piem., audit-žurnēšana, automātiska statusa aprēķināšana), tad šaurais vieta var būt nevis INSERT operācija, bet triggeru kods. Tad batch var samazināt Roundtrips, taču DB servera CPU paliek limitējošais faktors.

Administratoriem un tehniskajiem līderiem: veiktspējas problēmu gadījumā vērts paskatīties uz Wait Events/Locks un transakciju žurnālu. Bulk-Insert bieži ir tikai izraisītājs, ne cēlonis.

4) Datu tipi un implizītās konvertācijas

Viena no visbiežākajām „Kāpēc tas ir lēni?“ iemesliem: parametri tiek sasaistīti kā String, DB katrā rindā veic pārvēršanu uz Integer/Date/Decimal. Tas ir neredzams, bet dārgs. Stabilai veiktspējai:

• Iestatiet parametru datu tipus atbilstoši (datums kā datums, skaitlis kā skaitlis).
• Pie decimālskaitļiem jāņem vērā lokalizācijas slazdi (komats vs. punkts). FireDAC šeit parasti ir pareizs, bet jaukti avoti nav.
• Laika joslu/UTC stratēģiju noskaidrojiet iepriekš (timestampi importos ir klasika).

5) Kļūdu teksti domāti cilvēkiem, bet ne automatizācijai

Iesaistoši ir mēģināt parsēt kļūdas tekstu („duplicate key value violates unique constraint …“). Dari to tikai kā pēdējo iespēju. Labāki ir strukturēti kodi (SQLSTATE, Error Number). Diemžēl ne visi draiveri nodrošina visu vienādi labi. Plānojiet abas lietas: kodu un tekstu, papildus pēc izvēles „constraint nosaukums no teksta“, bet bez ciešas atkarības.

Debugēšanas norādes: kā ātri atrast bojāto rindu

Padarīt batch reproducējamu

Ja imports sporādiski neizdodas, nepieciešama reproducējamība. Saglabājiet katrai partijai nelielu diagnostikas failu vai žurniera ierakstu, kas satur:

• Partijas numuru un laiku
• ArraySize un transakcijas režīmu
• fachlicher atslēgu sarakstu (piem., ārējās ID) partijā

Tas bieži pietiek, lai vēlāk mērķtiecīgi palaistu mini-importu tikai šīm ID vērtībām.

Padarīt galīgo SQL redzamu (bet bez datu noplūdēm)

Debugēšanā gribi zināt: vai SQL ir korekta? Vai parametri ir pareizi? FireDAC nodrošina monitoringu/tracingu caur FDMoni-komponentēm un draiveru žurnēšanu. Produkcijai tuvojās vidēs ir svarīgi:

• Ieslēgt tracing mērķtiecīgi un tikai īslaicīgi (veiktspēja un datu aizsardzība).
• Parametru vērtības logot tikai drošā vidē vai maskētā veidā.
• Attiecībā uz personas datiem: žurnālā tikai tehniskās atslēgas (ID) un nekādi skaidteksta saturs.

Ja tu veic split-testēšanu: definē pārtraukšanas kritērijus

Pie binary split tu negribi dalīt mūžīgi. Nosaki apakšrobežu, piem., „zem 20 rindu pāriet uz vienrindas režīmu“. Un nosaki limitu, cik daudz kļūdu kopumā tolerēsi, pirms pārtrauci importu (piem., pie sistemātiskām mapping kļūdām). Citādi nonāksi bezgalīgās kļūdu sarakstēs un bloķēsi turpmāku apstrādi.

Kad šī piepūle patiešām atmaksājas (un kad ne)

Array DML ar kļūdu apstrādi pa rindu īpaši atmaksājas, ja:

• Liels rindu skaits tiek apstrādāts (tūkstošiem līdz miljoniem).
• Mazs skaits rindu ar kļūdām ir, bet tu vēlies tomēr ļaut pārējām rindām iziet cauri.
• Imports ražošanas režīmā jādarbojas stabilā veidā (piem., nakts apstrāde, serviss bez UI).
• Tev jāatgriež kļūdu saraksts funkciju nodaļai/avotam (ar rindu atsauci).

Tas mazāk atmaksājas, ja:

• tu raksti tikai pāris desmitu rindu (atsevišķi INSERT ir ok),
• datu kvalitāte ir tik slikta, ka 30–50% rindu neizdodas (tad jēdzīgāka ir staging-stratēģija),
• tu jau izmanto DB-natīvu bulk-load procedūru (piem., COPY in PostgreSQL, BCP/BULK INSERT in SQL Server) – dann ist Array DML nicht das Werkzeug.

Alternatīvā arhitektūra: Staging-Tabelle statt „Direkt ins Ziel“

Ja tu regulāri saskaries ar jauktu datu kvalitāti, tīri „Insert direkt in die Zieltabelle“ bieži ir nepareizs lēmums. Eine Staging-Tabelle (Vorstufe) ir tabula, kurā tu vispirms saglabā datus tehniski pareizā formā (vajadzības gadījumā ar elastīgiem datu tipiem), un tikai pēc tam validē un pārvieto uz mērķa tabulu.

Priekšrocības ekspluatācijā:

• Nepareizi datu ieraksti paliek saglabāti ar iespēju tos izsekot (ieskaitot neapstrādātos datus).
• Tu vari veikt validāciju atsevišķi un atkārtojami.
• Tu atdala saskarnes pieņemšanu no funkcionālās apstrādes.

Array DML ir tad bieži ātrais ceļš in die Staging-Tabelle, kamēr pārvietošana uz mērķa tabulu notiek kā set-bāzēts SQL (vai Stored Procedure). Tas pārvieto kļūdu apstrādi vairāk uz DB pusi, kas atkarībā no organizācijas (DBA-Rollen, Deployment) var būt pamatoti vai nevēlami.

Ekspluatācija und Administration: Was IT-Leads und Admins dazu wissen sollten

Monitoring: Fehlerquote und Durchsatz sind die Kernmetriken

Lai nodrošinātu stabilu lielapjoma importa darbību, divas metrikas sniedz vairāk informācijas nekā vienīgi „Laufzeit“:

• Caurlaide: rindu skaits minūtē (vai uz partiju) inkl. maksimuma/mediānas rādītājiem.
• Kļūdu īpatsvars: nepareizo rindu skaits uz izpildi, ideālā gadījumā grupēts pēc kļūdu klasēm (Unique, FK, NOT NULL, tipu konflikts).

Ja tu regulāri redzi šos abus rādītājus, tu agrīni pamanīsi, vai avotā kaut kas ir mainījies (piem., jauns formāts) vai vai mērķsistēma (piem., jauni ierobežojumi) ir kļuvusi stingrāka.

Bloķēšana und Lastfenster

Bulk-INSERTi var radīt bloķēšanas un IO slodzes efektus. Ja vienlaikus lietotāji strādā ar tām pašām tabulām, tev jāizvērtē izolācijas līmeņi, indeksi un, ja nepieciešams, partionēšana. Praktiski tas nozīmē: vai nu izvietot importus noslodzes logos, vai veidot datu plūsmu tā, lai tā koeksistē ar darbību (piem., über Staging + asynchrone Übernahme).

Konkrēta Checkliste für einen robusten Bulk-Insert mit Array DML

• Partijas lielums noteikt (sākuma vērtība 500–1 000) un mērāmi noregulēt.
• Eksplizīta transakcija: Commit par partiju kā noklusējumu, „Commit am Ende“ tikai apzināti.
• Parametru tipu stabilitāte: noteikt, neuzspiest implicitus pārvēršanas (Casts).
• Kļūdu konteksts katram ierakstam: saglabāt (ārējā ID, avota rinda).
• Kļūdu stratēģija: partijas līmenī pirmām kārtām, pēc tam Split/Fallback, galu galā logēt pa rindām.
• Logging: kodi + teksts, taču atbilstīgi datu aizsardzībai; reģistrēt Batch-ID un Lauf-ID.
• Atkārtota palaišana: nodrošināt idempotenci (atslēga/Upsert/Import-ID).

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

FireDAC Bulk-Insert ar Array DML ir spēcīgs rīks, kamēr tu neizturies tā, it kā kļūdu nebūtu. Reālos datu plūsmās vienmēr parādās izņēmumi: dublikāti, trūkstošas atsauces, bojātas datuma vērtības. Tāpēc pareizā pieeja ir: Array DML veiktspējai, kombinēts ar kontrolētu izolācijas stratēģiju (Split vai Fallback) un kļūdu sarakstu, kas izsekojams pa rindu. Tādējādi tu vienlaikus iegūsti ātrumu un ekspluatācijas drošību – un tieši tas ir svarīgi, ja importa procesi nedrīkst darboties tikai laboratorijā, bet tiem jāizpildās uzticami katru nakti.

Ja jūs vēlaties stabilizēt esošu importa vai saskarnes procesu Delphi/FireDAC (veiktspēja, transakcijas, atjaunošana, logēšana), mēs to labprāt strukturēti pārrunāsim tehniskā sarunā:

Šai tēmai ir nozīmīgi arī Delphi Bulk Insert un Bulk Insert Delphi FireDAC. Raksts šos aspektus saprotami ierindo un parāda, kas ikdienā ir svarīgs.

Apspriest projektu vai modernizācijas ieceri ar Net-Base.