FireDAC: Inserció massiva amb DML en array i gestió neta d'errors per fila

Un BDE-substitució amb connexió nativa Bulk-Insert amb Array DML sol ser la manera més ràpida d’inserir molts registres en una base de dades: en lloc de fer mil insercions individuals, es vincula un array de paràmetres i s’envia de cop al servidor. A la pràctica, però, el nus apareix ràpidament: un registre trenca un índex únic, un camp NOT NULL està buit, una clau forana no coincideix – i de sobte no queda clar, quina fila ha matat el batch, si una part ja s’ha escrit i com continuar de manera neta sense crear incoherències de dades.

Precisament això és el que tractem aquí: com utilitzar Array DML perquè obtinguis per fila informació d’errors fiable, mantinguis la transacció sota control i puguis, en producció, rastrejar què ha passat. L’enfocament no està en la lectura acadèmica de l’API, sinó en el cas límit que apareix regularment en imports reals: un batch gran, poques files defectuoses, però vols igualment velocitat.

FireDAC Bulk-Insert amb Array DML: Per què l’Array DML és beneficiós en un Bulk-Insert

Array DML (Data Manipulation Language) significa en FireDAC: no vincules paràmetres com a valor únic, sinó com un array. FireDAC envia llavors (segons el controlador/BD) menys roundtrips, pot treballar de forma més eficient al costat del servidor i redueix dràsticament la sobrecàrrega al client. Això és especialment rellevant en tres situacions:

• ETL- i processos d’importació: CSV/XML/JSON d’entrada, normalització/mapeig, després a taula d’staging o taula de destinació.
• Búfers d’interfícies: REST- o MQ-payloads es recopilen i es persisteixen periòdicament.
• Taules de registre/esdeveniments: moltes insercions petites on domina la latència.

El benefici no ve gratis. Amb Array DML trasllades la complexitat de «molts statements individuals» a «un statement amb moltes files». Això és bo per al rendiment, però exigeix més per a la diagnosi d’errors, la lògica de transaccions i la reexecució.

El cas límit típic: un batch, una fila defectuosa

El clàssic en producció: importes 50.000 files. Tries una ArraySize de 1.000 perquè no vulguis un roundtrip per cada fila. El batch 17 falla. La BD només retorna „duplicate key“ o „violates foreign key constraint“. A la UI o al log del servei sovint només apareix: „ExecSQL failed“.

Sense un maneig d’errors adequat solen passar dues coses dolentes:

• Descartes tot el batch, encara que 999 de 1.000 files estiguin correctes.
• Tornes a les insercions individuals i perds el benefici de rendiment de manera permanent.

L’objectiu és un tercer camí: conservar la rendiment per batch, però registrar els defectes amb precisió (índex de fila, valors de clau, text d’error de la BD) i opcionalment commitar „good rows“ – depenent de com de crítics siguin la consistència i la idempotència (executar diverses vegades sense efectes duplicats) en el teu procés.

FireDAC Array DML: Els paràmetres rellevants (sense mites)

Per a Bulk-Insert amb Array DML, a la pràctica sempre hi ha els mateixos paràmetres decisius:

1) ArraySize i mida del batch

ArraySize (en TFDQuery/TFDCommand) determina quantes „files“ FireDAC es processen en una crida. Més gran no és automàticament millor. Massa gran vol dir: més memòria al client, més payload a la xarxa, bloquejos i càrrega del log més grans al servidor i, en cas d’error, un major „Blast Radius“. Per a imports robustos sovint una mida de batch entre 200 i 2.000 és un bon punt de partida, depenent del nombre de columnes, dels BLOBs i de la latència.

2) Límits de la transacció

Has de prendre una decisió clara: Commit pro Batch o Commit per a tot l’import. Això no és una qüestió de gust, sinó una decisió operativa:

• Commit pro Batch: limita els bloquejos i el registre de transaccions, facilita la recuperació, però els estats intermedis són visibles (segons l’Isolation Level). Un error en el batch 17 deixa els batch 1–16 al sistema.
• Commit al final: «Tot o res», més consistent en un sentit funcional, però amb grans volums assumeixes riscos de bloquejos llargs, rollback massiu i, en cas d’error, es perd tot.

Per a molts processos d’interfície i d’importació, „Commit pro Batch“ és l’estratègia operativa més realista – pero només si tens ben definides la idempotència i l’estratègia de duplicats (p. ex. mitjançant claus naturals, upserts o una Import-ID).

3) UpdateOptions i Prepared Statements

Per a batches repetits val la pena mantenir l’statement preparat. „Prepare“ vol dir: FireDAC permet que la DB parsegi/compili l’statement i el reutilitzi. Segons la BD això pot tenir un efecte notable, sobretot amb alta freqüència. L’important aquí no és un „Trick 17“, sinó: reutilització consistent del mateix objecte Query (o del mateix TFDCommand) i tipus de paràmetres estables.

Gestió d’errors per fila clara: el que realment necessites

Si vols tractar errors „per fila“, necessites tres coses:

1. Assignació: quin índex d’array (0..N-1) ha fallat?
2. Context: quins valors clau funcionals té aquesta fila (p. ex. ID externa, número de client, segell temporal)?
3. Control: què fas després? Aturar, saltar només les files dolentes o dividir el batch?

FireDAC pot, segons el controlador, retornar errors per element de l’array. A la pràctica això no està «simplement sempre disponible». Has de preveure que algunes bases de dades/proveïdors només reportin el primer error o que un error en el batch impedeixi l’execució de la RESTa. Precisament per això, un patró robust sol ser de dos nivells:

• Nivell A: prova el batch com Array DML.
• Nivell B: si el batch falla, divideix-lo (a meitat) o cau de manera controlada a l’execució per files – però només per aquest batch – i registra-ho de forma clara.

Això sembla més feina, però en rutes d’importació és la diferència entre „a les 02:00 tot s’atura“ i „l’importació continua, 7 files acaben a la llista d’errors“.

Un patró pràctic: primer el batch, després aïllar de manera dirigida

El següent patró ha demostrat la seva eficàcia per a solucions de programari properes al procés en què la qualitat de les dades és variada:

Pas 1: Posar les dades en una estructura de lot (inclòs el context d’error)

Desa les dades a importar no només com a valors bruts, sinó amb el context mínim: ID externa, número de línia de la font, eventualment hash/checksum. Això no és „Nice to have“: en cas d’error no voldràs parsejar la CSV de nou per esbrinar què està malament.

Pas 2: Executar Array DML

Configures ArraySize amb la longitud del lot, vincules els paràmetres com a arrays i executes ExecSQL. Important: mantén els tipus de paràmetre estables (p. ex., per a camps numèrics no vincular a vegades com a String i altres vegades com a Integer), si no la DB produirà casts implícits o FireDAC haurà de convertir per cada element.

Pas 3: En cas d’error – acotar el lot en lloc de repetir a cegues

Si ExecSQL falla, tens dues opcions robustes:

• Binary Split (dividir per la meitat): divideix el lot en dues meitats i prova cada meitat novament com a Array DML. Repeteix això fins que arribis a una quantitat petita que puguis comprovar individualment. Avantatge: mantens gran part del rendiment quan només poques línies estan malmeses. Desavantatge: requereix més lògica i amb errors sistemàtics (p. ex. tipus de dada incorrecte) aporta poc.
• Fallback a línies individuals per a aquest lot: configures ArraySize=1 (o vincules valors individuals) i executes línia per línia, registres errors i continues. Avantatge: senzill, garantit per línia. Desavantatge: en aquest lot perds velocitat.

A la pràctica combino ambdós: primer divido 1–2 vegades (per passar ràpid els «blocs bons»), després, amb RESTes petites, passo a línies individuals per enregistrar informació d’error inequívoca.

Objectes d’error i missatges: què hauries d’extreure de FireDAC

FireDAC encapsula errors de DB en Exceptions (típicament EFDDBEngineException) amb informació detallada. Per a l’explotació n’hi ha tres nivells importants:

• Codi d’error DB (específic de la DB): p. ex. SQLSTATE a PostgreSQL, Error Number a SQL Server.
• Nom del constraint/objecte: sovint contingut al text d’error (Unique-Index, FK-Constraint).
• Context de l’statement: taula, operació, si escau valors de paràmetres (amb precaució amb dades personals).

Si vols registrar per línia, en cas d’error a més has d‘identificar la línia. FireDAC pot, en segons quins casos, proporcionar l’índex de l’array. No et fiïs només d’això. Construeix sempre addicionalment un índex propi (posició dins del lot) i registra per aquesta posició com a mínim una clau de negoci.

Paranys que en imports reals costen temps

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

