FireDAC: Inserção em lote com DML em array e tratamento de erros por linha

Uma BDE-Ablosung com ligação nativa Bulk-Insert com Array DML é frequentemente a forma mais rápida de inserir muitos registos numa base de dados: em vez de mil inserts individuais, liga-se um array de parâmetros e envia-se tudo de uma vez ao servidor. Na prática, porém, o ponto crítico surge rapidamente: um registo viola um Unique-Index, um campo NOT NULL está vazio, um Foreign Key não corresponde – e de repente fica incerto, qual linha matou o batch, se parte já foi escrita e como você prossegue de forma limpa, sem gerar incoerências de dados.

É exatamente sobre isso que se trata aqui: como você usa Array DML de modo a obter por linha informações de erro fiáveis, manter a transação sob controlo e, em produção, poder rastrear o que aconteceu. O foco não está numa leitura académica da API, mas no caso-limite que surge regularmente em importações reais: um grande batch, poucas linhas com erro, mas você quer velocidade.

FireDAC Bulk-Insert mit Array DML: por que Array DML é vantajoso no Bulk-Insert

Array DML (Data Manipulation Language) significa em FireDAC: você liga parâmetros não como valor único, mas como array. FireDAC envia então (conforme o driver/DB) menos roundtrips, pode trabalhar de forma mais eficiente no lado do servidor e reduz drasticamente o overhead no cliente. Isso é particularmente relevante em três situações:

• Fluxos ETL e de importação: CSV/XML/JSON de entrada, normalização/mapeamento, depois para tabela de staging ou tabela de destino.
• Buffer de interface: REST- ou MQ-Payloads são agregadas e periodicamente persistidas.
• Tabelas de log/eventos: muitos inserts pequenos, em que a latência domina.

O ganho não vem de graça. Com Array DML você transfere complexidade de “muitas instruções individuais” para “uma instrução com muitas linhas”. Isso é bom para desempenho, mas mais exigente para diagnóstico de erros, lógica de transação e retentativa.

O caso-limite típico: um batch, uma linha com erro

O clássico em produção: você importa 50.000 linhas. Você escolhe uma ArraySize de 1.000 porque não quer um roundtrip por linha. O batch 17 falha. O DB reporta apenas “duplicate key” ou “violates foreign key constraint”. Na UI ou no log do serviço muitas vezes aparece apenas: “ExecSQL failed”.

Sem um tratamento de erros adequado, costumam acontecer duas coisas ruins:

• Você descarta todo o batch, embora 999 de 1.000 linhas estivessem ok.
• Você recorre a inserts individuais e perde a vantagem de desempenho de forma permanente.

O objetivo é um terceiro caminho: manter a performance por batch, mas registrar com precisão o defeito (índice da linha, valores-chave, texto de erro do DB) e, opcionalmente, commitar as „good rows“ – dependendo de quão crítica é a consistência e a idempotência (executar várias vezes sem efeito duplicado) no seu processo.

FireDAC Array DML: Os ajustes relevantes (sem mitos)

Para Bulk-Insert com Array DML, na prática são sempre os mesmos ajustes que fazem a diferença:

1) ArraySize e tamanho do batch

ArraySize (em TFDQuery/TFDCommand) determina quantas „linhas“ FireDAC são processadas em uma chamada. Maior nem sempre é melhor. Muito grande significa: mais memória no cliente, mais payload na rede, locks maiores/maior carga de log no servidor e, em caso de erro, maior „raio de impacto“. Para importações robustas, frequentemente um tamanho de batch entre 200 e 2.000 é um bom ponto de partida, dependendo do número de colunas, BLOBs e da latência.

2) Limite de transação

Você precisa de uma decisão clara: Commit por batch ou Commit para todo o import. Isso não é questão de gosto, mas uma decisão operacional:

• Commit por batch: limita locks e o log de transações, facilita o reinício, mas estados intermediários ficam visíveis (dependendo do nível de isolamento). Erro no Batch 17 deixa os Batch 1–16 no sistema.
• Commit no fim: „tudo ou nada“, mais consistente em termos funcionais, mas com grandes volumes você arrisca locks longos, grande rollback e, em caso de erro, perde-se tudo.

Para muitos processos de interface e importação, „Commit por batch“ é a estratégia operacional mais realista – mas somente se você tiver idempotência e uma estratégia de duplicatas bem definida (por exemplo por chaves naturais, Upserts ou um ID de importação).

3) UpdateOptions e Prepared Statements

Em batches repetidos vale a pena manter o statement preparado. „Prepare“ significa: FireDAC permite que a DB faça o parse/compile do statement e o reutilize. Dependendo da DB isso pode ter um efeito perceptível, sobretudo em alta frequência. O importante aqui não é um „truque“, mas: reutilização consistente do mesmo objeto Query (ou do mesmo TFDCommand) e tipos de parâmetro estáveis.

Tratamento de erros por linha: o que você realmente precisa

Se você quer tratar erros „por linha“, precisa de três coisas:

1. Associação: qual índice do array (0..N-1) falhou?
2. Contexto: quais valores-chave funcionais essa linha tem (por exemplo ID externa, número do cliente, timestamp)?
3. Controle: o que você faz depois? Interromper, pular apenas as linhas ruins ou dividir o batch?

FireDAC pode, dependendo do driver, retornar erros por elemento do array. Na prática, isso nem sempre está disponível. Você deve considerar que alguns bancos de dados/fornecedores reportam apenas o primeiro erro ou que um erro no batch pode impedir a execução do RESTante. Exatamente por isso um padrão robusto é geralmente em duas etapas:

• Fase A: Tente o batch como Array DML.
• Fase B: Se o batch falhar, divida-o (ao meio) ou recorra controladamente a linhas individuais – mas apenas para esse batch – e registre os erros de forma clara.

Isto parece trabalho extra, mas em fluxos de importação é a diferença entre „à(s) 02:00 tudo parar“ e „o import continua, 7 linhas vão para a lista de erros“.

Um padrão prático: primeiro o batch, depois isolar de forma direcionada

O padrão a seguir tem se mostrado eficaz para soluções de software próximas ao processo, nas quais a qualidade dos dados é mista:

Passo 1: Empacotar os dados numa estrutura de batch (incl. contexto de erro)

Armazene os dados a importar não apenas como valores brutos, mas com contexto mínimo: ID externa, número da linha na fonte, eventualmente hash/checksum. Isso não é „nice to have“: em caso de erro você não quer ter de analisar novamente o CSV para descobrir o que está quebrado.

Passo 2: Executar Array DML

Você define ArraySize para o comprimento do batch, vincula parâmetros como arrays e executa ExecSQL. Importante: mantenha os tipos de parâmetro estáveis (p.ex. não vincule campos numéricos ora como String, ora como Integer), caso contrário a DB produzirá casts implícitos ou FireDAC terá de converter por elemento.

Passo 3: Caso de erro – RESTringir o batch em vez de repetir cegamente

Se ExecSQL falhar, você tem duas opções robustas:

• Binary Split (dividir ao meio): partir o batch em duas metades e tentar cada metade novamente como Array DML. Repete-se isso até chegar a uma quantidade pequena que você possa verificar individualmente. Vantagem: mantém boa parte da performance quando só algumas linhas estão com problemas. Desvantagem: mais lógica, e em casos de erro sistemático (p.ex. tipo de dado incorreto) traz pouco benefício.
• Fallback para linhas individuais para esse batch: você define ArraySize=1 (ou vincula valores individuais) e executa linha a linha, registra os erros e segue em frente. Vantagem: simples, garante verificação por linha. Desvantagem: você perde desempenho nesse batch.

Na prática eu combino ambos: primeiro dividir 1–2 vezes (para processar „blocos bons“ rapidamente), depois, em quantidades residuais pequenas, mudar para linhas individuais para registrar informações de erro claras.

Objetos de erro e mensagens: o que você deve extrair de FireDAC

FireDAC encapsula erros de BD em exceções (tipicamente EFDDBEngineException) com informações detalhadas. Para operação são importantes três níveis:

• Código de erro do BD (específico do SGBD): p.ex. SQLSTATE no PostgreSQL, Error Number no SQL Server.
• Nome da constraint/objeto: frequentemente presente no texto do erro (índice único, FK-Constraint).
• Contexto do statement: tabela, operação, eventualmente valores de parâmetros (cautela com dados pessoais).

Se você quiser registrar por linha, em caso de erro também precisa identificar a linha. FireDAC pode, em certas circunstâncias, fornecer o índice do array. Não dependa exclusivamente disso. Construa sempre adicionalmente um índice próprio (posição no batch) e registre para essa posição pelo menos uma chave de negócio.

