Netzwerk-Latenzen bei insert verringern

Im Allgemeinen liefern SQL-Datenbanken die beste Performance, wenn sie am gesamten Problem auf einmal arbeiten0. Anstatt Zeilen einzeln abzurufen oder – Gott behüte – Daten aus mehreren Tabellen in der Applikation zusammenzuführen, sollte man eine einzelne Abfrage verwenden, die benötigten Daten – und nur diese – auf einmal abruft. Das Gleiche gilt natürlich auch, wenn man Daten ändert: um drei Zeilen zu löschen sollte man einedelete-Anweisung mit einer entsprechenden where-Klausel verwenden. Aber wie könnte man drei Zeilen auf einmal einfügen?

Tatsächlich gibt es ein sehr alte SQL-Funktion, die genau das ermöglicht: die values-Klausel kann mehre Zeile erzeugen.

INSERT INTO tbl (   S1 ,    S2 ,    S3 )
         VALUES ('Z1S1', 'Z1S2', 'Z1S3')
              , ('Z2S1', 'Z2S2', 'Z2S3')
              , ('Z3S1', 'Z3S2', 'Z3S3')

Das Hauptproblem bei diesem Ansatz ist, dass die Anweisung selbst, sowie die Anzahl und Position der Bind-Parameter1 von der Anzahl der Zeilen abhängt. Das ist kein großes Problem für Datenbanken – heutzutage wird ohnehin fast jede SQL-Anweisung als „dynamisches SQL“ ausgeführt. Im Quellcode könnte das aber sehr wohl zu Problemen führen. Im allgemeinen ist daher die Verwendung einer Batch-API2 die bessere Option.

Neben den praktischen Aspekten unterscheiden sich die beiden Ansätze auch semantisch: die meisten Batch-APIs führen die Anweisung in einer Latenz-Optimierten weise mehrfach aus3. Das bedeutet, dass jede Anweisung unabhängig von den anderen fehlschlagen kann. Wenn eine von drei Zeilen einen Constraint verletzt, werden die anderen beiden trotzdem eingefügt.

Wenn man jedoch mehrere Zeilen mit einer einzelnen Anweisung einfügt, sieht das anders aus: Der SQL-Standard verlangt für einzelne Anweisungen ein alles-oder-nichts verhalten4. Wenn im Beispiel oben eine der drei Zeilen einen Constraint verletzt, wird keine eingefügt. Abhängig von den Anforderungen kann entweder das eine, oder aber das andere Verhalten gewünscht sein.

Dieser semantische Unterschied betrifft auf Statement-Trigger: diese werden durch eine typische Batch-API mehrfach ausgelöst, durch ein einzelnes insert mit mehreren Zeilen jedoch nur einmal.

Zusammenfassend ist zu sagen, dass das Einfügen mehrere Zeilen mit einer einzelnen insert-Anweisung nützlich ist, wenn man das Alles-oder-Nichts-Verhalten benötigt und die Anzahl der Zeilen fix ist – oder zumindest in bekannten Grenzen bleibt. Ansonsten sind Batch-APIs die bessere Methode, um Latenzen zu reduzieren.

Kompatibilität

1. Nur ohne Schlüsselwort row
2. hängt von der Storage-Engine ab: InnoDB: ja, MyISAM: nein
3. Unterstützt kein insert…values mit mehreren Zeilen. Insert…select ist aber atomar

Konforme Alternativen

Wie oben beschrieben ist die Verwendung einer Batch-API der gängige Weg, die Latenzen zu reduzieren. Batch-APIs können in der Regel auch für update oder delete verwendet werden.

Wenn man zusätzlich eine alles-oder-nichts verhalten benötigt, muss man Transaktionen oder Savepoints verwenden.

Proprietäre Alternativen

insert all (Oracle)

Oracle unterstützt ein Multi-Tabllen-Insert, das es auch ermöglicht mehrere Zeilen mit einer insert-Anweisung einzufügen (Dokumentation).