Schlechte SQL Performance bei Join?

[Fuerchau]

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 ?

[schatte]

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

[BenderD]

... 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

[KingofKning]

... 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]

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]

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

[andreaspr@aon.at]

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

Hi KingofKning,
bin da ganz deiner Meinung.
Ich glaube aber nicht, dass die beiden DB-Systeme ident sind. Oracle ist eine DB die man mit einer DB2 vergleichen kann, aber MySQL und das mit so einem großen unterschied?? Da könnten wir ja gleich MS-Access gegen die DB2 antreten lassen.
Da wird mit Sicherheit irgendwas sein was extrem bremst. Eventuell Primärschlüsseln die bei MySQL vorhanden sind und bei der DB2 nicht? Dadurch hätte MySQL gegen der DB2 einen (nicht unwesendlichen) Vorteil.
Deshalb würde ich auch (wie ich auch schon oben geschrieben habe) nachschauen was der Flaschenhals ist und dann sieht man schon genaueres.

Eventuell sind die Einstellungen in der QAQQINI nicht korrekt. Schaun obs in der QUSRSYS eine QAQQINI gibt und welche Werte die hat. Wenn in der Lib eine vorhanden ist wird die nämlich als für alle SQL-Anweisungen als Default hergenommen.

Noch ein kleiner Tipp: Egal ob mit SQL oder Native I/O bei DDS Tabellen werden die Daten nur beim Lesen geprüft und bei SQL-Tabellen nur beim Schreiben. Da mehr gelesen wird als geschrieben sind SQL-Tabellen auch Performanter.

[BenderD]

