sql durch empfolene zugriffe langsamer

[Robi]

Hi *all

ich habe hier eine View die 3 Dateien miteinander verknüpft und mit Union noch 'Reste' hinten anklebt.
Wenn ich diese View im SQL anzeigen lasse, mit oder ohne Selektion ist das 'erträglich'
(1-2 Sekunden).
Mit einem SQLRPGLE Pgm mache ich mehrere Count(*) auf die View mit verschiedenen selects. Teilweise mit einer weiteren Verknüpfung.

Das Pgm zeigt 24 Zeilen, je Zeile werden 4 Sql ausgeführt.
Die Summe aller gezählten Sätze (über alle 24 Zeilen) ist < 1 Mio.

Auf das Bild warte ich ca. 4 Minuten !!
Und das seltsame ist. das ich das Gefühl habe, das es durch die vielen LF die ich anlegen sollte (lt. STRDBG) eher langsamer geworden ist.
Bei den LF ist m.E. einiges blödsinniges dabei, ich habe sie aber trotzdem angelegt.
Bsp.:
LF1 Feld5, Feld3, Feld7, Feld1
LF2 Feld1, Feld3, Feld5, Feld7
...
Im Prinzip immer diese 4 Felder in allen möglichen Kombinationen.
Seltsamerweise auch mit nur 3 Feldern trotzdem es ein lf mit 4 Feldern in gleicher Folge gibt
.
Egal, ich habe alles angelegt was empfohlen wurde.
Aber gefühlt ist das Pgm nun deutlich langsamer.

Es wird nun kein LF mehr empfohlen, auch nicht via Navigator

Hat noch jemand einen Tipp Wo ich drehen kann?



create view hissc#v01 as(
-- wieviele Monate?
-- alle Felder des unique keys in dem select
with xx as (select dec(substr(inhacd, 1, 2), 2, 0) as mon from cbuchp01
where finrcd = 0 and sprccd = 'D' and sakzcd = '6' and
sartcd = 'FORM' and rkeycd = 'CNT'),

-- aktuellste Satz je Teilnehmer?
yy as (
-- aktuelle Daten, deren 'normaler' Wert nicht älter als 24Monate ist
-- wobei die 24 aus dem codebuch kommt
select max(hcdejj*10000+hcdemm*100+hcdett) as dd, hcsunr, hcsun2, hcsu22
from hisscp
where hcklas = 'K10'
group by hcsunr, hcsun2, hcsu22
having max(hcdejj*10000+hcdemm*100+hcdett) >=
dec(replace(char(current_date-
(select mon from xx) Months, iso), '-', ''), 8, 0)),

-- hisscp satz da ich ja den Wert brauche
a as (
select dd as d, a.hcsunr, a.hcsu22, a.hcsun2,
hckate as maxwert
from hisscp a inner join yy on
yy.hcsunr=a.hcsunr and yy.hcsun2=a.hcsun2 and
yy.hcsu22=a.hcsu22 and
yy.dd = a.hcdejj*10000+a.hcdemm*100+a.hcdett
where a.hcklas = 'K10'
)
-- hisscp verknüpft mit aktenp und mandap
select azkey1, azkey2, azkey3, d, maxwert, dec(case when azsun2 = 3 then 2
else 1 end, 1, 0) as cnt,
azmk, azmst, makzfo, azdwit, azdwim, azdwij, azfonr
from aktenp, a, mandap
where a.hcsu22=azsun2 and a.hcsunr=azsunr and
azmaan = maaunr and azmanr = mamdnr
-- und nun noch die, die keinen gültigen Wert haben
union all
select azkey1, azkey2, azkey3, dec(20491231, 8, 0) as d,
'00000' as maxwert, azsun2 as cnt,
azmk, azmst, makzfo, azdwit, azdwim, azdwij, azfonr
from aktenp left outer join a on
azsunr=a.hcsunr and azsun2=a.hcsun2
inner join mandap on
azmanr = mamdnr and
azmaan=maaunr
where a.hcsunr is null
)



und



* je Zeile diese beiden sql, 2 mal, 2. mal als makzfo = 1
C/EXEC SQL
C+ SET :AMSFO_F =(SELECT COUNT(*) FROM HISSC#V01 WHERE AZFONR = :MASN
C+ AND AZDWIJ*10000+AZDWIM*100+AZDWIT <= :DTV AND MAKZFO = 0 AND
C+ MAXSCORE <> '00000')
C/END-EXEC
* ANZAHL MIT Wert GEMÄß MK/MST FREMD
C/EXEC SQL
C+ SET :AMSMS_F =(SELECT COUNT(*) FROM HISSC#V01 WHERE AZMK*1000+AZMST
C+ IN(SELECT DISTINCT MKKETT*1000+MKSTUF FROM MAKESP WHERE MKFORM = :MASN)
C+ AND AZDWIJ*10000+AZDWIM*100+AZDWIT <= :DTV AND MAKZFO = 0 AND
C+ MAXSCORE <> '00000')
C/END-EXEC



