View Full Version : SQL Performance
Die View sieht so aus:
CREATE VIEW PPGIDPV
(RSPD00, RSPF00, IITM00, JDSC00, QPR100, QPR200,
EANR00, IBON00, OABC00, OCOA00, IACG00, USPL00,
WCST00, PSTY00, STVL00, SUPO00, COSS00,
PRDT1N, ITTY1N,
PLNU1N, PLNG1N, PURU1N, BYGG1N, FXSU1N, OPRP1N, DISP1N,
OQUI1N, LOTI1N, IDRE1N, SRQF1N, BTFL1N, SPTT1N, PRCI1N,
QCFW1N, HDSP1N, CRPF1N, PRCP1N, GICP1N, STQF1N, STQE1N,
RTGN1N, RTSS1N,
ITMS1I,
TXT11G, TXT21G, TXT31G)
AS SELECT
RSPD00, RSPF00, IITM00, JDSC00, QPR100, QPR200,
EANR00, IBON00, OABC00, OCOA00, IACG00, USPL00,
WCST00, PSTY00, STVL00, SUPO00, COSS00,
PRDT1N, ITTY1N,
PLNU1N, PLNG1N, PURU1N, BYGG1N, FXSU1N, OPRP1N, DISP1N,
OQUI1N, LOTI1N, IDRE1N, SRQF1N, BTFL1N, SPTT1N, PRCI1N,
QCFW1N, HDSP1N, CRPF1N, PRCP1N, GICP1N, STQF1N, STQE1N,
RTGN1N, RTSS1N,
ITMS1I,
cast(
(case when t1.TEXT1G is null then ' ' else t1.text1g end)
as char(50)) as text1,
cast(
(case when t2.TEXT1G is null then ' ' else t2.text1g end)
as char(50)) as text2,
cast(
(case when t3.TEXT1G is null then ' ' else t3.text1g end)
as char(50)) as text3
FROM GIDPF300
LEFT OUTER JOIN GIDPF01N ON
ITNR1N = IITM00 AND
LGNT1N||SCPB1N = 'ATAT'
LEFT OUTER JOIN GIDPF01I ON
ITNR1I = IITM00 AND
LGNT1I||SCPB1I||CMPN1I = '000000'
LEFT OUTER JOIN GIDPF01g as t1 ON
t1.ITNR1G = IITM00 AND
t1.ITNR1G = IITM00 AND
t1.LGNT1G||t1.SCPB1G||t1.CMPN1G = '000000' AND
t1.TXTY1G = ' ' and t1.TXTP1G = 'IDS' and t1.LNGG1G = 'DE' AND
t1.posn1G = '0010'
LEFT OUTER JOIN GIDPF01g as t2 ON
t2.ITNR1G = IITM00 AND
t2.LGNT1G||t2.SCPB1G||t2.CMPN1G = '000000' AND
t2.TXTY1G = ' ' and t2.TXTP1G = 'IDS' and t2.LNGG1G = 'DE' AND
t2.posn1G = '0020'
LEFT OUTER JOIN GIDPF01g as t3 ON
t3.ITNR1G = IITM00 AND
t3.LGNT1G||t3.SCPB1G||t3.CMPN1G = '000000' AND
t3.TXTY1G = ' ' and t3.TXTP1G = 'IDS' and t3.LNGG1G = 'DE' AND
t3.posn1G = '0030'
Satzanzahl
GIDPF300 - 1,8 Millionen
GIDPF01N - 450.000
GIDPF01I - 320.000
GIDPF01G - 2,1 Millionen
Wenn jemand eine Idee hat, wie man optimieren kann - her damit.
Das einzige was im SQL "fix" ist, ist am Ende ORDER BY (aber auch wieder abhängig von der Eingabe: upper(JDSC00) oder IITM00) und neu "optimize for 20 rows".
Das ist keine Anwendung für eine VIEW wenn du auf die View auch noch einen Select losläßt.
Concats im Join führen fast IMMER zu einem Tablescan, was die Laufzeit erklärt.
Hier streikt auch der Optimizer, da erst eine temporäre Tabelle (bzw. Index) für die View gebildet werden muss und erst DANACH die Daten selektiert werden.
case when t3.TEXT1G is null then ' ' else t3.text1g end
Besser "coalesce(TTEXT1G, ' ')"
Ein Typecast ist da nicht erforderlich.
Und nun überlege dir, wie du an Stelle der View den Select im Programm direkt aufbaust (s.o.) udn gezielt die Where-bedingungen berücksichtigst.
Das ist keine Anwendung für eine VIEW wenn du auf die View auch noch einen Select losläßt.
Jetzt bin ich verwirrt!
Für mich war bis jetzt DAS der klassische Fall für eine View:
Ich habe
- mehrere Tables
- die ich zusammenhänge
- und über SQL-Statements kreuz und quer selektieren kann.
Oder habe ich da irgendwo etwas missverstanden?
Wenn eine View bereits WHERE enthält und Joins Typecasting (auch concat) enthalten ist das die denkbar schlechteste Lösung.
Bei kleinen Datenbeständen mag das noch angehen und performant sein.
Bei diesen Datenbeständen jedoch ist das die Katastrophe (wie du ja selber merkst).
Views sind dann sinnvoll, wenn klare 1:N (n=1-32)-Beziehungen über fixe Schlüssel vorhanden sind.
Alles andere ist dann eher Anwendungslogik.
Zumal über Views kein Index angelegt werden kann !
Allerdings:
Whereklauseln sind nicht unbedingt von Nachteil.
Und:
Zerlege die
t1.LGNT1G||t1.SCPB1G||t1.CMPN1G = '000000'
in
t1.LGNT1G = '00' and t1.SCPB1G='00' and t1.CMPN1G = '00'
Da wird dann direkt selektiert und nicht erst ein temporärer Index benötigt.
PS:
Wobei ich da keine Joinbeziehung erkennen kann.
Hallo,
erstmal würde ich alle nicht Verknüpfungen in den Join Bedingungen in die where Klausel aufnehmen.
Für die Join Felder werden in jedem Fall Indexe benötigt, ansonsten geht das immer in den Wind (ein Cross Join, schon ist man bei Milliarden von Sätzen!!!); wenn ich da nichts übersehen habe, dann landen die CAT Felder alle in der Where Klausel, nicht so toll, aber nicht tödlich. Bei dem order By ist der upper natürlich schädlich, den könnte man durch ein zusätzliches Feld (kann man durch Trigger pflegen, ohne Änderung der Anwendung, die verwendet eine View ohne das Feld) wegbekommen. Über dieses Feld muss natürlich ebenfalls ein Index da sein.
Wenn ich das richtig sehe, werden alle Dateien über denselben Key verjoint, sprich der Pessimizer sollte eigentlich in Abhängigkeit von der where Klausel (wie sieht die übrigens aus???) die Table mit der höchsten Selektivität nach links stellen, was bei vielen Treffern wegen dem Order by auf die erste problematisch werden kann. Dann könnte es einen Versuch wert sein die Sortierung "breiter" zu machen und über einen kompletten Key der ersten Table zu ziehen, oder per QAQQINI (force Join order) die erste Tabelle nach links zu zwingen.
Falls die beteiligten Tabellen "viele" (= etliche zig) Indexe haben sollte man auch mal ein Auge darauf haben, ob der Optimizer einen Timeout bekommt.
Für die relevanten (= die mit der höchsten Selektivität) Where Klausel Felder werden natürlich ebenfalls passende Indexe benötigt.
mfg
Dieter Bender
Die View sieht so aus:
CREATE VIEW PPGIDPV
(RSPD00, RSPF00, IITM00, JDSC00, QPR100, QPR200,
EANR00, IBON00, OABC00, OCOA00, IACG00, USPL00,
WCST00, PSTY00, STVL00, SUPO00, COSS00,
PRDT1N, ITTY1N,
PLNU1N, PLNG1N, PURU1N, BYGG1N, FXSU1N, OPRP1N, DISP1N,
OQUI1N, LOTI1N, IDRE1N, SRQF1N, BTFL1N, SPTT1N, PRCI1N,
QCFW1N, HDSP1N, CRPF1N, PRCP1N, GICP1N, STQF1N, STQE1N,
RTGN1N, RTSS1N,
ITMS1I,
TXT11G, TXT21G, TXT31G)
AS SELECT
RSPD00, RSPF00, IITM00, JDSC00, QPR100, QPR200,
EANR00, IBON00, OABC00, OCOA00, IACG00, USPL00,
WCST00, PSTY00, STVL00, SUPO00, COSS00,
PRDT1N, ITTY1N,
PLNU1N, PLNG1N, PURU1N, BYGG1N, FXSU1N, OPRP1N, DISP1N,
OQUI1N, LOTI1N, IDRE1N, SRQF1N, BTFL1N, SPTT1N, PRCI1N,
QCFW1N, HDSP1N, CRPF1N, PRCP1N, GICP1N, STQF1N, STQE1N,
RTGN1N, RTSS1N,
ITMS1I,
cast(
(case when t1.TEXT1G is null then ' ' else t1.text1g end)
as char(50)) as text1,
cast(
(case when t2.TEXT1G is null then ' ' else t2.text1g end)
as char(50)) as text2,
cast(
(case when t3.TEXT1G is null then ' ' else t3.text1g end)
as char(50)) as text3
FROM GIDPF300
LEFT OUTER JOIN GIDPF01N ON
ITNR1N = IITM00 AND
LGNT1N||SCPB1N = 'ATAT'
LEFT OUTER JOIN GIDPF01I ON
ITNR1I = IITM00 AND
LGNT1I||SCPB1I||CMPN1I = '000000'
LEFT OUTER JOIN GIDPF01g as t1 ON
t1.ITNR1G = IITM00 AND
t1.ITNR1G = IITM00 AND
t1.LGNT1G||t1.SCPB1G||t1.CMPN1G = '000000' AND
t1.TXTY1G = ' ' and t1.TXTP1G = 'IDS' and t1.LNGG1G = 'DE' AND
t1.posn1G = '0010'
LEFT OUTER JOIN GIDPF01g as t2 ON
t2.ITNR1G = IITM00 AND
t2.LGNT1G||t2.SCPB1G||t2.CMPN1G = '000000' AND
t2.TXTY1G = ' ' and t2.TXTP1G = 'IDS' and t2.LNGG1G = 'DE' AND
t2.posn1G = '0020'
LEFT OUTER JOIN GIDPF01g as t3 ON
t3.ITNR1G = IITM00 AND
t3.LGNT1G||t3.SCPB1G||t3.CMPN1G = '000000' AND
t3.TXTY1G = ' ' and t3.TXTP1G = 'IDS' and t3.LNGG1G = 'DE' AND
t3.posn1G = '0030'
Satzanzahl
GIDPF300 - 1,8 Millionen
GIDPF01N - 450.000
GIDPF01I - 320.000
GIDPF01G - 2,1 Millionen
Wenn jemand eine Idee hat, wie man optimieren kann - her damit.
Das einzige was im SQL "fix" ist, ist am Ende ORDER BY (aber auch wieder abhängig von der Eingabe: upper(JDSC00) oder IITM00) und neu "optimize for 20 rows".
(wie sieht die übrigens aus???)
Zum Beispiel so:
SELECT * FROM PPGIDPV WHERE
RSPD00 = 'ATAT ' and RSPF00 = 'J' and ITTY1N <> 'VO' and
QPR100 like '0301__03____________%' order by IITM00 optimize for 20 rows
oder
SELECT * FROM PPGIDPV WHERE
RSPD00 = 'ATAT ' and RSPF00 = 'J' and IITM00 >= 'H000015'
and ITTY1N <> 'VO' and UPPER(JDSC00) >= 'BA 148/2 '
order by upper(JDSC00) optimize for 20 rows
oder
SELECT * FROM PPGIDPV WHERE
RSPD00 = 'ATAT ' and RSPF00 = 'J' and
UPPER(JDSC00) like '%/2 LI%' order by upper(JDSC00) optimize for 20 rows
Die Cat-Anweisungen in der View habe ich bereits umgebaut. Wurde damit schneller.
Das die UPPER-Anweisung stört ist mir klar - kann aber leider die Datenbank nicht umbauen:mad:
Wenn ich beim "left join" die Beziehung in die Where-Klausel verlege, wird daraus in "inner join".
Hier allerdings sind in der Join-Beziehung Felder aus der linken Tabelle mit Konstanten an Stelle von Feldern auf der rechten Seite benannt.
Solche Bedingungen stellen keine Beziehung dar und gehören in die Where-Klausel.
Hallo,
wenn ich das richtig lese, sind die Felder der where Klausel alle aus der ersten Table.
Das erste der Beispiele sollte eigentlich schnell sein (< 1 sec), ansonsten liegt klar ein Problem mit Indexen vor, oder der Query Optimizer hat einen Schuss (was bei der famosen SQE recht häufig ist). Dann muss das ausanalysiert werden, bevor man sich mit weiterem beschäftigt.
Das zweite Beispiel hat durch den order by upper(...) eine Laufzeitkomponente, die von der Größe des Resultsets abhängt. So ganz schlimm dürfte das aber nicht sein, wenn ein Index über JODSC00 vorhanden ist (der logischerweise dieses Feld als erstes haben muss).
Das dritte Beispiel erzwingt durch das UPPER(JDSC00) like '%/2 LI%' in der Where Klausel einen Full Table scan, unabhängig von allen eventuell vorhandenen Indexen, das kann nicht schnell gehen!
Das mit der Datenbank, das kann EDV fachlich gesehen kein Problem sein, allenfals politisch hierarchisch:
- neue Datei GIDPF301 mit zusätzlichem Feld UJDSC00
- View GIDPF300 mit allen Feldern ausser UJDSC00
- Trigger an GIDPF301 anhängen der UJDSC00 mit upper(JDSC00) füllt
- Umbau deiner View
Angemerkt sei an dieser Stelle noch, dass das durchaus ein typischer Fall für eine View ist, die sind nämlich für den Programmierer (für den wirds einfacher), und nicht für die Datenbank (die wird dafür gekauft, dass sie das abkann).
Zum Beispiel so:
SELECT * FROM PPGIDPV WHERE
RSPD00 = 'ATAT ' and RSPF00 = 'J' and ITTY1N <> 'VO' and
QPR100 like '0301__03____________%' order by IITM00 optimize for 20 rows
oder
SELECT * FROM PPGIDPV WHERE
RSPD00 = 'ATAT ' and RSPF00 = 'J' and IITM00 >= 'H000015'
and ITTY1N <> 'VO' and UPPER(JDSC00) >= 'BA 148/2 '
order by upper(JDSC00) optimize for 20 rows
oder
SELECT * FROM PPGIDPV WHERE
RSPD00 = 'ATAT ' and RSPF00 = 'J' and
UPPER(JDSC00) like '%/2 LI%' order by upper(JDSC00) optimize for 20 rows
Die Cat-Anweisungen in der View habe ich bereits umgebaut. Wurde damit schneller.
Das die UPPER-Anweisung stört ist mir klar - kann aber leider die Datenbank nicht umbauen:mad:
Auch für das Upper/like im where gilt, dass das nicht so tragisch ist, wenn es vorher bereits Schlüsseleinschränkungen gibt:
where k1=w1 and k2=w2 and upper(k3) like '....'
Über K1 und K2 sollte dann natürlich ein Index vorhanden sein.
übrigens:
like '0301__03____________%'
==
like '0301__03%'
Die Bedingungen in der Verknüpfung LEFT OUTER JOIN GIDPF01g as t3 ON in die abschließende WHERE-Klausel zu verpacken funktioniert nicht ganz - meistens sind unter dieser Bedingung keine Sätze vorhanden. Darum auch - mittlerweile umgebaut - "coalesce(T3.text1g, ' ')."