Schlechte SQL Performance bei Join?

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

[cbe]

Hallo allerseits!

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

hier stimme ich Dieter voll zu, das zweistufige Arbeiten hat mir auch schon deutlich Laufzeit gespart.
Ich mag allerdings dabei keine Objekte explizit erstellen, WITH ist mir sympatischer, und das geht auch mehrstufig:

with x as (
select IXFNAM, IXTEXT, IXRECL from matindex
where
      IXFNAM = 'DBTEXT'
  and IXTEXT =    'KUNDE'),
y as (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)
select  'DBTEXT', PDPRN2, PDPRN3, PDKDK, PDMA, PDBTDT, Txt
FROM y   order by pdbtdt

(Ich hoffe, die Syntax stimmt so...)

Hiermit erzwingt man, dass das Sortieren erst am Schluss gemacht wird, was manchmal sparsamer ist.
Würde mich interessieren, ob es hier auch hilft.

Ob der Right-Join hier Sinn macht usw. will ich gar nicht weiter ansprechen, das wurde ja schon diskutiert.

Gruß, Christian

[BenderD]

... frei nach Theorie zieht der Optimizer das gesamte SQL Statement zur Optimierung heran, arbeitet also nach dem Prinzip: entscheidend ist, was hinten rauskommt. Hier hält sich ein weiterer Mythos hartnäckig, das die Performance einer Abfrage von der geschickten Formulierung abhänge. Wenn sich der Optimizer von der Art der Formulierung der gleichen Abfrage (:= gleiches Ergebnis!) beeindrucken lässt, dann ist das ein Bug im Sinne von SQL (da zähle ich auch die Existenz zweier Query Engines dazu, die unter sich auswürfeln wer dran ist), oder ein Seiteneffekt des Nebenkriteriums des Optimizers: der nimmt das aktuell best bewerteteste Ergebnis, wenn ihm das gut genug ist, oder lange genug gesucht wurde.

Mehrstufigkeit wird also durch with Formulierungen nur zufällig erreicht, eine temporäre Tabelle erzwingt das. Ich bin kein Freund davon, aber damit kann man Teile mit hoher Selektivität (kleine Trefferzahl) vorziehen, um damit Abfragen zu beschleunigen.

Für die Ausgangslage, da könnte noch das parallel Database Feature weiterhelfen (nicht billig!), das verschiebt die Optimierung in die Richtung, die hier gebraucht wird.

D*B

Hallo allerseits!

hier stimme ich Dieter voll zu, das zweistufige Arbeiten hat mir auch schon deutlich Laufzeit gespart.
Ich mag allerdings dabei keine Objekte explizit erstellen, WITH ist mir sympatischer, und das geht auch mehrstufig:

with x as (
select IXFNAM, IXTEXT, IXRECL from matindex
where
      IXFNAM = 'DBTEXT'
  and IXTEXT =    'KUNDE'),
y as (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)
select  'DBTEXT', PDPRN2, PDPRN3, PDKDK, PDMA, PDBTDT, Txt
FROM y   order by pdbtdt

(Ich hoffe, die Syntax stimmt so...)

Hiermit erzwingt man, dass das Sortieren erst am Schluss gemacht wird, was manchmal sparsamer ist.
Würde mich interessieren, ob es hier auch hilft.

Ob der Right-Join hier Sinn macht usw. will ich gar nicht weiter ansprechen, das wurde ja schon diskutiert.

Gruß, Christian

[Pikachu]

Leg mal eine logische Datei für DBTEXT mit einem Zugriffspfad über die Spalten PDLFDN und PDBTDT an und probier dann mal mit ORDER BY PDLFDN, PDBTDT.

[schatte]

Leg mal eine logische Datei für DBTEXT mit einem Zugriffspfad über die Spalten PDLFDN und PDBTDT an und probier dann mal mit ORDER BY PDLFDN, PDBTDT.

Hallo Pikachu,

das geht dann flott. Aber es ist ja dann nicht mehr nach dem Datum (PDBTDT) sortiert.

@Holger: In der Datei DBTEXT sind 800.000 Sätze enthalten. Es enthält 5 Textspalten (PDTXT1 - 5 jeweils 50 Stellen lang), eine eindeutige laufende Nummer (PDLFDN), ein Datumsfeld (PDBTDT). Nun möchte ich die Textspalten per Freitextselektion durchsuchen. Damit ich nicht jeden Datensatz mit SQL UCASE(TEXT) Like '%SUCHBEGRIFF%' prüfen muss, lasse ich jeden Abend eine Wort Indexierung erstellen (Tabelle MATINDEX). Jedes Wort wird dabei in Großschrift in die Datei MATINDEX geschrieben mit einem Bezug auf die laufende Nummer (PDLFDN) der Datei DBTEXT.

Bisher hatte ich für die Abfrage ein Cobol Programm erstellt, was für den Suchbegriff jeweils die Sätze aus der MATINDEX liest und die dazugehörigen DBTEXT Datensätze in ein Sortfile einliest, damit nach PDBTDT sortiert werden kann.

Dieses Cobolprogramm wollte ich nun durch ein SQL Cobolprogramm ersetzen.


Gruß
Matthias

[schatte]

(right oder left join ist hier verkehrt!!! das ist ein klassischer Fall für einen inner join).

Hallo,

ich habe das ganze nun auch mal auf einer 170er (220CPW) mit V5R2 getestet.
Beide Tabellen habe ich nun per SQL (CREATE TABLE) erstellt.
Erstellte Indexe:
MATINDEXI2: IXFNAM, IXTEXT, IXRECL
DBTEXTI1: PDBTDT (Empfohlen vom SQL Optimizer)

Nun frage ich einen Suchbegriff ab, der insgesamt nur aus 85 Zeilen besteht. Dies dauert beim INNER JOIN 46 Sekunden (eine Seite weiter blättern dauert 19 Sek!) und beim RIGHT JOIN nur 0,1 Sek (Blättern auch nur 0,0 Sek!).

Bei Abfrage eines Suchbegriffs, der insgesamt aus 38450 Zeilen besteht, ist das Ergebnis anders:
INNER JOIN: 0,2 Sek
RIGHT JOIN: 7,2 Sek (wobei das Blättern hier ebenfalls etwas flotter ist als beim INNER Join)

Muss ich also in meiner Indexierungsdatei erstmal abfragen wieviel Zeilen von der Abfrage betroffen sind und dann jeweils den RIGHT JOIN oder den INNEr JOIN verwenden? ;-)

Gruß
Matthias

[BenderD]

... das Problem ist die join order
- die Auswahlfelder sind in der Tabelle a
- die Sortierfelder sind in der Tabelle b
--bei großer Trefferzahl wäre es günstiger die Sortierung vorzuziehen
--bei kleiner Trefferzahl wäre es günstiger die Auswahl vorzuziehen

durch die Auswahl des Joins wird die Entscheidung des Optimizers nach a oder b beeinflusst!

Wenns um Anzeige geht, kann man das auch durch Optimize for 20 rows beeinflussen (BTW: das ist einer der Gründe, warum der Ooops Nerv und explain schrott ist!)

Hast du beide Konstellationen und beides soll schnell sein, erreichst du das auch durch Redundanz, sprich: Aufnahme der Sortierfelder in die MATINDEX (was bei schwacher Rechenleistung sicher das Beste ist)

D*B

Hallo,

ich habe das ganze nun auch mal auf einer 170er (220CPW) mit V5R2 getestet.
Beide Tabellen habe ich nun per SQL (CREATE TABLE) erstellt.
Erstellte Indexe:
MATINDEXI2: IXFNAM, IXTEXT, IXRECL
DBTEXTI1: PDBTDT (Empfohlen vom SQL Optimizer)

Nun frage ich einen Suchbegriff ab, der insgesamt nur aus 85 Zeilen besteht. Dies dauert beim INNER JOIN 46 Sekunden (eine Seite weiter blättern dauert 19 Sek!) und beim RIGHT JOIN nur 0,1 Sek (Blättern auch nur 0,0 Sek!).

Bei Abfrage eines Suchbegriffs, der insgesamt aus 38450 Zeilen besteht, ist das Ergebnis anders:
INNER JOIN: 0,2 Sek
RIGHT JOIN: 7,2 Sek (wobei das Blättern hier ebenfalls etwas flotter ist als beim INNER Join)

Muss ich also in meiner Indexierungsdatei erstmal abfragen wieviel Zeilen von der Abfrage betroffen sind und dann jeweils den RIGHT JOIN oder den INNEr JOIN verwenden? ;-)

Gruß
Matthias

[B.Hauser]

M.E. ist das ganze auch eine Sache der Query Engine!

Mit Release V5R2 wird alles (was einen join hat) noch mit der CQE ausgeführt. CQE optimiert nur basierend auf Schätzwerten und prüft nicht die tatsächlichen Daten (Statistiken wurden erst mit Release V5R2 eingeführt und werden nur von der SQE geprüft!). In Deinem Fall wird also festgelegt, dass in der vorgegebenen Konstellation ein bestimmter Zugriff der optimale ist. Der Index-Advice nur auf das Datum ist m.E. nicht korrekt bzw. komplett (ist aber noch CQE bei der noch der ORDER BY sehr stark in die Berechnung einbezogen wird). Die lfd Nr. sollte auf alle Fälle mit dabei sein.

Das Verhalten auf dieser Maschine und das Verhalten auf einer Maschine mit einem höheren Release und der Verwendung der SQE kann m.E. nicht über einen Kamm gescheert werden.

Übrigens ... der Visual explain berücksichtigt auch das Optimierungsziel (OPTIMIZE FOR X ROWS). ... und macht eigentlich nur den verwendeten Zugriffsplan sichtbar!

Alle dynamischen SQL Interfaces (z.B.STRSQL, iSeries Navigator, embedded dynamisches SQL, JDBC ...) werden per Default so optimiert, dass der erste Block der Daten möglichst schnell zurückkommt, während statisches SQL per Default so optimiert wird, dass alle Daten möglichst schnell zurückkommen.
Mit Optimize for X rows kann man z.B. bei statischem SQL, z.B. bei seitenweisen Subfile-Anzeige das Optimierungsziel verändern. Ist x eine sehr kleine Zahl ist das Optimierungsziel *FIRSTIO ist x eine sehr große Zahl oder ALL wird das Optimierungsziel *ALLIO verwendet. Das Optimierungsziel kommt eigentlich nur dann zum Zug, wenn es um die Entscheidung geht Table Scan oder doch lieber Index Zugriff.

Birgitta