FireDAC: Bulk-Insert Array DML:llä ja selkeä rivikohtainen virheenkäsittely

Yksi BDE-Ablosung mit nativer Anbindung Bulk-Insert mit Array DML on usein nopein tapa saada paljon tietueita tietokantaan: sen sijaan, että lähetettäisiin tuhat erillistä insert-lauseketta, parametrien sijaan sidotaan taulukko ja lähetetään ne kerralla palvelimelle. Käytännössä ongelma kuitenkin ilmenee nopeasti: yksi tietue rikkoo Unique-Indexin, ein NOT NULL -feld on tyhjä, vierasavain ei täsmää – ja yhtäkkiä ei ole selvää, mikä rivi pysäytti batchin, onko osa jo kirjoitettu ja miten jatkat puhtaasti ilman tietoinkon­sistentseja.

Tästä tässä on kyse: miten käytät Array DML:ää niin, että saat riviä kohti luotettavat virhetiedot, pidät transaktion hallinnassa ja tuotannossa voit jäljittää, mitä tapahtui. Painopiste ei ole akateemisessa API-lukemisessa, vaan reunatapauksessa, joka todellisissa tuonnissa ilmaantuu säännöllisesti: suur i batch, muutama rikkinäinen rivi, mutta haluat silti nopeuden.

FireDAC-massasisäänajo Array DML:llä — miksi Array DML kannattaa massasisäänajossa

Array DML (Data Manipulation Language) tarkoittaa FireDAC: siinä sidot parametrit eivät yksittäisinä arvoina vaan taulukkona. FireDAC lähettää sitten (riippuen ajurista/tietokannasta) vähemmän roundtripejä, voi käsitellä palvelinpuolella tehokkaammin ja vähentää asiakkaan overheadia merkittävästi. Tämä on erityisen relevanttia kolmessa tilanteessa:

• ETL- und Importstrecken: CSV/XML/JSON sisään, normalisointi/mapping, sitten staging- tai kohdetaulukkoon.
• Schnittstellen-Puffer: REST- tai MQ-payloadit kerätään ja tallennetaan jaksoittain.
• Protokoll-/Event-Tabellen: paljon pieniä inserttejä, joissa latenssi on hallitseva.

Hyöty ei kuitenkaan tule ilmaiseksi. Array DML siirtää monimutkaisuuden „monista yksittäisistä statementeista“ yhden statementin sisälle, jossa on monta riviä. Se on hyvä suorituskyvylle, mutta vaativampi virheiden diagnosoinnin, transaktio­logiikan ja uudelleenkäynnistyksen kannalta.

Tyypillinen reunatapaus: yksi batch, yksi rikkinäinen rivi

Klassikko tuotannossa: tuot 50 000 riviä. Valitset ArraySizen 1 000:ksi, koska et halua roundtrippiä joka riville. Batch 17 epäonnistuu. Tietokanta raportoi usein vain „duplicate key“ tai „violates foreign key constraint“. Käyttöliittymässä tai palvelulogissa lukee silloin usein vain: „ExecSQL failed“.

Ilman kunnollista virheenkäsittelyä tapahtuu yleensä kaksi huonoa asiaa:

• Hylkäät koko batchin, vaikka 999 / 1 000 rivistä olisi kunnossa.
• Palaat yksittäisiin insertteihin ja menetät suorituskykyedun pysyvästi.

Tavoitteena on kolmas tie: säilyttää batch-suorituskyky, mutta kirjata virhekohtaisesti (rivien indeksi, avainarvot, DB-virheteksti) ja valinnaisesti commitoida „hyvät rivit” – riippuen siitä, kuinka kriittisiä yhdenmukaisuus ja idempotenssi (usein toistaminen ilman kaksinkertaista vaikutusta) ovat prosessissasi.

FireDAC Array DML: Keskeiset säätöparametrit (ilman myyttejä)

Array DML:n Bulk-inserteissä käytännössä samat säätöparametrit ovat ratkaisevia:

1) ArraySize ja batch-koko

ArraySize (TFDQuery/TFDCommand:n yhteydessä) määrää, kuinka monta „riviä“ FireDAC yhdellä kutsulla käsitellään. Suurempi ei automaattisesti ole parempi. Liian suuri tarkoittaa: enemmän muistia klientissä, suurempi siirrettävä payload verkossa, suuremmat lukot/transaction-log-kuorma palvelimella ja virhetilanteessa suurempi vaikutusalue. Kestävissä tuonneissa tyypillinen lähtökohta on usein 200–2 000 eräkoko, riippuen sarakkeiden määrästä, BLOBeista ja latenssista.

