FireDAC: Bulk-Insert z Array DML i precyzyjnym obsługiwaniem błędów na poziomie wiersza

Ein BDE-Ablosung mit nativer Anbindung Bulk-Insert mit Array DML ist oft der schnellste Weg, um viele Datensätze in eine Datenbank zu bekommen: statt tausend einzelner Inserts wird ein Parameter-Array gebunden und in einem Rutsch an den Server geschickt. In der Praxis kommt der Knackpunkt aber schnell: Ein Datensatz verletzt einen Unique-Index, ein NOT NULL-Feld ist leer, ein Foreign Key passt nicht – und plötzlich ist unklar, welche Zeile das Batch gekillt hat, ob ein Teil schon geschrieben wurde und wie du sauber weitermachst, ohne Dateninkonsistenzen zu erzeugen.

Genau darum geht es hier: Wie du Array DML so einsetzt, dass du pro Zeile belastbare Fehlerinfos bekommst, die Transaktion im Griff behältst und im Betrieb nachvollziehen kannst, was passiert ist. Der Fokus liegt nicht auf akademischer API-Lektüre, sondern auf dem Randfall, der in echten Importen regelmäßig aufschlägt: Ein großer Batch, wenige kaputte Zeilen, aber du willst trotzdem Tempo.

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

Array DML (Data Manipulation Language) bedeutet in FireDAC: Du bindest Parameter nicht als Einzelwert, sondern als Array. FireDAC sendet dann (je nach Treiber/DB) weniger Roundtrips, kann serverseitig effizienter arbeiten und reduziert den Overhead im Client drastisch. Das ist in drei Situationen besonders relevant:

• ETL- und Importstrecken: CSV/XML/JSON rein, Normalisierung/Mapping, dann in Staging- oder Zieltabelle.
• Schnittstellen-Puffer: REST- oder MQ-Payloads werden gesammelt und periodisch persistiert.
• Protokoll-/Event-Tabellen: viele kleine Inserts, bei denen Latenz dominiert.

Der Gewinn kommt aber nicht gratis. Mit Array DML verschiebst du Komplexität von „viele einzelne Statements“ zu „ein Statement mit vielen Zeilen“. Das ist gut für Performance, aber anspruchsvoller für Fehlerdiagnose, Transaktionslogik und Wiederanlauf.

Der typische Randfall: Ein Batch, eine kaputte Zeile

Der Klassiker im Betrieb: Du importierst 50.000 Zeilen. Du wählst eine ArraySize von 1.000, weil du nicht für jede Zeile einen Roundtrip willst. Batch 17 scheitert. Die DB meldet nur „duplicate key“ oder „violates foreign key constraint“. In der UI oder im Service-Log steht dann oft nur: „ExecSQL failed“.

Ohne sauberes Error-Handling passieren dann meist zwei schlechte Dinge:

• Du wirfst den ganzen Batch weg, obwohl 999 von 1.000 Zeilen ok wären.
• Du fällst auf Einzel-Inserts zurück und verlierst den Performancevorteil dauerhaft.

Celem jest trzecia droga: zachować wydajność batchu, ale dokumentować z dokładnością do błędu (indeks wiersza, wartości kluczy, tekst błędu DB) i opcjonalnie zatwierdzać „good rows” – w zależności od tego, jak krytyczne dla twojego procesu są spójność i idempotencja (wielokrotne uruchomienie bez podwójnego efektu).

FireDAC Array DML: Kluczowe parametry (bez mitów)

Dla Bulk-Insert przy użyciu Array DML w praktyce zawsze decydują te same ustawienia:

1) ArraySize i rozmiar batchu

ArraySize (przy TFDQuery/TFDCommand) określa, ile „wierszy” FireDAC jest przetwarzanych w jednym wywołaniu. Większe nie zawsze oznacza lepsze. Zbyt duże wartości oznaczają: więcej pamięci po stronie klienta, większy payload w sieci, większe blokady/obciążenie logu na serwerze i w razie błędu większy „Blast Radius”. Dla solidnych importów dobrym punktem wyjścia jest często rozmiar batchu między 200 i 2.000, w zależności od liczby kolumn, BLOBów i latencji.

2) Granica transakcji

