2012-04-18Schlaue Logik

Eine besondere Funktionalität von SQL-Datebanken 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 kann man mit Bind-Parametern aber gering halten.

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 einzelne nicht benutzt 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.

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.

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.

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.