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, daß 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

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.

Properitä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).

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Fußnoten

  1. Ausnahmen bestätigen die Regel. Es gibt wohl auch Datenbanken, bei denen diese Aussage öfter falsch als richtig ist.

  2. Das Beispiel enthält zu Demonstrationszechen eingebettete Werte. In Programmen sollte man selbstverständlich immer Bind-Parameter verwenden. Nicht wahr, Bobby?

  3. Wie zum Beispiel Javas PreparedStatement.addBatch(). Der Performancegewinn hängt jedoch von der Implementierung ab.

  4. Das ist eine gängige Art Batch-APIs umzusetzen. Es könnte aber Batch-APIs geben, die im HIntergrund eine einzelne Anweisung verwenden. Prüfe die Dokumentation.

  5. „Checking of Constraints“: §4.10.1 in SQL-92 beziehungsweise §4.23.2 in SQL:2016.

„modern SQL“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz