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!