Performance sqlrpgle / strsql

[Robi]

Hallo *all

ich habe hier eine View die 11 große Dateien, teilweise 1:1 teilweise 1:n.
3 mal wird mit inner- und 7 mal mit left outer join verknüpft.
Für alle verknüpfungen gibt es passende LF.

Das ganze dient einer sehr komplexen Matchcode Abfrage, bei der, im extremfall, selekektionskriterien aller 11 Dateien berücksichtigt werden.

Ein SQLRpgle Pgm baut, nach einer Eingabemaske, das SQL zusammen
vorherige Test im Strsql haben ergeben das es nicht super schnell, aber erträglich ist.

Nun mache ich eine ganz einfache Abfrage auf 1 Feld aus 1 Datei und warte im Pgm mehrer Minuten auf die Anzeige, während strsql die 8 Sätze nach 4 Sekunden anzeigt.

Debug empfielt nur keys die es bereits gibt, aber in anderer Reihenfolge.
Key1, key2, key3 gibt es, key3, key2, key1 wird emfohlen.

Momentan kann ich mir nicht erklähren wo das Pgm so viel Zeit verliert.
(doch, beim fetch, aber wieso?)

Was kann ich noch kontrollieren

Danke
Robi

[BenderD]

... Database Monitor, bzw. Ausführung unter debug bringt wahrscheinlich Auffschluss. Der Unterschied der Laufzeiten könnte auch daran liegen, dass STRSQL für die ersten Sätze optimiert. Versuchsweise könnte man im Programm eine optimize Klausel einfügen - select ... optimize for 1 row

D*B

[Fuerchau]

Dieses Problem habe ich auch immer wieder.
Der neue Optimizer mag halt manchmal die Schlüsselreihenfolge nicht.
Dies ist insbesonders der Fall wenn man nicht alle Vergleiche mit "=" führt.

STRSQL und embedded SQL werden grundsätzlich anders optimiert.
Leider ist das nicht dokumentiert, was STRSL hier tatsächlich macht.

Du kannst aber im SQL-Statement folgenden Code hinzufügen:
- optimize for n rows (also z.B. optimize for 1 rows)
- optimize for all rows (das ist der Default)
Im STRSQL wird halt davon ausgegangen, dass du nicht alle Daten sehen willst.
Was dir dann halt passieren kann ist, dass beim weiterlesen SQL immer mal wieder stockt da die Daten erst mal wieder zusammengesucht werden müssen. Dies ist beim Blättern in STRSQL auch schön zu beobachten.

Vielleicht solltest du dir die Komplexität deines SQL's noch mal überdenken, da ja je nach Selektion wieder unterschiedlichst optimiert werden muss.

Die Zeit geht immer erst beim Fetch drauf, da ja dann erst die Daten ermittelt werden.
Der Open wählt nur die Zugriffswege aus.

[Robi]

debug:
für alle Dateien diese Meldung
Alle Zugriffspfade wurden für Datei AZ_ALL2 berücksichtigt.

Alle, bis auf eine Datei, mit einem 0 Eintrag, d.h. der Zugrifspfad wurde verwendet.
Die eine Datei enhällt den Selektionsbegriff

... where Upper(NAME) like 'JOK%'
Ich habe einen Index auf UPPER(NAME) gelegt, der wird aufgelistet aber nicht verwendet.
Verknüpfung dieser Datei mit der 'Basis' Datei: Kundennr und Erweiterung, Key(LF) vorhanden.
Auch ein Index auf Upper(Name), KDNR und ERWNR wird nicht verwendet.

Für 6 Dateien habe ich eine Empfehlung wie der zugriffspfad werden soll, alles Zugriffe die es gibt, nur
in anderer Reihenfolge.
Habe die verknüpfung in der view gedreht.
aus a.key1 = b.key1 and a.key2 = b.key2 habe ich
a.key2 = b.key2 and a.key1 = b.key1 gemacht.

hat nix gebracht!
Robi

[Robi]

Nachtrag:
der fetch im Rpg, aufgelöst als call sql...
braucht sowohl beim fetch first als auch beim den weiteren fetch next endlos lange
daher vermute ich, das auch ein optimize for 1 row nix hilft

