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.

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.

Über unser Buch „SQL Performance Explained“
Für alle Anwendungsentwickler […] sollte der schmale Band […] eine Pflichtlektüre sein — ADMIN-Magazin

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 Funktion LOWER 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 oder UPPER verwendet wird. Die Hibernate API bietet diese Möglichkeit ebenfalls an, die ignoreCase() 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.

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

?Recent questions at
Ask.Use-The-Index-Luke.com

0
votes
2
answers
731
views

different execution plans after failing over from primary to standby server

2 days ago Markus Winand ♦♦ 741
oracle index update
1
vote
1
answer
68
views

Generate test data for a given case

Sep 14 at 18:11 Markus Winand ♦♦ 741
testcase postgres
0
votes
1
answer
217
views

Database design suggestions for a data scraping/warehouse application?

Aug 27 at 09:29 Markus Winand ♦♦ 741
mysql optimization database