View Full Version : Schlechte SQL Performance bei Join?
Hallo zusammen,
folgende Konstellation:
- Tabelle A: Datensätze mit eindeutiger ID und einem langem Textfeld
- Tabelle B: Datei für die Indizierung von Tabelle A. Enthält zu jedem Wort des Textefeldes von Tabelle A eine Zeile mit Bezug auf die eindeutige ID
Mit folgendem SQL wird nun auf die Tabelle zugriffen:
with x as (
select IXFNAM, IXTEXT, IXRECL from matindex
where
IXFNAM = 'DBTEXT'
and IXTEXT = 'KUNDE')
SELECT
'DBTEXT', PDPRN2, PDPRN3, PDKDK, PDMA, PDBTDT,
rtrim(PDTXT1)||rtrim(PDTXT2)||rtrim(PDTXT3)||
rtrim(PDTXT4)||rtrim(PDTXT5) as Txt
FROM DBTEXT right join x on
IXRECL = PDLFDN order by pdbtdt
Die MATINDEX Datei ist hier die Indizierungsdatei (B) und die DBTEXT die Tabelle (A).
Ich habe schon verschiedene Variationen durchprobiert mit EXISTS, JOIN und nun diese common table expression.
Die aktuelle Variante ist von der Performance her nun die Beste, aber allgemein bin ich mit der Leistung nicht zufrieden.
Als Grund für die mangelhafte Performance habe ich das ORDER BY auf ein Datumsfeld ausgemacht. Wenn das SQL z.B. 9000 Sätze ausgibt braucht es auf einer 4000 CPW Power6 Maschine unter V5R4M0 etwa 20 Sekunden. Ohne ORDER BY sind es nur ca. 2 Sekunden.
Was kann ich tun um diese Abfrage zu verbessern? Laut Visual Explain habe ich schon bei beiden Tabellen Zugriff über einen optimalen Index. Warum dauert das dennoch so lange?
Die selben Abfragen (ohne CTE, aber mit Subselect) unter MySQL mit den gleichen Daten brauchen nur einen Bruchteil der Zeit.
Was mache ich hier falsch?
Gruß
Matthias Schatte
Was mich als erstes interessieren würde ist, welche Query Engine führt die Abfrage aus?
So wie Du es beschreibst tippe ich stark auf die CQE. Ist es die CQE versuche die Ursache zu finden (und zu bereinigen), warum die CQE verwendet wurde.
Hast Du eigentlich versucht anstatt eines Right Join die Abfrage zu drehen, d.h. die CTE voranstellen und dann über einen Left Join zu verknüpfen.
Weiterhin sollte zumindest ein Zugriffsweg über IXFNAM, IXTEXT, IXRECL vorhanden sein. Ebenso sollte für die 2. Datei ein Index über PDLFDN, PDBTDT. Versuche ggf. Zugriffswege (SQL Indices) mit Schlüssel-Feldern in der zuvorgenannten Reihenfolge anzulegen.
Birgitta
RIGHT JOIN?
Vielleicht ist es ein Problem mit NULL-Werten in der Spalte mit dem Datum, nach dem sortiert wird.
Wieso eigentlich Right Join ?
Auf Grund der Abfrage ist es doch eher ein inner join, da die Where-Klausel der Tabelle 1 eine Einschränkung unabhängig von der Beziehung zu Tabelle 2 ist.
Du solltest also
a) die Reihenfolge der Datenbeziehungen prüfen
b) entsprechende Indizes über die Where-/Join-/Order-Felder haben
Und was den Performancevergleich angeht:
Wieviele parallele Job's laufen auf dem MySQL-Server im Vergleich zur AS/400 ?
Wieviele Datensätze sind in der MySQL-DB und der AS/400-DB ?
Hallo,
die Abfrage wird von der SQE durchgeführt laut Visual Explain und eure Indexvorschläge habe ich getestet.
Das Ergebnis, auch mit den verschiedenen Join-Varianten, ist nun folgendes:
Bei dem RIGHT JOIN (oder LEFT mit getauschten Positionen) ist die Performance vergleichbar mit einem INNER JOIN. Wird jedoch in der CTE auf die Tabelle MATINDEX ein WHERE auf einen Begriff durchgeführt, der nicht vorhanden ist, so dauert die Abfrage mit dem INNER JOIN beim ersten Aufruf extrem lange (29 Sekunden) und bei den folgenden Aufrufen immer 2,5 Sekunden.
Der RIGHT und LEFT JOIN benötigen für die Abfrage auf einen Begriff, der nicht gefunden wird, immer 0,0 bis 0,3 Sekunden. Deshalb habe ich auch den RIGHT JOIN verwendet.
Was kann ich hier noch tun? Die Abfrage soll ja in beiden Fällen schnell sein. Also wenn Sätze in der MATINDEX gefunden werden, als auch wenn keine gefunden werden.
Zu MySQL:
Ich habe sowohl auf der iSeries als auch auf der MySQL Maschine (Linux) ausserhalb des Tagesbetriebes getestet und war somit alleine auf der Maschine. Die Daten sind exakt die selben auf beiden Maschinen.
Gruß
Matthias Schatte
... ich würde erst mal Software defect bei IBM reklamieren, vielleicht ist der Bug ja aus einem neueren Release Stand draußen. Ansonsten könnte man noch versuchen die Ausführung durch die alte Query Engine zu erzwingen (DDS LF mit Omit anlegen), die hat den Bug wahrscheinlich noch nicht gehabt.
Wenn mehr Hardware dem nicht auf die Sprünge hilft, musst du das halt auf MySql laufen lassen...
D*B
Hallo,
die Abfrage wird von der SQE durchgeführt laut Visual Explain und eure Indexvorschläge habe ich getestet.
Das Ergebnis, auch mit den verschiedenen Join-Varianten, ist nun folgendes:
Bei dem RIGHT JOIN (oder LEFT mit getauschten Positionen) ist die Performance vergleichbar mit einem INNER JOIN. Wird jedoch in der CTE auf die Tabelle MATINDEX ein WHERE auf einen Begriff durchgeführt, der nicht vorhanden ist, so dauert die Abfrage mit dem INNER JOIN beim ersten Aufruf extrem lange (29 Sekunden) und bei den folgenden Aufrufen immer 2,5 Sekunden.
Der RIGHT und LEFT JOIN benötigen für die Abfrage auf einen Begriff, der nicht gefunden wird, immer 0,0 bis 0,3 Sekunden. Deshalb habe ich auch den RIGHT JOIN verwendet.
Was kann ich hier noch tun? Die Abfrage soll ja in beiden Fällen schnell sein. Also wenn Sätze in der MATINDEX gefunden werden, als auch wenn keine gefunden werden.
Zu MySQL:
Ich habe sowohl auf der iSeries als auch auf der MySQL Maschine (Linux) ausserhalb des Tagesbetriebes getestet und war somit alleine auf der Maschine. Die Daten sind exakt die selben auf beiden Maschinen.
Gruß
Matthias Schatte
andreaspr@aon.at
07-08-10, 05:47
die Abfrage wird von der SQE durchgeführt laut Visual Explain und eure Indexvorschläge habe ich getestet.
Im Visual Explain wird das SQL auf mehrere Prozesse aufgelöst. Da kannst du dir auch gleich ansehen, welche(r) Prozess(e) so lange dauert.
KingofKning
07-08-10, 09:24
... ich würde erst mal Software defect bei IBM reklamieren, vielleicht ist der Bug ja aus einem neueren Release Stand draußen. Ansonsten könnte man noch versuchen die Ausführung durch die alte Query Engine zu erzwingen (DDS LF mit Omit anlegen), die hat den Bug wahrscheinlich noch nicht gehabt.
Wenn mehr Hardware dem nicht auf die Sprünge hilft, musst du das halt auf MySql laufen lassen...
D*B
Und das bei einer Maschine die 88 als Datenbankmaschine angetreten ist.
Die wird dann von einer "freien" Software wie mysql in die Tasche gesteckt.
Schon traurig
GG
holgerscherer
07-08-10, 13:58
Und das bei einer Maschine die 88 als Datenbankmaschine angetreten ist.
Moment - Dieters Sätze sind manchmal mit einer gewissen Vorsicht zu geniessen, ein wenig Ironie und Bissigkeit kann schon dabei sein. Da ich gerade neben ihm sitze, gehe ich davon aus, dass das nicht seine ultimative Meinung zur Lösung des Problems ist. Aber wie geschrieben, nun brauchts Detailinfos.
Nachtrag: ich werde so aus dem zweiten Blick nicht schlau, was Du genau aus welchen Dateien holst. Könntest Du die beiden Dateien genau beschreiben, was wo liegt, und *wann* Du welche Sätze mit welcher Kondition Verknüpfung holen willst. Und - bei solchen Dingen - ist Releasestand und PTF-Stand wichtig.
-h
KingofKning
07-08-10, 16:31
Hallo Holger,
wie Du weißt bin ich ein Fan der schwarzen Kiste, aber ich hatte gehofft das die Kiste im direketen Vergleich zu mysql besser ist ohne das man PTF Stand und intime Kenntnisse der QueryEngine haben muß.
Das man mit dem Wissen von Birgitta mehr aus der Kiste rausholen kann ist schon klar aber auch einer wie ich sollte damit glücklich werden.
GG