View Full Version : sql durch empfolene zugriffe langsamer
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
andreaspr@aon.at
22-10-15, 09:04
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
@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
22-10-15, 16:09
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)
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.
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
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
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.
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?