[NEWSboard IBMi Forum]
Seite 1 von 2 1 2 Letzte
  1. #1
    Registriert seit
    Sep 2004
    Beiträge
    327

    SQL in RPG dauert ewig

    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

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    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..)

    Code:
    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.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  3. #3
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    @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!
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  4. #4
    Registriert seit
    Sep 2004
    Beiträge
    327
    Zitat Zitat von Fuerchau Beitrag anzeigen

    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ß

  5. #5
    Registriert seit
    Sep 2004
    Beiträge
    327
    Zitat Zitat von BenderD Beitrag anzeigen
    @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?

  6. #6
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    Zitat Zitat von itec01 Beitrag anzeigen
    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.
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  7. #7
    Registriert seit
    Mar 2002
    Beiträge
    5.286
    @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.
    AS400 Freeware
    http://www.bender-dv.de
    Mit embedded SQL in RPG auf Datenbanken von ADABAS bis XBASE zugreifen
    http://sourceforge.net/projects/appserver4rpg/

  8. #8
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Ü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.
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  9. #9
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    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.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  10. #10
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    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.
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  11. #11
    Registriert seit
    Sep 2004
    Beiträge
    327
    Danke für die konstruktiven Antworten.
    Ich baue nun das SQL auf das exists statement um, weil die OR Verknüpfung sicherlich nicht gut ist.
    Das könnte schon das Problem gewesen sein. Echt blöd ist halt, dass sich RPG mal wieder anders verhält als der Rest. Ich gehe immer so vor, dass ich mir das SQL im Visualizer oder ACS Editor baue und auch auf Performance hin teste und dann ins RPG übertrage. Klar wird dann dort auch noch einmal ein Test gemacht, aber bei den vielen Selektionsauswahlen kann man nicht alle Kombinationen testen, das darf dann der User tun.
    @Brigitta, ja das stimmt mit der logischen. Hier greift sich der Optimizer eh was er gerne hätte. Ist geändert.

  12. #12
    Registriert seit
    Aug 2006
    Beiträge
    2.073
    Zitat Zitat von B.Hauser Beitrag anzeigen
    Mit SQL sollte ausschließlich auf Tabellen/physische Dateien und Views zugegriffen werden.
    Beim Zugriff auf eine logische Datei wird die Abfrage zunächst.....
    Erinnert mich ein wenig an Deinen Artikel vor genau einem Jahr. Wobei mein Kleinhirn noch nicht den Unterschied zwischen LF und View erkannt hat. (Außer evtl. Schlüssel) Ist aber auch nicht so wichtig, da ich nur mit tausenden Datensätzen zu tuen habe und mit Kisten die schon > 10 Jahre sind viele auch älter 20 Jahre.
    https://midrange.de/sql-indices-anst...ien-verwenden/

    GG 3735

Similar Threads

  1. Drucker und iSeries ... das ewig leidige Thema
    By Flappes in forum NEWSboard Drucker
    Antworten: 3
    Letzter Beitrag: 01-02-17, 14:06
  2. JAVAMAIL braucht ewig
    By Chris.jan in forum IBM i Hauptforum
    Antworten: 6
    Letzter Beitrag: 06-06-16, 14:57
  3. Telnet connection dauert extrem lange
    By Mr-Ferret in forum IBM i Hauptforum
    Antworten: 3
    Letzter Beitrag: 28-02-14, 11:35
  4. SAVSECDTA mit BRMS dauert sehr lange
    By Peter Kosel in forum IBM i Hauptforum
    Antworten: 1
    Letzter Beitrag: 27-11-02, 12:32
  5. FTP Anmeldung dauert ewig!
    By cassandra in forum IBM i Hauptforum
    Antworten: 8
    Letzter Beitrag: 10-09-02, 16:01

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •