DEBUG - Abgesetzten SQL herausfinden

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

[BenderD]

... die where in (subselect) Abfragen haben häufig eine Tendenz zum full table scan und in diesem Fall bezieht sich der wirksame Selektor auch noch auf die größere Tabelle. Solche Abfragen sind anfällig für Wechsel in der Zugriffsstrategie. Wenn man selber Zusatzinformationen über die Selektivität hat, kann man eventuell versuchen über eine Order by clause eine Entscheidung nahezulegen. Oder die mehrstufige Variante, die immer schnell ist (in diesem Fall könnte man den subselect in eine UDT packen, das funzt dann,bis die UDTs ordentlich implementiert sind...)

Dieter

[AG1965_2]

Witzig, die Maschine sammelt Infos wie die NSA, kann aber scheinbar genau so selten sinnvolle Schlüsse daraus ziehen. :-)

[Fuerchau]

Häufig kann man die "in select(..)"/"not in (select..)" durch eine Exists-Klausel beschleunigen, da hier häufig nur ein Zugriff erfolgt.
Die Exists-Klausel wurde meines Wissens nach erst mit V4R5 eingeführt.
Leider habe ich noch genau einen AS/400-Kunden mit V4R3 (man glaubt es kaum).
Hier existiert der Exists nicht (schönes Wortspiel).