sql durch empfolene zugriffe langsamer

[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

[andreaspr@aon.at]

Die Aufteilung könnte wie folgt aussehen ...

Dieses (eigentlich sehr simple) SQL kann auf Grund von riesen Datenmengen und entsprechte auswahlkriterien der Key-Felder enorm lang dauern:

SELECT * FROM TAB1
WHERE (SP1 = 'XXXX' OR SP2 = 'YYY') 
AND SP3 <> '1'
AND SP3 <> '2' 
AND SP4 <> 'M' 
ORDER BY SP5 DESC
FETCH FIRST 102 ROWS ONLY

Mit einer Aufteilung kann die DB die Anfrage besser verarbeiten und diverse Indices gezielt, und vor allem auch Parallel, verarbeiten:

WITH p AS (
SELECT id FROM TAB1 
WHERE SP1 = 'XXXX' 
AND SP3 <> '1' 
AND SP3 <> '2' 
AND SP4 <> 'M' ),
 
v AS (
SELECT id FROM TAB1 
WHERE SP2 = 'YYY' 
AND SP3 <> 'L' 
AND SP3 <> 'S' 
AND SP3 <> '1' ) ,
 
a AS (
SELECT id FROM 
    (SELECT id FROM  p 
     Order By SP5 Desc 
     FETCH FIRST 102 ROWS ONLY) T1
UNION
SELECT id FROM 
    (SELECT id FROM v 
     Order By SP5 Desc 
     FETCH FIRST 102 ROWS ONLY) T2
)
 
SELECT * FROM TAB1 WHERE id IN (SELECT id FROM a)
Order By SP5 Desc
FETCH FIRST 102 ROWS ONLY;

Es sieht zunächst mal etwas viel aus, ist es aber im grunde nicht.
Und ein weiterer Vorteil mit WITH ist, dass du jede Abfrage einzeln testen und optimieren kannst.
Sicher gibt es auch noch andere Möglichkeiten, diese hat für mich jedoch perfekt geklappt.

lg Andreas

[Robi]

@andreas
Im Prinzip klar, aber im konkreten Bsp sehe ich keine Optimierung

Die View:

create view hissc#v01 as(                                                      
-- wieviele Monate?                                                            
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 = 'FORMMK' and rkeycd = 'BONCNT'),                 
-- aktuellste Satz?                                                            
    yy  as (                                                                   
-- 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                   
-- having max(dadate) >= (current_date - (select mon from xx) Months)),        
                                                                     ),        
-- hisscp satz da ich ja den Score brauche                                     
    a as (                                                                     
   select dd as d, a.hcsunr, a.hcsu22, a.hcsun2, hckate as maxscore           
      from hisscp a                                                            
      inner join yy on yy.hcsunr = a.hcsunr and yy.hcsun2 = a.hcsun2 and       
                       yy.hcsu22 = a.hcsu22 and yy.hcdejj = a.hcdejj and       
                       yy.hcdemm = a.hcdemm and yy.hcdett = a.hcdett           
      where a.hcklas = 'K10')                                                  
                                                                               
 -- hisscp verknüpft mit aktenp und mandap und makesp                          
 select azkey1, azkey2, azkey3, d, maxscore, dec(case when azsun2 = 3 then 2   
                                                       else 1 end, 1, 0) as cnt,
        azmk, azmst, makzfo, azdwit, azdwim, azdwij, azfonr,                   
        dec(azdwij*10000+azdwim*100+azdwit, 8, 0) as dawivo, mkform            
   from   aktenp, a, mandap, makesp                                            
   where  a.hcsu22 = azsun2 and a.hcsunr = azsunr and azmaan = maaunr and      
            azmanr = mamdnr and     azmk = mkkett and  azmst = mkstuf and      
            mkmkgr = mamkgr and     azmk <> 99 and azmk <> 999                 
                                                                               
 -- und nun noch die, die keinen gültigen score haben                          
 union all                                                                     
 select azkey1, azkey2, azkey3, dec(20491231, 8, 0) as d,    
                       '00000' as maxscore, dec(1, 1, 0)                as cnt,
       azmk, azmst, makzfo, azdwit, azdwim, azdwij, azfonr,                    
       dec(azdwij*10000+azdwim*100+azdwit, 8, 0) as dawivo, mkform             
  from   aktenp left outer join a on azsunr = a.hcsunr and azsun2 = a.hcsun2   
  inner join mandap on azmanr = mamdnr and azmaan = maaunr                     
  inner join makesp on azmk = mkkett and azmst = mkstuf and mkmkgr = mamkgr    
  where a.hcsunr is null and azmk <> 99 and azmk <> 999                        
  );                                                                           

und die Abfrage im Pgm

C/EXEC SQL                                              
C+ SET :AMSFO_E =(SELECT COUNT(*) FROM HISSC#V01 WHERE  
C+     (AZFONR = :MASN  OR MKFORM = :MASN)              
C+      AND DAWIVO <= :DTV  AND MAKZFO = 1 AND          
C+      MAXSCORE <> '00000' AND AZFONR <> 712)     
C/END-EXEC


umgebaut auf 

with a as (sELECT dec(COUNT(*), 8, 0) as c FROM HISSC#V01 WHERE    
           AZFONR = :masn AND DAWIVO <= :DTV  AND MAKZFO = 1 AND 
           MAXSCORE <> '00000' AND AZFONR <> 712),                 
     b as (sELECT dec(COUNT(*), 8, 0) as c FROM HISSC#V01 WHERE    
           mkform = :masn AND DAWIVO <= 20151022  AND MAKZFO = 1 AND 
           MAXSCORE <> '00000' AND AZFONR <> 712)                  
select a.c+b.c from a, b

braucht deutlich länger

@Baldur

na ja
Decimal (gepackt) und Numeric (Zoned)
ist hier bunt durcheinander
Das kann ich nochmal versuchen.

Bin z.Zt. knapp unter 4 Minuten und habe von den insgesamt 26 empfohlen indices nur 5 angelegt.


@cbe
hilft hier wahrscheinlich nicht,
view in pf,
mehrere Lf auf pf
count
lf löschen
pf löschen

Die View hat um die 1,15 Mio Sätze
allein der count(*) ohne jeden Where dauert mehrere Minuten

Danke und Gruß
Robi

[andreaspr@aon.at]

Ich sehe hier 5-6 queries die zu einer großen gebaut werden.
Kann sein, dass ich was überlesen habe aber jetzt müsstest du analysieren welcher Teil so lange benötigt.
Hier hilfts enorm das SQL über den Visual Explain unter die Lupe zu nehmen.
Dort kannst du schön sehen, wo genau die Zeiten in die höhe schießen.
Sobald du das Problem gefunden hast kann man entsprechend vorgehen.

Mich würde da auch die Zeiten von diesem Abschnitt interessieren:

current_date-(select mon from xx)

[Fuerchau]

Noch mal zur Erklärung:

Eine "With"-Table ist nur eine vereinfachte Schreibweise und bedeutet nicht, dass tatsächlich ein Tabelle aufgebaut wird.
D.h, dass die XX-Tabelle u.U. mit jedem Einzelsatz wieder abgefragt wird, was die Anzahl zu verarbeitender Datensätze drastisch erhöht.
Das Ergebnis der XX-Tabelle ist für die Gesamtabfrage ja statisch, dieses kann man also einmalig vorher abfragen und in eine echte temporäre (QTEMP)-Tabelle ablegen.

Was mir auch noch auffällt ist, dass du die Gesamtabfrage ja je Subfile-Satz ggf. auch Mehrfach ausführst.
Dies führt natürlich dazu, dass du, selbst wenn die Abfrage nur 2 Sekunden dauert, bei 24 Abfragen halt fast 1 Minute zusammen kommt.
Da kannst du nicht mehr viel optimieren, da dies ja nur für die Einzelabfrage geht und du hier gerne unter 100ms kommen möchtest.

Da SQL aber Resultset-orientiert ist, frage ich mich, ob du die Abfrage nicht insgesamt so gestalten kannst, dass deine Subfile insgesamt gefüllt werden kann.
Selbst wenn dann die Abfrage noch immer 10 - 30 Sekunden benötigt, füllst du eben die Subfile in 10 - 30 Sekunden.

Viele Abfragen für eine Liste dauern halt auch mit SQL.

[B.Hauser]

Nur mal so eine oder zwei ganz dumme Frage:

Die Indices, die Du erstellt hast sind klassische Binary Radix Tree Indices, bei denen die Schlüssel-Werte Orignal-Spalten sind, d.h. Du hast keine derived oder sparsed Indices (mit zusätzlichen Spalten und/oder WHERE-Bedingungen) erstellt?
Evtl. bringen solche zusätzlichen Indices eine Performance-Verbesserung (allerdings muss die Syntax exakt übereinstimmen, ansonsten kann der Query Optimizer nicht zuordnen (, d.h. bei Spalten-Definition mit azdwij*10000+azdwim*100+azdwit wird bei azdwit + asdwim*100 + asdwij*10000 kein Index verwendet.

Hast Du Encoded Vector Indices erstellt (Mit neuen Spalten-Definitionen und/oder Where-Bedingungen) und vor allem mit Include-Anweisungen? Solche Sachen werden vom Advisor i.d.R. nicht vorgeschlagen, können jedoch den Zugriff beträchtlich beschleunigen.

Birgitta

[Robi]

Moin zusammen,

Ist übrigens V7R1 mit mindestens TR4

@Andreas
da das Statement in der View ist, finde ich es nicht im Visual explain
(strdbmon mit *detail, call pgm, enddbmon --> sql performance monitor)

Der Wert der dort geholt wird, ist ja 'einmalig'. Ich habe das mal rausgenommen und durch
den z.Zt. gültigen Wert 'fix' ersetzt.
Bringt rund 20 Sekunden.
Bin nun im besten Fall also bei rd. 3 Minuten

Was muß ich machen um die Zugriffszeit zu sehen
Kann ich irgendwo den Dateinamen sehen?


@Baldur

Das Ergebnis der XX-Tabelle ist für die Gesamtabfrage ja statisch, dieses kann man also einmalig vorher abfragen und in eine echte temporäre (QTEMP)-Tabelle ablegen.

Verstehe ich nicht! der Wert liegt ja in einer Datei!
Wie kann ich Ihn verwenden und dabei nur 1 mal lesen?
(ich dachte das erreiche ich mit dem with)

Da SQL aber Resultset-orientiert ist, frage ich mich, ob du die Abfrage nicht insgesamt so gestalten kannst, dass deine Subfile insgesamt gefüllt werden kann.

Da stehe ich auf dem Schlauch, wie meinst du das?
hast du ein kleines Bsp?


@Birgitta
Ich habe 'ganz normale' LF angelegt mit den Vorgeschlagenen Keys
Satzformat des LF = Satzformat des PF, also alle Felder
Ohne Select oder Ommit
(Habe im Hinterkopf, das LF mit select und ommit vom Optimizer ignoriert werden!?)


EVI macht keine Sinn, da die Datei 'lebt' und sich täglich erweitert

die jj*1000+mm*100+tt Geschichte ist doch bei der Satzauswahl und bei der Verknüpfung nicht mehr drin, da hab ich doch Balurs DATUMs Datei verwendet!

Robi