FireDAC: Bulk-Insert с Array DML и корректной обработкой ошибок по каждой строке

Одно BDE-Ablosung с нативным подключением Bulk-Insert с Array DML часто является самым быстрым способом загрузить много записей в базу данных: вместо тысячи отдельных INSERT-ов привязывается массив параметров и отправляется на сервер одним пакетом. На практике же быстро возникает проблема: запись нарушает уникальный индекс, поле с NOT NULL пустое, внешний ключ не совпадает — и вдруг непонятно, какая строка сорвала батч, была ли уже записана часть данных и как корректно продолжить, не породив несогласованности данных.

Именно об этом речь: как использовать Array DML так, чтобы вы получали надежную информацию об ошибках по строке, держали транзакцию под контролем и могли в эксплуатации проследить, что произошло. Фокус не на академическом разборе API, а на пограничном случае, который регулярно возникает при реальных импортах: большой батч, несколько повреждённых строк, но при этом вы хотите сохранить скорость.

FireDAC Bulk-Insert с Array DML: почему Array DML оправдан при Bulk-Insert

Array DML (Data Manipulation Language) означает в FireDAC: параметры привязываются не как отдельные значения, а массивом. FireDAC отправляет затем (в зависимости от драйвера/БД) меньше кругов обмена, может эффективнее работать на стороне сервера и существенно снижает оверхед на клиенте. Это особенно важно в трёх ситуациях:

• ETL- und Importstrecken: CSV/XML/JSON входные данные, нормализация/маппинг, затем в staging- или целевую таблицу.
• Schnittstellen-Puffer: REST- или MQ-пейлоады накапливаются и периодически сохраняются.
• Protokoll-/Event-Tabellen: много мелких INSERT-ов, где доминирует латентность.

Выигрыш не даётся бесплатно. С Array DML вы переносите сложность из «много отдельных операторов» в «один оператор с множеством строк». Это полезно для производительности, но повышает требования к диагностике ошибок, логике транзакций и повторному запуску.

Типичный пограничный случай: батч, одна повреждённая строка

Классика в эксплуатации: вы импортируете 50 000 строк. Вы выбираете ArraySize 1 000, потому что не хотите делать roundtrip для каждой строки. Батч 17 падает. БД возвращает лишь «duplicate key» или «violates foreign key constraint». В UI или в сервис-логе часто остаётся только: «ExecSQL failed».

Без корректной обработки ошибок обычно происходят две нежелательные вещи:

• Вы отбрасываете весь батч, хотя 999 из 1 000 строк были бы корректны.
• Вы возвращаетесь к одиночным INSERT-ам и на длительное время теряете преимущество по производительности.

Цель — третий путь: сохранить производительность пакетной обработки, но фиксировать с точностью до дефекта (индекс строки, ключевые значения, текст ошибки БД) и при необходимости коммитить «good rows» — в зависимости от того, насколько критичны согласованность и идемпотентность (многократный запуск без повторного эффекта) в вашем процессе.

FireDAC Array DML: Важные настройки (без мифов)

Для Bulk-Insert с Array DML на практике всегда решающими оказываются одни и те же настройки:

1) ArraySize und Batch-Größe

ArraySize (в TFDQuery/TFDCommand) определяет, сколько «строк» FireDAC обрабатывается в одном вызове. Больше не всегда лучше. Слишком большое значение означает: больше памяти на клиенте, больший payload в сети, большие блокировки/нагрузка на журнал на сервере и в случае ошибки — больший «blast radius». Для устойчивых импортов часто удобным начальным значением размера батча является диапазон между 200 и 2.000, в зависимости от числа столбцов, BLOBs и задержки.

2) Transaktionsgrenze

Нужно четко решить: Commit pro Batch или Commit für den gesamten Import. Это не вопрос вкуса, а операционное решение:

• Commit pro Batch: ограничивает блокировки и журнал транзакций, упрощает повторный запуск, но промежуточные состояния видимы (в зависимости от уровня изоляции). Ошибка в батче 17 оставит батчи 1–16 в системе.
• Commit am Ende: «всё или ничего», более согласованно с точки зрения предметной области, но при больших объёмах вы рискуете долгими блокировками, большим откатом и в случае ошибки — потерей всех изменений.

Для многих интерфейсных и импортных процессов «Commit pro Batch» является более реалистичной операционной стратегией — но только, если вы корректно обеспечили идемпотентность и стратегию обработки дублей (например, через натуральные ключи, Upserts или Import-ID).

3) UpdateOptions und Prepared Statements

