von Markus Winand.

Schlaue Logik


Eine besondere Funktionalität von SQL-Datenbanken ist die Unterstützung von Ad-hoc-Abfragen – neue Abfragen können jederzeit ausgeführt werden. Das ist nur möglich, weil der Optimizer die Abfrage zur Laufzeit analysiert und den Ausführungsplan erstellt. Das verursacht zwar einen gewissen Mehraufwand, den man mit Bind-Parametern aber gering halten kann.

Der wesentliche Punkt dieser kurzen Wiederholung ist, dass Datenbanken auf die Ausführung von dynamischen Abfragen vorbereitet sind – man sollte dynamische Abfragen also auch nutzen, wenn man sie braucht.

Es gibt aber eine weit verbreitete Praxis, bei der dynamische Abfragen zugunsten statischer vermieden werden – häufig wegen des Mythos, der besagt, dass dynamisches SQL langsam ist. Bei Datenbanken mit zentralem Ausführungsplan-Cache wie DB2, die Oracle Datenbank oder SQL Server verursacht diese Praxis aber mehr Probleme als sie löst.

Als Beispiel kann man sich eine Suchmaske für die Tabelle EMPLOYEES vor­stel­len, bei der man beliebige Bedingungen auf den Spalten SUBSIDIARY_ID, EMPLOYEE_ID und LAST_NAME verwenden kann. Dennoch kann man alle Fälle mit einer einzigen Abfrage abdecken, indem man eine „schlaue Logik“ verwendet.

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   AND ( UPPER(last_name) = :name   OR :name   IS NULL )

Zur besseren Lesbarkeit verwendet diese Abfrage benannte Bind-Parameter. In der where-Klausel sind alle drei Bedingungen angeführt. Wenn eine davon nicht benötigt wird, verwendet man einfach NULL anstatt eines Suchbegriffes. Dadurch wird der jeweilige Teil durch die Oder-Bedingung „ausgeschaltet“.

Die Logik der Abfrage ist einwandfrei. Die Verwendung der NULL entspricht sogar der dreiwertigen SQL-Logik. Dennoch haben wir es hier mit einem der schlimmsten Performance-Anti-Pattern überhaupt zu tun.

Die Abfrage kann nicht für eine bestimmte Bedingung optimiert werden, weil alle Bedingungen deaktiviert werden können. Daher bereitet der Opti­mizer einen Ausführungsplan vor, der alle Eventualitäten berücksichtigt.

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    2 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    2 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:NAME   IS NULL OR UPPER("LAST_NAME")=:NAME) 
       AND (:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID) 
       AND (:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID))

Selbst wenn es für jede Spalte einen Index gibt, wird keiner verwendet.

Es ist nicht etwa so, dass die Datenbank diese „schlaue“ Logik nicht auflö­sen kann. Aufgrund der Bind-Parameter erstellt die Daten­bank aber einen allgemein gültigen Ausführungsplan, der gecached werden kann. Dieser kann später, mit anderen Bind-Werten, wiederverwendet werden. Ohne Bind-Parameter weiß der Optimizer, welche Bedingungen tatsächlich aktiv sind, und kann einen geeigneten Index verwenden.

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE( subsidiary_id    = NULL     OR NULL IS NULL )
   AND( employee_id      = NULL     OR NULL IS NULL )
   AND( UPPER(last_name) = 'WINAND' OR 'WINAND' IS NULL )
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    1 |    2 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    2 |
|*2 |   INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')

Das ist aber keine Lösung für das Problem, sondern nur der Beweis, dass die Datenbank die Logik auflösen kann.

Warnung

Werte direkt in die SQL-Abfrage zu schreiben öffnet SQL-Injection-Schwachstellen und erhöht den Optimizer-Overhead.

Die richtige Lösung für dynamische Abfragen ist dynamisches SQL. Frei nach dem KISS-Prinzip sollte man in jeder Abfrage nur die relevanten Bedingungen anführen – sonst nichts.

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE UPPER(last_name) = :name

Der Bind-Parameter verbessert das Ausführungsplan-Caching und ver­hin­dert SQL-Injection-Attacken.

Tipp

Verwende dynamisches SQL, wenn du dynamische where-Klauseln brauchst.

Benutze dennoch Bind-Parameter, da sonst der Mythos, dass dyna­misches SQL langsam ist, wahr werden könnte.

Konstruktionen wie diese „schlaue“ Logik sind, in der einen oder anderen Form, weiter verbreitet als man glaubt. Daher haben alle Datenbanken, die einen zentralen Ausführungsplan-Cache verwenden, Gegenmaßnahmen ergriffen – oft werden damit neue Probleme eingeführt.

DB2

DB2 verwendet einen zentralen Ausführungsplan-Cache und ist von dem hier beschriebenen Problem voll betroffen.

Mit dem REOPT-Hint kann man den Optimierungsvorgang steuern. Bei der Standardeinstellung NONE wird ein generischer Ausfürhungsplan erstellt. Verwendet man den Hint REOPT(ALWAYS) erstellt der Optimizer für jede Ausführung einen neuen Plan, der die aktuellen Bind-Werte berücksichtigt. Dadurch wird der Ausfürhungsplan-Cache für diese Abfrage de facto abgeschalten.

