DEBUG - Abgesetzten SQL herausfinden

[BenderD]

OK, das ist doch mal eine gute Aussage!

Noch eine Frage:
Ich habe noch dunkel im Hinterkopf, dass vom SQL-PreCompiler bei der Modulerstellung auch temporäre Indizes erstellt werden, was bei dynamischen SQLs ebenfalls zur Laufzeit gemacht wird und hier die Laufzeit extrem erhöhen kann.
Ist das korrekt oder habe ich sich hier im Hinterkopf etwas verbuchselt?

mojo

- Irgendwelche Indexe legt der Compiler nicht an.
- Zur Compiletime wird ein Accessplan berechnet und in einem Package gespeichert (das ist eine Art vorläufiger prepare).
- Zur Runtime wird entschieden, ob der Accessplan noch verwendet werden soll.
- Static SQL wird zur Compiletime geprüft (soweit möglich)
- Accesspläne werden nach nicht dokumentierten Kriterien (es lohnt nicht drüber nachzudenken wie das im Moment gemacht wird, das kann sich ändern!!!) dirty gesetzt.
- ein statischer Accessplan kann schlechter sein als ein dynamisch ermittelter (das gilt für etliche Käse-Zaubereine)

Meine Empfehlungen:
- einfacher ist immer besser!!!
- Static wegen der Prüfungen bevorzugen
- first make it right
- Performance Optimierung nur wenn es tatsächlich zu langsam ist
- Hauptmaßnahme für Performance Optimierung ist das anlegen erforderlicher Zugriffspfade.
- (fast) alles andere ist Pille-Palle.

D*B

[mojo]

ein Punkt ist mir noch eingefallen.

-> Stichwort FULL OPEN

Wie ist es damit?
Bei einem DYNAMIC SQL muss ja vermutlich immer ein FULL OPEN gemacht werden, da sich ja auch die angesprochenen Tabellen ändern könnten, richtig?
Das sollte bei STATIC SQL nicht der Fall sein, oder?
Falls meine Vermutungen richtig sind wäre das bei großen Tabellen ggf. eine erheblicher Performance-Verlust, oder?

Könnte ich hierzu bitte auch noch eure fachkundige Meinung haben?

VG! mojo

[BenderD]

ein Punkt ist mir noch eingefallen.

-> Stichwort FULL OPEN

Wie ist es damit?
Bei einem DYNAMIC SQL muss ja vermutlich immer ein FULL OPEN gemacht werden, da sich ja auch die angesprochenen Tabellen ändern könnten, richtig?
Das sollte bei STATIC SQL nicht der Fall sein, oder?
Falls meine Vermutungen richtig sind wäre das bei großen Tabellen ggf. eine erheblicher Performance-Verlust, oder?

Könnte ich hierzu bitte auch noch eure fachkundige Meinung haben?

VG! mojo

... wieder einer von den Mythen:
- DB2/400 macht grundsätzlich einen lazy Close (versucht alle Tabellen offen zu halten)
- die einzige Operation, die einen Close sicher erzwingt ist ein disconnect
- ein full open dauert bei vorhandenem Index Millisekunden
- wenn da was länger dauert, dann liegt das am Aufbau eines temporären Index
- temporäre Indexe werden gecached (was bedeutet, dass ein erneuter open von der Größe einer Datei nicht abhängt und wieder Millisekunden dauert.
- guter Stil ist, dass man für unterschiedliche Tabellen und SQLs nicht dásselbe Statement/Cursor verwendet (warum auch?)

D*B

[mojo]

PERFECT!!

Vielen Dank in die Runde!
Hat mich einiges weiter gebracht!

VG! mojo

[Fuerchau]

Und just in diesem Moment ein neues Problem bei V7R1:

Bis vor ein paar Tagen lief ein SQL problemlos und performant.
Nun ist es so, dass die Datenbasis im Leben auch schon mal anwächst.
Und gerade deshalb scheint sich der Optimizer nun anders zu entscheiden.
Verknüpft wird der Kundenstamm (ca. 20.000) mit den Rechnungsdaten (jetzt > 3Mio).
Eigentlich ja überhaupt kein Problem.

Die Abfrage per ODBC (nicht per STRSQL) dauerte länger als 1 Stunde mit halbstündiger Pause für ca. 8000 Ergebnissätze!

Nun habe ich die Indexanalyse für diesen SQL neu gemacht, den vorgeschlagenen Index erstellt und die Abfrage ist wieder performant, also nur der reinen Netzübertragungszeit geschuldet.

Dies ist für mich das Zeichen, dass die Art der Optimierung sehr stark vom vorhandenen Datenvolumen abhängt und man sich ggf. auf die STRSQL-Analyse im Debugmodus auch nicht immer verlassen kann.
Dieselbe Abfrage dauerte nämlich mit oder ohne Index über STRSQL nur wenige Sekunden.

[Pikachu]

Nur die erste Seite am Bildschirm oder auch bei Ausgabe in eine Datei?

Dieselbe Abfrage dauerte nämlich mit oder ohne Index über STRSQL nur wenige Sekunden.

[BenderD]

... strsql und ODBC sind zwei völlig getrennt zu sehende Baustellen (interaktiv versus Server und die Subsystem Einstellungen differieren und STRSQL ist immer dynamic, wogegen ODBC meist ein Package konfiguriert hat, also static SQL´, wenn die Abfrage schon mal da war). Was die Datenmenge angeht, sind 20.000 * 3 Mio schon eine Menge Holz, falls da der Pessimizer einen cross join drausmacht.

D*B

[Fuerchau]

Bei 20.000 * 2.995 Mio war das halt noch kein Problem.
Zumal bei allen Varianten die Tabelle mit 20.000 Sätzen immer "nach Eingangsfolge" verwendet wird.
Es wird auch kein Index vorgeschlagen, obwohl beim Where Indexfelder verwendet werden.
Aber was soll's, im Moment tut er es ja wieder.

Hier sehne ich mich quasi nach der CQE.

[BenderD]

... wie sieht denn das SQL aus?

[B.Hauser]

Dies ist für mich das Zeichen, dass die Art der Optimierung sehr stark vom vorhandenen Datenvolumen abhängt und man sich ggf. auf die STRSQL-Analyse im Debugmodus auch nicht immer verlassen kann.
Dieselbe Abfrage dauerte nämlich mit oder ohne Index über STRSQL nur wenige Sekunden.

Nicht vom Datenvolumen, sondern von der Datenzusammensetzung.
Seit SQE werden Informationen über die Datenzusammensetzung über den Statistiksmanager (Job, der tief im Betriebssystem läuft) gesammelt. Wird ein SQL ausgeführt, so werden die vorhandenen Zugriffswege basierend auf den gesammelten Informationen in den Statistik-Daten bewertet.

Binary Radix Tree Indices (ganz normale Zugriffswege) werden nur verwendet, wenn ca. 15% der Daten verarbeitet werden. Anderenfalls wird, sofern kein entsprechender EVI vorhanden ist, die Tabelle komplett verarbeitet.

Die CQE hat dagegen nur basierend auf Schätzungen ( = in der Where-Bedingung entspr. ca. 10% der Daten) optimiert.
Aber auch da (und dieses Problem hatten wir mehr als einmal, und auch in den unterschieldichsten Releases, das hat nichts mit 7.1 zu tun) wurde zum Zeitpunkt x der zuvor verwendete Zugriffsweg nicht mehr verwendet.

Was die Unterschiede zwischen ODBC und STRSQL betrifft, so könnten unterschiedliche Optimierungsziele (*FIRSTIO und *ALLIO) den Ausschlag geben. Das Optimierungsziel kommt dann zum Zug, wenn der Optimizer sich nicht im Klaren ist, ob ein Index-Zugriff oder bereits ein Table Scan erfolgen soll. Mit *FIRSTIO wird die Entscheidung zugunsten des Index ausfallen bei
*ALLIO zugunsten des Table Scans.
Ich vermute mal stark, dass Du beim ODBC Zugriff *ALLIO verwendest. STRSQL verwendet per Default *FIRSTIO. Das würde nämlich auch Deine Beschwerden darüber, dass die Resultsets immer komplett übertragen werden müssen erklären.
Das Optimierungsziel kann über OPTIMIZE FOR x ROWS in einem SELECT-Statement beeinflusst werden. Ersetzt man x durch eine kleine Zahl, so wird *FIRSTIO verwendet. Ersetzt man dagegen x durch eine große Zahl oder ALL wird *ALLIO verwendet.

Birgitta

[Fuerchau]

Trotzdem muss STRSQL hier noch anders reagieren oder arbeiten, denn alle Varianten der Optimize-Klausel führen zu absolut keinen unterschiedlichen Ergebnissen.
Klar ist, dass bei ODBC ALLIO verwendet wird, da ja das Resultset komplett gebraucht wird.

Bei der CQE konnte ich jedenfalls beobachten, dass der Index verwendet wurde während die SQE dies nun nicht tut. Ich weiß auch nicht wie die SQE hier schätzen kann, da der verwendete SQL eigentlich keine Schätzung erlaubt:

select * from kunden
where Mandant = 1 and kundennr in (select kundennr from Rechnungen where Mandant=1 and Rechnungsdatum >= 20150101)

Bis vor wenigen Tagen reichte ein Index auf Rechnungen mit Mandant/Rechnungsdatum.
Nun musste ein Index Mandant/Kundennr/Rechnungsdatum erstellt werden.
Wobei sich dieser Index für mich nicht erklärt, da damit mehr Informationen gelesen werden müssen weil das Rechnungsdatum entscheidend ist.

In der Diagnose wird noch eine andere ominöse Optimierung ausgeworfen:
Es wird ein "left join datei2 b on a.k1=b.k1 and a.k2=b.k2 and a.k3=b.k3 and a.k4=b.k4" verwendet.
Der vorhandene Index für Datei2 ist K1/K2/K3/K4!
Die Indexempfehlung ist aber K1/K2/K4/K3?

Es wurden in letzter Zeit keine PTF's installiert, nur das Datenvolumen ist gewachsen.

[BenderD]

... der Index Mandant, Kundennummer, Rechnungsdatum erlaubt einen Index only access für den Subselect in der in Klausel (und kann auch eine Abschätzung für die Selektivität liefern).

D*B