Het massaal bijwerken van gegevens in meerdere gekoppelde tabellen is een klassiek probleem in industriële SQL-programmering. Met de ontwikkeling van zakelijke applicaties is er behoefte ontstaan aan het gelijktijdig bijwerken van grote hoeveelheden gegevens, terwijl hun consistentie wordt gegarandeerd. Historisch gezien werden cyclische benaderingen gebruikt, wat resulteerde in lage prestaties en lange blokkades. Later kwamen geavanceerde DML-operators (zoals MERGE), transactionele constructies en benaderingen met staging-tabellen.
Probleem is dat het bijwerken van gegevens veel tabellen met relaties betreft (bijvoorbeeld bestellingen en besteldetails), wat kan leiden tot het ontstaan van "wees" verwijzingen (orphan rows), verlies van prestaties door blokkades en onvoorspelbare belasting op de database.
Oplossing is gebaseerd op het gebruik van atomische transacties, UPDATE/DELETE/MERGE-operaties met JOIN-voorwaarden, en batchverwerking van gegevens. Een goede praktijk is om geaggregeerde wijzigingen uit te stellen in tijdelijke staging-tabellen en deze vervolgens batchgewijs via een transactie toe te passen. Voorbeeld voor SQL Server met behulp van MERGE:
BEGIN TRANSACTION; -- Voorbeeld van massale update van de hoofd- en gekoppelde tabel met MERGE MERGE INTO orders AS tgt USING temp_order_updates AS src ON tgt.id = src.id WHEN MATCHED THEN UPDATE SET tgt.status = src.status, tgt.updated_at = src.updated_at; MERGE INTO order_details AS tgt USING temp_detail_updates AS src ON tgt.order_id = src.order_id AND tgt.sku = src.sku WHEN MATCHED THEN UPDATE SET tgt.price = src.price, tgt.qty = src.qty; COMMIT;
Belangrijke kenmerken:
Kun je gewoon de hoofdtafel bijwerken en daarna de gekoppelde tabellen apart, zonder transacties, als de snelheidseisen streng zijn?
Apart UPDATE's buiten een transactie leiden tot ernstige inconsistenties in de gegevens bij een fout in een van de fasen — bijvoorbeeld als bestellingen zijn bijgewerkt, maar de details niet zijn bijgewerkt, is de logica verbroken. In moderne databases verhoogt het gebruik van transacties de overhead bij batchverwerking vrijwel niet.
Zal de prestaties verminderen als je één grote UPDATE met een subquery uitvoert? Kan dit leiden tot blokkades?
Ja, monolithische UPDATE's op grote tabellen leiden tot escalatie van blokkades, tabel-locks en stilstand van andere gebruikers. Het is beter om de verwerking in batches te splitsen met een beperking via WHERE ... AND rownum/id/limit.
Voorbeeld van een batch:
UPDATE orders SET status = 'closed' WHERE status = 'pending' AND id BETWEEN 100000 AND 199999;
GARANDEERT MERGE de atomariteit en de juiste volgorde van verwerking van gekoppelde tabellen?
Nee, MERGE werkt binnen één tabel. Voor het bijwerken van gekoppelde tabellen zijn aparte MERGE of UPDATE nodig, en beide handelingen moeten binnen dezelfde transactie worden geplaatst.
Een bedrijf werkte de status ("Voltooid") bij in een miljoen bestellingen met aparte aanvragen buiten een transactie: eerst de hoofdorders, daarna de details order_details. Onder druk viel de server "uit" — in geval van een fout bleven de details met een "open" status. Voordelen:
Nadelen:
Ze implementeerden staging-tabellen en groepsverwerking binnen een transactie. Eerst werden alle wijzigingen berekend en in tijdelijke tabellen geplaatst, daarna werden beide hoofdtabellen batchgewijs bijgewerkt. In geval van een fout — volledige terugdraaiing. Voordelen:
Nadelen: