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.

Für alle Anwendungsentwickler […] sollte der schmale Band […] eine Pflichtlektüre sein — ADMIN-Magazin

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
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   99 |   70 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   99 |   70 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEE_PK |   99 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("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
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           | 1000 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES | 1000 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("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.

Tipp

Histogramme sind vor allem bei ungleicher Verteilung nützlich.

Bei einer gleichmäßigen Verteilung genügt es oft, die Zahl der unterschiedlichen Werte durch die Anzahl der Zeilen insgesamt zu dividieren. Diese Methode wird auch bei Bind-Parametern verwendet.

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.

Tweet this tip

Tipp

Keine Bind-Parameter zu verwenden, ist, als würde man ein Programm jedes Mal neu kompilieren.

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.

Tipp

In der Praxis gibt es nur wenige Fälle, in denen sich die Werte auf den Ausführungsplan auswirken.

Im Zweifelsfall sollte man daher immer Bind-Parameter verwenden – schon alleine um SQL-Injections zu vermeiden.

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

C#

Ohne Bind-Parameter:

int subsidiary_id;
SqlCommand cmd = new SqlCommand(
                   "select first_name, last_name" 
                 + "  from employees"
                 + " where subsidiary_id = " + subsidiary_id
                 , connection);

Mit Bind-Parameter:

int subsidiary_id;
SqlCommand cmd =
       new SqlCommand(
                      "select first_name, last_name" 
                    + "  from employees"
                    + " where subsidiary_id = @subsidiary_id
                    , connection);
cmd.Parameters.AddWithValue("@subsidiary_id", subsidiary_id);

Siehe: Dokumentation zur Klasse SqlParameterCollection.

Java

Ohne Bind-Parameter:

int subsidiary_id;
Statement command = connection.createStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = " + subsidiary_id
                  );

Mit Bind-Parameter:

int subsidiary_id;
PreparedStatement command = connection.prepareStatement(
                    "select first_name, last_name" 
                  + "  from employees"
                  + " where subsidiary_id = ?"
                  );
command.setInt(1, subsidiary_id);

Siehe: Dokumentation zur Klasse PreparedStatement.

Perl

Ohne Bind-Parameter:

my $subsidiary_id;
my $sth = $dbh->prepare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = $subsidiary_id"
                );
$sth->execute();

Mit Bind-Parameter:

my $subsidiary_id;
my $sth = $dbh->prepare(
                  "select first_name, last_name" 
                . "  from employees"
                . " where subsidiary_id = ?"
                );
$sth->execute($subsidiary_id);

Siehe: Placeholders and Bind Values“ in der DBI-Dokumentation.

PHP

Das folgende Beispiel zeigt einen MySQL-Zugriff ohne Bind-Parameter:

$mysqli->query("select first_name, last_name" 
             . "  from employees"
             . " where subsidiary_id = " . $subsidiary_id);

Mit Bind-Parameter:

if ($stmt = $mysqli->prepare("select first_name, last_name" 
                           . "  from employees"
                           . " where subsidiary_id = ?")) 
{
   $stmt->bind_param("i", $subsidiary_id);
   $stmt->execute();
} else {
  /* handle SQL error */
}

Siehe: Dokumentation zu mysqli_stmt::bind_param und „Prepared statements and stored procedures“ in der PDO-Dokumentation.

Ruby

Ohne Bind-Parameter:

dbh.execute("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = #{subsidiary_id}");

Mit Bind-Parameter:

dbh.prepare("select first_name, last_name" 
          + "  from employees"
          + " where subsidiary_id = ?");
dbh.execute(subsidiary_id);

Siehe: „Quoting, Placeholders, and Parameter Binding“ im Ruby DBI Tutorial

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

Beachte

Bind-Parameter kann man nur für Werte verwenden.

Das bedeutet, dass man Tabellen oder Spaltennamen nicht durch Bind-Parameter ersetzen kann. Daher funktioniert keiner der beiden folgenden Bind-Parameter:

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

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

Wenn man die Struktur einer SQL-Anweisung zur Laufzeit ändern muss, muss man dynamisches SQL verwenden.

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Cursor Sharing und Auto Parameterization

Je komplexer der Optimizer und die SQL-Anweisung werden, desto wichtiger ist ein Cache für Ausführungspläne. Daher hat sowohl SQL Server als auch die Oracle Datenbank einen Mechanismus um SQL-Abfragen automatisch zu parametrisieren. Dabei ersetzt die Datenbank Werte, die direkt in der SQL-Anweisung stehen, durch Platzhalter. Diese Funktion heißt bei der Oracle Datenbank CURSOR_SHARING beziehungsweise forced parameterization bei SQL Server.

Beide Funktionen sind aber nur Notlösungen für Applikationen, die generell keine Bind-Parameter verwenden. Den Entwicklern wird damit die Möglichkeit genommen, gezielt auf Bind-Parameter zu verzichten.

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.