... das ist eines dieser Märchen, die ungeprüft von einem zum anderen weiter erzählt werden (sorry, dass es dich jetzt mit meiner Antwort erwischt). Dieser Mythos geht zurück auf einen Artikel von Dan Cruikshank, der vom IBM Marketing weiter verbreitet wird (http://www-03.ibm.com/systems/resour...e_DDS_SQL.pdf), obwohl er der DB2/400 ein verheerendes Zeugnis ausstellt: das würde nämlich bedeuten, dass bei einer beschädigten Tabelle aus einer SQL Tabelle jeder beliebige Dreck hochkäme...
Ich habe mir das bereits bei erscheinen mal näher angesehen:
- die Maschine hungert (muss eine alte Möhre sein) die braucht für einen read so um die 3 Millisekunden
- CPU Verbrauch ist bei SQL und DDL ziemlich identisch (und welche Ressource braucht Prüfung?)
- die Verarbeitungszeit ist klar durch "Waits" dominiert (99%), was in diesem Fall I/O bedeutet.
Schlussfolgerung: die Begründung für die Zeitunterschiede ist glasklar falsch (Dummfug, der Dummfugigsten Sorte, würde Fred Feuerstein sagen). Was näher liegt ist, dass sich die Blockgrößen beim lesen unterscheiden, ist aber spekulativ.

Kommen wir zu den Zeitunterschieden und deren Relevanz:
Auffallend sind auf den ersten Blick die seltsamen Skalierungen und die unterschlagenen Differenzen. Die Gesamtlaufzeit der Testprogramme differiert von 21 zu 25 sec, der Teil für die Leseschleife von 15 zu 26. Selbst wenn ich jetzt annehme, dass die Unterschieder korrekt und typisch wären, ergibt sich im vorliegenden Fall, dass aus den 2 Sekunden (lesen ohne order by) 1,5 Sekunden würden, die 20 Sekunden würden dann auf 19,5 Sekunden abnehmen.

Konsequenz 1: glaube keiner Benchmark, die du nicht selber gefälscht hast!
Konsequenz 2: Es gibt keine Patentrezepte (die wären dann schon eingebaut)

D*B

Noch ein kleiner Tipp: Egal ob mit SQL oder Native I/O bei DDS Tabellen werden die Daten nur beim Lesen geprüft und bei SQL-Tabellen nur beim Schreiben. Da mehr gelesen wird als geschrieben sind SQL-Tabellen auch Performanter.

[andreaspr@aon.at]

... (sorry, dass es dich jetzt mit meiner Antwort erwischt).
...
Konsequenz 1: glaube keiner Benchmark, die du nicht selber gefälscht hast!
Konsequenz 2: Es gibt keine Patentrezepte (die wären dann schon eingebaut)

Da wir alle hier sind damit wir auch was lernen können, habe ich mit der Antwort auch kein Problem.
Ich selbst bin mit der Zeit sehr vorsichtig geworden zu behaupten was falsch und was richtig ist, wenn ich es nicht zu 99% weis!
Das Beispiel in dem 4 Tabellen erstellt werden (2 DDS & 2 SQL, je eine Tabelle mit einer Dec-Spalte und die andere Tabelle mit einer Char-Spalte) zeigt für mich, dass ich in eine DDS-Tabelle (Spalte Numeric 7 0) sehrwohl Werte aus einem Char-Feld hinzufügen kann. Auch wenn es sich um alphanummerische Werte handelt.
CPYF FROMFILE(TESTLIB/T1) TOFILE(TESTLIB/T2) MBROPT(*ADD) FMTOPT(*NOCHK)
Bei SQL-Tabellen geht das zwar auch, jedoch gibt es (im gegensatz zu DDS) EINE Fehlermeldung, wenn im Char-Feld keine nummerisch genormte Zeichenfolge enthalten ist.
Das ist kein Voodoo und kann auch jeder ausprobieren und testen.
Von dem her bedarf es schon einer sehr, sehr guten Erklärung, dass DAS nicht so ist wie es ist!
Wie sehr sich das auf die Performance auswirkt sei dahingestellt. Habe leider selbst noch keine aussagekräftige Benchmarks machen können.

[BenderD]

... in meinem Beitrag ging es klar um den Teil des lesens der Tabelle und um die Behauptung, dass SQL erstellte Tabellen Perfomancevorteile wegen - !!!andersartiger Prüflogik!!! - hätten. Wenn dich das tiefer interessiert - da ist noch mehr krumm, die Prüfung beim record level access erfolgt erst nach dem lesen, im Programm, beim übertragen der Felder (merkt man, wenn man mit Programm interner Beschreibung liest).

D*B

Da wir alle hier sind damit wir auch was lernen können, habe ich mit der Antwort auch kein Problem.
Ich selbst bin mit der Zeit sehr vorsichtig geworden zu behaupten was falsch und was richtig ist, wenn ich es nicht zu 99% weis!
Das Beispiel in dem 4 Tabellen erstellt werden (2 DDS & 2 SQL, je eine Tabelle mit einer Dec-Spalte und die andere Tabelle mit einer Char-Spalte) zeigt für mich, dass ich in eine DDS-Tabelle (Spalte Numeric 7 0) sehrwohl Werte aus einem Char-Feld hinzufügen kann. Auch wenn es sich um alphanummerische Werte handelt.
CPYF FROMFILE(TESTLIB/T1) TOFILE(TESTLIB/T2) MBROPT(*ADD) FMTOPT(*NOCHK)
Bei SQL-Tabellen geht das zwar auch, jedoch gibt es (im gegensatz zu DDS) EINE Fehlermeldung, wenn im Char-Feld keine nummerisch genormte Zeichenfolge enthalten ist.
Das ist kein Voodoo und kann auch jeder ausprobieren und testen.
Von dem her bedarf es schon einer sehr, sehr guten Erklärung, dass DAS nicht so ist wie es ist!
Wie sehr sich das auf die Performance auswirkt sei dahingestellt. Habe leider selbst noch keine aussagekräftige Benchmarks machen können.

[andreaspr@aon.at]

... in meinem Beitrag ging es klar um den Teil des lesens der Tabelle und um die Behauptung, dass SQL erstellte Tabellen Perfomancevorteile wegen - !!!andersartiger Prüflogik!!! - hätten. Wenn dich das tiefer interessiert - da ist noch mehr krumm, die Prüfung beim record level access erfolgt erst nach dem lesen, im Programm, beim übertragen der Felder (merkt man, wenn man mit Programm interner Beschreibung liest).

D*B

Wie ich geschrieben habe, kann ich über die Performance nichts sagen, damit könntest du auch recht haben. Ich will nur nicht, dass jetzt jeder glaubt, dass die Prüfung wie ich sie beschrieben habe von den Lesern auch als Mythos verstanden wird.

[BenderD]

... ich meine schon, was ich schreibe!
zur Problemstellung:
ich gehe mal davon aus, dass DBTEXT nicht klein ist und MATINDEX um etliches größer und dass die Ergebnismenge eine hohe Selektivität hat, also um ein Vielfaches kleiner als DBTEXT ist. (right oder left join ist hier verkehrt!!! das ist ein klassischer Fall für einen inner join).

Wenn obige Konstellation stimmt, hast du zwei Möglichkeiten:
1. den Index auf das Sortierfeld zu löschen (falls der anderweitig nicht benötigt wird)
2. Zweistufig arbeiten und im ersten Schritt ein Substrat ziehen (create table qtemp.ddd as (select ... from... !!! ohne order by!!!) und im zweiten Schritt select * from qtemp.ddd order by...)
Das auch denkbare Festschreiben der Join Reihenfolge per QAQQINI könnte auch klappen, würde ich aber lassen, das macht andere Queries kaputt.

Dass da eine Datenbank schneller als die andere sein kann, unabhängig von den Lizenzkosten, liegt daran, dass auf unterschiedliche Ziele optimiert wird - jede Datenbank hat ihre Stärken und Schwächen, die in einem inneren Zusammenhang stehen und da liegt es bei MySQL (bei der Abwesenheit von Lizenzkosten) schon nahe für eine spezifische Aufgabe, wenn sie denn kritisch ist, sich das Beste aus mehreren Welten zusammen zu packen!

D*B

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