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:



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?

[Fuerchau]

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.

[cbe]

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