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.
Wichtig
Bind Parameter nicht zu benutzen ist eine missbräuchliche Verwendung von dynamischem SQL.
Bind Parameter sind sowohl für Sicherheit als auch für Performance wichtig.
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.
Hinweis in eigener Sache
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
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.
- Java
Das folgende Beispiel zeigt die Verwendung der Hibernate Criteria Klassen:
Criteria criteria = session.createCriteria(Employees.class); if (subsidiaryId != null) { criteria.add(Restrictions.eq("subsidiaryId", subsidiaryId)); } if (employeeId != null) { criteria.add(Restrictions.eq("employeeId", employeeId)); } if (lastName != null) { criteria.add( Restrictions.eq("lastName", lastName).ignoreCase() ); }
Die folgende SQL-Abfrage wird für eine Oracle Datenbank generiert, wenn man nur nach
LAST_NAME
sucht:select this_.subsidiary_id as subsidiary1_0_0_, [... other columns ...] from employees this_ where lower(this_.last_name)=?
Wichtig ist nicht nur, dass Bind-Parameter verwendet werden, sonder auch dass die Funktion
LOWER
eingesetzt wird, um die Groß- und Kleinschreibung zu ignorieren (ignoreCase()). Hibernate verwendet die FunktionLOWER
auch bei ilike suchen. Dieser Umstand muss bei der funktionsbasierten Indizierung berücksichtigt werden.Die Java Persistence API (JPA) stelle eine ähnliche Funktion zur Verfügung, kann die Groß- und Kleinschreibung aber nicht ignorieren.
List<Predicate> predicates = new ArrayList<Predicate>(); if (lastName != null) { predicates.add(queryBuilder.equal( queryBuilder.upper(r.get(Employees_.lastName)) , lastName.toUpperCase()) ); } if (employeeId != null) { predicates.add(queryBuilder.equal( r.get(Employees_.employeeId) , employeeId) ); } if (subsidiaryId != null) { predicates.add(queryBuilder.equal( r.get(Employees_.subsidiaryId) , subsidiaryId) ); } query.where(predicates.toArray(new Predicate[0]));
Der JPA-Code ist nicht so einfach wie die Hibernate Variante, bietet dafür aber statische Typisierung. Da JAP selbst die Groß- und Kleinschreibung nicht ignorieren kann, muss man die Schreibweise selbst vereinheitlichen. Das hat den Vorteil, dass man steuern kann, ob
LOWER
oderUPPER
verwendet wird. Die Hibernate API bietet diese Möglichkeit ebenfalls an, dieignoreCase()
Variante wird aber dennoch oft benutzt, ohne die Nebenwirkungen zu kennen.- Perl
Das folgende Beispiel zeigt die Verwendung des Perl Paketes DBIx::Class.
my @search = (); if (defined $employee_id) { push @search, {employee_id => $employee_id}; } if (defined $subsidiary_id) { push @search, {subsidiary_id => $subsidiary_id}; } if (defined $last_name) { push @search, {'UPPER(last_name)' => uc($last_name)}; } my @employees = $schema->resultset('Employees') ->search({-and => \@search});
Für eine Oracle Datenbank wird bei einer Suche nach
LAST_NAME
die folgende Abfrage generiert:SELECT me.employee_id, me.subsidiary_id, me.last_name, me.first_name, me.date_of_birth FROM employees me WHERE ( UPPER(last_name) = :p1 )
- PHP
Das folgende Beispiel zeigt die Verwendung von PHP Doctrine:
$filter = $qb->expr()->andx(); if (isset($employee_id)) { $filter->add( $qb->expr()->eq('e.employee_id', ':employee_id')); $qb->setParameter('employee_id', $employee_id); } if (isset($subsidiary_id)) { $filter->add( $qb->expr()->eq('e.subsidiary_id', ':subsidiary_id')); $qb->setParameter('subsidiary_id', $subsidiary_id); } if (isset($last_name)) { $filter->add($qb->expr()->eq( $qb->expr()->upper('e.last_name'), ':last_name')); $qb->setParameter('last_name', strtoupper($last_name)); } if ($filter->count() > 0) { $qb->where($filter); }
Hierbei muss man die Bind-Parameter explizit verwenden.
Für eine MySQL Datenbank wird bei einer suche nach
LAST_NAME
die folgende Abfrage generiert:SELECT e0_.employee_id AS employee_id0, [... other columns ...] FROM employees e0_ WHERE UPPER(e0_.last_name) = ?
Tipp
Die lauffähigen Beispiele können heruntergeladen werden.
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.
Tipp
Artikel: „Planning for Re-Use“ [engl.] über das Cachen von Ausführungsplänen