[Fuerchau]

Éin kalkulierter Index hilft leider nicht wenn die Abfrage nicht auf "=" lautet.
Wenn du nicht mit "%xx%" arbeitest, kannst du ggf. den Index verwenden, wenn du auf
upper(Name) between VonName and BisName
abfragst.
VonName = 'JOK';
BisName = 'JOK' + *hival;
Aber auch hier gibts keine Garantie.

Hast du den "Optimize for" denn mal versucht?

[BenderD]

Nachtrag:
der fetch im Rpg, aufgelöst als call sql...
braucht sowohl beim fetch first als auch beim den weiteren fetch next endlos lange
daher vermute ich, das auch ein optimize for 1 row nix hilft

... hast Du da eine stored procedure mit Resultset gemacht? Schmeiß den Penner raus, das führt, ähnlich wie UDTFs zu einem optimize for all rows, der einen Hang zum full table scan hat - je mehr Tabellen gejoined werden, umso mehr tendiert das (fatalerweise) zum full table scan.
Wenn Du sicher bist, dass das resultiernde ResultSet klein ist, kannst Du auch versuchen in der stored procedure die hohen Selektivitäten vorzuziehen und temp tables zu verjoinen.

D*B

[Robi]

@D*B
Nee, nix Stored Procedure
Das Sql wird doch vom Compiler umgewandelt in einen call.
Und dieser call, der den 1. Fetch (fetch first from cursor01 into :f1, F2, ...)
dauert schon endlos.

@Baldur
ok, versuche mal auf between um zu stellen
Optimize n.n. versucht. mach ich auch gleich mal ...

anderer Test, 2 Selektions Kriterien, < 1 Sekunde interaktives sql, 2 Minuten 34 Sekunden SQLRPGLE.

Robi

[Fuerchau]

Ich glaube da ist nur die Umsetzung des Compilers in eine "CALL SQLROUTE"-Anweisung gemeint.
Ohne den DB-Monitor wirst du da wohl nicht rumkommen.
Probier doch mal den "optimize for 1 rows" erst mal aus.

[BenderD]

@D*B
Nee, nix Stored Procedure
Das Sql wird doch vom Compiler umgewandelt in einen call.
Und dieser call, der den 1. Fetch (fetch first from cursor01 into :f1, F2, ...)
dauert schon endlos.

Robi

... aus dem interaktiven SQL erfolgt hier auch nicht viel was anderes. Die lange Dauer für den ersten fetch könnte eben gerade auf einen full table scan hindeuten.
Die join order könnte man über die QAQQINI forcieren, würde ich aber normal nicht empfehlen. In jedem Fall würde ich einen order by machen, falls keiner da ist, das kann den Query Optimizer auch in eine Richtung schieben.

D*B

[B.Hauser]

Es gibt schon einen Unterschied zwischen STRSQL und statischem embedded SQL und zwar ist es das Optimierungsziel. Alle dynamsichen SQL-Statements (damit auch Abfragen aus STRSQL) werden per Default mit dem Optimierungsziel *FIRSTIO (der erste Datenblock muss so schnell wie möglich zurückgebracht werden). Statisches SQL wird per default mit Optimierungsziel *ALLIO optimiert, d.h. das komplette Ergenis muss so schnell wie möglich zurückgebracht werden.

Normalerweise hat das Optimierungsziel wenig Einfluss, kann aber genau in dem Moment wo sich der Optimizer zwischen Table Scan und halb-optimalem Index-Zugriff entscheidend sein.
Bei Dir ist genau diese Situation eingetreten.

Das Optimizerungsziel kann über die OPTIMIZE FOR X ROW-Anweisung beeinflusst weden.
Wird X durch eine kleine Zahl ersetzt, wird Optimierungsziel *FIRSTIO verwendet. Wird X durch eine sehr große Zahl oder ALL ersetzt wird Optimierungziel *ALLIO verwendet.

Birgitta

[Fuerchau]

Wobei ich diesem "Optimize for" nicht soi ganz traue, da ich trotz vieler Versuche selten einen Effekt erzielen konnte.