FireDAC: Inserción masiva con DML basado en arrays y manejo de errores por fila

Un BDE-Ablosung con conexión nativa Bulk-Insert con Array DML suele ser la forma más rápida de insertar muchos registros en una base de datos: en lugar de mil inserts individuales se enlaza un array de parámetros y se envía de una vez al servidor. En la práctica el punto crítico aparece pronto: un registro viola un índice único (Unique-Index), un campo NOT NULL está vacío, una Foreign Key no coincide — y de repente no está claro qué fila ha provocado el fallo del lote, si una parte ya se escribió y cómo continúas de forma ordenada sin generar inconsistencias de datos.

Exactamente de eso trata esto: cómo usar Array DML para que obtengas por fila información de error fiable, mantengas la transacción bajo control y puedas, en producción, reconstruir qué ocurrió. El enfoque no es una lectura académica de la API, sino el caso límite que en importaciones reales aparece con regularidad: un lote grande, pocas filas dañadas, pero quieres mantener la velocidad.

FireDAC Bulk-Insert con Array DML: Por qué tiene sentido usar Array DML en un Bulk-Insert

Array DML (Data Manipulation Language) significa en FireDAC: enlazas parámetros no como valores individuales, sino como arrays. FireDAC envía entonces (según el driver/DB) menos roundtrips, puede trabajar de forma más eficiente en el servidor y reduce drásticamente el overhead en el cliente. Esto es especialmente relevante en tres situaciones:

• Rutas de ETL e importación: CSV/XML/JSON entran, normalización/mapeo, luego a tabla de staging o de destino.
• Buffer de interfaces: REST- o MQ-Payloads se acumulan y se persisten periódicamente.
• Tablas de registro/eventos: muchos inserts pequeños en los que domina la latencia.

La ganancia no viene gratis. Con Array DML desplazas la complejidad de «muchas sentencias individuales» a «una sentencia con muchas filas». Eso es bueno para el rendimiento, pero exige más en diagnóstico de errores, lógica de transacción y reintentos.

El caso límite típico: un lote, una fila defectuosa

El clásico en producción: importas 50.000 filas. Eliges una ArraySize de 1.000 porque no quieres un roundtrip por cada fila. El batch 17 falla. La DB solo devuelve «duplicate key» o «violates foreign key constraint». En la UI o en el log del servicio a menudo solo aparece: «ExecSQL failed».

Sin un manejo de errores adecuado suelen ocurrir entonces dos cosas malas:

• Descartas todo el batch, aunque 999 de 1.000 filas estarían bien.
• Vuelves a los inserts individuales y pierdes la ventaja de rendimiento de forma permanente.

El objetivo es una tercera vía: mantener el rendimiento por lotes, pero registrar con precisión los defectos (índice de fila, valores clave, texto de error de la BD) y opcionalmente confirmar (commit) las filas buenas —dependiendo de lo crítico que sean la consistencia y la idempotencia (ejecuciones repetidas sin efecto duplicado) en tu proceso.

FireDAC Array DML: Los ajustes relevantes (sin mitos)

Para Bulk-Insert con Array DML, en la práctica siempre son decisivos los mismos ajustes:

1) ArraySize y tamaño del lote

ArraySize (en TFDQuery/TFDCommand) determina cuántas «filas» FireDAC procesa en una llamada. Más grande no es automáticamente mejor. Demasiado grande implica: más memoria en el cliente, mayor payload en la red, locks y carga de log mayores en el servidor y, en caso de error, mayor radio de impacto. Para importaciones robustas, a menudo un tamaño de lote entre 200 y 2.000 es un buen punto de partida, dependiendo del número de columnas, BLOBs y la latencia.

2) Límite de transacción

Necesitas una decisión clara: Commit por lote o Commit para toda la importación. No es una cuestión de preferencia, sino una decisión operativa:

• Commit por lote: limita bloqueos y el log de transacciones, facilita el reinicio, pero los estados intermedios son visibles (según el nivel de aislamiento). Errores en el lote 17 dejan los lotes 1–16 en el sistema.
• Commit al final: «todo o nada», más consistente desde un punto de vista funcional, pero con grandes volúmenes arriesgas bloqueos prolongados, un rollback grande y, en caso de error, se pierde todo.

Para muchos procesos de interfaz e importación, «Commit por lote» es la estrategia operativa más realista — pero solo si has gestionado correctamente la idempotencia y la estrategia de duplicados (p. ej., mediante claves naturales, upserts o una ID de importación).

3) UpdateOptions y sentencias preparadas

En lotes repetidos vale la pena mantener la sentencia preparada. «Prepare» significa: FireDAC permite que la BD analice/compile la sentencia y la reutilice. Dependiendo de la BD, eso puede tener un efecto perceptible, sobre todo a alta frecuencia. Aquí no se trata tanto de un «truco 17», sino de: reutilizar de forma consistente el mismo objeto Query (o el mismo TFDCommand) y mantener tipos de parámetros estables.

Gestión limpia de errores por fila: lo que realmente necesitas

Si quieres manejar errores «por fila», necesitas tres cosas:

1. Asignación: ¿Qué índice del array (0..N-1) ha fallado?
2. Contexto: ¿Qué valores clave de negocio tiene esta fila (p. ej., ID externa, número de cliente, marca de tiempo)?
3. Control: ¿Qué haces después? ¿Abortar, omitir solo las filas malas o dividir el lote?

FireDAC puede devolver, según el driver, errores por elemento del array. En la práctica esto no está «simplemente siempre disponible». Debes contar con que algunas bases de datos/proveedores solo reportan el primer error o que un error en el lote impida la ejecución del RESTo. Precisamente por eso, un patrón robusto suele ser de dos niveles:

• Etapa A: Intenta el lote como Array DML.
• Etapa B: Si el lote falla, divídelo (a la mitad) o vuelve de forma controlada a filas individuales —pero solo para ese lote— y registra correctamente.

Esto parece trabajo extra, pero en las rutas de importación es la diferencia entre «a las 02:00 de la madrugada todo se detiene» y «la importación continúa, 7 filas acaban en la lista de errores».

Un patrón práctico: primero el lote, luego aislar de forma dirigida

El siguiente patrón ha demostrado ser eficaz para soluciones de software cercanas al proceso en las que la calidad de los datos es mixta:

Paso 1: Empaquetar los datos en una estructura por lotes (incl. contexto de error)

No almacenes los datos a importar solo como valores crudos, sino con contexto mínimo: ID externa, número de fila en la fuente, posiblemente hash/checksum. Esto no es „Nice to have“: en caso de error no quieres volver a parsear la CSV para averiguar qué está roto.

Paso 2: Ejecutar Array DML

Estableces ArraySize al tamaño del batch, vinculas los parámetros como arrays y ejecutas ExecSQL. Importante: mantener estables los tipos de parámetro (p. ej., no vincular a veces como String y otras como Integer en campos numéricos), de lo contrario la DB producirá casts implícitos o FireDAC tendrá que convertir por elemento.

Paso 3: En caso de error – limitar el batch en lugar de repetir a ciegas

Si ExecSQL falla, tienes dos opciones robustas:

• Binary Split (dividir por la mitad): dividir el batch en dos mitades, intentar cada mitad nuevamente como Array DML. Repetir hasta que llegues a una cantidad pequeña que puedas comprobar individualmente. Ventaja: conservas gran parte del rendimiento si solo pocas filas están dañadas. Desventaja: más lógica, y ante errores sistemáticos (p. ej. tipo de dato incorrecto) es de poca utilidad.
• Fallback a filas individuales para este batch: pones ArraySize=1 (o vinculas valores individuales) y ejecutas fila por fila, registras errores y continúas. Ventaja: sencillo, garantiza por fila. Desventaja: en este batch pierdes velocidad.

En la práctica combino ambos: primero dividir 1–2 veces (para procesar rápidamente „bloques buenos“), luego, en cantidades residuales pequeñas, cambiar a filas individuales para registrar información de error clara.

Objetos de error y mensajes: Qué deberías extraer de FireDAC

FireDAC encapsula errores de la DB en excepciones (típicamente EFDDBEngineException) con información detallada. Para la operación son importantes tres niveles:

• Código de error de la BD (específico de la BD): p. ej. SQLSTATE en PostgreSQL, Error Number en SQL Server.
• Nombre de constraint/objeto: frecuentemente incluido en el texto de error (Unique-Index, FK-Constraint).
• Contexto de la sentencia: tabla, operación, en su caso valores de parámetros (con precaución con datos personales).

Si quieres registrar por fila, en caso de error además tienes que identificar la fila. FireDAC puede en ocasiones suministrar el índice del array. No te fíes únicamente de eso. Crea siempre además un índice propio (posición en el batch) y registra para esa posición al menos una clave de negocio.

Puntos críticos que consumen tiempo en importaciones reales

