FireDAC: Insertion en masse avec DML par tableau et gestion propre des erreurs par enregistrement

Une BDE-remplacement avec intégration native pour un Bulk-Insert via Array DML est souvent la manière la plus rapide d’insérer un grand nombre d’enregistrements dans une base de données : au lieu de mille INSERTs individuels, on lie un tableau de paramètres et on l’envoie d’un coup au serveur. En pratique, cependant, le point sensible apparaît rapidement : un enregistrement enfreint un index unique, un champ NOT NULL est vide, une clé étrangère ne correspond pas — et soudain il n’est plus clair quelle ligne a fait échouer le lot, si une partie a déjà été écrite et comment poursuivre proprement sans créer d’incohérences de données.

C’est précisément l’objet de cet article : comment utiliser Array DML de sorte à obtenir des informations d’erreur fiables par ligne, garder la transaction sous contrôle et pouvoir reconstituer en production ce qui s’est passé. L’accent n’est pas mis sur une lecture académique des API, mais sur le cas limite qui survient régulièrement dans de véritables imports : un gros lot, quelques lignes défectueuses, mais tu veux conserver la vitesse.

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

Array DML (Data Manipulation Language) signifie dans FireDAC : tu lies des paramètres non pas comme des valeurs uniques, mais comme des tableaux. FireDAC envoie alors (selon le pilote/la BD) moins de allers-retours, peut travailler plus efficacement côté serveur et réduit drastiquement l’overhead côté client. Cela devient particulièrement pertinent dans trois situations :

• Flux ETL et parcours d’import : CSV/XML/JSON entrants, normalisation/mapping, puis vers une table de staging ou de destination.
• Tampons d’interface : des MQ- ou REST-payloads sont accumulés et périodiquement persistés.
• Tables de journalisation / d’événements : de nombreux petits INSERTs où la latence domine.

Le gain n’est cependant pas gratuit. Avec Array DML, tu déplaces la complexité de « plusieurs statements individuels » vers « un statement contenant de nombreuses lignes ». C’est bon pour la performance, mais cela exige plus sur le plan du diagnostic d’erreurs, de la logique transactionnelle et du reprise.

Le cas limite typique : un lot, une ligne cassée

Le scénario classique en production : tu importes 50 000 lignes. Tu choisis une ArraySize de 1 000 pour éviter un aller-retour par ligne. Le lot 17 échoue. La BD ne renvoie que « duplicate key » ou « violates foreign key constraint ». Dans l’interface ou dans le log du service, on voit souvent seulement : « ExecSQL failed ».

Sans gestion d’erreurs soignée, deux mauvaises conséquences surviennent généralement :

• Tu jettes tout le lot, alors que 999 des 1 000 lignes auraient été valides.
• Tu rebascules sur des INSERTs individuels et perds définitivement l’avantage de performance.

L’objectif est une troisième voie : conserver la performance par lot, mais consigner de manière exacte les défaillances (index de ligne, valeurs de clé, texte d’erreur DB) et, en option, valider les « good rows » — selon la criticité de la cohérence et de l’idempotence (exécution répétée sans effet doublon) dans ton processus.

FireDAC Array DML : Les réglages pertinents (sans mythes)

Pour les insertions en masse avec Array DML, les mêmes réglages sont toujours déterminants en pratique :

1) ArraySize et taille du batch

ArraySize (pour TFDQuery/TFDCommand) détermine combien de « lignes » FireDAC sont traitées en un appel. Plus grand n’est pas automatiquement mieux. Trop grand signifie : plus de mémoire côté client, plus de charge utile sur le réseau, des verrous/une charge de log plus importants côté serveur et, en cas d’erreur, un plus grand « rayon d’impact ». Pour des imports robustes, une taille de batch entre 200 et 2 000 est souvent un bon point de départ, selon le nombre de colonnes, les BLOBs et la latence.

2) Frontière de transaction

Il te faut une décision claire : Commit par batch ou Commit pour l’ensemble de l’import. Ce n’est pas une question de goût, mais une décision opérationnelle :

• Commit par batch : limite les verrous et le journal de transactions, facilite le redémarrage, mais les états intermédiaires sont visibles (selon le niveau d’isolation). Une erreur dans le batch 17 laisse les batchs 1–16 dans le système.
• Commit à la fin : « tout ou rien », plus cohérent d’un point de vue métier, mais pour de gros volumes tu risques des verrous prolongés, de lourds rollbacks et, en cas d’erreur, la perte de tout.

Pour de nombreux processus d’interface et d’import, « Commit par batch » est la stratégie opérationnelle la plus réaliste — mais seulement si tu as réglé proprement l’idempotence et la stratégie de doublons (par ex. via des clés naturelles, des upserts ou un ID d’import).

3) UpdateOptions et Prepared Statements

Pour des batchs répétés, il vaut la peine de laisser la requête préparée. « Prepare » signifie : FireDAC laisse la DB parser/compilier la requête et la réutiliser. Selon la DB, cela peut avoir un effet sensible, surtout à haute fréquence. Il s’agit moins d’une astuce que de : la réutilisation systématique du même objet Query (ou du même TFDCommand) et des types de paramètres stables.

Gestion propre des erreurs par ligne : ce dont tu as vraiment besoin

Si tu veux traiter les erreurs « par ligne », tu as besoin de trois choses :

1. Identification : quel index de l’Array (0..N-1) a échoué ?
2. Contexte : quelles valeurs de clés métier possède cette ligne (p. ex. ID externe, numéro client, horodatage) ?
3. Contrôle : que fais-tu ensuite ? Abandonner, ignorer uniquement les lignes défectueuses, ou scinder le batch ?

FireDAC peut, selon le pilote, retourner des erreurs par élément d’Array. En pratique, ce n’est pas systématique. Il faut s’attendre à ce que certaines bases de données/fournisseurs n’annoncent que la première erreur ou qu’une erreur dans le batch empêche l’exécution du RESTe. C’est précisément pour cela qu’un modèle robuste est généralement en deux temps :

• Étape A : tenter le batch en Array DML.
• Étape B : si le batch échoue, le scinder (diviser par deux) ou retomber de façon contrôlée sur des lignes individuelles — mais seulement pour ce batch — et logger proprement.

Cela semble plus de travail, mais sur les chaînes d’import c’est la différence entre « à 02:00 tout s’arrête » et « l’import passe, 7 lignes atterrissent dans la liste d’erreurs ».

Un modèle pragmatique : batch d’abord, puis isolation ciblée

Le modèle suivant s’est avéré efficace pour des solutions logicielles proches du processus, dans lesquelles la qualité des données est hétérogène :

Étape 1 : encapsuler les données dans une structure de batch (y compris le contexte d’erreur)

Stockez les données à importer non seulement comme des valeurs brutes, mais avec un contexte minimal : ID externe, numéro de ligne dans la source, éventuellement hash/somme de contrôle. Ce n’est pas un « Nice to have » : en cas d’erreur, vous ne voulez pas reparcourir la CSV pour déterminer ce qui est cassé.

Étape 2 : exécuter l’Array DML

Vous réglez ArraySize sur la longueur du batch, liez les paramètres en tant que tableaux, et exécutez ExecSQL. Important : maintenir la stabilité des types de paramètres (p. ex. pour des champs numériques, ne pas lier parfois en tant que String, parfois en tant qu’Integer), sinon la DB produit des cast implicites ou FireDAC doit convertir élément par élément.

Étape 3 : cas d’erreur — RESTreindre le batch au lieu de répéter aveuglément

Si ExecSQL échoue, vous avez deux options robustes :

• Binary Split (division binaire) : diviser le batch en deux moitiés et tenter à nouveau chaque moitié en Array DML. Répétez jusqu’à obtenir un petit ensemble que vous pouvez vérifier individuellement. Avantage : vous conservez beaucoup de performance si seules quelques lignes sont défectueuses. Inconvénient : logique plus complexe, et en cas d’erreurs systémiques (p. ex. type de données incorrect) cela apporte peu.
• Fallback auf Einzelzeilen pour ce batch : vous réglez ArraySize=1 (ou liez des valeurs individuelles) et exécutez ligne par ligne, consignez les erreurs et continuez. Avantage : simple, garanti par ligne. Inconvénient : vous perdez de la vitesse sur ce batch.

En pratique, je combine les deux : d’abord scinder 1–2 fois (pour faire passer rapidement les « bons blocs »), puis, sur les petits RESTes, passer aux lignes individuelles afin de consigner des informations d’erreur précises.

Objets d’erreur et messages : ce que vous devriez extraire de FireDAC

FireDAC encapsule les erreurs DB dans des exceptions (typiquement EFDDBEngineException) avec des informations détaillées. Pour l’exploitation, trois niveaux sont importants :

• Code d’erreur DB (spécifique à la DB) : p. ex. SQLSTATE pour PostgreSQL, Error Number pour SQL Server.
• Nom de contrainte/objet : souvent présent dans le texte d’erreur (index unique, contrainte FK).
• Contexte de l’instruction : table, opération, éventuellement valeurs des paramètres (prudence avec les données à caractère personnel).

Si vous voulez consigner par ligne, vous devez en cas d’erreur en plus identifier la ligne. FireDAC peut, dans certains cas, fournir l’index du tableau. Ne vous fiez toutefois pas uniquement à cela. Ajoutez toujours un index propre (position dans le batch) et consignez pour cette position au minimum une clé métier.

