FireDAC: Bulk-Insert con DML basata su array e gestione degli errori per riga

Un BDE-Ablosung mit nativer Anbindung Bulk-Insert con Array DML è spesso il modo più rapido per inserire molti record in un database: invece di mille INSERT singoli si lega un array di parametri e lo si invia al server in un’unica operazione. Nella pratica il problema emerge però rapidamente: un record viola un indice unico, un campo NOT NULL è vuoto, una chiave esterna non corrisponde – e improvvisamente non è chiaro, quale riga ha fatto fallire il batch, se una parte è già stata scritta e come procedere in modo pulito senza generare incoerenze nei dati.

Di questo si tratta qui: come usare Array DML in modo che tu ottenga per riga informazioni d’errore affidabili, mantenga la transazione sotto controllo e possa ricostruire in esercizio cosa è successo. L’attenzione non è su una lettura accademica delle API, ma sul caso limite che negli import reali si presenta regolarmente: un batch grande, poche righe difettose, ma vuoi comunque mantenere la velocità.

FireDAC Bulk-Insert con Array DML: Perché Array DML conviene nel Bulk-Insert

Array DML (Data Manipulation Language) nella FireDAC significa: leghi i parametri non come singolo valore ma come array. FireDAC invia quindi (a seconda del driver/DB) meno roundtrip, può lavorare in modo più efficiente lato server e riduce drasticamente l’overhead sul client. Questo è particolarmente rilevante in tre situazioni:

• Percorsi ETL e pipeline di importazione: CSV/XML/JSON in ingresso, normalizzazione/mapping, poi in tabella di staging o di destinazione.
• Buffer delle interfacce: REST- o MQ-payload vengono raccolti e persistiti periodicamente.
• Tabelle di log/eventi: molti piccoli INSERT per i quali domina la latenza.

Il guadagno però non è gratuito. Con Array DML sposti la complessità da “molte singole statement” a “una statement con molte righe”. Questo è positivo per le prestazioni, ma richiede più attenzione per la diagnosi degli errori, la logica di transazione e la ri-esecuzione.

Il caso limite tipico: un batch, una riga difettosa

Il classico in esercizio: importi 50.000 righe. Scegli una ArraySize di 1.000 perché non vuoi un roundtrip per ogni riga. Il batch 17 fallisce. Il DB segnala solo „duplicate key“ o „violates foreign key constraint“. Nella UI o nei log di servizio spesso compare solo: „ExecSQL failed“.

Senza un handling degli errori pulito succedono di solito due cose negative:

• Butti via tutto il batch, anche se 999 righe su 1.000 sarebbero ok.
• Torni agli INSERT singoli e perdi il vantaggio di prestazioni in modo permanente.

L’obiettivo è una terza via: mantenere le pRESTazioni a batch, ma registrare con precisione i difetti (indice di riga, valori chiave, testo dell’errore DB) e opzionalmente eseguire il commit delle „righe valide“ – a seconda di quanto siano critici consistenza e idempotenza (esecuzione ripetuta senza effetti duplicati) nel tuo processo.

FireDAC Array DML: I parametri rilevanti (senza miti)

Per il Bulk-Insert con Array DML, in pratica sono sempre gli stessi parametri a fare la differenza:

1) ArraySize e dimensione del batch

ArraySize (per TFDQuery/TFDCommand) determina quante „righe“ FireDAC vengono elaborate in una chiamata. Più grande non è automaticamente meglio. Troppo grande significa: più memoria sul client, più payload sulla rete, blocchi/log maggiori sul server e, in caso di errore, un più ampio „Blast Radius“. Per import robusti, spesso una dimensione di batch tra 200 e 2.000 è un buon punto di partenza, a seconda del numero di colonne, dei BLOB e della latenza.

2) Confine della transazione

Serve una decisione chiara: Commit per batch o Commit per l’intero import. Non è una questione di gusto, ma una decisione operativa:

• Commit per batch: limita i blocchi e il log delle transazioni, rende il ripristino più semplice, ma gli stati intermedi sono visibili (a seconda del livello di isolamento). Un errore nel batch 17 lascia i batch 1–16 nel sistema.
• Commit alla fine: ‚tutto o niente‘, più coerente dal punto di vista funzionale, ma con grandi volumi rischi lock prolungati, grandi rollback e, in caso di errore, perdi tutto.

Per molti processi di interfaccia e import, ‚Commit per batch‘ è la strategia operativa più realistica – ma solo se hai gestito correttamente idempotenza e strategia per i duplicati (p. es. tramite chiavi naturali, Upserts o una Import-ID).

3) UpdateOptions e Prepared Statements

Per batch ripetuti conviene mantenere lo statement preparato. „Prepare“ significa: FireDAC permette al DB di parsare/compilare lo statement e riutilizzarlo. A seconda del DB questo può avere un effetto percepibile, soprattutto ad alta frequenza. Qui conta meno un „trucco 17“ e più: riuso coerente dello stesso oggetto Query (o dello stesso TFDCommand) e tipi di parametro stabili.

Gestione degli errori per riga: ciò di cui hai realmente bisogno

Se vuoi gestire gli errori „per riga“, ti servono tre cose:

1. Identificazione: quale indice dell’array (0..N-1) ha fallito?
2. Contesto: quali valori chiave applicativi ha questa riga (p. es. ID esterno, numero cliente, timestamp)?
3. Controllo: cosa fai dopo? Interrompi, escludi solo le righe errate o dividi il batch?

FireDAC può, a seconda del driver, RESTituire errori per elemento dell’array. Tuttavia nella pratica non è „automaticamente garantito“. Devi considerare che alcuni database/provider riportano solo il primo errore o che un errore nel batch impedisca l’esecuzione del RESTo. Proprio per questo un pattern robusto è di solito su due livelli:

• Fase A: prova il batch come Array DML.
• Fase B: se il batch fallisce, dividilo (a metà) o ricadi in modo controllato su righe singole – ma solo per quel batch – e registra in modo accurato.

Sembra più lavoro, ma nelle pipeline di import è la differenza tra „alle 02:00 di notte tutto si blocca“ e „l’import procede, 7 righe finiscono nella lista degli errori“.

Un modello pratico: batch prima, poi isolamento mirato

Il seguente modello si è dimostrato valido per soluzioni software vicine al processo, in cui la qualità dei dati è mista:

Passo 1: Inserire i dati in una struttura a batch (incluso il contesto di errore)

Memorizza i dati da importare non solo come valori grezzi, ma con il contesto minimo: ID esterno, numero di riga dalla fonte, eventualmente hash/checksum. Non è un „Nice to have“: in caso di errore non vuoi dover rieseguire il parsing della CSV per capire cosa non va.

Passo 2: Eseguire Array DML

Imposti ArraySize sulla lunghezza del batch, leghi i parametri come array ed esegui ExecSQL. Importante: mantenere stabili i tipi dei parametri (p. es. per i campi numerici non legare a volte come stringa e a volte come integer), altrimenti il DB produce cast impliciti o FireDAC deve convertire elemento per elemento.

Passo 3: Caso di errore – limitare il batch invece di ripetere alla cieca

Se ExecSQL fallisce, hai due opzioni robuste:

• Binary Split (dividere a metà): suddividere il batch in due metà e provare ciascuna metà nuovamente con Array DML. Ripeti finché non arrivi a una quantità ridotta che puoi verificare riga per riga. Vantaggio: mantieni molta performance se sono poche le righe corrotte. Svantaggio: più logica ed è poco utile in caso di errori sistematici (p. es. tipo dati errato).
• Fallback su righe singole per quel batch: imposti ArraySize=1 (o leghi singoli valori) ed esegui riga per riga, registrando gli errori e proseguendo. Vantaggio: semplice, garantito per riga. Svantaggio: in quel batch perdi velocità.

In pratica combino entrambi: prima 1–2 volte split (per far passare rapidamente i „blocchi buoni“), poi con quantità residue ridotte passare alle righe singole per loggare informazioni di errore univoche.

Oggetti errore e messaggi: cosa dovRESTi estrarre da FireDAC

FireDAC incapsula gli errori DB in eccezioni (tipicamente EFDDBEngineException) con informazioni di dettaglio. Per l’operatività sono importanti tre livelli:

• Codice errore DB (specifico del DB): p. es. SQLSTATE su PostgreSQL, Error Number su SQL Server.
• Nome del vincolo/oggetto: spesso presente nel testo dell’errore (index unico, FK-Constraint).
• Contesto della statement: tabella, operazione, eventualmente valori dei parametri (attenzione ai dati personali).

Se vuoi loggare per riga, nel caso di errore devi inoltre identificare la riga. FireDAC può, in certe circostanze, fornire l’indice dell’array. Non affidarti però solo a quello. Costruisci sempre un indice proprio (posizione nel batch) e registra per quella posizione almeno una chiave di dominio.

Insidie che negli import reali fanno perdere tempo