1) „Era solo una fila“ – pero la transacción ya está „dirty“

Según la BD y el driver, un error puede hacer que toda la ejecución de la sentencia se considere fallida y que la transacción quede en un estado en el que debas hacer rollback explícito o en el que sentencias posteriores fallen. Especialmente con algunos drivers, „tras un error seguir simplemente“ no es una suposición segura.

Consecuencia: Si trabajas dentro de una transacción y un batch falla, la vía estándar es: rollback del contexto del batch actual (o de toda la transacción) y volver a comenzar. Eso encaja bien con „Commit pro Batch“.

2) Autocommit vs. transacción explícita

Si no inicias una transacción explícita, a menudo el driver/provider decide cómo commitea las sentencias. Para importaciones masivas eso rara vez es lo que quieres. Las transacciones explícitas te dan control sobre:

• Duración de los bloqueos
• Comportamiento de rollback
• Puntos de reanudación

Y: Explícito no significa «una transacción enorme». Significa «consciente».

3) Triggers, Constraints y efectos secundarios

Array DML acelera la entrega, pero no automáticamente el trabajo del lado del servidor. Si en la tabla de destino tienes Triggers (p. ej. audit-logging, cálculo automático de estado), el cuello de botella puede no ser el insert, sino el código del trigger. Un lote puede reducir los roundtrips, pero la CPU del DB-Server sigue siendo el factor limitante.

Para admins y líderes técnicos: ante problemas de rendimiento conviene revisar Wait Events/Locks y el registro de transacciones. El bulk-insert será entonces solo el desencadenante, no la causa.

4) Tipos de datos y conversiones implícitas

Una de las razones más frecuentes de «¿por qué va lento?» es que los parámetros se enlazan como string y la DB hace cast por fila a Integer/Date/Decimal. Eso es invisible pero caro. Para un rendimiento estable:

• Establecer los tipos de datos de los parámetros correctamente (fecha como fecha, número como número).
• En decimales, prestar atención a las trampas de locale (coma vs. punto). FireDAC suele ser correcto aquí, pero las fuentes mixtas no lo son.
• Aclarar de antemano la estrategia de zonas horarias/UTC (los timestamps son un clásico en las importaciones).

5) Los mensajes de error son para personas, pero no para la automatización

Es tentador parsear el texto de error («duplicate key value violates unique constraint …»). Hazlo solo como última opción. Mejor usar códigos estructurados (SQLSTATE, Error Number). Lamentablemente no todos los drivers entregan todo con la misma calidad. Planifica por tanto ambos: código y texto, más opcionalmente «nombre de la constraint extraído del texto», pero sin dependencia rígida.

Pistas de depuración: Cómo localizar rápidamente la fila con error

Hacer el lote reproducible

Si una importación falla esporádicamente necesitas reproducibilidad. Guarda por lote un pequeño archivo de diagnóstico o una entrada de log que contenga:

• Número de lote y hora
• ArraySize y modo de transacción
• la lista de claves de negocio (p. ej. IDs externas) del lote

A menudo eso basta para, posteriormente, ejecutar un mini-import solo para esos IDs.

Hacer visible el SQL final (pero sin fugas de datos)

En depuración quieres saber: ¿la SQL es correcta? ¿los parámetros son los adecuados? FireDAC ofrece monitoring/tracing mediante componentes FDMoni y registro de driver. En entornos cercanos a producción es importante:

• Activar tracing de forma puntual y temporal (rendimiento y protección de datos).
• Registrar valores de parámetros solo en un entorno seguro o enmascarados.
• Para datos personales: en el log solo claves técnicas (IDs) y ningún contenido en texto claro.

Si haces split-testing: definir criterios de interrupción

En el binary split no quieres dividir indefinidamente. Establece un umbral inferior, p. ej. «por debajo de 20 filas cambia al modo individual». Y fija un límite de errores tolerables en total antes de abortar la importación (p. ej. ante problemas sistemáticos de mapeo). De lo contrario acabarás con listas de errores interminables y bloquearás el procesamiento posterior.

Cuándo realmente merece la pena el esfuerzo (y cuándo no)

Array DML con manejo de errores por fila compensa especialmente cuando:

• Muchas filas se procesan (miles hasta millones).
• Pocas filas contienen errores, pero quieres que el proceso continúe.
• La importación debe ejecutarse de forma estable en producción (p. ej. procesamiento nocturno, servicio sin IU).
• Necesitas devolver una lista de errores al área de negocio/fuente (con referencia a las filas).

No compensa tanto cuando:

• tú solo escribes unas pocas docenas de filas (inserts individuales están bien),
• la calidad de datos es tan mala que el 30–50% de las filas falla (entonces una estrategia de staging es más adecuada),
• de todos modos utilizas un procedimiento de carga masiva nativo de la BD (p. ej. COPY en PostgreSQL, BCP/BULK INSERT en SQL Server) – entonces Array DML no es la herramienta.

Arquitectura alternativa: tabla de staging statt „Directo al destino“

Si tú lidias regularmente con calidad de datos mixta, un „INSERT directo en la tabla objetivo“ suele ser la decisión equivocada. Una tabla de staging (etapa previa) es una tabla en la que primero almacenas los datos de forma técnicamente correcta (posiblemente con tipos flexibles), y solo después los validas y los transfieres a la tabla objetivo.

Ventajas en producción:

• Los registros erróneos permanecen almacenados de forma trazable (incl. datos en bruto).
• Puedes ejecutar la validación de forma separada y repetible.
• Desacoplas la aceptación de la interfaz del procesamiento funcional.

Array DML suele ser entonces el camino rápido hacia la tabla de staging, mientras que la transferencia a la tabla objetivo se realiza mediante SQL basada en conjuntos (o Stored Procedure). Eso traslada el manejo de errores en mayor medida al lado de la BD, lo que según la organización (roles DBA, despliegue) puede ser apropiado o indeseable.

Operación y administración: lo que los IT-Leads y administradores deben saber

Monitoreo: tasa de error y rendimiento son las métricas clave

Para la operación estable de una importación masiva, dos métricas son más informativas que el „tiempo de ejecución“ por sí solo:

• Rendimiento: filas por minuto (o por lote) incl. pico/mediana.
• Tasa de error: filas erróneas por ejecución, idealmente agrupadas por clases de error (Unique, FK, NOT NULL, conflicto de tipo).

Si observas regularmente estos dos valores, detectarás pronto si en la fuente ha cambiado algo (p. ej. un nuevo formato) o si el sistema destino (p. ej. nuevas restricciones) se ha vuelto más estricto.

Bloqueos y ventanas de carga

Las inserciones masivas pueden provocar bloqueos y carga de E/S. Si los usuarios trabajan en paralelo sobre las mismas tablas, debes considerar el nivel de aislamiento, los índices y, si procede, el particionamiento. En la práctica esto significa: o bien programar las importaciones en ventanas de baja carga, o diseñar el flujo de datos para que coexista con la operación en curso (p. ej. mediante staging + transferencia asíncrona).

Lista de comprobación concreta para una inserción masiva robusta con Array DML

• Tamaño de lote: definir (valor inicial 500–1.000) y afinar de forma medible.
• Transacción explícita: Commit por lote por defecto; „Commit al final“ solo de forma deliberada.
• Tipos de parámetros estables: establecerlos, no forzar conversiones implícitas.
• Contexto de error por registro: incluir (ID externa, fila de origen).
• Estrategia de errores: primero batch, luego Split/Fallback, registrar por fila.
• Logging: códigos + texto, pero conforme a la protección de datos; registrar Batch-ID y ID de ejecución.
• Reintento: garantizar idempotencia (clave/Upsert/Import-ID).

Conclusión: Array DML es rápido — la robustez proviene del proceso y de la estrategia de errores

Un FireDAC Bulk-Insert con Array DML es una herramienta potente, siempre que no finjas que no existen errores. En flujos de datos reales siempre hay valores atípicos: duplicados, referencias faltantes, valores de fecha corruptos. El enfoque correcto es, por tanto: Array DML para el rendimiento, combinado con una estrategia de aislamiento controlada (Split o Fallback) y una lista de errores trazable por fila. Así obtienes velocidad y fiabilidad operativa a la vez – y eso es precisamente lo que cuenta cuando las importaciones no solo se ejecutan en el laboratorio, sino que deben completarse de forma fiable cada noche.

Si deseáis estabilizar un proceso de importación o de interfaz existente en Delphi/FireDAC (rendimiento, transacciones, reanudación, registro), lo aclaramos con gusto de forma estructurada en una conversación técnica:

Para este tema también son importantes Delphi Bulk Insert y Bulk Insert Delphi FireDAC. El artículo sitúa estos aspectos de forma comprensible y muestra qué es relevante en la operativa diaria.

Discutir proyecto o iniciativa de modernización con Net-Base.