DEBUG - Abgesetzten SQL herausfinden

[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

[Fuerchau]

OK, das könnte ein Argument sein, wobei ich wieder Mandant/Rechnungsdatum/Kundennr vorziehen würde. Allein dadurch würde ich schon ca. 2,8 Mio. Keys weniger benötigen.
Mal sehen, vielleicht probiere ich das mal aus.