При повторяющихся батчах имеет смысл держать выражение подготовленным. «Prepare» означает, что FireDAC даёт БД просанировать/скомпилировать выражение и повторно его использовать. В зависимости от СУБД это может давать ощутимый эффект, особенно при высокой частоте. Здесь важнее не «трюк», а: последовательное повторное использование одного и того же объекта запроса (или того же TFDCommand) и стабильные типы параметров.

Корректная обработка ошибок по строке: что вам действительно нужно

Если вы хотите обрабатывать ошибки «по строке», вам нужны три вещи:

1. Сопоставление: какой индекс массива (0..N-1) завершился с ошибкой?
2. Контекст: какие предметно-ориентированные ключевые значения содержит эта строка (например, внешний идентификатор, номер клиента, временная метка)?
3. Управление: что вы делаете далее? Остановить процесс, пропустить только плохие строки или разделить батч?

FireDAC в зависимости от драйвера может возвращать ошибку для каждого элемента массива. На практике это не всегда доступно «по умолчанию». Нужно рассчитывать на то, что некоторые СУБД/провайдеры сообщают только первую ошибку, или что из-за ошибки в батче остальные элементы вовсе не выполняются. Именно поэтому устойчивый шаблон обычно двухступенчатый:

• Этап A: попытаться выполнить батч как Array DML.
• Этап B: если батч не проходит, разделите его (на половины) или контролируемо перейдите к обработке отдельных строк — но только для этого батча — и аккуратно залогируйте.

Это звучит как дополнительная работа, но для импортных конвейеров это разница между «в 02:00 ночи всё останавливается» и «импорт проходит, 7 строк попадают в список ошибок».

Практичный шаблон: сначала батч, затем целевая изоляция

Следующий подход зарекомендовал себя для процессно-ориентированных программных решений с неоднородным качеством данных:

Шаг 1: Упаковать данные в батч-структуру (включая контекст ошибок)

Сохраняй импортируемые данные не только как сырые значения, но и с минимальным контекстом: внешним ID, номером строки в источнике, возможно хешем/контрольной суммой. Это не «приятно иметь»: в случае ошибки ты не захочешь снова парсить CSV, чтобы выяснить, что сломалось.

Шаг 2: Выполнение Array DML

Устанавливаешь ArraySize равным длине батча, связываешь параметры как массивы и вызываешь ExecSQL. Важно: сохранять стабильность типов параметров (например, для числовых полей не связывать иногда как String, иногда как Integer), иначе DB будет выполнять неявные приведения типов или FireDAC придется выполнять преобразование по каждому элементу.

Шаг 3: В случае ошибки — ограничивать батч вместо слепого повтора

Если ExecSQL завершается ошибкой, у тебя есть два надёжных варианта:

• Binary Split (разделение пополам): разделить батч на две половины, для каждой половины снова попытаться выполнить Array DML. Повторяешь это до тех пор, пока не дойдёшь до небольшой партии, которую можно проверить поэлементно. Плюс: сохраняется высокая производительность, если повреждены лишь единичные строки. Минус: требуется больше логики, и при систематических ошибках (например, неверный тип данных) метод мало помогает.
• Fallback auf Einzelzeilen для этого батча: устанавливаешь ArraySize=1 (или связываешь одиночные значения) и выполняешь по одной строке, логируешь ошибки и продолжаешь. Плюс: просто, даёт гарантированную обработку по строкам. Минус: в рамках этого батча теряется скорость.

На практике я комбинирую оба подхода: сначала 1–2 раза разделяю (чтобы быстро пропустить «хорошие блоки»), затем при малых остатках переключаюсь на обработку по одиночным строкам, чтобы логировать однозначную информацию об ошибках.

Объекты ошибок и сообщения: что следует извлечь из FireDAC

FireDAC инкапсулирует ошибки БД в исключения (типично EFDDBEngineException) с детальной информацией. Для эксплуатации важны три уровня:

• Код ошибки БД (специфичный для СУБД): например, SQLSTATE в PostgreSQL, Error Number в SQL Server.
• Имя ограничения/объекта: часто содержится в тексте ошибки (Unique-Index, FK-Constraint).
• Контекст запроса: таблица, операция, при необходимости — значения параметров (осторожно с персональными данными).

Если ты хочешь логировать по строкам, в случае ошибки нужно дополнительно идентифицировать строку. FireDAC может в некоторых случаях вернуть индекс в массиве. Но не полагайся на это исключительно. Всегда добавляй собственный индекс (позицию в батче) и логируй для этой позиции как минимум один предметный ключ.

Подводные камни, которые в реальных импортaх отнимают время

1) «Да это же всего одна строка» — но транзакция уже «грязная»

В зависимости от СУБД и драйвера ошибка может привести к тому, что выполнение всего оператора считается провалившимся, и транзакция окажется в состоянии, когда нужно либо явно выполнить rollback, либо дальнейшие выражения будут завершаться ошибкой. Особенно у некоторых драйверов предположение «после ошибки просто продолжать» небезопасно.

Следствие: если ты работаешь внутри транзакции и батч терпит неудачу, стандартный путь — откат текущего контекста батча (или всей транзакции) и затем повторная попытка. Это хорошо сочетается с подходом «commit за батч».

2) Autocommit vs. явная транзакция

Если не запускать явную транзакцию, часто драйвер/провайдер решает, как коммитить выражения. Для bulk-импортов это редко совпадает с желаемым поведением. Явные транзакции дают контроль над:

• длительностью блокировок
• Поведение отката (Rollback)
• Точки повторного запуска

И: «эксплицитно» не значит „огромная транзакция“. Это значит „осознанно“.

3) Триггеры, ограничения и побочные эффекты

Array DML ускоряет передачу, но не автоматически работу на сервере. Если на целевой таблице есть триггеры (например, аудит‑логирование, автоподсчёт статуса), узким местом может быть вовсе не Insert, а код триггера. В этом случае пакетный режим уменьшит количество Roundtrip’ов, но загрузка CPU на сервере БД останется лимитирующим фактором.

Для администраторов и технических руководителей: при проблемах с производительностью имеет смысл смотреть на Wait Events/Locks и журнал транзакций. Bulk-Insert тогда зачастую лишь триггер, а не коренная причина.

4) Типы данных и неявные конверсии

Одна из самых частых причин «почему это медленно»: параметры привязывают как строки, и СУБД кастует по строке в Integer/Date/Decimal. Это незаметно, но дорого. Для устойчивой производительности:

• Задавайте типы параметров корректно (дата как дата, число как число).
• Для Decimal учитывайте ловушки локализации (запятая vs точка). FireDAC здесь чаще корректен, но смешанные источники — нет.
• Согласуйте стратегию часовых поясов/UTC заранее (метки времени при импортах — классическая проблема).

5) Тексты ошибок — для людей, но не для автоматизации

Соблазнительно парсить текст ошибки («duplicate key value violates unique constraint …»). Делайте это только в крайнем случае. Лучше использовать структурированные коды (SQLSTATE, Error Number). К сожалению, не все драйверы возвращают всё одинаково хорошо. Планируйте оба варианта: код и текст, плюс опционально «имя constraint из текста», но без жёсткой зависимости.

Советы по отладке: как быстро найти битую строку

Сделайте батч воспроизводимым

Если импорт периодически падает, нужна воспроизводимость. Сохраняйте для каждого батча небольшой диагностический файл или запись в логе, содержащую:

• Номер батча и время
• ArraySize и режим транзакции
• список предметных ключей (например, внешние ID) в батче

Этого часто достаточно, чтобы потом целенаправленно запустить мини‑импорт только для этих ID.

Показывать финальную SQL (но без утечек данных)

При отладке вы хотите знать: корректна ли SQL? Правильно ли параметры? FireDAC предоставляет мониторинг/трейсинг через компоненты FDMoni и логирование драйвера. В приближённых к продакшену средах важно:

• Включать трассировку целенаправленно и только временно (производительность и защита данных).
• Логировать значения параметров только в безопасной среде или в маскированном виде.
• Для персональных данных: в логах — только технические ключи (ID) и никаких данных в явном виде.

Если вы делаете split‑тесты: определите критерии остановки

При Binary Split не стоит делить бесконечно. Задайте нижнюю границу, например: „при менее 20 строк переключаться на одиночный режим“. И установите лимит на общее количество ошибок, которые вы терпите, прежде чем прервать импорт (например, при системных проблемах маппинга). Иначе вы получите бесконечные списки ошибок и заблокируете последующую обработку.

Когда трудозатраты действительно оправданы (а когда нет)

Array DML с обработкой ошибок на строку особенно оправдан, когда:

• обрабатывается много строк (тысячи до миллионов).
• немного строк ошибочно, но вы всё равно хотите пройтись по остальным.
• импорт должен стабильно работать в бою (например, ночная обработка, сервис без UI).
• вам нужно вернуть список ошибок источнику/бизнесу (с привязкой к строкам).

Это менее целесообразно, если:

• вы пишете всего несколько десятков строк (вставки по одной записи приемлемы),
• качество данных настолько низкое, что 30–50% строк не проходят (в этом случае целесообразнее стратегия стейджинга),
• вы уже используете нативный для СУБД метод массовой загрузки (например COPY в PostgreSQL, BCP/BULK INSERT в SQL Server) — тогда Array DML не является инструментом.

Альтернативная архитектура: стейджинг-таблица вместо «прямо в цель»

Если вы регулярно сталкиваетесь со смешанным качеством данных, «вставлять сразу в целевую таблицу» часто — неверное решение. Стейджинг-таблица (предварительная ступень) — это таблица, в которую вы сначала сохраняете данные технически корректно (возможно с гибкой типизацией), а затем выполняете валидацию и перенос в целевую таблицу.

Преимущества в эксплуатации:

• Некорректные записи остаются сохранёнными и прослеживаемыми (включая исходные данные).
• Вы можете выполнять валидацию отдельно и повторяемо.
• Вы отделяете приём данных от их предметной обработки.

Array DML часто является быстрым способом записать в стейджинг-таблицу, тогда как перенос в целевую таблицу выполняется как set-ориентированный SQL (или хранимая процедура). Это переносит обработку ошибок на сторону БД, что в зависимости от организации (DBA-роли, процесс деплоя) может быть желательно или нежелательно.

Эксплуатация и администрирование: что должны знать IT-руководители и админы

Мониторинг: доля ошибок и пропускная способность — ключевые метрики

Для стабильной эксплуатации массового импорта две метрики дают больше информации, чем «время выполнения» однозначно:

• Пропускная способность: строк в минуту (или в пакете) включая пик/медиану.
• Доля ошибок: ошибочные строки за запуск, желательно сгруппированные по классам ошибок (Unique, FK, NOT NULL, конфликт типов).

Если вы регулярно отслеживаете эти два показателя, вы быстро заметите, изменился ли источник (например новое форматирование) или ужесточились ограничения в целевой системе (например появились новые констрейнты).

Блокировки и окна нагрузки

Bulk-Insert’ы могут вызывать блокировки и повышенную IO-нагрузку. Если пользователи параллельно работают с теми же таблицами, необходимо учитывать уровни изоляции, индексы и при необходимости партиционирование. Практически это означает: либо планировать импорты в окна низкой нагрузки, либо строить поток данных так, чтобы он сосуществовал с рабочей нагрузкой (например через стейджинг + асинхронный перенос).

Конкретный чеклист для надёжного Bulk-Insert с Array DML

• Размер пакета установить (начальное значение 500–1 000) и измеряемо настраивать.
• Явная транзакция: Commit на пакет по умолчанию; «Commit в конце» делать сознательно.
• Стабильные типы параметров задать, не полагаться на неявные приведения типов.
• Контекст ошибки для каждой записи передавать (внешний идентификатор, строка-источник).
• Стратегия при ошибках: сначала на уровне пакета, затем разделение/резервный путь, логирование по строкам.
• Логирование: коды + текст, с соблюдением требований защиты данных; фиксировать Batch-ID и Run-ID.
• Повторный запуск: обеспечить идемпотентность (ключ/Upsert/Import-ID).

Вывод: Array DML быстрый — надёжным он становится через процесс и стратегию обработки ошибок

Ein FireDAC Bulk-Insert mit Array DML ist ein starkes Werkzeug, solange du nicht so tust, als gäbe es keine Fehler. В реальных потоках данных всегда есть выбросы: дубликаты, отсутствующие ссылки, повреждённые значения дат. Правильный подход поэтому: Array DML для производительности, в сочетании с контролируемой стратегией изоляции (Split или Fallback) и списком ошибок, отслеживаемым по каждой строке. Так ты получаешь и скорость, и эксплуатационную надёжность — и именно это имеет значение, когда импорты должны выполняться стабильно не только в лаборатории, но и каждую ночь.

Wenn ihr einen bestehenden Import- oder Schnittstellenprozess in Delphi/FireDAC stabilisieren wollt (Performance, Transaktionen, Wiederanlauf, Logging), klären wir das gern strukturiert im technischen Gespräch:

Для этой темы также важны Delphi Bulk Insert и Bulk Insert Delphi FireDAC. Статья упорядочивает эти аспекты понятным образом и показывает, на что обращать внимание в повседневной работе.

Обсудить проект или задачу по модернизации с Net-Base.