Segons la DB i el driver, un error pot fer que l’execució de l’instrucció es consideri fallida i que la transacció quedi en un estat en què calgui o bé fer un rollback explícit o bé les instruccions posteriors fallin. Especialment amb certs drivers, „després d’un error continuar simplement“ no és una suposició segura.

Conseqüència: si treballes dins d’una transacció i un lot falla, el camí estàndard és: Rollback del context del lot actual (o de tota la transacció) i tornar a començar. Això s’adapta bé a „Commit per lot“.

2) Autocommit vs. explizite Transaktion

Si no inicies una transacció explícita, sovint el driver/provider decideix com commiteja les instruccions. Per a imports massius això rarament és el que vols. Les transaccions explícites et donen control sobre:

• Durada dels locks
• Comportament de rollback
• Punts de reinici

I: Explícit no vol dir „una transacció enorme“. Vol dir „conscient“.

3) Trigger, Constraints und Nebenwirkungen

Array DML accelera la transferència, però no automàticament la feina al servidor. Si tens triggers a la taula de destinació (p. ex. audit-logging, càlcul automàtic d’estat), el coll d’ampolla potser no sigui l’INSERT, sinó el codi del trigger. Llavors un batch pot tenir menys roundtrips, però la CPU del servidor de BD continua sent el factor limitant.

Per a administradors i responsables tècnics: davant problemes de rendiment val la pena analitzar els Wait Events/Locks i el registre de transaccions. El bulk-insert serà llavors només el desencadenant, no la causa.

4) Datentypen und implizite Konvertierungen

Una de les raons més comunes del «Per què és lent?» és que els paràmetres s’enllacen com a string i la DB converteix per fila a Integer/Date/Decimal. Això és invisible, però costós. Per a un rendiment estable:

• Assignar els tipus de dades dels paràmetres de manera adequada (data com a data, nombre com a nombre).
• Amb els decimals, vigilar les trampes de localització (coma vs. punt). FireDAC sol ser correcte aquí, però les fonts mixtes no ho són.
• Clarificar prèviament l’estratègia de zones horàries/UTC (els timestamps en imports són un clàssic).

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

És temptador parsejar el text d’error («duplicate key value violates unique constraint …»). Fes-ho només com a última opció. Millor són codis estructurats (SQLSTATE, número d’error). Malauradament no tots els drivers retornen la mateixa informació. Planifica per tant ambdues coses: codi i text, més opcionalment «nom de la constraint a partir del text», però sense una dependència rígida.

Debugging-Hinweise: So findest du schnell die kaputte Zeile

Batch reproduzierbar machen

Si una importació falla esporàdicament, necessites reproduibilitat. Desa per batch un petit fitxer de diagnosi o una entrada de registre que contingui:

• Número de batch i hora
• ArraySize i mode de transacció
• la llista de claus d’àmbit funcional (p. ex. IDs externs) del batch

Això sovint és suficient per, posteriorment, llançar un mini-import només per a aquests IDs.

Finale SQL sichtbar machen (aber ohne Datenleaks)

En depuració vols saber: la SQL és correcta? Els paràmetres són els adequats? FireDAC ofereix monitoring/tracing a través de components FDMoni i logging de drivers. En entorns propers a producció és important:

• Activar el tracing de manera selectiva i només temporal (rendiment i protecció de dades).
• Registrar els valors dels paràmetres només en un entorn segur o enmascarats.
• Per a dades personals: al registre només claus tècniques (IDs) i cap contingut en text clar.

Wenn du split-testest: Abbruchkriterien definieren

En el binary split no vols dividir indefinidament. Estableix un llindar inferior, p. ex. «per sota de 20 files canvia a mode individual». I fixa un límit de quants errors toleres en total abans d’aturar l’import (p. ex. en problemes sistemàtics de mapeig). Altrament cauràs en llistes d’errors sense fi i bloquejaràs el processament posterior.

Wann sich der Aufwand wirklich lohnt (und wann nicht)

Array DML amb gestió d’errors per fila compensa especialment quan:

• Moltes files es processen (milers fins a milions).
• Poques files defectuoses hi ha, però vols que l’execució continui.
• L’import ha de funcionar de manera estable en producció (p. ex. processament nocturn, servei sense UI).
• Has de retornar una llista d’errors al departament/font d’origen (amb referència a la fila).

És menys útil quan:

• si només escrius unes poques desenes de línies (les insercions individuals són adequades),
• la qualitat de dades és tan baixa que el 30–50% de les línies fallen (en aquest cas, una estratègia d’estaging és més adient),
• si de totes maneres utilitzes un procediment de càrrega massiva nadiu de la BD (p. ex. COPY a PostgreSQL, BCP/BULK INSERT a SQL Server) – en aquest cas Array DML no és l’eina.

Arquitectura alternativa: taula d’estaging en lloc de „Directament a la destinació“

Si lluites regularment amb qualitat de dades mixta, un pur «Insert directe a la taula de destinació» sovint és la decisió equivocada. Una taula d’estaging (etapa prèvia) és una taula on emmagatzemes les dades primer tècnicament correctes (p. ex. amb tipus laxos), i només després les valides i les transfereixes a la taula de destinació.

Avantatges en el funcionament:

• Els registres erronis es mantenen emmagatzemats de manera rastrejable (incloses les dades en brut).
• Pots executar la validació de manera separada i repetible.
• Desacoples l’acceptació de la interfície del processament funcional.

Array DML sovint és el camí ràpid cap a la taula d’estaging, mentre que la transferència a la taula de destinació es fa mitjançant SQL basat en conjunts (o procediment emmagatzemat). Això trasllada el manejament d’errors més al costat de la BD, cosa que segons l’organització (rols de DBA, desplegament) pot ser adient o no desitjable.

Operació i administració: què han de saber els responsables IT i els administradors

Monitoratge: taxa d’errors i rendiment són les mètriques clau

Per a l’operació estable d’un import massiu, dues mètriques són més informatives que la «durada» per si sola:

• Rendiment: línies per minut (o per lot) incloent pic/mediana.
• Taxa d’errors: línies defectuoses per execució, idealment agrupades per classe d’error (Unique, FK, NOT NULL, conflicte de tipus).

Si visualitzes aquests dos valors de manera regular, detectaràs aviat si hi ha hagut canvis a la font (p. ex. un nou format) o si el sistema de destinació (p. ex. noves constraints) s’ha tornat més estricte.

Bloquejos i finestres de càrrega

Els inserts massius poden generar locking i càrrega d’E/S. Si, en paral·lel, usuaris treballen sobre les mateixes taules, has de tenir en compte el nivell d’aïllament, els índexs i, si cal, la partició. Pràcticament això vol dir: o bé programar els imports en finestres de càrrega, o dissenyar el flux de dades perquè coexisteixi amb l’operació en curs (p. ex. mitjançant estaging + incorporació asíncrona).

Llista de comprovació concreta per a un Bulk-Insert robust amb Array DML

• Mida del batch: establir-la (valor inicial 500–1.000) i ajustar-la de manera mesurable.
• Transacció explícita: commit per batch per defecte; «commit al final» només de manera conscient.
• Establir tipus de paràmetre estables, no forçar conversions implícites.
• Portar context d’error per registre (ID externa, línia d’origen).
• Estrategia d’errors: batch primer, després split/fallback, registrar per línia.
• Logging: codis + text, però conforme a la protecció de dades; capturar Batch-ID i Run-ID.
• Reexecució: assegurar idempotència (clau/upsert/Import-ID).

Conclusió: Array DML és ràpid – la robustesa prové del procés i de l’estratègia d’errors

Un FireDAC Bulk-Insert amb Array DML és una eina potent, sempre que no facis veure que no existeixen errors. En fluxos de dades reals sempre hi ha valors atípics: duplicats, referències inexistents, valors de data malmesos. L’enfocament net és per tant: Array DML per al rendiment, combinat amb una estratègia d’aïllament controlada (Split o Fallback) i una llista d’errors rastrejable per línia. Així obtens velocitat i seguretat operativa juntes – i això és el que compta quan els imports no només funcionen al laboratori, sinó que han de passar cada nit de manera fiable.

Si voleu estabilitzar un procés existent d’importació o d’interfície en Delphi/FireDAC (rendiment, transaccions, reinici, registre), ho abordarem de manera estructurada en una conversa tècnica:

Per a aquest tema també són importants Delphi Bulk Insert i Bulk Insert Delphi FireDAC. La publicació situa aquests aspectes de manera entenedora i mostra què cal tenir en compte en l’operativa diària.

Parlar del projecte o de la iniciativa de modernització amb Net-Base.