-
sql durch empfolene zugriffe langsamer
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?
Code:
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
Code:
* 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
Das Notwendige steht über dem technisch machbaren.
(klingt komisch, funktioniert aber!)
-
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.
-
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
Code:
...
-- 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
Code:
-- 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
Das Notwendige steht über dem technisch machbaren.
(klingt komisch, funktioniert aber!)
-
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
Das Notwendige steht über dem technisch machbaren.
(klingt komisch, funktioniert aber!)
-
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???
Das Notwendige steht über dem technisch machbaren.
(klingt komisch, funktioniert aber!)
-
Zitat von 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
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
-
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.
-
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
Das Notwendige steht über dem technisch machbaren.
(klingt komisch, funktioniert aber!)
-
]Primäre Datei = Datumsdatei ...
sieht nun so aus
Code:
-- 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:
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
Das Notwendige steht über dem technisch machbaren.
(klingt komisch, funktioniert aber!)
-
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?
Das Notwendige steht über dem technisch machbaren.
(klingt komisch, funktioniert aber!)
-
Welches Release hast du denn?
Es gibt manchmal auch Indexprobleme, wenn der Feldtyp nicht so ganz passt.
Auch der Autocast (ab V6) trifft da nicht immer.
Wichtig ist da die Unterscheidung wischen Decimal (gepackt) und Numeric (Zoned).
Dem RPG ist das bei direkten Zugriffen fast egal. Der Compiler meckert da manchmal die Keylist an, beim ILERPG gibts sogar wenig Gemecker bei z.B. "chain (F1:F2:F3) MyFile".
SQL mag hier nicht immer.
Da man seine Dateien früher nicht so konsistent angelegt hat wird schon mal gerne zwischen zoned und packed gemischt.
Hier musst du noch mal prüfen und ggf. per Cast den Quell-Typ an den Ziel-Typ anpassen.
Dies gilt besonders für berechnetet Felder da die nun mal gerne sehr groß generiert werden.
JJJJ*10000 + MM*100 + TT entspricht nicht dem Dec(8, 0) für den Join oder Where.
Also per cast(JJJJ*10000 + MM*100 as decimal(8, 0))" oder cast(JJJJ*10000 + MM*100 as numeric(8, 0))" anpassen.
Dies trifft auch auf Hostvariablen zu.
Der Typ der Hostvariablen sollte zum abgefragten Feld passen, damit ein Index verwendet werden kann.
Dies kommt z.T. halt noch aus V5-Zeiten um SQL's zu optimieren.
Ich habe diverse Konstrukte schrittweise aufgebaut und dabei dann eben die Performance beobachtet.
-
Hallo,
ich weiß nicht, ob Andreas das auch gemeint hat mit
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.
Wenn ich eine derart komplexe View habe, und diese dann mehrfach mit div. weiteren Abfragen, Counts, etc. verwenden soll, dann würde ich statt dessen immer eine temporäre Zwischen-PF befüllen, und dann über diese die weitere Verarbeitung machen.
Ob diese in QTEMP angelegt wird, oder ob sie schon irgendwo existiert (Parallelverarbeitung beachten), kann man dann je nach Fall entscheiden.
Für diese Arbeitsdatei könnte man ggf. auch Indexe anlegen, schon vorab als LF, oder bei QTEMP im Nachhinein - hängt von den Gegebenheiten wie z.B. Datenmenge ab.
Gruß, Christian
Similar Threads
-
By dschroeder in forum NEWSboard Programmierung
Antworten: 14
Letzter Beitrag: 04-04-14, 07:32
-
By NEWSolutions Redaktion in forum NEWSolutions artikel
Antworten: 0
Letzter Beitrag: 02-11-13, 10:53
-
By Newbie in forum IBM i Hauptforum
Antworten: 3
Letzter Beitrag: 04-07-02, 08:19
-
By Kent in forum IBM i Hauptforum
Antworten: 4
Letzter Beitrag: 19-06-01, 10:45
-
By becama in forum IBM i Hauptforum
Antworten: 3
Letzter Beitrag: 07-04-01, 09:08
Berechtigungen
- Neue Themen erstellen: Nein
- Themen beantworten: Nein
- You may not post attachments
- You may not edit your posts
-
Foren-Regeln
|
Erweiterte Foren Suche
Google Foren Suche
Forum & Artikel Update eMail
AS/400 / IBM i
Server Expert Gruppen
Unternehmens IT
|
Kategorien online Artikel
- Big Data, Analytics, BI, MIS
- Cloud, Social Media, Devices
- DMS, Archivierung, Druck
- ERP + Add-ons, Business Software
- Hochverfügbarkeit
- Human Resources, Personal
- IBM Announcements
- IT-Karikaturen
- Leitartikel
- Load`n`go
- Messen, Veranstaltungen
- NEWSolutions Dossiers
- Programmierung
- Security
- Software Development + Change Mgmt.
- Solutions & Provider
- Speicher – Storage
- Strategische Berichte
- Systemmanagement
- Tools, Hot-Tips
Auf dem Laufenden bleiben
|
Bookmarks