FireDAC: Bulk-Insert cu DML în array și gestionare curată a erorilor la nivel de rând

Un BDE-Ablosung mit nativer Anbindung Bulk-Insert cu Array DML este adesea cea mai rapidă cale de a introduce multe înregistrări într-o bază de date: în loc de o mie de inserturi individuale se leagă un array de parametri și se trimite totul dintr-o dată la server. În practică însă apare rapid problema: o înregistrare încalcă un index unic, un câmp NOT NULL este gol, o cheie externă nu se potrivește — și brusc nu mai e clar care rând a stricat batch-ul, dacă o parte a fost deja scrisă și cum continui corect, fără a genera inconsistențe de date.

Exact despre asta este vorba aici: cum folosești Array DML astfel încât să obții pentru fiecare rând informații de eroare solide, să păstrezi tranzacția sub control și să poți urmări în producție ce s-a întâmplat. Accentul nu este pe lectură academică a API-ului, ci pe cazul limită care apare frecvent în importuri reale: un batch mare, câteva rânduri corupte, dar vrei totuși viteză.

FireDAC Bulk-Insert cu Array DML: De ce merită Array DML la Bulk-Insert

Array DML (Data Manipulation Language) în FireDAC înseamnă: legi parametrii nu ca valori individuale, ci ca array. FireDAC trimite apoi (în funcție de driver/DB) mai puține roundtrips, poate lucra mai eficient pe server și reduce drastic overhead-ul în client. Acest lucru contează în mod deosebit în trei situații:

• Procese ETL și trasee de import: CSV/XML/JSON intră, normalizare/mapping, apoi în tabelul de staging sau țintă.
• Buffer pentru interfețe: payload-urile REST sau MQ sunt colectate și persistate periodic.
• Tabele de jurnal / evenimente: multe inserturi mici, unde latența domină.

Beneficiul nu vine însă gratuit. Cu Array DML muți complexitatea de la „multe statement‑uri individuale” la „un statement cu multe rânduri”. Asta e bun pentru performanță, dar mai exigent pentru diagnosticarea erorilor, logica tranzacțiilor și reexecuție.

Cazul limită tipic: un Batch, un rând corupt

Clasic în producție: imporți 50.000 de rânduri. Alegi o ArraySize de 1.000 pentru că nu vrei un roundtrip pentru fiecare rând. Batch 17 eșuează. DB-ul raportează doar „duplicate key” sau „violates foreign key constraint”. În UI sau în Service-Log apare adesea doar: „ExecSQL failed”.

Fără un error-handling curat se întâmplă de obicei două lucruri rele:

• Renunți la întregul Batch, deși 999 din 1.000 de rânduri ar fi fost OK.
• Revii la inserări individuale și pierzi definitiv avantajul de performanță.

Scopul este o a treia cale: păstrezi performanța batch, dar registrezi defectele punctual (indexul rândului, valorile cheie, textul erorii DB) și opțional faci commit pe „rândurile bune” – în funcție de cât de critică este consistența și idempotenta (executare repetată fără efect dublu) în procesul tău.

FireDAC Array DML: Parametrii relevanți (fără mituri)

Pentru bulk-insert cu Array DML, în practică aceiași parametri sunt întotdeauna decisivi:

1) ArraySize und Batch-Größe

ArraySize (la TFDQuery/TFDCommand) determină câte „rânduri“ FireDAC sunt procesate într-un apel. Mai mare nu înseamnă automat mai bine. Prea mare înseamnă: mai multă memorie în client, mai mult payload pe rețea, lock-uri mai mari/sarcină mai mare pe jurnalul tranzacțiilor pe server și, în caz de eroare, un „blast radius” mai mare. Pentru importuri robuste, o mărime a batch-ului între 200 și 2.000 este adesea un punct de plecare bun, în funcție de numărul de coloane, BLOB-uri și latență.

2) Transaktionsgrenze

Ai nevoie de o decizie clară: Commit pe batch sau Commit pentru întregul import. Nu este o chestiune de gust, ci o decizie operațională:

• Commit pe batch: limitează blocajele și jurnalul tranzacțiilor, relansare mai simplă, dar stările intermediare sunt vizibile (în funcție de nivelul de izolare). Erorile în Batch 17 lasă Batch 1–16 în sistem.
• Commit la final: „totul sau nimic”, mai consistent din punct de vedere funcțional, dar la volume mari riști blocări îndelungate, rollback extins și, în caz de eroare, pierderea întregului lot.

Pentru multe interfețe și procese de import, „Commit pe batch” este strategia operațională mai realistă – dar doar dacă ai reglementat clar idempotenta și strategia pentru duplicate (de ex. prin chei naturale, upsert-uri sau o Import-ID).

3) UpdateOptions und Prepared Statements

La batch-uri repetate merită să lași instrucțiunea pregătită. „Prepare“ înseamnă: FireDAC permite DB-ului să parseeze/compileze instrucțiunea și să o refolosească. În funcție de DB, asta poate avea un efect perceptibil, în special la frecvență ridicată. Important aici nu este vreo „șmecherie”, ci: utilizarea consecventă a aceluiași obiect Query (sau a aceluiași TFDCommand) și tipuri de parametri stabile.

Gestionare clară a erorilor pe rând: Ce ai cu adevărat nevoie

Dacă vrei să tratezi erorile „pe rând”, ai nevoie de trei lucruri:

1. Asignare: Care index al array-ului (0..N-1) a eșuat?
2. Context: Ce valori cheie de business are acest rând (de ex. ID extern, număr client, timestamp)?
3. Control: Ce faci apoi? Oprești, sari doar rândurile defecte sau împarți batch-ul?

FireDAC poate, în funcție de driver, să returneze erori per element de array. În practică însă asta nu este pur și simplu întotdeauna disponibil. Trebuie să te aștepți ca unele baze de date/provideri să raporteze doar prima eroare sau ca un eșec din batch să împiedice executarea RESTului. Din acest motiv, un model robust este, de obicei, în două etape:

• Etapa A: Încearcă batch-ul ca Array DML.
• Etapa B: Dacă batch-ul eșuează, împarte-l (jumătate) sau revino controlat la rânduri individuale – dar doar pentru acel batch – și înregistrează riguros.

Sună a muncă în plus, dar în traseele de import este diferența între „la 02:00 noaptea totul se oprește” și „importul rulează, 7 rânduri ajung în lista de erori”.

Un model practic: mai întâi batch, apoi izolarea țintită

Următorul model s-a dovedit eficient pentru soluții software apropiate de proces, în care calitatea datelor este mixtă:

Pasul 1: Împachetează datele într-o structură de batch (inclusiv contextul erorii)

Salvează datele care urmează să fie importate nu doar ca valori brute, ci cu context minim: ID extern, numărul rândului din sursă, eventual hash/checksumă. Acesta nu este un „Nice to have“: în caz de eroare nu vrei să fii nevoit să parcurgi din nou CSV-ul pentru a afla ce s-a stricat.

Pasul 2: Execută Array DML

Setezi ArraySize la lungimea batch-ului, legi parametrii ca matrici și execuți ExecSQL. Important: menține tipurile parametrilor stabile (de ex. pentru câmpuri numerice să nu le legi uneori ca String, alteori ca Integer), altfel DB produce cast-uri implicite sau FireDAC trebuie să convertească element cu element.

Pasul 3: Caz de eroare – RESTrânge batch-ul în loc să repeti orbeste

Dacă ExecSQL eșuează, ai două opțiuni solide:

• Binary Split (jumătățire): împarte batch-ul în două jumătăți, încearcă fiecare jumătate din nou ca Array DML. Repeți până când rămâi la o cantitate mică pe care o poți verifica individual. Avantaj: păstrezi mare parte din performanță dacă doar câteva rânduri sunt corupte. Dezavantaj: logică mai complexă și, în caz de erori sistematice (de ex. tip de date greșit), aduce puțin beneficiu.
• Fallback la rânduri individuale pentru acest batch: setezi ArraySize=1 (sau legi valori individuale) și execuți rând cu rând, loghezi erorile și continui. Avantaj: simplu, garantat per rând. Dezavantaj: în acest batch pierzi viteză.

În practică le combin: mai întâi împarți de 1–2 ori (pentru a trece rapid prin „blocuri bune”), apoi la RESTuri mici treci la rânduri individuale pentru a înregistra informații de eroare clare.

Obiecte de eroare și mesaje: ce ar trebui să extragi din FireDAC

FireDAC encapsulează erorile DB în excepții (tipic EFDDBEngineException) cu informații detaliate. Pentru operare sunt importante trei niveluri:

• Cod de eroare DB (specific DB): de exemplu SQLSTATE la PostgreSQL, Error Number la SQL Server.
• Nume constraint/obiect: apare frecvent în textul erorii (Unique-Index, FK-Constraint).
• Contextul declarației: tabel, operațiune, eventual valori ale parametrilor (atenție la date cu caracter personal).

Dacă vrei să înregistrezi pe rând, trebuie în caz de eroare să identifici rândul. FireDAC poate, în unele cazuri, să returneze indexul în array. Nu te baza însă exclusiv pe asta. Construiește-ți întotdeauna suplimentar un index propriu (poziția în batch) și loghează pentru această poziție cel puțin o cheie funcțională.

Capcane care costă timp în importuri reale

1) „Era doar un rând“ – dar tranzacția este deja „dirty“

În funcție de DB și driver, o eroare poate face ca întreaga execuție a statement-ului să fie considerată eșuată și tranzacția să rămână într-o stare în care trebuie fie să faci rollback explicit, fie în care alte statement-uri vor eșua. Mai ales la anumiți drivere, „după o eroare să continui pur și simplu“ nu este o ipoteză sigură.

Consecință: Dacă lucrezi într-o tranzacție și un batch eșuează, calea standard este: Rollback des aktuellen Batch-Kontexts (sau al întregii tranzacții) și apoi reluare. Aceasta se potrivește bine cu „Commit pro Batch“.

2) Autocommit vs. explizite Transaktion

Dacă nu pornești o tranzacție explicită, driverul/providerul decide deseori cum comite statement-urile. Pentru importuri în volum mare asta rar este ceea ce dorești. Tranzacțiile explicite îți oferă control asupra:

• Durata blocării
• Comportament de rollback
• Puncte de reluare

Și: Explicit nu înseamnă „o tranzacție uriașă“. Înseamnă „conștient“.

3) Trigger, Constraints și efecte secundare

Array DML accelerează transferul, dar nu automat munca de pe server. Dacă ai trigger-e pe tabelul țintă (de ex. audit-logging, calcul automat de status), atunci blocajul poate să nu fie insert-ul, ci codul trigger-ului. Atunci un batch poate avea mai puține roundtrip-uri, dar CPU-ul pe serverul DB rămâne factorul limitativ.

Pentru administratori și responsabili tehnici: la probleme de performanță merită o analiză a Wait Events/Locks și a jurnalului de tranzacții. Bulk-insert-ul este adesea doar declanșatorul, nu cauza.

4) Tipuri de date și conversii implicite

Unul dintre cele mai frecvente motive pentru „De ce e lent?“: parametrii sunt legați ca string, DB face cast pentru fiecare rând la Integer/Date/Decimal. Asta este invizibil, dar costisitor. Pentru performanță stabilă:

• Setați tipurile de date ale parametrilor corect (dată ca dată, număr ca număr).
• La zecimale atenție la capcanele locale (virgulă vs. punct). FireDAC este de obicei corect aici, dar sursele mixte nu sunt.
• Clarificați dinainte strategia de fus orar/UTC (timestamp-urile sunt un clasic la importuri).

5) Textele de eroare sunt pentru oameni, nu pentru automatizare

E tentant să parsezi textul de eroare („duplicate key value violates unique constraint …“). Fă asta doar ca ultimă opțiune. Mai bune sunt codurile structurate (SQLSTATE, numărul erorii). Din păcate nu toți driverii furnizează toate informațiile la fel de bine. Planifică deci ambele: cod și text, plus opțional „nume-constraint din text“, dar fără dependență strictă.

Indicații de depanare: Cum găsești rapid rândul problematic

Fă batch-ul reproductibil

Dacă un import eșuează sporadic, ai nevoie de reproductibilitate. Salvează pentru fiecare batch un fișier mic de diagnostic sau o intrare de log care conține:

• Numărul batch-ului și ora
• ArraySize și modul tranzacțional
• lista cheilor funcționale (de ex. ID-uri externe) din batch

Aceasta este adesea suficient pentru a rula ulterior un mini-import doar pentru aceste ID-uri.

Faceți SQL-ul final vizibil (dar fără scurgeri de date)

În depanare vrei să știi: SQL-ul este corect? Sunt parametrii corecți? FireDAC oferă monitorizare/tracing prin componente FDMoni și logging al driverelor. În medii aproape de producție este important:

• Activează tracing-ul țintit doar temporar (performanță și protecția datelor).
• Înregistrează în log valorile parametrilor doar într-un mediu sigur sau mascate.
• Pentru date cu caracter personal: în log doar chei tehnice (ID-uri) și niciun conținut în clar.

Dacă faci split-test: definește criterii de oprire

La Binary Split nu vrei să împarți la infinit. Setează o limită inferioară, de ex. „sub 20 rânduri treci la modul individual“. Și stabilește un plafon pentru câte erori tolerezi în total înainte de a întrerupe importul (de ex. la probleme sistematice de mapping). Altfel vei ajunge la liste de erori fără sfârșit și vei bloca procesarea ulterioară.

Când merită cu adevărat efortul (și când nu)

Array DML cu gestionare a erorilor per rând merită în special dacă:

• Se procesează multe rânduri (mii până la milioane).
• Puține rânduri sunt eronate, dar vrei totuși să continui procesarea.
• Importul trebuie să ruleze stabil în producție (de ex. procesare nocturnă, serviciu fără UI).
• Trebuie să restitui o listă de erori către departamentul de specialitate/sursă (cu referință la rânduri).

Merită mai puțin dacă:

• scrii doar câteva zeci de rânduri (inserții individuale sunt ok),
• calitatea datelor este atât de slabă încât 30–50% din rânduri eșuează (atunci o strategie cu staging are mai mult sens),
• folosești oricum un procedeu de bulk-load nativ DB (de ex. COPY în PostgreSQL, BCP/BULK INSERT în SQL Server) – atunci Array DML nu este instrumentul potrivit.

Arhitectură alternativă: tabel de staging în locul „direct în destinație”

Dacă te confrunți regulat cu calitate mixtă a datelor, un „insert direct în tabela țintă” este adesea decizia greșită. O tabelă de staging (etapă preliminară) este o tabelă în care stochezi datele inițial corect din punct de vedere tehnic (eventual cu tipuri moi) și abia apoi le validezi și le transferi în tabela țintă.

Avantaje în operare:

• Înregistrările defecte rămân stocate și urmărite (inclusiv datele brute).
• Poți rula validarea separat și repetabil.
• Decuplezi asumarea la interfață de la procesarea funcțională.

Array DML este adesea calea rapidă către tabela de staging, în timp ce transferul în tabela țintă se face ca SQL set-based (sau Stored Procedure). Aceasta mută tratarea erorilor mai mult pe partea DB, ceea ce, în funcție de organizație (roluri DBA, deployment), poate fi util sau nedorit.

Operare și administrare: ce trebuie să știe liderii IT și administratorii

Monitorizare: rata de eroare și debitul sunt metricile cheie

Pentru operarea stabilă a unui import bulk, două metrici sunt mai relevante decât „timpul de execuție” singur:

• Debit: linii pe minut (sau pe batch), inclusiv vârf/mediană.
• Rata de eroare: linii defecte per execuție, ideal grupate pe clase de eroare (Unique, FK, NOT NULL, conflict de tip).

Dacă urmărești regulat aceste două valori, detectezi rapid dacă s-a schimbat sursa (de ex. format nou) sau dacă sistemul țintă (de ex. constrângeri noi) a devenit mai restrictiv.

Blocări și ferestre de încărcare

Bulk-inserts pot genera locking și încărcare IO. Dacă utilizatori lucrează paralel pe aceleași tabele, trebuie să te gândești la niveluri de izolare, indici și, eventual, partiționare. Practic înseamnă: fie programezi importurile în ferestre de încărcare, fie construiești fluxul de date astfel încât să coexiste cu operațiunea curentă (de ex. prin staging + preluare asincronă).

Checklistă concretă pentru un bulk-insert robust cu Array DML

• Dimensiunea batch-ului stabiliți-o (valoare inițială 500–1.000) și tunează măsurabil.
• Tranzacție explicită: commit per batch ca implicit; „commit la final” doar conștient.
• Tipuri de parametri stabile setate, evită forțarea de casturi implicite.
• Contextul erorii per înregistrare păstrat (ID extern, rând sursă).
• Strategia de eroare: batch mai întâi, apoi split/fallback, logare pe rând.
• Logging: coduri + text, dar conform protecției datelor; capturează Batch-ID și ID rulare.
• Reluare: asigură idempotenta (cheie/Upsert/Import-ID).

Concluzie: Array DML e rapid — devine robust prin proces și strategie de tratare a erorilor

Un FireDAC Bulk-Insert cu Array DML este un instrument puternic, atâta timp cât nu pretinzi că nu există erori. În fluxuri reale de date există întotdeauna abateri: înregistrări duplicate, referințe lipsă, valori de dată corupte. Abordarea curată este, prin urmare: Array DML pentru performanță, combinat cu o strategie de izolare controlată (Split sau Fallback) și o listă de erori trasabilă pe rând. Astfel obții viteză și siguranță în operare împreună – și exact asta contează când importurile nu rulează doar în laborator, ci trebuie să treacă fiabil în fiecare noapte.

Dacă voi doriți să stabilizați un proces existent de import sau o interfață în Delphi/FireDAC (performanță, tranzacții, reluare, logging), vom clarifica asta structurat într-o discuție tehnică:

Pentru acest subiect sunt importante și Delphi Bulk Insert și Bulk Insert Delphi FireDAC. Articolul ordonează aceste aspecte în mod clar și arată la ce trebuie să fiți atenți în practică.

Discutați proiectul sau inițiativa de modernizare cu Net-Base.