Armadi lhas que consomem tempo em importações reais

1) „Era só uma linha“ – mas a transação já está „dirty“

Dependendo do BD e do driver, um erro pode fazer com que toda a execução do statement seja considerada falhada e que a transação fique num estado em que você precise explicitamente fazer rollback ou em que statements subsequentes falhem. Especialmente com alguns drivers, „após um erro simplesmente continuar“ não é uma suposição segura.

Consequência: se você trabalha dentro de uma transação e um batch falha, o caminho padrão é: rollback do contexto do batch atual (ou de toda a transação) e então recomeçar. Isso se encaixa bem com „commit por batch“.

2) Autocommit vs. transação explícita

Se você não iniciar uma transação explícita, frequentemente o driver/provider decide como os statements são confirmados. Para importações em massa isso raramente é o que você quer. Transações explícitas dão controle sobre:

• Duração do bloqueio
• Comportamento de rollback
• Pontos de reinício

E: „explícito“ não significa „uma transação enorme“. Significa „intencional“.

3) Triggers, Constraints e efeitos colaterais

Array DML acelera a transferência, mas não automaticamente o trabalho no servidor. Se você tem triggers na tabela de destino (p. ex. Audit-Logging, cálculo automático de status), então o gargalo pode nem ser o INSERT, e sim o código do trigger. Um batch pode reduzir os roundtrips, mas a CPU do servidor de DB permanece o fator limitante.

Para administradores e líderes técnicos: em problemas de performance vale a pena verificar Wait Events/Locks e o log de transações. O Bulk-Insert é então apenas o gatilho, não a causa.

4) Tipos de dados e conversões implícitas

Uma das razões mais comuns para „Por que está lento?“: parâmetros são ligados como string e a DB faz cast por linha para Integer/Date/Decimal. Isso é invisível, mas caro. Para performance estável:

• Ajustar os tipos de dados dos parâmetros adequadamente (data como data, número como número).
• Para Decimals, atentar às armadilhas de locale (vírgula vs. ponto). FireDAC costuma estar correto aqui, mas fontes mistas não estão.
• Definir antecipadamente a estratégia de fusos horários/UTC (timestamps são um clássico em imports).

5) Mensagens de erro são para humanos, não para automação

É tentador parsar o texto do erro („duplicate key value violates unique constraint …“). Faça isso apenas como última opção. Melhores são códigos estruturados (SQLSTATE, Error Number). Infelizmente nem todos os drivers entregam tudo de forma consistente. Planeje, portanto, ambos: código e texto, mais opcionalmente o „nome do Constraint a partir do texto“, mas sem dependência rígida.

Dicas de debugging: como encontrar rapidamente a linha com erro

Tornar o batch reproduzível

Se um import falha esporadicamente, você precisa de reprodutibilidade. Salve por batch um pequeno arquivo de diagnóstico ou uma entrada de log que contenha:

• Número do batch e horário
• ArraySize e modo de transação
• a lista das chaves de negócio (p. ex. IDs externas) no batch

Isso costuma ser suficiente para, em seguida, executar um mini-import apenas para esses IDs.

Tornar o SQL final visível (mas sem vazamento de dados)

No debugging você quer saber: o SQL final está correto? os parâmetros estão corretos? FireDAC oferece Monitoring/Tracing via componentes FDMoni e logging do driver. Em ambientes próximos à produção é importante:

• Ativar tracing de forma direcionada e apenas temporária (desempenho e proteção de dados).
• Logar valores de parâmetros somente em ambiente seguro ou mascarados.
• Para dados pessoais: no log apenas chaves técnicas (IDs) e nenhum conteúdo em texto claro.

Se você testa por split: definir critérios de interrupção

No Binary Split você não quer dividir indefinidamente. Estabeleça um limite inferior, p. ex. „abaixo de 20 linhas mudar para modo individual“. E defina um limite de quantos erros você tolera no total antes de abortar o import (p. ex. em problemas sistemáticos de mapeamento). Caso contrário você entra em listas de erro sem fim e bloqueia o processamento subsequente.

Quando o esforço realmente compensa (e quando não)

Array DML com tratamento de erro por linha compensa especialmente quando:

• Muitas linhas são processadas (milhares a milhões).
• Poucas linhas estão com erro, mas você ainda quer que o processo continue.
• O import precisa rodar de forma estável em produção (p. ex. processamento noturno, serviço sem UI).
• Você precisa devolver uma lista de erros à área de negócio/fonte (com referência às linhas).

Vale menos a pena quando:

• você escreve apenas algumas dezenas de linhas (inserts individuais são aceitáveis),
• a qualidade dos dados é tão baixa que 30–50% das linhas falham (nesse caso, uma estratégia de staging faz mais sentido),
• você já utiliza um procedimento de carregamento em massa nativo do DB (p.ex. COPY no PostgreSQL, BCP/BULK INSERT no SQL Server) — então o Array DML não é a ferramenta.

Arquitetura alternativa: tabela de staging em vez de „direto para o alvo“

Se você lida regularmente com qualidade de dados mista, um simples „insert direto na tabela alvo“ costuma ser a decisão errada. Uma tabela de staging (pré-etapa) é uma tabela onde você armazena os dados inicialmente de forma tecnicamente correta (possivelmente com tipos permissivos) e só depois valida e transfere para a tabela alvo.

Vantagens na operação:

• Registros com erro permanecem armazenados de forma rastreável (incl. dados brutos).
• Você pode executar a validação de forma separada e repetível.
• Você desacopla a aceitação da interface do processamento de negócio.

Array DML costuma ser o caminho rápido para a tabela de staging, enquanto a transferência para a tabela alvo acontece como SQL baseado em conjuntos (ou Stored Procedure). Isso desloca o tratamento de erros mais para o lado do DB, o que, dependendo da organização (papéis de DBA, deployment), pode ser desejável ou indesejável.

Operação e administração: o que IT-Leads e admins devem saber

Monitoramento: taxa de erro e throughput são as métricas centrais

Para a operação estável de um bulk import, duas métricas são mais informativas do que apenas o ‚tempo de execução‘:

• Taxa de transferência: linhas por minuto (ou por lote) incl. pico/mediana.
• Taxa de erro: linhas com erro por execução, idealmente agrupadas por classes de erro (Unique, FK, NOT NULL, conflito de tipo).

Se você monitorar regularmente esses dois valores, detecta cedo se algo mudou na fonte (p.ex. novo formato) ou se o sistema alvo (p.ex. novas restrições) ficou mais restritivo.

Bloqueios e janelas de carga

Bulk-Inserts podem gerar locking e carga de I/O. Se usuários trabalham em paralelo nas mesmas tabelas, você precisa considerar Isolation Level, índices e, se necessário, particionamento. Na prática isso significa: ou agendar imports para janelas de carga, ou construir o fluxo de dados para coexistir com a operação em andamento (p.ex. via staging + tomada assíncrona).

Lista de verificação concreta para um Bulk-Insert robusto com Array DML

• Tamanho do lote definir (valor inicial 500–1.000) e otimizar de forma mensurável.
• Transação explícita: commit por lote como padrão; ‚commit ao final‘ apenas de forma consciente.
• Tipos de parâmetro estáveis definir, não forçar casts implícitos.
• Contexto de erro por registro acompanhar (ID externa, linha de origem).
• Estratégia de erro: lote primeiro, depois split/fallback, log por linha.
• Logging: códigos + texto, mas em conformidade com proteção de dados; capturar Batch-ID e ID de execução.
• Retentativa: garantir idempotência (chave/Upsert/Import-ID).

Conclusão: Array DML é rápido – a robustez vem pelo processo e pela estratégia de erro

Um FireDAC Bulk-Insert com Array DML é uma ferramenta poderosa, contanto que você não finja que não existem erros. Em fluxos de dados reais sempre há exceções: duplicatas, referências ausentes, valores de data corrompidos. A abordagem correta é, portanto: Array DML para desempenho, combinado com uma estratégia de isolamento controlada (Split ou Fallback) e uma lista de erros rastreável por linha. Assim você obtém velocidade e confiabilidade operacional ao mesmo tempo – e é exatamente isso que conta quando importações não rodam apenas em ambiente de teste, mas precisam ser executadas com confiabilidade todas as noites.

Se vocês querem estabilizar um processo de importação ou de interface existente em Delphi/FireDAC (desempenho, transações, retomada, logging), podemos esclarecer isso de forma estruturada em uma conversa técnica:

Para este tema são também importantes Delphi Bulk Insert e Bulk Insert Delphi FireDAC. O artigo contextualiza esses aspectos de maneira compreensível e mostra no que é preciso atenção na prática.

Discutir projeto ou iniciativa de modernização com Net-Base.