sql durch empfolene zugriffe langsamer

[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

[Fuerchau]

Mit dem "with" fallen leider viele rein.
Es ist nur eine andere Schreibweise für

select * from (select bla from myfile) xx

Dies kann ebenso auch beim "join (select blablla from myfile2) b on ..." verwendet werden.
Solange der Optimizer nicht behauptet eine temporäre Tabelle zu erstellen, wird die Information immer wieder neu erstelt. Die Quelldaten liegen dann ab dem 2. Mal nur schon im Cache wenn du Glück hast und sie nicht verdrängt wurden.

Oben schreibst du:
"Das Pgm zeigt 24 Zeilen, je Zeile werden 4 Sql ausgeführt."

Dabei schlackere ich schon mit den Ohren.
Kann man nicht ein SQL stricken, dass das Ergebnis der 24 Zeilen direkt erzeugt?
Dann kann ich per Fetch halt direkt Zeile per Zeile in die SFL ausgeben.

Wenn ein SQL mal 1 Sekunde dauert, dann ist eben 24 * 4 = 96 Sekunden macht per se 1,5 Minuten.
Deine einzelnen SQL's sind aber halt langsamer und die gilt es zu optimieren und ggf. ein dickeres SQL zu bilden.

[Robi]

Oben schreibst du:
"Das Pgm zeigt 24 Zeilen, je Zeile werden 4 Sql ausgeführt."

Dabei schlackere ich schon mit den Ohren.
Kann man nicht ein SQL stricken, dass das Ergebnis der 24 Zeilen direkt erzeugt?
Dann kann ich per Fetch halt direkt Zeile per Zeile in die SFL ausgeben.

Z.Zt. habe ich die select count(*) ja als set :wert = (select count(*) ... drin

aus den 4 je Zeile hab ich schon 2 je Zeile gemacht

Du meinst ich kann mein Ziel mit Prepare und group erreichen?
Ok, das versuch ich mal


Zu dem With

d.h. ich kann auf das with verzichten und an der Stelle

where dadate>=(current_date-(select mon from xx) Months)

auch gleich

where dadate>=(current_date-(
select dec(substr(inhacd, 1, 2), 2, 0)                       
              from   cbuchp01                                                  
              where  finrcd = 0 and sprccd = 'D' and sakzcd = '6' and          
                     sartcd = 'FORMMK' and rkeycd = 'BONCNT')) Months)

schreiben?

[B.Hauser]

SQL generiert so gut wie keine temporären Dateien (auch nicht für CTEs oder verschachtelte Sub-Selects!). Die Erstellung und Verarbeitung von temporären Dateien ist viel zu aufwändig. SQL hat ganz andere weit performantere Methoden als temporäre Dateien zu bilden.

Was wir hier tun ist reine Spekulation!
Ohne die aktuellen Daten zu kennen und ohne zu wissen welche Indices vorliegen, stochern wir nur im Trüben und raten uns einen ab.

Wie Andreas schon sagt, es muss analysiert werden. Lass die Abfrage über Visual Explain laufen und analysiere im Anschluss die einzelnen Knoten.

EVIs und Derived/Sparsed Indices machen durchaus Sinn! Aber ob und welche in der gezeigten Abfrage sinnvoll sind, lässt sich anhand der Abfrage ohne Daten nicht sagen!
Ich würde z.B. als erstes einen Index für die 1. CTE anlegen, mit dem dec(substr(inhacd, 1, 2), 2, 0) und den in den WHERE-Bedingungen angegebenen Spalten als Schlüssel. Für die folgenden CTEs sollte damit ein Index Only Access auf diese Tabelle möglich sein. ... und dann weiter sehen.

"Normale" Indices sollten nicht mehr als DDS beschriebene logische Dateien, sondern als echte Indices angelegt werden. (DDS ist "stabilized" und SQL Indices haben per Default eine größere Page Size).

Birgitta

[andreaspr@aon.at]

Mit WITH hast du viele vorteile. Angefangen von der Lesbarkeit bis hin die einzelnen Sub-Queries einfacher zu testen.
Es ist aber ähnlich wie bei einer View.
Wenn du das ganze SQL in eine View packst und dann ein Select * From View machst ändert sich auch nichts.
Einen Performance gewinn kannst du dann erhalten, wenn du es so aufsplittest wie ich es oben im Beispiel beschrieben habe.

Über das Forum ist es ab einer gewissen Stelle schwierig zu helfen.
In solchen Situationen biete ich bei meinen Kunden auch unterstützung via Telefon & TeamViewer an.

Im Visual Explain solltest du alles finden können, man muss halt nur wissen wo man suchen muss.
Es ist zugegeben anfangs nicht ganz einfach durchzublicken, aber es ist eines der wichtigstens Werkzeuge bei der Analyse

[Fuerchau]

Mir wäre lieber, wenn SQL viel öfters temporäre Tabellen erstellen würde und auch Indizes auf diese erstellt, dann müsste ich dies nicht dauernd selber machen.
Durch die ständige Wiederholung der Subselects auf der Einzelsatzebene ist es wirklich effektiver ein Zwischenergebnis mit Index zu erstellen als alle in ein dickes SQL zu packen.
Laufzeiten von Minuten sinken da oft auf Sekunden.

Ich habe schon früher temporäre Tabellen vorab erstellt, was heute ja per "Create as Select" viel einfacher ist.
Selbst wenn das tausende Sätze gibt, dauert das häufig unter einer Sekunde.
Mit anschließendem Index und verjoinen auf andere (auch temporäre) Daten gewinnt man enorm.

Der Optimizer kann ja auch nur raten was man eigentlich will.
Also gedanklich manchmal einen oder 2 Schritt/e zurück und dann noch mal.