SQL hängt sich auf

[Robi]

Moin zusammen

ich habe ein SQLRPGLE Pgm, das beim Fetch nix tut.
Interaktiv, im STRSQL- Mode, bekomme ich meine Daten.

/EXEC SQL                                                             
 DECLARE C3 DYNAMIC SCROLL CURSOR FOR                                
  WITH A AS (                                                        
   SELECT DISTINCT INT#KEY1, INT#KEY2, INT#KEY3, AZMANR, AZMAAN, AZMK,
                   AZMST,    KPLFDN                                  
     FROM MAGICP, AKTENP, ANKPOP                                     
    WHERE AZKEY1=INT#KEY1 AND AZKEY2=INT#KEY2 AND AZKEY3=INT#KEY3    
      AND INT#RDATU = 0 AND CMATURITY <> ' '  AND AZKEY1=KPKEY1 AND  
                                                  AZKEY2=KPKEY2 AND  
                                                  AZKEY3=KPKEY3 AND  
                                                  KPLFDN > 0 ),      
       B AS (                                                        
     SELECT AZMK, AZMST, KPLFDN, COUNT(*) AS B_ZAHL FROM A           
   GROUP BY AZMK, AZMST, KPLFDN),                                     
       C AS (                                                        
     SELECT KPLFDN, COUNT(*) AS C_ZAHL FROM A GROUP BY KPLFDN),      
       D AS (SELECT B.KPLFDN FROM B, C                               
  WHERE AZMK =999                                                    
   AND AZMST = 997 AND B.KPLFDN = C.KPLFDN AND B_ZAHL=C_ZAHL)        
       select DISTINCT INT#KEY1, INT#KEY2, INT#KEY3, AZMANR, AZMAAN  
         from MAGICP, AKTENP                                         
        WHERE AZKEY1=INT#KEY1 AND AZKEY2=INT#KEY2 AND AZKEY3=INT#KEY3
          AND INT#RDATU = 0 AND CMATURITY <> ' '                    
          AND (INT#KEY1, INT#KEY2, INT#KEY3) IN(                    
               SELECT KPKEY1, KPKEY2, KPKEY3                        
                 FROM ANKPOP                                        
                WHERE KPLFDN IN(SELECT KPLFDN FROM D))              
  ORDER BY AZMANR, AZMAAN                                           
END-EXEC 

Erläuterung:
A ermittelt die Daten, um die es geht
B Gruppiert und zählt aus diesen Daten über 3 Felder
C Gruppiert und zählt aus A die Daten über 1 Feld
D ermittelt die LFDN bei dehnen ALLE B Sätze die MK/MST 999/997 haben indem es die Anzahl mit der von C vergleicht.
Der Rest ermittelt die Daten passend zur LFDN.

Der Job steht und der PGMSTAPEL zeigt
QDBGETMQ0 in Anweisung 2942

Keine Meldung im Joblog

STRSQL macht das Problemlos.

Idee?

Danke

[Fuerchau]

Das liegt an der unterschiedlichen Optimierung, da STRSQL wohl noch die Classic-Query-Enging (CQE) verwendet und RPG die SQE.
Zuerst mal:
- Distinct verhindert i.W. einen Dynamic Scroll Cursor und führt auch häufig einen Tablescan aus.
- CTE's sind keine temporären Tabellen sondern werden je Zeile des Mainquery immer wieder ausgeführt!
Führe den SQL mal über 5250->SQL-Script ausführen und dem Explain aus und erstelle ggf. Indizes.
Du kannst auch einen "optimize for 1 row" am Ende ausprobieren.

[Robi]

Hmm,
SQL-Script ausführen macht das klaglos
5.191 Millisek. (15 Sätze)
Nix weiter.
Habe aber umgebaut und 'B' und 'C' so umgestellt, das 'A' nicht mehr nötig ist.
im SQLRPGLE geht das auch wieder nicht.
Löse den 'in ( Select...) nun noch durch einen 'modernen' inner join

Aber ob das hilft ...

[Robi]

Habe im letzten Part die Dateien nun mit inner join verbunden.

Dann geht es interaktiv endlos langsam! (26,953 MS)
Auch beim 2. oder 3 Aufruf von der selben Sitzung.

Das geht (interaktiv) am schnellsten aber leider als SQLRPGLE nicht:

WITH b AS (                                                       
 SELECT  AZMk,  AZMST, KPLFDN , count(*) as B_zahl                
     FROM MAGICP, AKTENP, ANKPOP                                   
    WHERE AZKEY1=INT#KEY1 AND AZKEY2=INT#KEY2 AND AZKEY3=INT#KEY3 
      AND INT#RDATU = 0 AND CMATURITY <> ' '  AND AZKEY1=KPKEY1 AND
                                                  AZKEY2=KPKEY2 AND
                                                  AZKEY3=KPKEY3 AND
                      KPLFDN > 0 group by azmk, azmst, kplfdn),   
       C AS (                                                     
     SELECT KPLFDN, COUNT(*) AS C_ZAHL                             
  FROM MAGICP, AKTENP, ANKPOP                                     
 WHERE AZKEY1=INT#KEY1 AND AZKEY2=INT#KEY2 AND AZKEY3=INT#KEY3    
   AND INT#RDATU = 0 AND CMATURITY <> ' '  AND AZKEY1=KPKEY1 AND  
                                               AZKEY2=KPKEY2 AND  
                                               AZKEY3=KPKEY3 AND  
      KPLFDN > 0 group by  kplfdn),                               
       D AS (SELECT B.KPLFDN FROM B, C  
WHERE AZMK =999                                                   
  AND AZMST = 997 AND B.KPLFDN = C.KPLFDN AND B_ZAHL=C_ZAHL)      
      select DISTINCT INT#KEY1, INT#KEY2, INT#KEY3, AZMANR, AZMAAN
        from MAGICP, AKTENP                                       
      WHERE AZKEY1=INT#KEY1 AND AZKEY2=INT#KEY2 AND AZKEY3=INT#KEY3
         AND INT#RDATU = 0 AND CMATURITY <> ' '                   
         AND (INT#KEY1, INT#KEY2, INT#KEY3) IN(                   
              SELECT KPKEY1, KPKEY2, KPKEY3                        
                FROM ANKPOP                                       
               WHERE KPLFDN IN(SELECT KPLFDN FROM D))             
 ORDER BY AZMANR, AZMAAN

So etwas hatte ich noch nie ...

[Fuerchau]

Das Hauptproblem ist das Erstellen der CTE's B und C, die Gruppen von A über Distinct bilden.
Ggf. helfen dir die OLAP-Funktionen hier weiter, da man Gruppenergebnisse auf Zeilenebene ermittelt.
Z.B. B als

,count(*) over(partition by AZMK, AZMST, KPLFDN) as B_ZAHL

und C als

,count(*) over(partition by KPLFDN) as C_ZAHL

Ggf. kannst du statt Count(*) auch Count(distinct feld1 concat feld2 concat feld3) verwenden.

Für die Optimierung solltest du die Schreibweise der Joins

FROM MAGICP, AKTENP, ANKPOP

in

FROM MAGICP
inner join AKTENP on ...
inner join ANKPOP on ...

anpassen, da die alte Schreibweise von der CQE besser behandelt wurde.
Ein "[not] In" kann meist besser als "where [not] exists (select * ....)" aufgelöst werden, wobei hier gerade Indizes eine wichtige Rolle spielen.
Durch Distinct in A kann aber kein Index verwendet werden. Also Prüfe hier, ob du Distinct wirklich brauchst.

[Fuerchau]

Nachtrag:

Da du hier Schlüssel zählen möchtest kannst du das durchaus auch mit Lateral-Joins erledigen.
Diese werden wie in einem Programm genau für eine aktuelle Zeile ausgeführt, also:

select * from master
inner join lateral (
select count(*) as Zahl
from Child
where master.key = child.key
) C on 1=1

[Robi]

Die schreibweise der Joins ist so deutlich schneller (hatte ich schon geschrieben)

Dein 'join lateral' muß ich mir mal ansehen, das habe ich nicht auf dem Schirm

[Fuerchau]

In der CQE ist das ab und zu schneller, die wird aber zur Laufzeit nicht mehr verwendet.
In der CQE kann man auch beobachten, dass tatsächlich temporäre Tabellen mit *QUERYnnn erstellt werden. Manchmal macht das auch die SQE, was man im Joblog mit Diagnose (Debug) auch sieht.
Der EXPLAIN (über SQL-Script) sagt dir, wo der Hase im Pfeffer liegt.

Wichtige Frage:
Gibt es für alle Join und Where-Beziehungen Indizes?

Bei deinem SQL kann man leider nicht erkennen, welches Feld aus welcher Quelle kommt.
Warum z.B. ist für "C" und den Count von KPLFDN ein Join über 3 Tabellen erforderlich?
Erfolgt da eine Vervielfältigung, da KPLFDN dann mehrfach auftritt?

[Robi]

Die int# Felder gehören zu MAGICP
Die KP Felder gehören zur ANKPOP
Die AZ Felder gehören der AKTENP.

Die Verknüpfungen müssen sein.
Wir leiten nun das obige SQL, erweitert um 'create Table' und 'with data' in eine Qtemp Datei.
(execute immediate)
Die lesen wir und es funktioniert 'normal' schnell.
Ein Fetch auf das obige Stmt habe ich nach über 3 Stunden abgebrochen, der war nicht langsam, der war tot!