SQL Performance

[Fuerchau]

Da Du von einer View sprichst ergibt sich da auch das Problem.

Der Optimizer nimmt die View als Basis für die betroffenen Dateien, aber löst den SQL dann intern ggf. neu auf.

Wenn du so dynamisch bist, kann es mitunter besser sein, den SQL komplett im Programm mit Angabe der Tabellen an Stelle der View zu verwenden.

Auch solltest du dir im klaren darüber sein, welche Joinbeziehungen über Schlüssel mit eindeutiger Ausprägung vorhanden sind.
Es gibt schon Probleme, wenn zwischen gezont und gepackt gejoint wird.
Da hilft dann auch tatsächlich kein Index.
Das selbe gilt auch für cast's oder diverse Berechnungen (substr, o.ä.).

Manchmal wird es ganz schön schnell, wenn man mit CommonTableExpression/Subqueries bereits Vorselektionen treffen kann und dann erst joint.

Es gibt da einfach zu viele Möglichkeiten.

[Dirschl]

@fuerchau

Die Anweisung: "optimize for 20 rows" bringt schon einmal eine leichte Verbesserung - Danke!

Kannst du mir bitte sagen (Syntax) wie ich das Subquery in eine View vor dem Join einbaue.

[Fuerchau]

Ich lese immer das Wort View !
Hast du nun tatsächlich eine View oder meinst du damit nur deinen Cursor ?

Ansonsten geht folgendes (Siehe SQL-Handbuch):

with
xTable1 as (select .... from ... where ....)
,xTable2 as (....)

select ... from mytable
where ...
inner join xTable1 on ...
left join xTable2 on ...

Besonderes Augenmerk ist darauf zu geben, dass diese Subqueries bereits Where-Bedingungen zur Einschränkung aufweisen sollten.
Ggf. kann im SubQuery ein Typecasting zum späteren Join gebildet werden, z.B. "digits(mynum) as mynum" o.ä., manchmal hilfts sogar.

Alternativien auch:

select ...
from myfile
inner join (select .... where ...) on ...
where ...

Es gibt da aber insofern Unterschiede, das Subqueries Full-Selects (mit Joins) sein können, Subselects aber keine Joins haben dürfen.

Ggf. hilft auch der skalare Subselect, wenn genau 1 Wert mit 1 Zeile oder NULL möglich ist:

select f1, f2, (select sum(mynum) from ... where ...) as mysum, f3, f4 ...

[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!