SQL in RPG dauert ewig

[itec01]

Guten Morgen,
je Anwender Selektion dauert ein SQL im RPG ewig.
Das selbe SQL im ACS SQL Editor bringt das Ergebnis sofort. Ich habe das SQL im debug mode laufen lassen und der optimizer hat nichts aussagekrätiges gefunden.
Einen Acces Path hat er vorgeschlagen, aber diesen Index gibt es schon.

Hier der Code dazu:
3030.00 c/exec sql
3031.00 c+ prepare s_caseoverview from :w@SqlString // hier steht der SQL string 3032.00 c/end-exec
3033.00 c/exec sql
3034.00 c+ declare c_caseoverview scroll cursor for s_caseoverview
3035.00 c/end-exec
3036.00 c/exec sql
3037.00 c+ open c_caseoverview
3038.00 c/end-exec

Hier die Debug Infos:
The query access plan has been rebuilt.
PREPARE of statement S_CASEOVERVIEW completed.
Query options retrieved file QAQQINI in library QUSRSYS.
**** Starting optimizer debug message for query .
Query options retrieved file QAQQINI in library QUSRSYS.
Temporary result file built for query.
All access paths were considered for file CHTRNP.
Additional access path reason codes were used.
All access paths were considered for file CDTRNP.
All access paths were considered for file OHTRNP.
All access paths were considered for file DNPTRNL17.
All access paths were considered for file CDTRNP.
All access paths were considered for file OHTRNP.
All access paths were considered for file CHTTRNP.
File CHTRNP processed in join position 1.
File CDTRNP processed in join position 2.
File OHTRNP processed in join position 3.
File DNPTRNL17 processed in join position 4.
File CDTRNP processed in join position 5.
File OHTRNP processed in join position 6.
File CHTTRNP processed in join position 7.
1 Access path(s) used for bitmap processing of file CHTRNP.
Access path suggestion for file CHTRNP.
3 tasks used for parallel index scan of file CHTTRNP.
3 tasks used for parallel index scan of file *SORTEDPTL.
Query options used to build the query access plan.
**** Ending debug message for query .
ODP created.
Blocking used for query.
Cursor C_CASEOVERVIEW opened.


Wieso verhält sich das RPG hier anders?
Klar, es geht um Dateien mit mehren Millionen records, aber wie schon geschrieben, das selbe SQL im ACS oder auch im DB Visualizer ausgeführt, ist das Ergebnis sofort da.

Achja, hier noch das SQL:
SELECT CHNUMB,
CHID,
CHWGHT,
CHWHCR,
CHWHCU,
CHLWHS,
CHPAID,
CHTRMT,
CHTDICC,
CHTDIYY,
CHTDIMM,
CHTDIDD,
CHTDITM,
CHDISC,
CHDLRC,
CHADRC,
CHSTAT,
CHSTAL,
CHTYPE,
CHCRCC,
CHCRYY,
CHCRMM,
CHCRDD,
CHCRTI,
CHCRDT,
CHPATY,
CHNETW,
CHLENG,
CHWIDT,
CHHIGH,
CHSTAK,
CHLQFL,
CHWHTD,
CHWHDP,
CHSLPF,
'CHTRNP' AS FROMFILE
FROM spefil.CHTRNP AS CH
INNER JOIN spefil.DNPTRNL17
ON CHID = DNPCHID
OR CHID = DNPCHOI
OR CHBAID = DNPCHID
OR CHBAID = DNPCHOI
LEFT OUTER JOIN spefil.CHTTRNP
ON CHID = CHTCHID
AND CHWHCU = CHTDWHS
WHERE (CHDISC = '09'
OR (CHDISC = ' '
AND '09' IN (SELECT T1.OHDISC
FROM spefil.OHTRNP AS T1
INNER JOIN spefil.CDTRNP AS T2
ON T1.OHSODN = T2.CDSODN
AND T1.OHODCC = T2.CDODCC
AND T1.OHODYY = T2.CDODYY
AND T1.OHODMM = T2.CDODMM
AND T1.OHODDD = T2.CDODDD
WHERE (T2.CDCHID = CH.CHID
OR (CH.CHBAID > 0
AND T2.CDCHID = CH.CHBAID)))))
AND (CHDLRC = '009080'
OR (CHDLRC = ' '
AND '009080' IN (SELECT T1.OHDLRC
FROM spefil.OHTRNP AS T1
INNER JOIN spefil.CDTRNP AS T2
ON T1.OHSODN = T2.CDSODN
AND T1.OHODCC = T2.CDODCC
AND T1.OHODYY = T2.CDODYY
AND T1.OHODMM = T2.CDODMM
AND T1.OHODDD = T2.CDODDD
WHERE (T2.CDCHID = CH.CHID
OR (CH.CHBAID > 0
AND T2.CDCHID = CH.CHBAID)))))
AND CHSTAL = '9'
AND CHWHCU = CHLWHS
AND CHPAID = 0
AND DNPDISC = '09'
AND DNPDLNO = 537968
AND DNPCHID > 0
AND DNPCHOI > 0

Danke.
Gruß Klaus

[Fuerchau]

Punkt 1:
Ersetze den " in (Select ...)" durch einen exists (select * ...).
Begründung:
der "in ()" wird je Quellzeile ausgeführt und liest alle Zeilen der Whereklausel, der Exists wird auch je Zeile ausgeführt, versucht aber über Index nach Möglichkeit nur 1 Zugriff.

Punkt 2:
Betrachte deinen in (select..)



IN (SELECT T1.OHDISC
FROM spefil.OHTRNP AS T1
INNER JOIN spefil.CDTRNP AS T2
ON T1.OHSODN = T2.CDSODN
AND T1.OHODCC = T2.CDODCC
AND T1.OHODYY = T2.CDODYY
AND T1.OHODMM = T2.CDODMM
AND T1.OHODDD = T2.CDODDD

WHERE (T2.CDCHID = CH.CHID
OR (CH.CHBAID > 0
AND T2.CDCHID = CH.CHBAID)))))


Da deine Where-Klausel nur die Tabelle T2 benötig dreh den Join u.U. einfach um.
Du könnteste zusätzlich statt einem "in Select" 2x Exists-Select verwenden.

Punkt 3:
STRSQL und ACS-SQL optimieren für die schnelle Erstanzeige, da i.d.R. nicht alle Sätze des Selects angesehen werden.
Mach dann aber mal in STRSQL die Eingabe "B" um alle Zeilen zu laden, dann wirst du auch hier die schlechte Performance sehen.

Punkt 4:
Manchmal macht es Sinn, die Felder der Gesamt-Where-Klausel an den Anfang zu stellen gefolgt von den Exists-Select.

Punkt 5:
Nur für die Lesbarkeit lege den Exists-Subselect mal separat in eine CTE, dann wird der SQL übersichtlicher.

Punkt 6:
*SORTEDPTL deutet auf eine intern erstellte sortierte Tabelle hin, die vorab ja erst mal aufgebaut werden muss. Wenn man sich diese sparen kann, zu mal du ja keinen Order By hast, ist schon viel gewonnen.

[BenderD]

@Unterschied: optimize for 1 rows sollte den Unterschied egalisieren
@Order by: bist Du sicher, dass Dir die Reihenfolge egal ist? Fehlende Order by Angaben sind oft fehlerhaft und führen zusätzlich zu Fehlentscheidungen des Optimizers und full table scans.

D*B

PS: viel, fängt bei etlichen Hundert Millionen Sätzen an!

[itec01]

Punkt 3:
STRSQL und ACS-SQL optimieren für die schnelle Erstanzeige, da i.d.R. nicht alle Sätze des Selects angesehen werden.
Mach dann aber mal in STRSQL die Eingabe "B" um alle Zeilen zu laden, dann wirst du auch hier die schlechte Performance sehen.



Danke Dir, werde es umbauen, aber was meinst du mit "B"?
Ich habe im ACS das Visual Explain laufen und auch da ist das SQL schnell.

Gruß

[itec01]

@Unterschied: optimize for 1 rows sollte den Unterschied egalisieren
@Order by: bist Du sicher, dass Dir die Reihenfolge egal ist? Fehlende Order by Angaben sind oft fehlerhaft und führen zusätzlich zu Fehlentscheidungen des Optimizers und full table scans.

D*B

PS: viel, fängt bei etlichen Hundert Millionen Sätzen an!

Sorry, vergessen. Order by ist drin, der wird dynamisch zusammengesetzt.
Wie meinst du mit optimize for 1 rows? Kommt das ans Ende des SQL statements oder in die declare Anweisung?

[BenderD]

Sorry, vergessen. Order by ist drin, der wird dynamisch zusammengesetzt.
Wie meinst du mit optimize for 1 rows? Kommt das ans Ende des SQL statements oder in die declare Anweisung?

...ans Ende des SQL Statements. Variable order by Klauseln in ein und demselben Statement verhindern natürlich das cachen von Optimierungen und Zugriffspfaden - ist nicht gerade ein performance Konstrukt, macht aber nicht den Unterschied zwischen sofort und ewig.

[BenderD]

@Baldur: in oder exists ist zumindest hier Pille Palle. Wenn man sich die debug infos des Optimizers ansieht, joined er die Sätze, die er nicht braucht weg, was hier wohl auch das beste ist.

[B.Hauser]

Über Optimze for X rows, das am Ende des SELECT-Statements angegeben werden kann, kann das Optimierungs-Ziel beeinflusst werden.
Wird für x eine kleine Zahl angegeben, so wird so optimiert, dass der erste Block an Daten so schnell wie möglich ausgegeben wird (Optimierungsziel *FIRSTIO).
Wird für x eine große Zahl oder ALL angegeben, so wird so optimiert, dass das gesamte Result Set so schnell wie möglich ausgegeben wird (Optimierungsziel *ALLIO).
Im ersten Fall wird ggf. noch ein sub-optimaler Index verwendet im zweiten Fall wird im Zweifel ein Table-Scann ausgeführt.
... allerdings wird das Optimierungsziel, bzw. die Angabe des Optimierungsziels an dieser Stelle nicht viel bringen, da per Default nur Statische SQL-Statement mit *ALLIO ausgeführt werden. Alle dynamischen Abfragen (hierbei handelt es sich um ein dynamisches SQL) werden per default mit *FIRSTIO ausgeführt.
... es sei denn ihr hättet irgendwie in der QAQQINI am OPTIMIZATION_GOAL herumgeschraubt.

Ich würde viel ehrer versuchen, ob die beiden Sub-Selects in den WHERE-Bedingungen nicht in die From-Anweisung verschoben werden können oder noch besser in eine Common Table Expression.
Wenn ich das nämlich richtig sehe wird an dieser Stelle die gleichen beiden verknüpften Tabellen 2x gelesen.

[Fuerchau]

Da stimme ich dir nur bedingt zu, meine Erfahrungen sind da andere.
Der Exists wird wohl etwas anders implementiert sein und bietet mir bei Verwendung eines Index bisher immer das beste Ergebnis.

Kritisch sehe ich auch diese Angaben
"1 Access path(s) used for bitmap processing of file CHTRNP."
Dies ist für kleinere Dateien lohnenswert, für größere wird es problematisch. Ursache könnte der order by sein.

Wahrscheinlich ist auch, dass der Sort entfallen kann, wenn für die Order by Felder ein Index vorhanden ist. Das hängt im Endeffekt auch vom gewünschten Ergebnis ab.
Ohne Index muss ja erst mal alles eingelesen und sortiert werden.

Was ACS/STRSQL angeht, so habe ich die Variante mit "optimize for 1 rows" auch schon probiert (in verschiedenen Releases), konnte aber bzgl. des Vergleichs zu STRSQL da auch nichts verbessern.
Da laut Trace auf der IBM i (PCSACC/400) der SQL nicht verändert wurde, kann es "optimize" einfach nicht sein, es muss noch eine andere, undokumentierte Funktion (ggf. CLI-Attribute) sein, die man außerhalb von CLI nicht erreichen kann.

Bei den "ALL ACCESS PATHS..." Nachrichten kann man per F1 prüfen, welcher Index denn im Endeffekt verwendet wurde. Dieser kann und muss nicht der günstigste gewesen sein sondern wurde als "geringstes Risiko" ausgewählt.

Beispiel "in":
Hier ist eine Or-Klausel im Spiel, die beim Zugriff zu mehreren Sätzen führen kann.
Existiert ein Index über OHDISC und ein weiterer über OHDLRC als auch über OHTRNP der betroffenen Tabellen?
Wenn nein sollte man da über einen Exist nachdenken und Indizes erstellen.

[B.Hauser]

Ich denke wir können jetzt noch lange herumphilosphieren, ohne die tatsächliche Umgebung zu kennen (Anzahl Datensätze, Zugriffswege, IBM i Release etc.) können wir nur im Dunkeln tappen.

Vielleicht noch eine Anmerkung:
Wenn ich die Abfrage richtig interpretiere wird zumindest an einer Stelle (DNPTRNL17) auf eine logische Datei zugegriffen.
Mit SQL sollte ausschließlich auf Tabellen/physische Dateien und Views zugegriffen werden.
Beim Zugriff auf eine logische Datei wird die Abfrage zunächst vom Query Optimizer umgeschrieben. Dabei wird das DDS der logischen analysiert und dann die Feld-Auswahl, Join-Anweisungen und SELECT/OMIT-Anweisungen ausgelesen. Im Anschluss daran wird die Abfrage basierend auf der physischen Datei und den DDS Informationen der logischen neu geschrieben.
Erst dann erfolgt die Optimierung. An dieser Stelle ist nicht mehr bekannt, dass ursprünglich eine logische Datei angegeben war.
Wenn der Zugriffsweg der logischen Datei in der Abfrage verwendet wird, ist das nichts weiter als Zufall.