Checkliste für DB2-Programmierung
SQL - Die Abfragesprache der relationalen Datenbank DB2 ist sehr mächtig. Doch nicht alles, was
sich damit formulieren lässt, ist auch sinnvoll. Und nicht alles, was möglich und sinnvoll ist,
ist auch schnell. Mit der folgenden Checkliste kann man seine Programmierung auf grundsätzliche
Performancefresser und weitere "Unschärfen" hin überprüfen.
Halten Sie Ihr SQL-Statement so einfach wie möglich !
Geben Sie in der SELECT-Klausel nur Spalten an, die Sie auch wirklich benötigen.
Jede Spalte bedeutet für DB2 Verwaltungsaufwand (z.B. für das Ermitteln der Speicheradresse und deren Übergabe an die DB2-Schnittstelle).
Ist eine Spalte in der SELECT-Klausel angegeben, ohne dass sie tatsächlich benötigt wird, so wird dieser Aufwand umsonst betrieben. Auch ist es möglich, dass DB2 einen anderen Zugriffspfad wählt, um die zusätzlichen (unnötigen) Spalten zur Verfügung stellen zu können. Eine Überprüfung und gegebenenfalls Anpassung der Spaltenliste kann die Performance verbessern, ohne dass ein besonderer Tuning-Aufwand betrieben werden muss. Dies gilt in besonderem Masse dann, wenn die Ergebnismenge auch noch sortiert werden muss.
Achten Sie besonders auf:
- eine Spalte wird eingelesen, jedoch nicht weiterverarbeitet
- durch eine (spätere) Programmänderung wird eine Spalte nicht mehr benötigt, jedoch wurde der SELECT nicht angepasst.
- der Wert ist bereits bekannt, er wird z.B. in der WHERE-Bedingung vorgegeben.
Geben Sie in der ORDER BY-Klausel nur Spalten an, nach denen wirklich sortiert werden muss
Grundsätzlich muss nicht nach Spalten sortiert werden, die nur einen einzigen Wert annehmen können ( z.B. weil sie in der WHERE - Klausel mit ' = ' abgefragt werden, oder weil sie in der SELECT-Klausel als Konstante angegeben wurden )
Prüfen Sie auch, ob Sie überhaupt eine ORDER BY - Klausel benötigen. In einigen Fällen ist die Reihenfolge, in der die Daten verarbeitet werden irrelevant.
Halten Sie Ihr SQL-Statement lesbar !
Verwenden Sie kurze Korrelationsnamen
Ein
SELECT A.spalte1 , B.spalte2
FROM owner1.tabelle1 A, owner2.tabelle2 B
WHERE A.key1 = B.key2 ;
Ist immer noch leichter zu lesen als ein
SELECT owner1.tabelle1.spalte1, owner2.tabelle2.spalte2
FROM owner1.tabelle1 , owner2.tabelle2
WHERE owner1.tabelle1.key1 = owner2.tabelle2.key2 ;
Verwenden Sie bei Joins für jede Spalte den Korrelationsnamen
Geben Sie bei einem Join bei allen Spalten ( SELECT-Klausel , WHERE-Bedingung ) den Korrelationsnamen mit an, selbst wenn die Spalte nur in einer der beteiligten Tabelle vorkommt. Sie erleichtern damit den anderen Mitarbeitern das Lesen des Statements.
Prüfen Sie die WHERE-Bedingung !
( und wenn sie überzeugt sind, dass sie optimal ist, dann prüfen Sie sie noch einmal )
ist alles stage 1 - fähig ?
Prüfen Sie jede Bedingung, ob sie stage-1-fähig ist.
Formulieren Sie -sofern möglich- jede andere Bedingung um, bis ein Index verwendet werden könnte. Dies gilt auch, wenn über die entsprechende Spalte(n) kein Index definiert ist, da stage-1-Abfragen grundsätzlich performanter sind.
Beispiel:
WHERE :host BETWEEN spalte1 AND spalte2
ist nicht indexfähig, kann aber umformuliert werden zu:
WHERE spalte1 <= :host
AND spalte2 >= :host
Noch ein Beispiel:
WHERE SUBSTR (spalte,3,5) = 'ABCDE'
ist nicht indexfähig, kann aber umformuliert werden zu:
Where spalte LIKE '_ _ ABCDE%'
Seien Sie ruhig kreativ. Es ist viel mehr möglich, als Sie denken.
Verzichten Sie auf skalare Funktionen in der WHERE-Bedingung
z.B.:
Hat eine DB2-Spalte den Typ DATE, so ist in der WHERE-Bedingung die Abfrage
WHERE Spalte = :Host
effektiver als die Abfrage
WHERE Spalte = DATE (:Host).
DB2 behandelt die Hostvariable automatisch als Datum, die skalare Funktion DATE() verlangsamt dagegen die Evaluierung der Bedingung und verhindert gegebenenfalls die Verwendung eines Index.
Blättern Sie richtig, bzw. formulieren Sie die Wiederaufsetz-Bedingung korrekt
Sh. weiter unten
Bringen Sie die ANDs nach aussen
Werden in einer WHERE-Klausel mehrere kombinierte Bedingungen mittels OR verknüpft, so sollte geprüft werden, ob ein Teil der kombinierten Bedingungen immer gleich ist. Ist dies der Fall, so sollte dieser Teil der Bedingungen nach aussen gezogen werden. So sollte beispielsweise die Bedingung
WHERE ( Spalte1 = :host_var1
AND Spalte2 = :host_var2
AND Spalte3 = :host_var3 )
OR ( Spalte1 = :host_var1
AND Spalte2 = :host_var2
AND Spalte4 = :host_var4 )
umformuliert werden zu
WHERE Spalte1 = :host_var1
AND Spalte2 = :host_var2
AND ( Spalte3 = :host_var3
OR Spalte4 = :host_var4 )
Im zweiten Fall kann DB2 einen Index über ( Spalte1 , Spalte2 ) effektiver verwenden als bei der ersten Variante.
Entfernen Sie alle unnötigen oder redundanten Bedingungen
Redundante Bedingungen sollten Sie nur verwenden, um den Zugriffspfad gezielt zu manipulieren. Ob das gewünschte Ergebnis erreicht wurde, muss dann natürlich mit EXPLAIN überprüft werden. Derartige Kunstgriffe sollten im Programm gut dokumentiert werden !
Verwenden Sie Spalten nicht zusammen mit arithmetischen Operationen
Formulieren Sie ein:
WHERE column + 100 > :host ;
um in ein performanteres
WHERE column > :host - 100 ;
Kodieren Sie die restriktivste Bedingung zuerst
Ist eine Bedingung nicht erfüllt, dann wird der Satz nicht weiter geprüft. Je früher daher ein Satz ausgefiltert werden kann, desto performanter ist die Query. Die grundsätzliche Reihenfolge im DB2 ( zuerst Indexcolumns, dann stage-1, dann stage-2 ) kann dabei nicht umgangen werden, aber mehrere gleichartige Bedingungen werden in der Reihenfolge der Codierung geprüft.
Subqueries
Kodieren Sie die Subquery mit dem höchsten Filterfaktor zuerst.
Hat Ihre Query mehrere Subqueries, so hat die Reihenfolge der Kodierung innerhalb der WHERE-Bedingung Einfluss auf die Performance.
Beispiel: Eine Tabelle beinhaltet 10.000 Sätze, davon erfüllen 9.000 Sätze die subquery1 und 1.000 Sätze die subquery2, dann gilt:
WHERE EXISTS ( subquery1 )
AND EXISTS ( subquery2 );
die erste Bedingung muss für jeden Satz geprüft werden ( 10.000 mal ), die zweite aufgrund der AND Verknüpfung nur noch für diejenigen Sätze, die die erste Bedingung erfüllen ( also 9.000 mal ). Insgesamt sind das 19.000 Prüfungen. Würde dagegen kodiert:
WHERE EXISTS ( subquery2 )
AND EXISTS ( subquery1 );
käme man auf nur 10.000 + 1.000 , also auf 11.000 Prüfungen.
Diese Regel ist natürlich nur dann sinnvoll anzuwenden, wenn beide Subqueries ungefähr gleich aufwändig zu evaluieren sind. Ist der Aufwand für DB2 unterschiedlich hoch ( weil z.B. subquery1 ein normaler SELECT ist und subquery2 ein JOIN über 18 Tabellen ) muss dies entsprechend berücksichtigt werden.
Unabhängig von der Reihenfolge der Kodierung werden jedoch non-correlated subqueries immer vor correlated subqueries ausgeführt.
Wählen Sie den optimalen Subquery-Typ
Hier gibt es keine absoluten Regeln. Als Anhaltspunkt kann dienen:
Eine uncorrelated subquery wird einmalig ausgeführt und das Ergebnis dann in die WHERE-Bedingung des äusseren Selects integriert.
Eine correlated subquery wird für jeden Satz des äusseren Selects einmal ausgeführt. Eine uncorrelated subquery dürfte die bessere Wahl sein, wenn:
- es keinen effizienten Index für die inner query gibt.
- die inner query nur wenige Ergebniszeilen liefert
- viele Sätze der Outer query geprüft werden müssen.
In den übrigen Fällen wird eine correlated query performanter sein.
Join oder Subquery
Wenn Sie die Wahl haben, ein SELECT-Statement entweder als Join oder als Subquery zu formulieren, dann verwenden Sie den Join. Dies ist in der Regel der performancegünstigere Weg. ( DB2 schreibt teilweise die Queries selbständig um ).
Prüfen Sie die Zugriffspfade
Überprüfen Sie die Reports von Explain ( oder einem Analyse-Tool ), ob der von Ihnen erwartete Zugriffspfad auch verwendet wird. Dies gilt insbesonders, wenn Sie mit Performance-Optionen arbeiten ( z.B. OPTIMIZE FOR nn ROWS ). Denken Sie auch daran, dass die Zugriffspfade in Produktions- und Testsystemen unterschiedlich sein können.
UNION
Ein UNION ALL ist einem UNION vorzuziehen. UNION ( ohne ALL ) eliminiert doppelte Zeilen. Er wirkt demnach wie ein SELECT DISTINCT ...
Dazu muss DB2 aber die Ergebnistabelle sortieren. Kann aufgrund der Daten und des SELECTs sichergestellt werden, dass doppelte Zeilen nicht auftreten können, dann erspart die Angabe von UNION ALL möglicherweise einen SORT.
Setzen Sie regelmässig Commits
Jedes Update-Programm sollte regelmässig COMMITs ( bzw. Checkpoints ) absetzen. Die Frequenz hängt dabei primär von der Anzahl der Updates ab. Jedoch sollte keine Unit of Work (UOW) länger als fünf Minuten uncommitted sein. Die Frequenz ist optimalerweise von aussen steuerbar.
Programme, die lediglich lesende Zugriffe ausführen brauchen keine COMMITs zu setzen.
Wiederaufsetzen
Das Ausformulieren der optimalen WHERE-Bedingung eines Cursors für das Wiederaufsetzen (z.B. nach einem Fehler, oder um bei Online-Anwendungen das Blättern zu ermöglichen ) ist nicht gerade einfach. Dies gilt insbesonders, wenn sich der Schlüssel aus mehreren Spalten zusammensetzt. Es bieten sich folgende Varianten an ( selbstverständlich muss der Inhalt der zuletzt verarbeiteten oder der ersten weiterzuverarbeitenden Zeile bekannt sein -- in den unteren Beispielen steht in den Hostvariablen die letzte verarbeitete Zeile ):
Variante 1:
SELECT ...
WHERE
Spalte1 > :Host1
OR ( Spalte1 = :Host1 AND Spalte2 > :Host2 )
OR ( Spalte1 = :Host1 AND Spalte2 = :Host2 AND Spalte3 > :Host3 )
ORDER BY Spalte1 , Spalte2 , Spalte3;
Aufgrund der OR - Verknüpfung kann DB2 jedoch einen Index über die Spalten 1 - 3 nicht effektiv nutzen. Diese Variante bietet sich demnach nur an, wenn durch weitere Where-Bedingungen die Grösse der Ergebnis-Tabelle sehr gering gehalten werden kann.
Variante 2:
SELECT ...
WHERE
Spalte1 >= :Host1
AND ( Spalte1 > :Host1
OR ( Spalte1 = :Host1 AND Spalte2 > :Host2 )
OR ( Spalte1 = :Host1 AND Spalte2 = :Host2 AND Spalte3 > :Host3 ) )
ORDER BY Spalte1 , Spalte2 , Spalte3;
Hier wird die zusätzliche Bedingung Spalte1 >= :Host1 eingefügt. Sie ist zwar redundant, ist jedoch mit den anderen Bedingungen mit AND verbunden. Dies erlaubt DB2 eine effektivere Nutzung eines Index über Spalte1. Jedoch kann auch hier die Performance aufgrund der Oder-Verknüpfungen leiden. Jedoch ist diese Variante der Variante 1 immer vorzuziehen.
Variante 3:
SELECT ...
WHERE
Spalte1 >= :Host1
AND NOT ( Spalte1 = :Host1 AND Spalte2 < :Host2 )
AND NOT ( Spalte1 = :Host1 AND Spalte2 = :Host2 AND Spalte3 <= :Host3)
ORDER BY Spalte1 , Spalte2 , Spalte3;
Diese als Exclusion-Methode bekannte Variante sagt nicht "nimm das, was grösser ist", sondern "nimm das, was nicht kleiner ist". Durch diese Negierung der Bedingung kann völlig auf die Verwendung von OR's verzichtet werden, was zu einer effektiven Verwendung eines Index führen kann.
Für ein bestehendes Problem sollten immer die Varianten 2 und 3 ausgetestet werden, da eine generelle Aussage "A ist besser als B" nicht möglich ist, sondern mehrere Faktoren eine Rolle spielen.
Variante 4:
SELECT ...
WHERE Spalte1 > :Host1
UNION ALL
SELECT ...
WHERE Spalte1 = :Host1
AND Spalte2 > :Host2
UNION ALL
SELECT ...
WHERE Spalte1 = :Host1
AND Spalte2 = :Host2
AND Spalte3 > :Host3;
ORDER BY Spalte1 , Spalte2 , Spalte3;
Diese Variante führt im schlimmsten Fall dazu, dass die Tabelle dreimal komplett durchgelesen wird, nämlich dann, wenn kein geeigneter Index zur Verfügung steht. Im besten Fall wird DB2 eine Verarbeitungslogik ähnlich Variante 1 wählen. Der Vorteil "Gute Indexnutzung, weil lauter AND-Bedingungen" wird durch den Nachteil "Mehrfachselect aufgrund Union All" mehr als aufgehoben. Von dieser Variante wird ausdrücklich abgeraten.