Die letzte Einstellung ist ONCE. Dabei werden die Werte der Bind-Parameter der ersten Ausführung bei der Erstellung des Ausführungsplanes verwendet. Das führt aber zu einem unvor­her­seh­baren Verhalten: Die Werte der ersten Ausführung beeinflussen alle weiteren Ausführungen. Der Aus­füh­rungs­plan kann sich aber jederzeit ändern, wenn die Datenbank neu gestartet wird oder der zwischen­gespeicherte Plan aus dem Cache entfernt wird.

MySQL

Bei der MySQL Datenbank gibt es dieses Problem nicht, da sie keinen Ausführungsplan-Cache hat. Die Einführung wurde in einem Feature-Request 2009 diskutiert. Es scheint aber so, als wäre der MySQL-Optimizer so einfach, dass sich ein Cache nicht auszahlt.

Oracle

Die Oracle Datenbank verwendet einen zentralen Ausführungsplan-Cache (SQL area) und ist von dem hier beschriebenen Problem voll betroffen.

Mit Version 9i wurde das sogenannte  bind peeking  als Gegen­maß­nah­me eingeführt. Dabei ver­wen­det der Optimizer die Bind-Werte der ersten Ausführung bei der Erstellung des Ausführungsplanes. Das führt aber zu einem unvor­her­seh­baren Verhalten: Die Werte der ersten Ausführung beeinflussen alle weiteren Ausführungen. Der Aus­füh­rungs­plan kann sich aber jederzeit ändern, wenn die Datenbank neu gestartet wird oder der zwischen­gespeicherte Plan aus dem Cache entfernt wird.

Mit Version 11g wurde adaptive cursor sharing eingeführt, um das Problem zu lösen. Dabei kann die Datenbank mehrere Ausführungs­pläne für eine SQL-Anweisung verwalten. Weiters wird die Selektivität der Bind-Paramter geschätzt, und mit dem Plan im Cache gespeichert. Der Schlüssel beim Zugriff auf diesen Cache wird um die Selektivitäts­bereiche erweitert, für die dieser Ausführungs­plan gilt. Wenn die Selek­tivitäts-Schätzungen einer späteren Ausführung in den Bereich eines Ausführungs­planes im Cache fallen, kann dieser wieder­verwendet werden. Andernfalls wird ein neuer Ausführungsplan erstellt, und mit denen im Cache verglichen. Bei einer Übereinstimmung wird stattdessen ein neuer Plan im Cache gespeichert, der auch die Selek­tivität der aktuellen Bind-Parameter abdeckt. Wenn es keine Überein­stimmung gibt, wird der neue Ausführungs­plan zusammen mit den Selek­tivitäten im Cache abgelegt.

PostgreSQL

PostgreSQL speichert Ausführungspläne nur für offene Abfragen. Zum Beispiel, solange das PreparedStatement offengehalten wird. Daher tritt das oben beschriebene Problem nur auf, wenn dasselbe PreparedStatement mehrfach ausgeführt wird. Beachte, dass der PostgreSQL JDBC-Treiber den Cache erst bei der fünften Ausführung aktiviert. Siehe Konkrete Werte und der Query Plan Cache.

SQL Server

SQL Server verwendet das sogenannte  parameter sniffing. Dabei ver­wen­det der Optimizer die Bind-Werte der ersten Ausführung bei der Erstellung des Ausführungsplanes. Das führt aber zu einem unvor­her­seh­baren Verhalten: Die Werte der ersten Ausführung beeinflussen alle weiteren Ausführungen. Der Aus­füh­rungs­plan kann sich aber jederzeit ändern, wenn die Datenbank neu gestartet wird oder der zwischen­gespeicherte Plan aus dem Cache entfernt wird.

Ab SQL Server 2005 gibt es neue Optimizer-Hints, mit denen man mehr Einfluss auf parameter sniffing und recompiling nehmen kann. Mit dem Hint RECOMPILE umgeht man den Ausführungsplan-Cache für eine Anweisung. Mit OPTIMIZE FOR kann man konkrete Werte angeben, für die der Ausführungsplan optimiert werden soll. Mit dem USE PLAN-Hint kann man sogar einen ganzen Ausführungsplan übergeben, der benutzt werden soll.

Die ursprüngliche Implementierung des RECOMPILE-Hints hat aber nicht alle Bind-Werte berücksichtigt. Bei der neuen Implementierung in SQL Server 2008 wurde auch ein neuer Fehler eingeführt. Damit wurde das alles sehr unübersichtlich. Erland Sommarskog hat die relevanten Informationen für alle Versionen zusammengetragen.

Obwohl diese heuristischen Methoden die Probleme mit der „schlauen“ Logik manchmal mildern können, sind diese Mechanismen eigentlich dafür gedacht, die Probleme von Bind-Parametern im Zusammenhang mit Histo­grammen oder LIKE-Filtern zu beheben.

Die zuverlässigste Methode, einen optimalen Ausführungsplan zu erhalten, ist, keine unnötigen Bedingungen in die SQL-Anweisung aufzunehmen.

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

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz