DEBUG - Abgesetzten SQL herausfinden

[BenderD]

Wenn ich das recht in Erinnerung habe, dann sind PREPAREd Statements doch dynamische SQLs, richtig?

Falls ja, sind diese nich von der Performance her viel schlechter, weil der SQL-PreCompiler bei der Modulerstellung keine Zugriffspfade aufbauen kann?
Da werden doch dann bei jeder Ausführung des Statements aufs Neue die Zugriffspfade aufgebaut, oder?

... der Unterschied zwischen dynamic und static SQL liegt in den meisten praktischen Fällen unter der Messbarkeitsschwelle. Man kann ein Statement häufig einmal preparen und dann n mal ausführen, was den Aufwand minimiert , dann fällt der zusätzliche Aufwand nur einmal an und ab da hat man die gleiche Geschwinndigkeit wie bei static. Bei komplexen Statements wird der Access Plan zudem auch bei static SQL häufig dirty gesetzt und neu optimiert, sodass kein Unterschied zu dynamic SQL besteht.

D*B

[mojo]

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

[Fuerchau]

Der SQL-Compiler erstellt keine temporären Indizes (wäre ja noch schöner).

Alles zusammen wird in ein SQLPKG gepackt und in das Modul eingebettet (PRTSQLINF).
Zur Laufzeit werden dann die Optimierungen durchgeführt.
Ohne Prepare läuft aber sowieso nichts.
Statische SQL's werden bei der Verwendung "prepared", Dynamische SQL's werden entweder manuell per Prepare oder automatisch beim "Execute direct" prepared.
Dabei übernimmt die SQL-Runtime die Analyse.
Es werden (häufig) Konstanten dann automatisch in temporäre Hostvariablen überführt und Parametermarker eingesetzt (warum auch immer).
Es ist sogar noch nicht mal gewährleistet, dass 2 Open's mit unterschiedlichen Parametern den selben Zugriffsweg benutzen wobei nur die Wahrscheinlichkeit eben hoch ist.

Verschärft wird das seit V7R1 dahingehend, dass alle Namen zur Laufzeit geprüft werden müssen, ob sie in den Tabellen oder als Variable vorhanden sind.
Der Precompiler wirft nämlich keine Fehler mehr aus, wenn man sich bei Feldnamen vertippt hat.
Er geht nun davon aus, dass dies ja später vorhandene Variablen sein könnten.
Man kann also solche Fehler dann erst zur Laufzeit und nicht mehr zur Compilezeit finden zumal man bestimmte Buchstabendreher ja gerne überliest.

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