Danke
Robi

[Fuerchau]

Generell gilt ja:
Alle Berechnungen in einer Where/Group-By-Klausel führen eher selten zu einer Indexverwendung.
Hierfür ist es besser, wenn die Anwendung es nicht selber bringt, einen fertigen Schlüssel zur Hand zu haben.
Ich habe mir hierfür, bereits vielfach diskutiert, eine PF mit den Schlüsseln JJ, MM, TT, JJJJ, JJJJMMTT, MMTT, JJJJ-MM-DD (*ISO-Datum) für 1900 - 2100 angelegt (du kannst natürlich auch mehr).
Somit kann ich die alten Dateien per "Inner Join" mittels JJJJ, MM, TT verbinden und das ISO-Datumfeld oder das DEC(8, 0)-Feld mit JJJJMMTT direkt abfragen.

Da der Optimizer ja nicht weiß was du willst, kann er auch nicht wirklich optimieren.

Als erstes musst du die unsäglichen Datumsberechnungen (JJJJ*10000+MM+100+TT) eliminieren und mit der neuen PF verbinden, dann helfen auch Keys.
Dein Having-Konstrukt ist dann einfacher zu verwenden.
Wobei das Having ggf. eher in die Where-Klausel zu stecken ist.

Ein
Select ... where xxx in (select distinct ...)
sollte auch in einen
Select ... where exists (select * from xfile where xxx = xfile.key and ...)
geändert werden können, da hierfür ein Index für xfile eindeutig festlegbar ist.

[Robi]

Hi und danke, aber das ars noch nicht ...
Habe eine Datei erstellt mit
jjjj
mm
tt
jjjjmmtt
jjjj-mm-tt (Typ L)
und von19000101 bis 20991231 gefüllt

dann die abfrage umgebaut
1. verknüpft mit der Datums Datei
2. - 24 Monate auf das L Feld umgestellt statt der Datumskonvertierung nach numerisch


...
-- wobei die 24 aus dem codebuch kommt
select max(dajjmt) as dd, hcsunr, hcsun2, hcsu22 from hisscp
inner join datump on hcdejj = dajjjj and hcdemm = damm and hcdett = datt
where hcklas = 'K10'
group by hcsunr, hcsun2, hcsu22
having max(dadate) >= (current_date - (select mon from xx) Months)),


und weiter


-- hisscp satz da ich ja den Wert brauche
a as (
select dd as d, a.hcsunr, a.hcsu22, a.hcsun2,
hckate as maxwert
from hisscp a
inner join datump on hcdejj = dajjjj and hcdemm = damm and hcdett = datt
inner join yy on yy.hcsunr=a.hcsunr and yy.hcsun2=a.hcsun2 and
yy.hcsu22=a.hcsu22 and yy.dd = dajjmt
where a.hcklas = 'K10'
)


Der 1. Aufruf brachte 3,5 Minuten statt vorher 4
dann alle empfohlenen LF aufgebaut und nun 6,5 Minuten gebraucht!

Den Rest deiner Empfehlungen setze ich auch noch um aber bisher was's das leider nicht!

Robi

[Robi]

wenn ich den
having max(dadate) >= (current_date - (select mon from xx) Months)),
durch eine
where dadate >= (current_date - (select mon from xx) Months)

ersetze
verdoppelt sich die Laufzeit, das bringt also nix

[Robi]

Sch...
auch die Umstellung auf exists bringt nichts!

Aber wenn ich die empfohlenen LF lösche!!! bin ich 20-35 Sekunden schneller!
Wie kann das sein???

[andreaspr@aon.at]

wenn ich den
having max(dadate) >= (current_date - (select mon from xx) Months)),
durch eine
where dadate >= (current_date - (select mon from xx) Months)

ersetze
verdoppelt sich die Laufzeit, das bringt also nix

Bei der HAVING ... Version wird er zunächst mal einen passenden Index basierend auf die Key-Felder im WHERE suchen und den Filter des HAVINGs erst mit den Ergebnissen der davor ermittelten Sätze umsetzen.

Wenn du das HAVING ins WHERE rein stellst und die Indices ... sagen wir mal "Sub-Optimal" sind, kann es durch einen schlechten Index sogar langsamer sein.


Aber wenn ich die empfohlenen LF lösche!!! bin ich 20-35 Sekunden schneller!
Wie kann das sein???
Das ist einer von vielen Grund warum ich den Index-Advisor nicht empfehlen kann.
Ein falscher Index der zwar auf die Key-Felder super passt, wo aber andere Auswahlkritieren nicht übereinstimmen, kann es schon passieren, dass die DB zickt und sagt:
"So a schaß, nichts ist so wie ichs gern hätt, ich geh lieber zu fuß" --> Table Scan
Mit solchen Problemen hab ich auch schon öfters zu tun gehabt.

Lösung: die Queries aufteilt in mehere Sub-Queries, für die entsprechende Indices angelegt werden.
Und WHERE Bedingungen die einen Table-Scan auslösen würde bzw. die auch bei einem Index viel Zeit verbraten (erkennst du im Visual Explain), in die oberste Ebene verschieben.

Damit habe ich schon einige Queries wo mit kombinationen von LIKE, MAX, ORDER BY usw. eine extrem schlechte Performance hatten, wieder in die millisek. bereich bringen konnte.

lg Andreas

[Fuerchau]

Die Empfehlungen sind leider häufiger für die Katz wenn man das über STRSQL im Debug-Modus betreibt.
Embedded-SQL ist da schon besser, da hier "Batch"-Optimiert wird, während STRSQL immer Dialog-Optimiert arbeitet. Entgegen anderen Meinungen lässt sich STRSQL auch nicht umstimmen.

Und 20 - 35 Sekunden bei 4 Minuten ist ja nix.

Nun zum Aufbau des SQL's.
Auch hier habe ich die Erfahrung gemacht, dass man die Joins manchmal umdrehen muss und genau einen Index zusätzlich benötigt.

Also:
Die primäre Datei sollte nun die Datumsdatei werden, da man hier die Einschränkung auf den Zeitbereich mit wenigen Sätzen hinbekommt.
Nun verknüpft man über die JJJJ/MM/TT-Felder auf die eigentliche Tabelle.
Man braucht dort natürlich auch den Schlüssel auf die JJJJ/MM/TT-Felder.
Da nun zusätzlich eine Where-Klausel auf die Haupttabelle benötigt wird, bildet man einen neuen Index über die JJJJ/MM/TT-Felder und hängt die zusätzlichen Felder aus der Where-Klausel dran.
Dabei sollte man eine Wertigkeit, Priorität beachten:

Zuerst alle Felder mit "=", danach alle mit "</<=" oder ">/>=" oder Between, zum Schluss dann noch like. Wobei ein "like 'xxx%'" wie "=" gewertet werden kann.

[Robi]

Moin und danke!

also ...
@andreas

Lösung: die Queries aufteilt in mehere Sub-Queries, für die entsprechende Indices angelegt werden.
Und WHERE Bedingungen die einen Table-Scan auslösen würde bzw. die auch bei einem Index viel Zeit verbraten (erkennst du im Visual Explain), in die oberste Ebene verschieben.
Ich hab das zwar mit dem VE angesehen (das ist doch das mit dem STRDBMON und dem Iseries Navigator?) Kann aber mit der Graphik nicht viel anfangen. Am Ende habe ich 'nur' die dort empfohlenen Indizes angelegt.
Da ich ja schon 2 Sqls habe (create table und select ...) fehlt mir für ein zerschlagen in mehrere Sub Query momentan die Phantasie!?

@Baldur
Ich habe zwar im Debug modus nach den zu erstellenden indices gesucht, aber schon mit dem echt laufenden Pgm. Und die Indices waren identisch mit den VE Empfehlungen (s.o.)
Primäre Datei = Datumsdatei ... werd ich mal versuchen,

melde mich

Gruß
Robi

[Robi]

]Primäre Datei = Datumsdatei ...
sieht nun so aus


-- aktuelle Score Daten, deren 'normaler' Score nicht älter als 24Monate ist
-- wobei die 24 aus dem codebuch kommt
select max(dajjmt) as dd, hcsunr, hcsun2, hcsu22, hcdejj, hcdemm, hcdett
from datump
inner join hisscp on hcdejj = dajjjj and hcdemm = damm and hcdett = datt
where dadate>=(current_date-(select mon from xx) Months) and hcklas='K10'
group by hcsunr, hcsun2, hcsu22, hcdejj, hcdemm, hcdett



Ich habe diesmal > 18 Minuten bis zur Anzeige gebraucht!
bin vorzeitig im Navigator gewesen und sehe da diese Anzeige:

354

Da sieht man das er lange braucht, ja.
Wenn ich eine der Zeilen mit VE öffne, kommt eine Graphik die 4 mal "TABLE SCAN" beinhaltet.

und nun ? was hilft mir das?

Wenn ich das nicht beschleunige Köpfen die mich, 18 Minuten ...
da waren ja die 4 von gestern noch richtig super ...

Robi

[Robi]

Da es ohne die LF schneller ging habe ich die erstmal wieder gelöscht.
Dein Umbau mit der Datum Datei habe ich gelassen
Jetzt empfiehlt er mit ein LF auf die Datumsdatei das es bereits gibt!
Was ist da los?