1) „Era solo una riga“ – ma la transazione è già „dirty“

A seconda del DB e del driver un errore può far considerare fallita l’intera esecuzione della statement e porre la transazione in uno stato in cui devi esplicitamente fare il rollback o in cui ulteriori statement falliranno. Soprattutto con certi driver „dopo un errore continuare come nulla fosse“ non è una supposizione sicura.

Conseguenza: se lavori in una transazione e un batch fallisce, il percorso standard è: Rollback del contesto del batch corrente (o dell’intera transazione) e poi ripartire. Questo si adatta bene al modello „Commit per batch“.

2) Autocommit vs. transazione esplicita

Se non avvii una transazione esplicita, spesso è il driver/provider a decidere come vengono committate le statement. Per import in bulk raramente è ciò che desideri. Le transazioni esplicite ti danno controllo su:

• Durata dei lock
• Comportamento di rollback
• Punti di riavvio

E: „esplicito“ non significa „una transazione enorme“. Significa „consapevole“.

3) Trigger, vincoli e effetti collaterali

Array DML accelera il trasferimento, ma non automaticamente il lavoro lato server. Se sulla tabella di destinazione hai trigger (es. audit-logging, calcolo automatico dello stato), il collo di bottiglia potrebbe non essere l’INSERT, ma il codice del trigger. Un batch può ridurre i roundtrip, ma la CPU sul server DB rimane il fattore limitante.

Per amministratori e responsabili tecnici: in caso di problemi di performance vale la pena esaminare Wait Events/Locks e il log delle transazioni. Il bulk-insert è spesso solo il fattore scatenante, non la causa.

4) Tipi di dato e conversioni implicite

Una delle ragioni più comuni del tipo „Perché è lento?“: i parametri vengono vincolati come stringhe e il DB effettua il cast riga per riga a Integer/Date/Decimal. È invisibile, ma costoso. Per performance stabili:

• Impostare correttamente i tipi di dato dei parametri (data come data, numero come numero).
• Per i decimali fare attenzione alle insidie locali (virgola vs punto). FireDAC è di solito corretto qui, ma le fonti miste non lo sono.
• Definire in anticipo la strategia su fusi orari/UTC (i timestamp sono un classico negli import).

5) I messaggi di errore sono per le persone, non per l’automazione

È allettante parsare il testo dell’errore („duplicate key value violates unique constraint …“). Fallo solo come ultima opzione. Meglio usare codici strutturati (SQLSTATE, Error Number). Purtroppo non tutti i driver forniscono le informazioni nello stesso modo. Prevedi quindi entrambe le cose: codice e testo, più opzionalmente „nome del vincolo dal testo“, ma senza dipendenze rigide.

Suggerimenti per il debugging: come trovi rapidamente la riga guasta

Rendere riproducibile il batch

Se un import fallisce sporadicamente, serve riproducibilità. Salva per ogni batch un piccolo file di diagnostica o una voce di log che contenga:

• Numero del batch e ora
• ArraySize e modalità di transazione
• l’elenco delle chiavi di business (es. ID esterni) nel batch

Spesso è sufficiente per avviare in un secondo momento un mini-import mirato solo per quegli ID.

Rendere visibile la SQL finale (ma senza fughe di dati)

Nel debugging vuoi sapere: la SQL è corretta? I parametri sono corretti? FireDAC offre monitoring/tracing tramite componenti FDMoni e logging del driver. In ambienti vicini alla produzione è importante:

• attivare il tracing in modo mirato e solo temporaneamente (performance e protezione dei dati).
• loggare i valori dei parametri solo in un ambiente sicuro o in forma mascherata.
• per i dati personali: nel log solo chiavi tecniche (ID) e nessun contenuto in chiaro.

Se testi lo split: definire criteri di interruzione

Con il binary split non vuoi dividere all’infinito. Imposta una soglia inferiore, es. „sotto 20 righe passa alla modalità singola“. E definisci un limite su quanti errori tolleri complessivamente prima di interrompere l’import (es. in caso di problemi sistematici di mapping). Altrimenti finisci con liste di errori senza fine e blocchi l’elaborazione successiva.

Quando lo sforzo vale la pena (e quando no)

Array DML con gestione degli errori per riga vale particolarmente la pena quando:

• Vengono processate molte righe (migliaia fino a milioni).
• Poche righe sono errate, ma vuoi comunque proseguire l’elaborazione.
• L’import deve funzionare stabilmente in produzione (es. elaborazione notturna, servizio senza UI).
• Devi restituire una lista degli errori al reparto/fonte (con riferimento alle righe).

