DB2-SQL - SK Consulting Services GmbH

SQL Performance - Tips
Do's & Don'ts
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 1 -
Inhaltsverzeichnis
I.
Richtlinien bei der Verwendung von SQL
1.1. In Programmen "verbotene" SQL- Anweisungen
1.2
SQL - Anweisungen, die die Indexnutzung verhindern
1.4. Ungünstig formuliert e SQL - Anweisungen
1.5
Ungünstig formulierte JOIN - Anweisungen
2.
Benutzen des EXPLAIN-Statements
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 2 -
I.
Richtlinien bei der Verwendung von SQL
1.1. In Programmen "verbotene" SQL- Anweisungen
•
alle DDL-Anweisungen inkl. ALTER
•
alle Anweisungen, die Einträge im DB2-Katalog verursachen:
!
!
!
GRANT und REVOKE
PREPARE, EXECUTE
Lesen im Katalog während des Programmablaufs ( SELECT ... FROM SYSIBM. ??? )
Was ist mit dem Aufbau temporärer Tabellen in einem
Programm ????
(CREATE <table> am Programmanfang DROP am Ende des Programms)
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 3 -
I.
Richtlinien bei der Verwendung von SQL
1.2
SQL - Anweisungen, die die Indexnutzung verhindern
Bestimmte SQL-Formulierungen lassen keine Indexnutzung zu
und sollten folglich neu formuliert werden
1.
Selektion mit OR ohne "=" als Operator
z.B. FELDA > 60 OR FELDA < 50
2.
Verwendung von "NOT" in Prädikaten oder Ausdrücken
NOT IN, NOT BETWEEN, NOT LIKE, <>
3.
LIKE-Prädikate auf Felder mit FIELDPROC-Prozedur
4.
Vergleiche mit Hostvariablen oder anderen Feldern mit
!
ungleichem Datentyp
!
ungleicher Länge oder Präzision
5.
Vergleiche mit ungleich dimensionierten Literalen
z.B. WHERE ABTEILUNG = "A00 " anstatt
WHERE ABTEILUNG = "A00"
6.
Vergleiche mit aritmethischen Ausdrücken im Prädikat
z. B. ... WHERE PREIS > EK-PREIS * 1.3
Wichtig ist in diesen Zusammenhang immer, daß ein
möglichst selektiver Index
auf die, oder eines der Felder gelegt ist, die im SQL-Prädikat zu
Vergleichen herangezogen werden("composite indexes" ??).
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 4 -
I.
Richtlinien bei der Verwendung von SQL
1.3
SQL - Anweisungen ohne Indexnutzung
SQL - Anweisungen, die eine Indexbenutzung zulassen, aber
aufgrund der EXPLAIN-Auswertung erkannt wurde, daß kein
Index genutzt wird
Verwendung von Indizes ist für DB2 V5 möglich, wenn nach
einer Spalte selektiert wird mit:
1.
Vergleichsoperationen (ohne OR (!))
A = 540; A > 450; A < 650;
2.
BETWEEN,
z.B. FELDA BETWEEN 100 AND 300
3.
IN,
4.
LIKE mit definiertem Beginn des Suchkriteriums
z.B. FELDA LIKE "D%" oder FELDA LIKE "__D"
5.
OR-Verknüpfungen mit "=" als Operator
z.B. FELDA = 123 OR FELDA = 456 OR FELDB =234
6.
Angabe von "NOT" mit < oder > Operator
z. B. NOT FELDA > 123 oder FELDA ^> 123
z.B. FELDA IN (123, 450, 564)
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 5 -
I.
Richtlinien bei der Verwendung von SQL
1.4. Ungünstig formuliert e SQL - Anweisungen
Zu dieser Kategorie gehören "queries", die anders formuliert
eine höhere Performance garantieren könnten. z.B.:
Auflösung von OR Verknüpfungen durch UNION
SELECT *
WHERE
OR
Þ
wird aufgelöst in
SELECT *
WHERE
UNION
SELECT *
WHERE
Þ
FROM KONTO
KTO_NR = 123
KTO_NR = 456
FROM KONTO
KTO_NR = 123
FROM KONTO
KTO_NR = 456
Auflösung von OR Verknüpfungen durch IN
SELECT *
WHERE
FROM KONTO
KTO_NR IN ( 123, 456 )
Die Ergebnisse hieraus müssen in jedem Fall über
EXPLAIN verifiziert werden.
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 6 -
I.
Richtlinien bei der Verwendung von SQL
Auch "queries" mit Sub-Select's sind in diese Überlegungen
mit einzubeziehen
Þ
Auflösung von "subqueries" / "correlated queries" in
JOIN - Formulierungen
z.B.
SELECT
FROM
WHERE
NAME, VORNAME , TEL_NR
MITARBEITER
PERSNR IN
( SELECT
ABT_LTNR
FROM
ABTEILUNG )
Zur Ermittlung aller ABTLTNR kann KEIN Index benutzt werden!!!
Bei der folgenden Formulierung kann jedoch ein Index gewählt werden:
SELECT
FROM
WHERE
copyright S.K. Consulting GmbH, München
NAME, VORNAME, TEL_NR
MITARBEITER ,
ABTEILUNG
PERSNR = ABT_LTNR
DB2_SQL_PERF - 7 -
I.
1.5
Richtlinien bei der Verwendung von SQL
Ungünstig formulierte JOIN - Anweisungen
SQL-Anweisungen, die ungünstige JOIN-Formulierungen
enthalten können die Perfromance ebenfalls nachhaltig negativ
beeinflussen...
Die Angabe "innerer" und "äußerer" Tabellen in der richtigen
Reihenfolge kann bei einer JOIN-Operation erhebliche
Zeiteinsparungen zur Folge haben, wenn die Tabelle, auf
die das am stärksten einschränkende Selektionskriterium
zeigt zuerst bearbeitet werden kann.
Beispiel:
SELECT
FROM
WHERE
AND
F.NAME, T.NAME, VORNAME, SEMINAR
TEILNEHMER T, FIRMA F
T.FNR
=
F.FNR
F.PLZ
=
"8000"
Die richtige Reihenfolge wäre:
SELECT
FROM
WHERE
AND
F.NAME, T.NAME, VORNAME, SEMINAR
FIRMA F, TEILNEHMER T
F.FNR
=
T.FNR
F.PLZ
=
"8000"
Das zugrundeliegende Modell:
meldet
FIRMA
(FNR)
TEILNEHMER
(FNR,LFD_NR)
wird gemeldet
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 8 -
1.
Richtlinien bei der Verwendung von SQL
Dasselbe gilt für Spalten, die beim JOIN zum Vergleich herangezogen werden, in der einen Tabelle als "foreign key" in der
anderen als "primary key" also eindeutig vorliegen.
Vergleiche sollten immer auf die eindeutigen Wertebereiche laufen (!)
Beispiel:
SELECT
FROM
F.NAME, T.NAME, VORNAME, SEMINAR
FIRMA
F,
TEILNEHMER T
WHERE
F.FNR
=
T.FNR
AND T.FNR
=
4321
Die Formulierung ist ungünstig, da T.FNR "foreign key" in TEILNEHMER
ist und daher Werte mehrfach vorkommen können: "Mehrere
Teilnehmer können von derselben Firma sein."
Besser ist folgende Formulierung:
SELECT
FROM
WHERE
AND
F.NAME, T.NAME, VORNAME, SEMINAR
FIRMA
F,
TEILNEHMER T
F.FNR
=
T.FNR
F.FNR
=
4321
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 9 -
2. Benutzen des EXPLAIN-Statements
EXPLAIN stellt Optimizer-Informationen in die PLAN_TABLE und
gibt Auskunft über
Þ
Index-Benutzung
Þ
Zugriffsmodus
Þ
SORT-Alghorithmen
Þ
Erzeugen von temporären Tabellen
EXPLAIN hilft Performance-Engpässe
zu erkennen und legt entsprechende
Informationen in der Tabelle PLAN_TABLE ab
copyright S.K. Consulting GmbH, München
DB2_SQL_PERF - 10 -