Musisz podjąć jasną decyzję: zatwierdzenie po batchu lub zatwierdzenie dla całego importu. To nie kwestia gustu, lecz decyzja operacyjna:

• Zatwierdzenie po batchu: ogranicza blokady i dziennik transakcji, upraszcza ponowne uruchomienie, ale stany pośrednie są widoczne (w zależności od poziomu izolacji). Błąd w batchu 17 pozostawia batchi 1–16 w systemie.
• Zatwierdzenie na końcu: „wszystko albo nic”, bardziej spójne w sensie domenowym, ale przy dużych wolumenach ryzykujesz długie blokady, duży rollback i w razie błędu utratę wszystkich zmian.

Dla wielu procesów integracyjnych i importowych „zatwierdzenie po batchu” jest bardziej realistyczną strategią operacyjną – ale tylko, jeśli masz poprawnie uregulowaną idempotencję i strategię obsługi duplikatów (np. przez klucze naturalne, upserty lub identyfikator importu).

3) UpdateOptions i Prepared Statements

Przy powtarzających się batchach opłaca się utrzymywać zapytanie w stanie przygotowanym. „Prepare” oznacza: FireDAC pozwala bazie danych sparsować/kompilować zapytanie i ponownie je wykorzystać. W zależności od DB może to dać odczuwalny efekt, zwłaszcza przy dużej częstotliwości. Istotne jest tu mniej „szybkie triki”, a bardziej: konsekwentne ponowne użycie tego samego obiektu Query (lub tego samego TFDCommand) oraz stabilne typy parametrów.

Czyste Error-Handling per wiersz: czego naprawdę potrzebujesz

Jeśli chcesz obsługiwać błędy „per wiersz”, potrzebujesz trzech rzeczy:

1. Przypisanie: który indeks w tablicy (0..N-1) się nie powiódł?
2. Kontekst: jakie domenowe wartości kluczy ma ten wiersz (np. zewnętrzne ID, numer klienta, znacznik czasu)?
3. Sterowanie: co robisz potem? Przerwać, pominąć tylko złe wiersze, czy podzielić batch?

FireDAC może w zależności od sterownika zwracać błędy dla poszczególnych elementów tablicy. W praktyce jednak nie jest to „automatycznie dostępne” we wszystkich przypadkach. Musisz liczyć się z tym, że niektóre bazy danych/dostawcy zgłaszają tylko pierwszy błąd albo że błąd w batchu przerywa wykonanie reszty. Dokładnie z tego powodu solidny wzorzec jest zwykle dwustopniowy:

• Etap A: Spróbuj wykonać batch jako Array DML.
• Etap B: Jeśli batch się nie powiedzie, podziel go (na pół) lub kontrolowanie wróć do przetwarzania pojedynczych wierszy – ale tylko dla tego batchu – i dokładnie zaloguj.

Brzmi jak dodatkowa praca, ale w ścieżkach importu to różnica między „o 02:00 w nocy wszystko stoi” a „import przechodzi, 7 wierszy trafia na listę błędów”.

Praktyczny wzorzec: najpierw batch, potem selektywne izolowanie

Poniższy wzorzec sprawdził się w rozwiązaniach programowych blisko procesów, w których jakość danych jest zmienna:

Krok 1: Umieść dane w strukturze wsadowej (wraz z kontekstem błędu)

Przechowuj dane do zaimportowania nie tylko jako wartości surowe, lecz z minimalnym kontekstem: zewnętrzne ID, numer wiersza z źródła, ewentualnie hash/checksumę. To nie jest „Nice to have“: w przypadku błędu nie chcesz ponownie parsować CSV, żeby ustalić, co jest nie tak.

Krok 2: Wykonaj Array DML

Ustawiasz ArraySize na długość wsadu, wiążesz parametry jako tablice i wykonujesz ExecSQL. Ważne: zachowaj stabilność typów parametrów (np. nie wiąż pól numerycznych raz jako String, raz jako Integer), inaczej DB wygeneruje implicitne rzutowania lub FireDAC będzie musiał konwertować każdy element.

Krok 3: Scenariusz błędu – zawęź wsad zamiast powtarzać w ciemno

Jeśli ExecSQL zawiedzie, masz dwie solidne opcje:

• Binary Split (połowienie): podziel wsad na dwie połówki i spróbuj ponownie wykonać każdą z nich jako Array DML. Powtarzasz to, aż dojdziesz do niewielkiej liczby rekordów, które możesz sprawdzić pojedynczo. Zaleta: zachowujesz dużą wydajność, jeśli uszkodzonych jest tylko kilka wierszy. Wada: więcej logiki, a przy błędach systemowych (np. nieprawidłowy typ danych) przynosi niewiele.
• Fallback auf Einzelzeilen dla tego wsadu: ustawiasz ArraySize=1 (lub wiążesz pojedyncze wartości) i wykonujesz wiersz po wierszu, logujesz błędy i kontynuujesz. Zaleta: proste, gwarantuje działanie dla każdego wiersza. Wada: tracisz tempo w tym wsadzie.

W praktyce łączę obie metody: najpierw 1–2 razy dzielę (żeby szybko przetworzyć „dobre bloki”), potem przy niewielkich pozostałościach przełączam się na wiersze pojedyncze, aby zalogować jednoznaczne informacje o błędach.

Obiekty błędów i komunikaty: co powinieneś wydobyć z FireDAC

FireDAC kapsułkuje błędy DB w wyjątkach (zwykle EFDDBEngineException) z informacjami szczegółowymi. Dla eksploatacji istotne są trzy poziomy:

• Kod błędu DB (specyficzny dla DB): np. SQLSTATE w PostgreSQL, Error Number w SQL Server.
• Nazwa ograniczenia/obiektu: często zawarta w tekście błędu (indeks unikatowy, ograniczenie FK).
• Kontekst instrukcji: tabela, operacja, ewentualnie wartości parametrów (ostrożnie z danymi osobowymi).

Jeśli chcesz logować per wiersz, musisz w przypadku błędu dodatkowo zidentyfikować wiersz. FireDAC może w niektórych przypadkach zwrócić indeks tablicy. Nie polegaj jednak wyłącznie na tym. Zawsze dodaj własny indeks (pozycja we wsadzie) i zaloguj dla tej pozycji przynajmniej jeden klucz biznesowy.

Pułapki, które kosztują czas przy rzeczywistych importach

1) „To była tylko jedna linia“ – ale transakcja jest już „dirty”

W zależności od DB i sterownika błąd może spowodować, że wykonanie całego instrukcji zostanie uznane za nieudane, a transakcja znajdzie się w stanie, w którym musisz ją jawnie cofnąć lub w którym kolejne instrukcje zakończą się niepowodzeniem. Zwłaszcza w niektórych sterownikach „po błędzie po prostu kontynuować” nie jest bezpiecznym założeniem.

Konsekwencja: jeśli pracujesz w transakcji i wsad zawiedzie, standardowy przebieg to: rollback bieżącego kontekstu wsadu (lub całej transakcji) i ponowne uruchomienie. To dobrze pasuje do podejścia „commit per batch”.

2) Autocommit vs. jawna transakcja

Jeśli nie rozpoczynasz jawnej transakcji, to często sterownik/provider decyduje, jak committować instrukcje. Dla importów masowych rzadko jest to pożądane. Jawne transakcje dają Ci kontrolę nad:

• czas trwania blokady
• Zachowanie cofania transakcji (rollback)
• Punkty ponownego uruchomienia

I: „jawnie“ nie oznacza „ogromnej transakcji“. Oznacza „świadomie“.

3) Trigger, Constraints und Nebenwirkungen

Array DML przyspiesza przekazywanie danych, ale nie automatycznie pracę po stronie serwera. Jeśli na tabeli docelowej masz triggery (np. audit-logging, automatyczne obliczanie statusu), to wąskim gardłem może wcale nie być sam INSERT, lecz kod triggera. Wówczas batch może mieć co prawda mniej roundtripów, ale CPU na serwerze baz danych pozostanie czynnikiem ograniczającym.

Dla administratorów i liderów technicznych: przy problemach z wydajnością warto spojrzeć na Wait Events/Locks i dziennik transakcji. Bulk-Insert jest wtedy tylko wyzwalaczem, nie przyczyną.

4) Datentypen und implizite Konvertierungen

Jedna z najczęstszych przyczyn „dlaczego to jest wolne?“: parametry są wiązane jako stringi, a baza danych rzutuje (castuje) każdą linię na Integer/Date/Decimal. To jest niewidoczne, ale kosztowne. Dla stabilnej wydajności:

• Ustaw odpowiednie typy parametrów (data jako data, liczba jako liczba).
• W przypadku typów decimal zwróć uwagę na pułapki związane z lokalizacją (przecinek vs. kropka). FireDAC jest tu zwykle poprawne, ale mieszane źródła — nie.
• Uzgodnij strategię stref czasowych/UTC zawczasu (timestampy to klasyk przy importach).

5) Fehlertexte sind für Menschen, aber nicht für Automatisierung

Kusi, aby parsować tekst błędu („duplicate key value violates unique constraint …“). Rób to wyłącznie jako ostateczność. Lepiej polegać na strukturalnych kodach (SQLSTATE, numer błędu). Niestety nie wszystkie sterowniki dostarczają tych informacji jednakowo dobrze. Zaplanuj więc oba: kod i tekst, plus opcjonalnie „nazwa constraintu wyciągnięta z tekstu“, ale bez silnej zależności.

Debugging-Hinweise: So findest du schnell die kaputte Zeile

Batch reproduzierbar machen

Jeśli import sporadycznie zawodzi, potrzebujesz odtwarzalności. Zapisz dla każdego batcha mały plik diagnostyczny lub wpis w logu, który zawiera:

• Numer batcha i czas
• ArraySize i tryb transakcji
• listę kluczy biznesowych (np. zewnętrzne ID) w batchu

To często wystarcza, aby później uruchomić celowo mini-import tylko dla tych ID.

Finale SQL sichtbar machen (aber ohne Datenleaks)

W trakcie debugowania chcesz wiedzieć: czy SQL jest poprawne? Czy parametry są prawidłowe? FireDAC oferuje monitoring/tracing poprzez komponenty FDMoni i logowanie sterowników. W środowiskach zbliżonych do produkcyjnych ważne jest:

• Włączaj tracing selektywnie i tylko tymczasowo (wydajność i ochrona danych).
• Loguj wartości parametrów tylko w bezpiecznym środowisku lub zmaskowane.
• W przypadku danych osobowych: w logach tylko techniczne klucze (ID) i żadnych danych w postaci jawnej.

Wenn du split-testest: Abbruchkriterien definieren

Przy binarnym dzieleniu nie chcesz dzielić w nieskończoność. Ustaw dolną granicę, np. „poniżej 20 wierszy przełącz na tryb pojedynczych operacji“. I ustaw limit, ile błędów łącznie tolerujesz, zanim przerwiesz import (np. przy systematycznych problemach mapowania). W przeciwnym razie wpadniesz w nieskończące się listy błędów i zablokujesz dalsze przetwarzanie.

Wann sich der Aufwand wirklich lohnt (und wann nicht)

Array DML z obsługą błędów na poziomie wiersza szczególnie się opłaca, gdy:

• Przetwarzanych jest dużo wierszy (tysiące do milionów).
• Niewiele wierszy jest błędnych, ale chcesz mimo to przetworzyć resztę.
• Import musi działać stabilnie w środowisku produkcyjnym (np. przetwarzanie nocne, serwis bez UI).
• Musisz odesłać listę błędów do działu biznesowego/źródła (z odniesieniem do wierszy).

Opłaca się to mniej, gdy:

• piszesz tylko kilka tuzinów wierszy (pojedyncze INSERTy są w porządku),
• jakość danych jest tak niska, że 30–50% wierszy zawodzi (wtedy sensowniejsza jest strategia stagingowa),
• i tak używasz natywnego dla bazy narzędzia do ładowania wsadowego (np. COPY w PostgreSQL, BCP/BULK INSERT w SQL Server) – wtedy Array DML nie jest narzędziem.

Alternatywna architektura: tabela staging zamiast „bezpośrednio do celu”

Jeśli regularnie masz do czynienia z mieszanymi jakościowo danymi, czyste „INSERT bezpośrednio do tabeli docelowej” często jest niewłaściwą decyzją. Tabela staging (faza wstępna) to tabela, w której najpierw zapisujesz dane technicznie poprawnie (ew. z miękkimi typami), a dopiero potem walidujesz je i przenosisz do tabeli docelowej.

Zalety w eksploatacji:

• Błędne rekordy pozostają zapisane w sposób możliwy do śledzenia (w tym dane surowe).
• Walidację możesz wykonywać oddzielnie i powtarzalnie.
• Oddzielasz przyjęcie danych przez interfejs od przetwarzania merytorycznego.

Array DML jest wtedy często szybkim sposobem do tabeli staging, podczas gdy przeniesienie do tabeli docelowej odbywa się jako SQL oparte na zbiorach (lub procedura składowana). To przesuwa obsługę błędów bardziej na stronę bazy danych, co w zależności od organizacji (role DBA, proces wdrożeniowy) może być sensowne lub niepożądane.

Eksploatacja i administracja: was kierownicy IT i administratorzy powinni wiedzieć

Monitoring: wskaźnik błędów i przepustowość są kluczowymi metrykami

Dla stabilnej eksploatacji importu wsadowego dwie metryki mówią więcej niż sama „czas trwania”:

• Przepustowość: wiersze na minutę (lub na Batch) łącznie z wartościami pikowymi/medianą.
• Wskaźnik błędów: błędne wiersze na przebieg, najlepiej pogrupowane według klas błędów (Unique, FK, NOT NULL, konflikt typów).

Jeśli regularnie obserwujesz te dwie wartości, szybko zauważysz, czy coś zmieniło się u źródła (np. nowy format) albo czy system docelowy (np. nowe constraints) stał się bardziej restrykcyjny.

Blokady i okna obciążenia

Bulk-inserty mogą powodować blokady i obciążenie IO. Jeśli równolegle użytkownicy pracują na tych samych tabelach, musisz rozważyć poziomy izolacji, indeksy i ewentualnie partycjonowanie. W praktyce oznacza to: albo wykonywać importy w oknach niskiego obciążenia, albo zaprojektować przepływ danych tak, żeby koegzystował z działającym systemem (np. przez staging + asynchroniczne przejęcie).

Konkretna lista kontrolna dla odpornego Bulk-Insertu z Array DML

• Batch-Größe ustalić (wartość początkowa 500–1 000) i mierzalnie stroić.
• Eksplizytna transakcja: commit na Batch jako domył, „commit na końcu” tylko świadomie.
• Stabilne typy parametrów ustawić, nie wymuszać rzutowań niejawnych.
• Przenosić kontekst błędu dla każdego rekordu (zewnętrzne ID, wiersz źródłowy).
• Strategia błędów: najpierw batch, potem rozbicie/fallback, logowanie na poziomie wiersza.
• Logowanie: kody + tekst, ale zgodne z ochroną danych; rejestrować Batch-ID i Run-ID.
• Ponowne uruchomienie: zapewnić idempotencję (klucz/Upsert/Import-ID).

Wniosek: Array DML jest szybki – odporność zapewniają proces i strategia obsługi błędów

FireDAC Bulk-Insert z użyciem Array DML to skuteczne narzędzie, o ile nie udajesz, że błędów nie ma. W rzeczywistych strumieniach danych zawsze pojawiają się odstępstwa: duplikaty, brakujące referencje, uszkodzone wartości dat. Dlatego właściwe podejście to: Array DML dla wydajności, połączone z kontrolowaną strategią izolacji (Split lub Fallback) oraz listą błędów możliwą do prześledzenia na poziomie wiersza. Dzięki temu uzyskujesz jednocześnie szybkość i bezpieczeństwo operacyjne – i właśnie na tym polega różnica, gdy importy nie działają tylko w laboratorium, lecz muszą wykonywać się niezawodnie każdej nocy.

Jeśli chcecie ustabilizować istniejący proces importu lub proces interfejsu w Delphi/FireDAC (wydajność, transakcje, wznowienie, logowanie), chętnie omówimy to uporządkowanie w rozmowie technicznej:

W kontekście tego zagadnienia ważne są także Delphi Bulk Insert oraz Bulk Insert Delphi FireDAC. Artykuł porządkuje te aspekty w sposób zrozumiały i pokazuje, na co zwracać uwagę w codziennej pracy.

Omów projekt lub przedsięwzięcie modernizacyjne z Net-Base.