Ha meno senso quando:

• scrivi solo qualche dozzina di righe (insert singoli vanno bene),
• la qualità dei dati è così scadente che il 30–50% delle righe fallisce (in tal caso è più sensata una strategia di staging),
• utilizzi comunque una procedura di bulk-load nativa del DB (es. COPY in PostgreSQL, BCP/BULK INSERT in SQL Server) – in quel caso Array DML non è lo strumento.

Architettura alternativa: tabella di staging invece di «inserimento diretto nella tabella di destinazione»

Se devi affrontare regolarmente dati di qualità mista, un semplice «insert diretto nella tabella di destinazione» spesso è la scelta sbagliata. Una tabella di staging (fase preliminare) è una tabella in cui salvi i dati prima in modo tecnicamente corretto (eventualmente con tipi permissivi), e solo dopo li convalidi e li trasferisci nella tabella di destinazione.

Vantaggi in esercizio:

• I record errati rimangono memorizzati in modo tracciabile (incl. dati grezzi).
• Puoi eseguire la validazione separatamente e in modo ripetibile.
• Disaccoppi l’accettazione dell’interfaccia dall’elaborazione funzionale.

Array DML è spesso la via rapida verso la tabella di staging, mentre il trasferimento nella tabella di destinazione avviene tramite SQL set-based (o Stored Procedure). Questo sposta la gestione degli errori maggiormente sul lato DB, il che, a seconda dell’organizzazione (ruoli DBA, Deployment), può essere sensato o non desiderabile.

Esercizio e amministrazione: cosa dovrebbero sapere IT-Leads e Admin

Monitoring: tasso di errore e throughput sono le metriche chiave

Per il funzionamento stabile di un bulk-import due metriche sono più informative della sola «durata»:

• Throughput: righe al minuto (o per batch) incl. picco/mediana.
• Tasso di errore: righe errate per esecuzione, idealmente raggruppate per classe di errore (Unique, FK, NOT NULL, conflitto di tipo).

Se osservi regolarmente questi due valori, riconosci presto se alla fonte è cambiato qualcosa (es. nuovo formato) o se il sistema di destinazione (es. nuovi vincoli) è diventato più restrittivo.

Blocchi e finestre di carico

I bulk-insert possono generare locking e carico IO. Se utenti lavorano in parallelo sulle stesse tabelle, devi considerare Isolation Level, indici e eventualmente partizionamento. In pratica: o esegui gli import nelle finestre di carico, oppure progetti il flusso dati in modo che coesista con l’operatività in corso (es. tramite staging + acquisizione asincrona).

Checklist concreta per un bulk-insert robusto con Array DML

• Dimensione del batch: impostare (valore iniziale 500–1.000) e ottimizzare misurabilmente.
• Transazione esplicita: commit per batch come default, „commit alla fine“ solo consapevolmente.
• Impostare tipi di parametro stabili, non forzare cast impliciti.
• Includere il contesto di errore per record (ID esterna, riga di origine).
• Strategia di errore: batch prima, poi split/fallback, log per riga.
• Logging: codici + testo, ma conforme alla protezione dei dati; registrare Batch-ID e ID dell’esecuzione.
• Ri-esecuzione: garantire idempotenza (chiave/Upsert/Import-ID).

Conclusione: Array DML è veloce – diventa robusto grazie al processo e alla strategia di gestione degli errori

Un FireDAC Bulk-Insert con Array DML è uno strumento potente, purché tu non faccia finta che gli errori non esistano. Nei flussi di dati reali ci sono sempre valori anomali: duplicati, riferimenti mancanti, valori di data corrotti. L’approccio corretto è quindi: Array DML per le prestazioni, combinato con una strategia di isolamento controllata (Split o Fallback) e una lista di errori tracciabile per riga. In questo modo ottieni velocità e affidabilità operativa insieme – e proprio questo conta quando gli import non girano solo in laboratorio, ma devono essere eseguiti in modo affidabile ogni notte.

Se voi volete stabilizzare un processo esistente di importazione o di interfaccia in Delphi/FireDAC (Performance, transazioni, ripartenza, logging), lo definiamo volentieri in modo strutturato in un colloquio tecnico:

Per questo tema sono importanti anche Delphi Bulk Insert e Bulk Insert Delphi FireDAC. L’articolo inquadra questi aspetti in modo comprensibile e mostra a cosa prestare attenzione nella pratica quotidiana.

Discutere progetto o intervento di modernizzazione con Net-Base.