Pièges qui font perdre du temps lors d’importations réelles

1) « Ce n’était qu’une ligne » — mais la transaction est déjà « dirty »

Selon la DB et le pilote, une erreur peut faire considérer l’exécution complète de l’instruction comme échouée et laisser la transaction dans un état où vous devez soit effectuer un rollback explicite, soit où d’autres instructions échouent. Avec certains pilotes, « après une erreur continuer simplement » n’est pas une hypothèse sûre.

Conséquence : si vous travaillez dans une transaction et qu’un batch échoue, le chemin standard est : Rollback du contexte du batch en cours (ou de la transaction entière) puis reprise. Cela s’accorde bien avec « Commit pro Batch ».

2) Autocommit vs. transaction explicite

Si vous ne démarrez pas de transaction explicite, le pilote/fournisseur décide souvent comment il commit les instructions. Pour les imports en masse, ce n’est généralement pas ce que vous voulez. Les transactions explicites vous donnent le contrôle sur :

• Durée des verrous
• Comportement de rollback
• Points de reprise

Et : explicite ne signifie pas « une transaction énorme ». Cela signifie « délibéré ».

3) Déclencheurs, contraintes et effets secondaires

Array DML accélère la remise, mais pas automatiquement le travail côté serveur. Si vous avez des triggers sur la table cible (p. ex. journalisation d’audit, calcul automatique d’état), le goulot d’étranglement peut ne pas être l’insert, mais le code du trigger. Un lot peut réduire les allers-retours, mais la CPU du serveur de BD reste le facteur limitant.

Pour les administrateurs et responsables techniques : en cas de problèmes de performance, regardez les wait events/locks et le journal des transactions. Le bulk-insert est alors souvent le déclencheur, pas la cause.

4) Types de données et conversions implicites

L’une des raisons les plus fréquentes du « pourquoi c’est lent ? » : les paramètres sont liés comme des chaînes, la BD convertit ligne par ligne en entier/date/décimal. C’est invisible, mais coûteux. Pour des performances stables :

• Définir correctement les types de paramètres (date en tant que date, nombre en tant que nombre).
• Pour les décimales, attention aux pièges liés aux paramètres régionaux (virgule vs point). FireDAC est en général correct ici, mais les sources mixtes ne le sont pas.
• Clarifier au préalable la stratégie de fuseaux horaires/UTC (les timestamps sont un classique lors des imports).

5) Les messages d’erreur sont pour les humains, pas pour l’automatisation

Il est tentant d’analyser le texte d’erreur (« duplicate key value violates unique constraint … »). Ne le faites qu’en dernier recours. Les codes structurés (SQLSTATE, numéro d’erreur) sont préférables. Malheureusement, tous les pilotes ne fournissent pas tout de la même façon. Prévoyez donc les deux : code et texte, plus éventuellement « nom de la contrainte extrait du texte », mais sans dépendance stricte.

Conseils de débogage : comment trouver rapidement la ligne défectueuse

Rendre le lot reproductible

Si un import échoue de manière sporadique, vous avez besoin de reproductibilité. Enregistrez pour chaque lot un petit fichier de diagnostic ou une entrée de log contenant :

• Numéro du lot et horodatage
• ArraySize et mode de transaction
• la liste des clés métier (p. ex. IDs externes) du lot

Cela suffit souvent pour relancer ensuite un mini-import ciblé uniquement sur ces IDs.

Rendre le SQL final visible (mais sans fuite de données)

Lors du débogage vous voulez savoir : le SQL est-il correct ? Les paramètres sont-ils justes ? FireDAC propose du monitoring/tracing via des composants FDMoni et le logging des pilotes. En environnement proche de la production, il est important :

• Activer le tracing de manière ciblée et temporaire (performance et protection des données).
• Ne logger les valeurs de paramètres que dans un environnement sécurisé ou masquées.
• Pour les données personnelles : ne consigner dans les logs que des clés techniques (IDs) et aucun contenu en clair.

Si vous testez par découpage (split) : définir des critères d’arrêt

Avec le split binaire, vous ne voulez pas diviser indéfiniment. Fixez un seuil minimal, p. ex. « en dessous de 20 lignes, basculer en mode individuel ». Et définissez une limite du nombre d’erreurs tolérées au total avant d’interrompre l’import (p. ex. en cas de problèmes systématiques de mapping). Sinon vous vous retrouvez avec des listes d’erreurs sans fin et vous bloquez le traitement ultérieur.

Quand l’effort en vaut vraiment la peine (et quand ce n’est pas le cas)

Array DML avec gestion d’erreur par ligne vaut particulièrement le coup lorsque :

