Dynamisches SQL ist langsam

Der wahre Kern hinter diesem Mythos ist sehr einfach: Dynamisches SQL kann langsam sein, wenn man es falsch anwendet.

Um den Mythos zu verstehen, müssen einige Begriffe definiert werden:

Eingebettetes SQL (embedded SQL)

Das Einbetten von SQL (embedded SQL) direkt in Programmcode ist vor allem bei prozeduralen Datenbanksprachen wie zum Beispiel Oracle PL/SQL oder Microsoft Transact-SQL verbreitet. Es ist aber auch möglich SQL direkt in andere Sprachen, wie zum Beispiel C, einzubetten.

Das Einbetten der SQL-Anweisungen in andere Sprachen ermöglicht eine sehr einfache Integration der beiden Sprachen. Andererseits werden die SQL-Anweisungen statisch in das Programm eingebettet – die SQL-Anweisungen können zur Laufzeit nicht geändert werden.

Dynamisches SQL (dynamic SQL)

Dynamisches SQL wird in der Applikation als String behandelt. Das Programm kann die Abfrage also zur Laufzeit ändern. Es ist die häufigste Art auf Datenbanken zuzugreifen.

Statisches SQL (static SQL)

Ich verwende den Begriff statisches SQL für SQL-Anweisungen, die sich zur Laufzeit nicht ändern. Unabhängig davon, ob es eingebettetes SQL ist, das zur Laufzeit nicht geändert werden kann, ob es dynamisches SQL ist, das zwar geändert werden könnte, aber nicht geändert wird.

Die Krux mit diesen Definitionen ist, dass dynamisches SQL auch statisches SQL sein kann. Anders ausgedrückt, es gibt verschiedene Ausprägungen von dynamischem SQL:

String sql = "SELECT first_name, last_name"
           + "  FROM employees"
           + " WHERE employee_id = " + employeeId;

ResultSet rs = con.executeQuery(sql);

Ist das Beispiel dynamisches SQL oder nicht? Die SQL-Abfrage wird zur Laufzeit dynamisch erstellt und als String an die Datenbank übergeben, daher handelt es sich um dynamisches SQL. Unter der Annahme, dass sich der Wert der employeeId ändern kann, ist es jedoch kein statisches SQL. Das Beispiel zeigt also dynamisches SQL, und das kann in diesem Fall tatsächlich zu Performanceproblemen führen. Allerdings nicht, weil es dynamisches SQL ist, sondern weil keine Bind Variablen benutzt werden. SQL Bind Variables – z.B. das Fragezeichen ? oder :name – sind Platzhalter für Werte, die erst zur Laufzeit bekannt sind. Das bedeutet, dass das Beispiel durch Verwendung eines Platzhalters auch als statisches SQL verfasst werden kann.

Echtes dynamisches SQL (dynamisches SQL, das nicht auch statisches SQL ist) verändert die Struktur einer SQL-Anweisung zur Laufzeit. Das folgende Beispiel zeigt eine bedingte where-Klausel mittels dynamischen SQL, das kann mit Bind-Variablen nicht erreicht werden:

String where = "";
if (subsidiaryId != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "subsidiary_id = " + subsidiaryId;
}
if (employeeId != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "employee_id = " + employeeId;
}
if (lastName != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "UPPER(last_name) = '"+lastName.toUpperCase()+"'";
}
String SQL = "SELECT employee_id, first_name, last_name "
           + "  FROM employees" 
           +   where;
// execute SQL

Das Programm baut eine SQL-Abfrage auf, die Angestellte nach einer beliebigen Kombination aus drei Kriterien sucht. Obwohl der Programm-Code sehr umständlich ist, kann die Abfrage vorhandene Indizes optimal nutzen. Dennoch ist dieser Ansatz sehr problematisch, da er Tür und Tor für SQL-Injection Angriffe öffnet und den Optimizer stark belastet. Das Einbetten von Werten in SQL-Anweisungen führt dazu, dass der Optimizer einen neuen Ausführungsplan für jeden neuen Wert, bzw. alle Wertekombinationen, erstellen muss. Der Abschnitt „Bind-Parameter“ erklärt das im Detail. Das Performanceproblem bei diesem Code ist nicht das dynamische SQL, es sind die eingebetteten Werte.

