Parametrisierte Abfragen

Dieser Abschnitt behandelt ein Thema, das in den meisten SQL-Büchern fehlt: parametrisierte Abfragen und Bind-Parameter.

Bind-Parameter – auch dynamische Parameter oder Bind-Variablen ge­nannt – sind ein alternativer Weg, Daten an Datenbanken zu übergeben. Anstatt die Daten direkt in die SQL-Anweisung zu schreiben, benutzt man Platzhalter wie zum Beispiel ?, :name oder @name. Die tatsächlichen Werte werden der Datenbank über einen eigenen API-Aufruf zur Verfügung gestellt.

Bei manuellen Abfragen spricht nichts dagegen, die Werte direkt in die SQL-Anweisung zu schreiben. Bei Programmen gibt es aber zwei gute Gründe, die für die Verwendung von Bind-Parametern sprechen:

Sicherheit

Bind-Variablen sind der beste Weg SQL-Injections zu verhindern.

Performance

Datenbanken mit einem Ausführungsplan-Cache wie SQL Server oder die Oracle Datenbank können Ausführungspläne wiederverwenden, wenn eine SQL-Anweisung mehrfach ausgeführt wird. Dadurch kann der Aufwand, einen Ausführungsplan zu erstellen, vermieden werden. Dieser Mechanismus funktioniert aber nur, wenn genau dieselbe SQL-Anweisung wiederholt ausgeführt wird. Stehen andere Werte in der SQL-Anweisung, erstellt die Datenbank den Ausführungsplan nochmals.

Bei der Verwendung von Bind-Parametern stehen nicht die eigentlichen Werte, sondern nur Platzhalter in der SQL-Anweisung. Der SQL-String ändert sich durch die Werte also nicht. Dadurch ist es für die Datenbank dieselbe Anweisung, auch wenn sie mit verschieden Werten ausgeführt wird.

Aber natürlich gibt es auch Ausnahmen, wenn die betroffene Datenmenge, wie im folgenden Beispiel, stark von den eigentlichen Werten abhängig ist.

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

Bei einer kleinen Zweigstelle liefert ein Indexzugriff die beste Performance. Wenn man aber eine größere Zweigstelle betrachtet, kann es besser sein, die ganze Tabelle zu lesen:

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 30

In diesem Fall erfüllt das Histogramm für die SUBSIDIARY_ID-Spalte seinen Zweck. Der Optimizer verwendet es, um die Häufigkeit der Werte, die direkt in der SQL-Anweisung angegeben sind, zu ermitteln. Dadurch erhält er für die beiden Abfragen unterschiedliche Zeilenschätzungen. Das führt in weiterer Folge dazu, dass die Cost-Werte der beiden Plan-Varianten unterschiedliche sind. Am Schluss wählt der Optimizer den Ausführungsplan mit den geringsten Kosten. Das ist bei der kleineren Zweigstelle der Ausführungsplan mit dem Indexzugriff.

Der Cost-Wert der Operation TABLE ACCESS BY INDEX ROWID ist aber sehr stark von der Anzahl der Zeilen abhängig. Da die größere Zweigstelle circa zehn Mal so groß ist, steigt der Cost-Wert um diesen Faktor an. Dadurch steigen die Gesamtkosten sogar über die Kosten eines Full-Table-Scans. Wenn der Optimizer am Schluss den Ausführungsplan mit den geringsten Kosten auswählt, ist das bei der großen Zweigstelle also der Ausführungsplan ohne Indexzugriff.

Bei der Verwendung von Bind-Parametern hat der Optimizer aber keine konkreten Werte, deren Häufigkeit er mit dem Histogramm ermitteln könnte. Daher gelangt der Optimizer immer zur selben Zeilenschätzung und zum selben Cost-Wert. Am Schluss wählt er also immer denselben Ausführungsplan aus.

Vergleicht man den Optimizer mit einem Compiler, verhalten sich Bind-Parameter wie Programmvariablen, während Werte, die direkt in der SQL-Anweisung stehen, Konstanten entsprechen. So wie ein Compiler konstante Ausdrücke bereits bei der Übersetzung auflösen kann, kann der Optimizer konkrete Werte beim Erstellen des Ausführungsplanes berücksichtigen. Im Gegensatz dazu sind die Werte der Bind-Parameter für den Optimizer – vereinfacht gesagt – nicht sichtbar.

Aus diesem Blickwinkel scheint es etwas widersprüchlich, dass Bind-Parameter die Performance erhöhen können. Denn mit konkreten Werten kann der Optimizer immer den besten Ausführungsplan auswählen. Die Frage ist nur: zu welchem Preis? Denn das Erstellen aller Ausführungsplan-Varianten kann auch ein erheblicher Aufwand sein. Ein Aufwand, der sich nur dann auszahlt, wenn am Ende nicht immer dasselbe Ergebnis steht.

Bei der Frage, ob der Optimizer einen allgemeinen oder angepassten Ausführungsplan erstellen soll, steht die Datenbank vor einem Dilemma. Denn um zu wissen, ob sich der Optimierungs-Aufwand auszahlt, muss die Datenbank zuerst mehrere Ausführungspläne erstellen. Aber genau das möchte man durch einen Ausführungsplan-Cache vermeiden. Die Datenbank-Hersteller versuchen dieses Dilemma mit heuristischen Methoden zu lösen, haben dabei aber nur sehr beschränkten Erfolg.

Als Entwickler kann man der Datenbank bei diesem Dilemma aber helfen, indem man Bind-Parameter gezielt einsetzt. Das heißt, dass man prinzipiell immer Bind-Parameter verwenden sollte, außer bei Werten, die sich auf den Ausführungsplan auswirken sollen.

Ungleich verteilte Status-Codes wie „erledigt“ und „noch nicht erledigt“ sind ein gutes Beispiel. Dabei ist die Zahl der noch nicht erledigten Einträge meist gering im Vergleich zu den erledigten. In diesem Fall macht ein Indexzugriff nur dann Sinn, wenn man die noch nicht erledigten Einträge sucht. Ein anderes Beispiel sind Partitionen – das ist, wenn man Tabellen und/oder Indizes auf mehrere Speicherplätze aufteilt. Die Werte können dann beeinflussen, welche Partitionen bei einer Abfrage durchsucht werden müssen. Auch bei LIKE-Bedingungen kann es durch Bind-Parametern zu suboptimalen Ausführungsplänen kommen, wie wir im nächsten Abschnitt sehen werden.

Die folgenden Beispiele zeigen die Verwendung von Bind-Parameter in einigen gängigen Programmiersprachen.

Im SQL-Standard ist nur das Fragezeichen (?) als Platzhalter vorgesehen. Verwendet man mehrere Platzhalter, unterschieden sie sich nur durch die Position in der SQL-Anweisung – erster, zweiter, dritter, … und so fort. Das kann beim Arbeiten mit Code sehr unpraktisch sein, da sich die Nummerierung ändert, wenn man Platzhalter hinzufügt oder entfernt. Daher bieten manche Datenbanken proprietäre Lösungen für benannte Platzhalter an. Zum Beispiel mit einem Klammeraffen (@name) oder Doppelpunkt (:name).

String sql = prepare("SELECT * FROM ? WHERE ?");

sql.execute('employees', 'employee_id = 1');