• Beaucoup de lignes sont traitées (des milliers à des millions).
• Peu de lignes sont erronées, mais vous souhaitez malgré tout poursuivre le traitement.
• L’import doit fonctionner de manière stable en production (p. ex. traitement nocturne, service sans interface utilisateur).
• Vous devez renvoyer une liste d’erreurs au service métier/source (avec référence aux lignes).

Cela vaut moins la peine si :

• vous n’écrivez que quelques dizaines de lignes (des insertions individuelles sont acceptables),
• la qualité des données est si mauvaise que 30–50% des lignes échouent (dans ce cas, une stratégie de staging est plus appropriée),
• vous utilisez de toute façon une procédure de chargement massif native à la BD (p. ex. COPY dans PostgreSQL, BCP/BULK INSERT dans SQL Server) — alors Array DML n’est pas l’outil.

Architecture alternative : table de staging au lieu de « Directement dans la cible »

Si vous êtes régulièrement confronté à une qualité de données hétérogène, un simple « insert direct dans la table cible » est souvent le mauvais choix. Une table de staging (pré-étape) est une table où vous stockez d’abord les données de manière techniquement correcte (éventuellement avec des types permissifs), puis vous les validez et les transférez ensuite dans la table cible.

Avantages en exploitation :

• Les enregistrements erronés restent stockés de manière traçable (y compris les données brutes).
• Vous pouvez exécuter la validation séparément et de façon répétable.
• Vous découplez l’acceptation de l’interface du traitement métier.

Array DML est souvent alors le moyen rapide d’insérer dans la table de staging, tandis que le transfert vers la table cible se fait via du SQL basé sur les ensembles (ou une procédure stockée). Cela déplace la gestion des erreurs davantage côté BD, ce qui peut être pertinent ou indésirable selon l’organisation (rôles DBA, déploiement).

Exploitation et administration : ce que les IT-Leads et administrateurs doivent savoir

Monitoring : le taux d’erreur et le débit sont les métriques clés

Pour l’exploitation stable d’un import massif, deux métriques sont plus parlantes que la « durée » seule :

• Débit : lignes par minute (ou par lot) incluant pic/médiane.
• Taux d’erreur : lignes erronées par exécution, idéalement regroupées par classes d’erreur (Unique, FK, NOT NULL, conflit de type).

Si vous suivez régulièrement ces deux valeurs, vous détectez tôt si la source a changé (p. ex. nouveau format) ou si le système cible (p. ex. nouvelles contraintes) est devenu plus strict.

Verrouillage et fenêtres de charge

Les bulk-inserts peuvent provoquer du verrouillage et une charge IO. Si des utilisateurs travaillent en parallèle sur les mêmes tables, vous devez réfléchir au niveau d’isolation, aux index et éventuellement au partitionnement. Concrètement : soit planifier les imports pendant des fenêtres de charge, soit concevoir le flux de données pour qu’il coexiste avec l’exploitation en cours (p. ex. via staging + reprise asynchrone).

Checklist concrète pour un Bulk-Insert robuste avec Array DML

• Taille de lot : définir (valeur initiale 500–1 000) et optimiser de manière mesurable.
• Transaction explicite : commit par lot par défaut, « commit à la fin » uniquement en connaissance de cause.
• Types de paramètres stables : définir et ne pas forcer de conversions implicites.
• Contexte d’erreur par enregistrement : l’accompagner (ID externe, ligne source).
• Stratégie d’erreur : batch d’abord, puis split/fallback, journaliser par ligne.
• Journalisation : codes + texte, mais conforme à la protection des données ; enregistrer l’ID du lot et l’ID d’exécution.
• Relance : garantir l’idempotence (clé/upsert/ID d’import).

Conclusion : Array DML est rapide – la robustesse s’obtient par le processus et la stratégie de gestion des erreurs

Un FireDAC Bulk-Insert avec Array DML est un outil puissant, tant que tu ne fais pas comme si les erreurs n’existaient pas. Dans des flux de données réels, il y a toujours des valeurs aberrantes : doublons, références manquantes, dates invalides. L’approche propre est donc : Array DML pour la performance, combiné à une stratégie d’isolation contrôlée (Split ou Fallback) et une liste d’erreurs traçable par ligne. Ainsi tu obtiens vitesse et sécurité opérationnelle ensemble – et c’est précisément ce qui compte lorsque les imports ne tournent pas seulement en laboratoire, mais doivent s’exécuter de manière fiable chaque nuit.

Si vous souhaitez stabiliser un processus d’import ou d’interface existant dans Delphi/FireDAC (performance, transactions, redémarrage, logging), nous clarifions cela volontiers de manière structurée lors d’un entretien technique :

Pour ce sujet, les Bulk Insert Delphi et Bulk Insert Delphi FireDAC sont également importants. L’article replace ces aspects de manière compréhensible et montre ce qui compte au quotidien.

Discuter d’un projet ou d’une modernisation avec Net-Base.