2) Transaktioraja

Tarvitset selkeän päätöksen: commit eräkohtaisesti vai commit koko tuonnille. Tämä ei ole makuasia, vaan operatiivinen päätös:

• Commit eräkohtaisesti: rajaa lukot ja transaktiojournalin, helpompi uudelleenkäynnistää, mutta välitilat ovat näkyvissä (riippuen isolaatiotasosta). Virhe erässä 17 jättää erät 1–16 järjestelmään.
• Commit lopussa: „kaikki tai ei mitään”, loogisesti konsistentimpi, mutta suurissa määrissä riskinä pitkät lukot, suuri rollback ja virhetilanteessa kaiken menetys.

Monille rajapinta- ja tuontiprosesseille „commit eräkohtaisesti” on realistisempi operointistrategia – mutta vain, jos olet järjestänyt idempotenssin ja kaksoiskappaleiden käsittelyn selkeästi (esim. luonnollisilla avaimilla, Upserts tai import-ID).

3) UpdateOptions ja Prepared Statements

Toistuvissa erissä kannattaa pitää lausunto valmisteltuna. „Prepare” tarkoittaa: FireDAC antaa DB:n jäsentää/kompiloida lausunnon ja käyttää sitä uudelleen. Riippuen tietokannasta tällä voi olla merkittävä vaikutus, erityisesti korkealla suoritusfrekvenssillä. Tärkeämpää kuin mikään temppu on: saman Query-objektin (tai saman TFDCommandin) johdonmukainen uudelleenkäyttö ja vakaat parametrityypit.

Siisti rivikohtainen virheenkäsittely: mitä todella tarvitset

Jos haluat käsitellä virheitä „rivikohtaisesti“, tarvitset kolme asiaa:

1. Määrittely: mikä array-indeksi (0..N-1) epäonnistui?
2. Konteksti: mitkä ovat tämän rivin liiketoiminnan avainarvot (esim. ulkoinen ID, asiakasnumero, aikaleima)?
3. Ohjaus: mitä teet sen jälkeen? Keskeytätkö, hyppäätkö vain huonoista riveistä yli vai jaatko erän?

FireDAC voi ajurista riippuen palauttaa virheen per array-alkio. Käytännössä tätä ei kuitenkaan voi olettaa aina saatavilla. On varauduttava siihen, että jotkin tietokannat/tarjoajat raportoivat vain ensimmäisen virheen tai että erän virhe estää jäljellä olevien suorittamisen. Tästä syystä robusti malli on yleensä kaksitasoinen:

• Taso A: Yritä suorittaa erä Array DML:na.
• Taso B: Jos erä epäonnistuu, jaa se (puolita) tai palaa hallitusti yksittäisriveihin – mutta vain tälle erälle – ja kirjaa virheet selkeästi.

Tämä kuulostaa lisätyöltä, mutta tuontiketjuissa se on ero sen välillä, että „klo 02:00 kaikki pysähtyy” ja että „tuonti jatkuu, 7 riviä päätyy virhelistalle”.

Käytännöllinen malli: ensin erä, sitten kohdennettu eristäminen

Seuraava malli on osoittautunut toimivaksi prosessiläheisissä ohjelmistoratkaisuissa, joissa datan laatu vaihtelee:

Vaihe 1: Tallenna data erärakenteeseen (mukaan lukien virhekonteksti)

Tallenna tuotavat tiedot pelkkien arvojen sijaan vähintään seuraavalla kontekstilla: ulkoinen ID, rivinumero lähteestä, tarvittaessa hash/tarkistussumma. Tämä ei ole „mukava lisä“: virhetapauksessa et halua parsia CSV:tä uudelleen selvittääksesi, mikä on rikki.

Vaihe 2: Suorita Array DML

Aseta ArraySize erän pituudeksi, sido parametrit taulukkoina ja suorita ExecSQL. Tärkeää: pidä parametrityypit vakaina (esim. numeerisia kenttiä ei saa sitoa joskus merkkijonona, joskus kokonaislukuna), muuten tietokanta tekee implisiittisiä castauksia tai FireDAC joutuu muuntamaan jokaisen alkion.

Vaihe 3: Virhetilanne – rajoita erää sen sijaan, että toistat umpimähkään

Jos ExecSQL epäonnistuu, sinulla on kaksi luotettavaa vaihtoehtoa:

• Binary Split (puolittaminen): jaa erä kahteen osaan ja yritä kumpaakin osaa uudelleen array-DML:llä. Toista tämä, kunnes päädyt pieneen määrään, jonka voit tarkistaa yksitellen. Etu: säilytät suurimman osan suorituskyvystä, jos vain muutama rivi on viallinen. Haitta: lisää loogisuutta, ja systemaattisten virheiden (esim. väärä tietotyyppi) tapauksessa hyöty on vähäinen.
• Paluu yhden rivin käsittelyyn tälle erälle: aseta ArraySize=1 (tai sido yksittäisarvot) ja suorita rivi riviltä, kirjaa virheet ja jatka. Etu: yksinkertainen, takaa rivikohtaisen käsittelyn. Haitta: tässä erässä menetät nopeutta.

Käytännössä yhdistän molempia: ensin puolitan 1–2 kertaa (saadakseni „hyvät lohkot“ nopeasti läpi), sitten pienissä jäljelläolevissa määrissä siirryn yksittäisrivien käsittelyyn, jotta saan yksiselitteiset virhetiedot lokiin.

Virheobjektit ja viestit: mitä sinun kannattaa poimia FireDAC:stä

FireDAC kapseloi tietokantavirheet poikkeuksiin (tyypillisesti EFDDBEngineException), jotka sisältävät yksityiskohtaiset tiedot. Toiminnan kannalta kolme tasoa ovat olennaisia:

• Tietokantavirhekoodi (DB-kohtainen): esim. SQLSTATE PostgreSQL:ssä, Error Number SQL Serverissä.
• Rajoite-/objektin nimi: usein virhetekstissä mukana (unique-index, FK-constraint).
• Lausekkeen konteksti: taulu, operaatio, tarvittaessa parametrien arvot (varo henkilötietoja).

Jos haluat kirjata riveittäin, sinun tulee virhetilanteessa lisäksi tunnistaa rivi. FireDAC voi joissain tapauksissa antaa array-indeksin. Älä kuitenkaan luota pelkästään siihen. Rakenna aina lisäksi oma indeksi (sijainti erässä) ja kirjaa tähän sijaintiin vähintään yksi liiketoimintatason avain.

Karikot, jotka vievät aikaa todellisissa tuontitapauksissa

1) „Se oli vain yksi rivi“ – mutta transaktio on jo „dirty“

Riippuen tietokannasta ja ajurista virhe voi johtaa siihen, että koko lausekkeen suoritus katsotaan epäonnistuneeksi ja transaktio jää tilaan, jossa sinun on joko tehtävä eksplisiittinen rollback tai jossa seuraavat lauseet epäonnistuvat. Erityisesti joidenkin ajurien kohdalla „virheen jälkeen vain jatkaminen“ ei ole luotettava oletus.

Seuraus: jos työskentelet transaktion sisällä ja erä epäonnistuu, oletuspolku on: rollback nykyisestä eräkontekstista (tai koko transaktiosta) ja aloita uudelleen. Tämä sopii hyvin „commit per erä“ -malliin.

2) Autocommit vs. eksplisiittinen Transaktio

Jos et aloita eksplisiittistä transaktiota, ajuri/provider usein päättää, miten se commitoi lauseet. Massatuonteihin se harvoin on haluttu käytös. Eksplisiittiset transaktiot antavat sinulle hallinnan seuraavista:

• lukituksen kesto
• Rollback-käyttäytyminen
• Uudelleenkäynnistyskohdat

Ja: Eksplisiittinen ei tarkoita „valtavaa transaktiota“. Se tarkoittaa „tietoista“.

3) Triggerit, constraintit ja sivuvaikutukset

Array DML nopeuttaa siirtoa, mutta ei automaattisesti palvelinpuolen työtä. Jos kohdetaulussa on triggereitä (esim. audit-lokin kirjaus, automaattinen tilan laskenta), pullonkaula ei välttämättä ole INSERT vaan triggerkoodi. Silloin batchilla voi olla vähemmän roundtrips, mutta DB-palvelimen CPU pysyy rajoittavana tekijänä.

Järjestelmänvalvojille ja teknisille leadseille: suorituskykyongelmissa kannattaa katsoa Wait Events/Locks ja transaktioloki. Bulk-Insert on silloin vain laukaisija, ei syy.

4) Tietotyypit ja implisiittiset muunnokset

Yksi yleisimmistä „Miksi tämä on hidas?“ -syistä: parametrit bindataan merkkijonoina, ja DB castaa rivikohtaisesti Integeriksi/Dateksi/Decimaliksi. Se on näkymätöntä mutta kallista. Vakaan suorituskyvyn varmistamiseksi:

• Aseta parametrien tietotyypit oikein (päivämäärä päivämääränä, luku lukuna).
• Desimaalien kohdalla huomioi locale-ansat (pilkku vs. piste). FireDAC on tässä yleensä oikea, mutta sekoitetut lähteet eivät ole.
• Sovi aikavyöhykkeistä/UTC-strategiasta etukäteen (aikaleimat ovat tuontien klassikko).

5) Virhetekstit ovat ihmisille, mutta eivät automaatiolle

On houkuttelevaa parsia virhetekstiä („duplicate key value violates unique constraint …“). Tee se vain viimeisenä keinona. Parempia ovat jäsennellyt koodit (SQLSTATE, virhenumero). Valitettavasti kaikki ajurit eivät tarjoa kaikkea yhtä hyvin. Suunnittele siis molemmat: koodi ja teksti, sekä valinnainen „constraint-nimen poisto tekstistä“, mutta ilman tiukkaa riippuvuutta.

Debuggausvinkit: Näin löydät nopeasti rikkinäisen rivin

Tee batch toistettavaksi

Jos tuonti epäonnistuu satunnaisesti, tarvitset toistettavuutta. Tallenna jokaista batchia kohden pieni diagnostiikkatiedosto tai lokimerkintä, joka sisältää:

• Batch-numero ja aika
• ArraySize ja transaktiotila
• lista toiminnallisista avaimista (esim. ulkoiset ID:t) batchissa

Se riittää usein käynnistämään jälkikäteen kohdennetun pienimuotoisen tuonnin vain näille ID:ille.

Näytä lopullinen SQL (mutta ilman tietovuotoja)

Debuggauksessa haluat tietää: Onko SQL oikea? Ovatko parametrit oikein? FireDAC tarjoaa monitoroinnin/tracingin FDMoni-komponenttien ja ajurin lokituksen kautta. Tuotantoon läheisissä ympäristöissä on tärkeää:

• Ota tracing käyttöön kohdennetusti ja vain väliaikaisesti (suorituskyky ja tietosuoja).
• Kirjaa parametrien arvot vain turvallisessa ympäristössä tai maskattuna.
• Henkilötietojen kohdalla: lokiin vain tekniset avaimet (ID:t), ei selväkielisiä tietoja.

Jos teet split-testausta: määrittele keskeytyskriteerit

Binary Splitissä et halua jakaa loputtomasti. Aseta alaraja, esim. „alle 20 riviä → vaihda yksittäistilaan“. Aseta myös raja, kuinka monta virhettä kokonaisuutena sallit, ennen kuin keskeytät tuonnin (esim. systemaattisten mapping-ongelmien tapauksessa). Muuten saat loputtomia virhelistoja ja estät jatkokäsittelyn.

Milloin vaiva todella kannattaa (ja milloin ei)

Array DML, jossa on rivikohtainen virheenkäsittely, kannattaa erityisesti, kun:

• Suuria rivimääriä käsitellään (tuhansia tai miljoonia).
• Vain harvat rivit ovat virheellisiä, mutta haluat silti käsitellä koko joukon.
• Tuonnin on toimittava vakaasti tuotannossa (esim. yökäsittely, palvelu ilman UI:ta).
• Sinun täytyy palauttaa virheraportti liiketoimintayksikölle/lähteelle (riviviittaus mukana).

Se ei ole yhtä kannattavaa, kun:

• kirjoitat vain muutaman kymmenen riviä (yksittäiset INSERT-operaatiot ovat ok),
• datan laatu on niin huono, että 30–50 % riveistä epäonnistuu (silloin staging-strategia on järkevämpi),
• käytät joka tapauksessa DB-natiivista bulk-load-menettelyä (esim. COPY PostgreSQL:ssä, BCP/BULK INSERT SQL Serverissä) – silloin Array DML ei ole oikea työkalu.

Alternative Architektur: Staging-Tabelle statt „Direkt ins Ziel”

Jos kamppailet säännöllisesti vaihtelevan datalaadun kanssa, puhdas „insert suoraan kohdetauluun“ on usein väärä valinta. Eine staging-taulukko (esiaste) on taulukko, johon tallennat tiedot ensin teknisesti oikein (tarvittaessa pehmeämmillä tyypeillä) ja validoit ne vasta sen jälkeen ja siirrät kohdetauluun.

Käytön edut:

• Virheelliset tietueet säilyvät jäljitettävänä (mukaan lukien raakadata).
• Voit suorittaa validoinnin erillisenä ja toistettavana prosessina.
• Erotat rajapinnan vastaanoton ja toiminnallisen käsittelyn.

Array DML on usein nopea tapa staging-taulukkoon, kun taas siirto kohdetauluun tehdään set-pohjaisella SQL:llä (tai stored procedurella). Tämä siirtää virheenkäsittelyä enemmän DB-puolelle, mikä voi olla organisaatiosta riippuen järkevää tai ei-toivottavaa (DBA-roolit, deployment).

Käyttö ja hallinnointi: mitä IT-johtajien ja ylläpitäjien pitäisi tietää

Valvonta: virhesuhde ja läpäisy ovat keskeiset mittarit

Bulk-tuonnin vakaaseen käyttöön kaksi mittaria kertovat enemmän kuin pelkkä „suoritusaika“:

• Läpäisy: rivejä per minuutti (tai per erä) mukaan lukien huippu/mediaani.
• Virhesuhde: virheelliset rivit per ajo, mieluiten ryhmiteltyinä virheluokittain (Unique, FK, NOT NULL, tyyppikonflikti).

Kun seuraat näitä kahta arvoa säännöllisesti, huomaat varhain, onko lähteessä tapahtunut muutos (esim. uusi formaatti) tai onko kohdejärjestelmästä tullut tiukempi (esim. uudet constraintit).

Lukitukset ja kuormitusikkunat

Bulk-insertit voivat aiheuttaa lukituksia ja IO-kuormitusta. Jos käyttäjät työskentelevät samanaikaisesti samoilla tauluilla, sinun on huomioitava isolaatio­taso, indeksit ja tarvittaessa partitiointi. Käytännössä tämä tarkoittaa: joko ajoita importit kuormitusikkunoihin, tai suunnittele datavirta niin, että se voi olla rinnakkain käynnissä olevan toiminnan kanssa (esim. staging + asynkroninen siirto).

Konkretti tarkistuslista robustille Bulk-Insertille Array DML:llä

• Eräkoko määriteltävä (aloitusarvo 500–1 000) ja mitattavasti hienosäädettävä.
• Eksplisiittinen transaktio: commit per erä oletuksena; „commit lopussa“ vain tietoisesti käytettynä.
• Parametrityypit vakaiksi asetettava, älä pakota implisiittisiä tyyppimuunnoksia.
• Virhekonteksti per tietue mukana (ulkoiset ID:t, lähderivi).
• Virhestrategia: ensin erätasoinen käsittely, sitten split/fallback, lokitus per rivi.
• Lokitus: koodit + teksti, mutta tietosuojan mukaisesti; tallenna erä-ID ja ajo-ID.
• Uudelleenkäynnistys: varmista idempotenssi (avain/upsert/tuonti-ID).

Fazit: Array DML ist schnell – robust wird es durch Prozess und Fehlerstrategie

FireDAC Bulk-Insertin ja Array DML:n yhdistelmä on tehokas työkalu, kunhan et teeskennä, ettei virheitä ole. Todellisissa datavirroissa esiintyy aina poikkeuksia: duplikaatit, puuttuvat viitteet, virheelliset päivämääräarvot. Puhtaan lähestymistavan tulisi siksi olla: Array DML suorituskyvyn vuoksi, yhdistettynä kontrolloituun eristämisstrategiaan (Split tai Fallback) ja riveittäin jäljitettävään virhelistaan. Näin saat suorituskyvyn ja toimintavarmuuden yhdistettyä – ja juuri sillä on merkitystä, kun tuonnit eivät vain pyöri laboratoriossa, vaan ne on suoritettava luotettavasti joka yö.

Jos haluatte vakauttaa olemassa olevan tuonti- tai rajapintaprosessin in Delphi/FireDAC (suorituskyky, transaktiot, uudelleenkäynnistys, lokitus), selvitämme sen mielellämme jäsennellysti teknisessä keskustelussa:

Tähän aiheeseen liittyen ovat myös Delphi Bulk Insert ja Bulk Insert Delphi FireDAC tärkeitä. Artikkeli jäsentää nämä näkökohdat selkeästi ja osoittaa, mihin arjessa kannattaa kiinnittää huomiota.

Keskustele projektista tai modernisointihankkeesta Net-Base kanssa.