SQL Performance

[Dirschl]

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".

[Fuerchau]

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.

[Dirschl]

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?

[Fuerchau]

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.

[BenderD]

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".

[Dirschl]

(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

[BenderD]

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

[Fuerchau]

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%'

[Dirschl]

übrigens:

like '0301__03____________%'
==
like '0301__03%'

Ist mir schon klar - war nur reine Bequemlichkeit beim programmieren - es können auch noch weiter hinten in dem Feld Einschränkungen kommen - dürfte aber von der Performance egal sein.

@BenderD
Mit dem Umbau hab ich kein fachliches Problem - ist mir schon klar wie das zu lösen ist - nur ich DARF NICHT!

[Fuerchau]

Wenn ich mir die View so anschaue, stellt sich mir die Frage ob tatsächlich "LEFT JOIN" benötigt wird.

Left join heißt ja, dass auf der rechten Seite durchaus Sätze fehlen dürfen, also NULL zurückkommt.

Die Bedingungen sehen allerdings mehr so aus, dass ein "inner join" besser erscheint.

Dann würde ich die Beziehung auch eher in die Where-Klausel legen:

select ...
from file1 a, File2 b
where a.key=b.key and ...

[Dirschl]

Left join heißt ja, dass auf der rechten Seite durchaus Sätze fehlen dürfen, also NULL zurückkommt.

Das Join auf GIDPF01G (3x) kann tatsächlich NULL zurückbringen - diese Sätze sind sehr oft nicht vorhanden!
Daher auch die Case-Abfrage auf NULL (mittlerweile auf "coalesce" umgebaut).

[BenderD]

@Baldur:
den left outer kann man nicht weglassen, das sind 1 zu 0 bis n Beziehungen zwischen der ersten Table und ihren Satelliten und von denen, die es gibt, will man nur bestimmte haben. Von daher war die Formulierung in der join Klausel durchaus adäquat und dies darf das Problem eigentlich nicht verursachen.

@Dirschl:
wielange läuft denn nun genau die Abfrage:
SELECT * FROM PPGIDPV WHERE
RSPD00 = 'ATAT ' and RSPF00 = 'J' and ITTY1N <> 'VO' and
QPR100 like '0301__03____________%' order by IITM00 optimize for 20 rows
und wenn das länger als 1 sec dauert, welche Indexe hast du genau auf der RSPD00 und was wird für ein Access Plan generiert (sieht man unter debug, oder mit dem DBMON)?

Dieter Bender

PS: die Abfragen mit Like und Wildcard % am Anfang, die kann man eigentlich nur mit Hardware (Prozessor und parallel Database Feature, oder Platte für zusätzliche Riesen Indexe) flott machen.

Wenn ich mir die View so anschaue, stellt sich mir die Frage ob tatsächlich "LEFT JOIN" benötigt wird.

Left join heißt ja, dass auf der rechten Seite durchaus Sätze fehlen dürfen, also NULL zurückkommt.

Die Bedingungen sehen allerdings mehr so aus, dass ein "inner join" besser erscheint.

Dann würde ich die Beziehung auch eher in die Where-Klausel legen:

select ...
from file1 a, File2 b
where a.key=b.key and ...