SQL Optimierung V5R4 Teil II

[KingofKning]

... das mit der Auslagerung von Teilabfragen in eine UDF ist frei nach Fred Feuerstein Dummfug von der Dummfugigsten Sorte, damit schickt man jede Query Engine in den tiefsten Wald.
Wenn es denn die Summe nicht tut, kann man das auch in subselects auflösen.
In etwa so:
select knd
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201301)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201302)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201303)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201304)
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201305)
...
, (select ist from planz p2 where p1.knd = p2.knd and p2.monat = 201404)
from planz p1

geht sicher noch eleganter, indem man die festen Monate rausnimmt, aber für einen ertsen Test, ob das brummt, reicht das sicherlich aus. (Selbstredend braucht man dann einen Index über Kunde und Monat).
wenn Du dann die leeren noch raus und das ganze in einem string haben willst, packst Du die Stringeritis noch drumherum.

D*B

Hallo, ich wollte das heute mal umsetzen, bekomme aber einen:

Unterbrechungspunkt unüberwachter Nachrichten anzeigen

Anweisung/Instruktion . . . . . . . . . : /0533
Programm . . . . . . . . . . . . . . . : QDBGETM
Rekursionsebene . . . . . . . . . . . . : 1

Funktionsfehler X'1716' in Maschineninstruktion. Interne Speicherauszugs-ID

zurück.
Das Statement lautet:
Aktuelle Verbindung besteht zur relationalen Datenbank S65FFEB1.
select knd
, (select ist from planz p2 where p1.adkto = p2.knd and p2.monat =
201301)
, (select ist from planz p2 where p1.adkto = p2.knd and p2.monat =
201302)
, (select ist from planz p2 where p1.adkto = p2.knd and p2.monat =
201303)
from planz p2, adr01pf p1

Im Dump kann ich nichts erkennen.

SPEICHERAUSZUG FÜR NICHT-ÜBERWACHTE DURCHBRUCHNACHRICHT
.NACHRICHTEN-ID MCH3203
.NACHRICHTENDATEI- QCPFMSG BIBLIOTHEK- *LIBL
.WERTIGKEIT- 60
.NACHRICHTENART- 0F
.SENDE-
..PROGRAMM- #dbreseq
BIBLIOTHEK-
..INSTRUKTION- 001D40
.EMPFANGS-
..PROGRAMM- QDBGETM BIBLIOTHEK- QSYS
..INSTRUKTION- 0533
.NACHRICHT-
Funktionsfehler X'1716' in Maschineninstruktion. Interne Speicherauszugs-ID .
.NACHRICHTENDATEN
000000 99D4A028 DCD1F000 1716 *rMµ }J0 *
.NR. NACHRICHTENDATENFELD 01
000000 F1F2F6F0 F5F1F4F1 F0F4F2F5 F2 *1260514104252 *

Was mache ich falsch?

GG

BTW. Mit IE sieht der Text so aus wie ich ihn geschrieben habe.

[Fuerchau]

Sowas deutet eher auf fehlende PTF's hin.

[KingofKning]

Also eigentlich habe ich sie noch alle.....

Für unser Projekt Vedalohn hatte ich alle PTFs installiert. Aber ich kann ja nochmal schauen lassen ob er noch was installieren will.

GG

[BenderD]

... sieht für mich auch nach einem Bug in der Query engine aus. Aufpassen muss man natürlich auch, dass man im Hauptselect nix doppelt kriegt (select distinct kunde oder so). Alternativ kann man da auch die Datei 24 mal dranjoinen, dann hat man die Umsätze pro Monat auch nebeneinander.

D*B

[Fuerchau]

Ggf. finden SQL die Bezüge nicht richtig da du die Table-ID's doppelt vergibst.

Tabelle P2 gibt es außerhalb des Subselects als auch innerhalb!
Mach dies mal eindeutig, wobei du nicht für jeden Subselect eine neue ID brauchst.

[KingofKning]

Hallo,
selbst wenn ich es umbaue dann bekomme ich einen SQL-Fehler.

select p1.adkto
, (select ist from planz where p1.adkto = knd and monat =
201301)
, (select ist from planz where p1.adkto = knd and monat =
201302)
, (select ist from planz where p1.adkto = knd and monat =
201303)
from soe01/adr01pf p1 where adkto = 11173

Vielleicht sollte ich doch nochmal schauen ob ich die UDF performanter bekomme.

GG

[B.Hauser]

Ich würde auch mal ganz lapidar sagen, dass 24 unabhängige SELECTs das Ganze auch nicht unbedingt performanter machen.
Dabei werden im Gegensatz zu der UDF (alle 24 Werte als String in einer Spalte) auch 24 einzelne Spalten generiert.
Ist das so gewollt?
Anstatt 24 einzelnen Sub-SELECTS würde ich es mit einer einzigen Common Table Expression oder einem einzigen Sub-Select in der FROM-Anweisung versuchen.

Select P1.adKto,
         Sum(Case When Monat = 201301 Then Coalesce(IST, 0) else 0),
         Sum(Case When Monat = 201302 Then Coalesce(IST, 0) else 0),
         Sum(Case When Monat = 201303 Then Coalesce(IST, 0) else 0),
         ...
From SOE01/ADR01PF p1 Left outer Join
    (Select Knd, Sum(Ist) Ist
        from Planz
        Group By Knd) x on p1.adkto = Knd
Where ....
Group by P1.ADKTO 
Order By P1.ADKTO;