Durch die Verwendung von Bind-Parametern, können beide Probleme vermieden werden. Der Programm-Code zum Erzeugen der SQL-Abfrage wird dadurch aber noch umständlicher. ORM Werkzeuge stellen oft einen relativ bequemen Weg zur Verfügung um, dynamisches SQL mit Parametrisierung zu generieren.

Im Zusammenspiel mit Bind-Parametern kann dynamisches SQL für alle Kombinationen optimale Ausführungszeiten erzielen. Die angepasste Auswahl eines Indexes ermöglicht bessere Performance als Konstruktionen, wie sie im Abschnitt „ Schlaue Logik für Bedinget where-Klauseln“ als Anti-Pattern vorgestellt wurden:

SELECT first_name, last_name
  FROM employees
 WHERE (     employee_id = ? OR ? IS NULL)
   AND (   subsidiary_id = ? OR ? IS NULL)
   AND (UPPER(last_name) = ? OR ? IS NULL)

Dennoch gibt es Fälle, bei denen dynamisches SQL langsamer als diese „schlaue Logik“ sein kann. Wenn zum Beispiel eine sehr einfache Abfrage sehr oft ausgeführt wird. Dazu müssen aber noch zwei Begriffe erklärt werden:

Hard Parsing

Hard Parsing bedeutet, dass der Optimizer einen neuen Ausführungsplan aus einem SQL-String erstellt. Dazu müssen alle möglichen Join-Reihenfolgen und Indizes berücksichtigt werden. Hard Parsing ist sehr aufwendig.

Soft Parsing

Soft Parsing bedeutet, dass ein passender Ausführungsplan im Cache gefunden wird. Danach müssen eventuell noch Zugriffsberechtigungen geprüft werden, der Ausführungsplan selbst kann aber wiederverwendet werden. Das ist eine verhältnismäßig einfache Operation.

Um einen passenden Ausführungsplan im Cache zu finden, wird meist ein Hash-Code des SQL-Strings als Schlüssel verwendet. Ein Ausführungsplan kann also nur dann gefunden werden, wenn die exakt gleiche SQL-Anweisung zuvor ausgeführt wurde. Andernfalls findet ein Hard-Parse statt. Durch das Einbetten von Werten in die SQL-Anweisung, wenn also keine Bind-Parameter verwendet werden, sieht jede SQL-Abfrage anders aus, sodass bei neuen Werten wieder ein Hard-Parse angestoßen wird.

Selbst in dem Fall, dass die exakt gleiche Anweisung, mit denselben Werten, nochmal ausgeführt wird, besteht das Risiko, dass der Ausführungsplan bereits aus dem Cache entfernt wurde. Ohne Verwendung von Bind-Parametern sieht jede SQL-Anweisung anders aus, und die Ausführungspläne werden nicht nur mehrfach erzeugt, sondern auch mehrfach im Cache abgespeichert. Dadurch fallen ältere Abfragen schneller aus dem Cache.

Neben Soft- und Hard-Parsing, gibt es auch eine Möglichkeit SQL-Anweisungen ganz ohne Parse auszuführen. Dazu muss die Abfrage offen gehalten werden, wie im folgenden Java Beispiel:

PreparedStatement pSQL = con.prepareStatement("select ...");
for (String last_name:last_names) {
    pSQL.setString(1, last_name.toUpperCase());
    ResultSet rs = pSQL.executeQuery();
    // process result
}
pSQL.close();

Das PreparedStatement wird nur einmal angelegt, kann aber mehrfach ausgeführt werden. Es erfolgt nur ein Parse für die SQL-Abfrage, wenn das PreparedStatement erstellt wird. In der Schleife gibt es keinen Parse mehr.

Wenn man diesen Code umbaut, um dynamisches SQL zu verwenden, muss der prepareStatement Aufruf in die Schleife gelegt werden. Dadurch findet bei jeder Schleifenwiederholung ein Parse statt. Das kann, selbst wenn es nur ein Soft Parse ist, zu erheblichen Verzögerungen durch Netzwerklatenzen führen.

Obwohl dieser Trick, eine SQL-Anweisung ohne neues prepare mehrfach auszuführen, nur wenig verbreitet ist, verwenden die meisten prozeduralen Sprachen wie PL/SQL diesen Trick sehr oft. Eingebettet Abfragen werden oft zusammen mit dem Source-Code übersetzt, sodass während der Ausführung kein Parsing mehr stattfindet. In solchen Fällen kann dynamisches SQL wirklich deutlich langsamer sein.