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