oder

With x as (Select Knd, Sum(Ist)
          From Planz
          Group By Knd)
Select P1.adKto,
         Sum(Case When Monat = 201301 Then Coalesce(IST, 0) else 0),
         Sum(Case When Monat = 201302 Then Coalesce(IST, 0) else 0),
         Sum(Case When Monat = 201303 Then Coalesce(IST, 0) else 0),
         ...
From SOE01/ADR01PF p1 Left outer Join on P1.ADKTO = KND
Where ....
Group By P1.ADKTo
Order By P1.ADKTO;

Birgitta

[Fuerchau]

Meine Erfahrung besagt, dass die 24 Subselects häufig schneller sind (es ist ja ggf. nicht immer ein Satz da) als eine CTE (ist auch nur eine derived table wie from (select ...) x).
Das häufigste Performanceproblem ist, dass der Optimizer nicht so arbeitet wie ein Programmierer sich das denkt.
Die CTE wird ja nicht vorab als Tabelle gebildet sondern je Satz des Hauptselect's ausgeführt!

[KingofKning]

Hallo,

ich bin jetzt hingegangen, und habe mir ein Duplikat der planz gemacht und mit einem select insert die passenden daten als kopie reingestellt, läuft ratzfatz, verstehe also nicht warum er in der Original UDF eine Timeout bekommt.

Jetzt habe ich ein hoffentlich letztes Problem, er beschwert sich

Nachrichten-ID . . . . : SQL0303 Bewertung . . . . . . : 30
Nachrichtenart . . . . : Diagnose
Sendedatum . . . . . . : 26.05.14 Sendezeit . . . . . . : 14:52:44

Nachricht . . . : Host-Variable *N nicht kompatibel.
Ursache . . . . : Eine Anweisung FETCH, SELECT, CALL, SET, VALUES INTO, GET
DIAGNOSTICS, GET DESCRIPTOR oder SET DESCRIPTOR kann nicht ausgeführt
werden, da die Datenart von Host-Variable *N nicht mit der Datenart des

Und so sieht die UDF jetzt aus:

CREATE FUNCTION rptrade/istumsatz ( PARARTNR dec(6) ) RETURNS
CHAR(500) LANGUAGE SQL READS SQL DATA BEGIN
DECLARE RETURNVAL CHAR ( 500 ) NOT NULL DEFAULT ' ' ;
DECLARE WORK CHAR ( 500 ) NOT NULL DEFAULT ' ' ;
FOR CSRC1 AS C1 CURSOR
FOR SELECT ist
FROM rptrade/planz2
WHERE knd = PARARTNR
DO SET work = work CONCAT ';' CONCAT
Right('0000000000' concat char(Dec(CSRC1.ist, 8, 2)), 10);
END FOR ;
set work = work concat ';0000000,00;0000000,00;' concat
'0000000,00;0000000,00;0000000,00;0000000,00;' concat
'0000000,00;0000000,00;0000000,00;0000000,00;' concat
'0000000,00;0000000,00;';
SET RETURNVAL = left(work, 133);
RETURN LTRIM(RETURNVAL) ;

end


Der Aufruf erfolgt mit select adkto, istumsatz(adkto) from adr01pf
where adsa06 = 201
Ein strdbg bringt mich nicht weiter, ich weiß nicht was er von mir will.

GG

[Fuerchau]

DECLARE WORK VARCHAR(500)

oder

set WORK = TRIM(WORK) concat ...

Dein Problem ist, dass WORK bereits 500 Leerzeichen enthält an die du auch noch was anhängen willst was zum Überlauf führt.
Überlauf ist aber nicht erlaubt.

Bei embedded SQL kann man ja NULL-Anzeiger definieren die dann mit "-2" einen Verlust ausweisen.
In SQL-Language gibt es aber keinen expliziten NULL-Anzeiger.

[B.Hauser]

Ich weiß nicht ob das der Grund ist, aber Du hast die Felder mit fixer Länge (CHAR) definiert.
Des weiteren verknüpfst Du die Ergebnisse, ohne das WORK-Feld entsprechend abgetrimmt zu haben.

Deine Performance Probleme könnten auch durch Felder mit fixer Länge mit verursacht worden sein. Besser ist auf alle Fälle mit variablen Längenfeldern zu arbeiten.

@Fuerchau:
Auch wenn nur ein einziger Satz zurückgegeben wird, werden 24 verschiedene ODPs generiert, d.h. 24 x durch die FULL Optimierung gegangen.

Ich hab's gerade mal ausprobiert: 3 Sub-Selects versus 1 CTE --> 5 fache Zeit für die Sub-Selects!

Birgitta

[BenderD]

... das mit den ODPs beim Subselects ist eigentlich ein Dreckeffekt, aber die Group by lösung mit dem wegblenden der jeweils anderen Monaten durch ein case ist ohnehin eleganter. Den Join braucht man dann eigentlich auch nicht, da der Kunde ja in den group Feldern zur Verfügung steht.
Eine Bemerkung (zur dann ebenfalls obsoleten) CTE: das ist nur eine Formulierungshilfe für den Programmierer, die Query engine löst das normalerweise auf und berechnet den access plan neu orientiert an der Ergebnismenge (merkt man zuweilen an abbrechenden Queries wg. ungültiger Feldinhalte). Ich ziehe in den meisten Fällen eine View vor, das verbirgt (wo es geht) einen Teil der Komplexität.

D*B