SQL Performance

[Dirschl]

Hallo

Ich habe folgendes Problem:

Ich habe in einem RPGIV Programm ein dynamisches SQL über eine sehr komplexe View gelegt.

Das Select-Statement wird über ein externes Programm aufgrund der Eingabeparameter gefüllt.

Funktioniert soweit ganz gut.

ABER: Das Performance-Verhalten im Programm schwankt zwischen 6 und 110 Sekunden (bei der gleichen Auswahl).

Führe ich das SQL-Statement interaktiv aus, liegt die Laufzeit immer bei 4-5 Sekunden.

Diverse Indexe die mir vorgeschlagen wurden, habe ich auch angelegt - ändert aber nichts - z.T. werden sie sogar ignoriert.

Hat jemand eine Erkärung/Lösung für das Problem (110 Sekunden kann ich einem Anwender nicht als Wartezeit zumuten)?

[Fuerchau]

STRSQL optimiert da leider anders für die schnelle Anzeige.

Ggf. hilft "optimize for n rows" noch weiter, ansonsten halt die Gründe über das Ablehnen von Indizees genauer analysieren oder das SQL umbauen.

[BenderD]

Hallo,

der Erklärungen könnten da viele hinter stecken, aber die Infos sind doch a weng dürftig...

mfg

Dieter Bender

Hallo

Ich habe folgendes Problem:

Ich habe in einem RPGIV Programm ein dynamisches SQL über eine sehr komplexe View gelegt.

Das Select-Statement wird über ein externes Programm aufgrund der Eingabeparameter gefüllt.

Funktioniert soweit ganz gut.

ABER: Das Performance-Verhalten im Programm schwankt zwischen 6 und 110 Sekunden (bei der gleichen Auswahl).

Führe ich das SQL-Statement interaktiv aus, liegt die Laufzeit immer bei 4-5 Sekunden.

Diverse Indexe die mir vorgeschlagen wurden, habe ich auch angelegt - ändert aber nichts - z.T. werden sie sogar ignoriert.

Hat jemand eine Erkärung/Lösung für das Problem (110 Sekunden kann ich einem Anwender nicht als Wartezeit zumuten)?

[B.Hauser]

wie Dieter schon sagt, die Hinweise sind dürftig.
Aber ...

Dynamisches SQL ist grundsätzlich nicht die beste Lösung. Besonders dann nicht, wenn das gleiche SQL-Statements mehrfach nur mit unterschiedlichen Variablen-Werten aufbereitet wird.

Damit nimmst Du dem Optimizer jegliche Möglichkeit einen einmal erstelleten Daten Pfad (ODP) wiederzuverwenden. Das heißt bei jeder Ausführung muss nicht nur die komplette Optimierung ausgeführt werden, sondern es muss zusätzlich noch eine Syntax-Prüfung des Strings und eine Konvertierung des Strings in ein ausführbares SQL-Statement erfolgen.

Die Optimierung als solches ist der zeitaufwändigste Prozess bei der ganzen Ausführung:

• Ein Access Plan muss gebildet, oder zumindest validiert werden. Bei statischem SQL wird der letzte Access Plan im Programm-Objekt selber gespeichert und kann mit PRTSQLINF angezeigt werden. Bei erneuter Ausführung kann dieser Access Plan geprüft werden. Bei dynamischem SQL werden keine Access Plans im Programm-Objekt gespeichert, d.h. sofern die Ausführung nicht mit der SQL Query Engine (SQE) erfolgt, bei der Access Plans im systemweiten SQE Plan Cache gespeichert werden und damit validiert werden kann, muss der Access Plan bei jeder Ausführung komplett neu gebildet werden:
  
  • das SQL-Statement muss analysiert und
  • gegebenenfalls umgeschrieben werden,
  • alle Zugriffswege müssen bewertet werden,
  • es muss entschieden werden ob und welche temporären Objekte (z.B. Hash Tables, relative record Listen, Indices, Datenstrukturen ...) gebildet werden müssen.
• Erst nachdem ein aktueller Access Plan vorliegt, kann der Datenpfad geöffnet werden, d.h. die temporären Objekte werden gebildet und mit Hilfe der im Access Plan festgelegten Zugriffswege mit Daten gefüllt.

Nach der ersten Ausführung eines SQL Statements wird der ODP immer wieder komplett gelöscht. War der ODP wiederverwendbar, wird beim zweiten Durchlauf der im Job Cache gesicherte Access Plan validiert und der ODP erneut geöffnet. Nach dem zweiten Durchlauf bleibt der ODP geöffnet, d.h. bei der Ausführung des OPEN-Befehls werden lediglich die Daten in den temporären Objekten aktualisiert.

Achtung: Man sollte die Option CLOSQLCSR (im Compile Command) nicht verändern. Der Default-Wert ist *ENDACTGRP, d.h. der full close, also das Löschen des ODPs erfolgt mit Beendigung der Aktivierungsgruppe. Solange die Aktivierungsgruppe geöffnet ist, kann ein ODP wiederverwendet werden. Bei der Option *ENDMOD, wird der ODP komplett gelöscht, sobald die Ausführung des Moduls beendet wurde. Damit muss bei jedem Durchlauf ein FULL OPEN, also die komplette Optimierung erfolgen.

Übrigens: Auch das Ändern der Bibliotheksliste führt bei unqualifizierten Zugriffen auf physische Dateien/Tabellen mittels SQL zu einem Hard Close der ODPs.

Birgitta

[BenderD]

wobei die zeitlichen Größenordnungen verschiedener Effekte sehr unterschiedlich sind (folgend ein paar meiner Erfahrungswerte, nicht ohne Bauch):

Syntaxcheck: millisekunden
Optimizer: hundertstel bis zehntel Sekunden
ODP Neuerstellung: bei vorhandenem Index milli sec
ODP Neuerstellung: bei erstelltem Index siehe temp Index
Open Close bei vorhandem Zugriffsweg unter der Messbarkeit
Hard Close: millisec (wird fast nur bei disconnect wirksam und da immer)

full Table scan: pro Satz sub millisec mal Anzahl der Sätze (können Minuten und mehr sein)
Index Aufbau ohne Mitbenutzung: > full Table scan
Index Aufbau mit Mitbenutzung: << full Table scan

mfg

Dieter Bender

wie Dieter schon sagt, die Hinweise sind dürftig.
Aber ...

Dynamisches SQL ist grundsätzlich nicht die beste Lösung. Besonders dann nicht, wenn das gleiche SQL-Statements mehrfach nur mit unterschiedlichen Variablen-Werten aufbereitet wird.

Damit nimmst Du dem Optimizer jegliche Möglichkeit einen einmal erstelleten Daten Pfad (ODP) wiederzuverwenden. Das heißt bei jeder Ausführung muss nicht nur die komplette Optimierung ausgeführt werden, sondern es muss zusätzlich noch eine Syntax-Prüfung des Strings und eine Konvertierung des Strings in ein ausführbares SQL-Statement erfolgen.

Die Optimierung als solches ist der zeitaufwändigste Prozess bei der ganzen Ausführung:

• Ein Access Plan muss gebildet, oder zumindest validiert werden. Bei statischem SQL wird der letzte Access Plan im Programm-Objekt selber gespeichert und kann mit PRTSQLINF angezeigt werden. Bei erneuter Ausführung kann dieser Access Plan geprüft werden. Bei dynamischem SQL werden keine Access Plans im Programm-Objekt gespeichert, d.h. sofern die Ausführung nicht mit der SQL Query Engine (SQE) erfolgt, bei der Access Plans im systemweiten SQE Plan Cache gespeichert werden und damit validiert werden kann, muss der Access Plan bei jeder Ausführung komplett neu gebildet werden:
  
  • das SQL-Statement muss analysiert und
  • gegebenenfalls umgeschrieben werden,
  • alle Zugriffswege müssen bewertet werden,
  • es muss entschieden werden ob und welche temporären Objekte (z.B. Hash Tables, relative record Listen, Indices, Datenstrukturen ...) gebildet werden müssen.
• Erst nachdem ein aktueller Access Plan vorliegt, kann der Datenpfad geöffnet werden, d.h. die temporären Objekte werden gebildet und mit Hilfe der im Access Plan festgelegten Zugriffswege mit Daten gefüllt.

Nach der ersten Ausführung eines SQL Statements wird der ODP immer wieder komplett gelöscht. War der ODP wiederverwendbar, wird beim zweiten Durchlauf der im Job Cache gesicherte Access Plan validiert und der ODP erneut geöffnet. Nach dem zweiten Durchlauf bleibt der ODP geöffnet, d.h. bei der Ausführung des OPEN-Befehls werden lediglich die Daten in den temporären Objekten aktualisiert.

Achtung: Man sollte die Option CLOSQLCSR (im Compile Command) nicht verändern. Der Default-Wert ist *ENDACTGRP, d.h. der full close, also das Löschen des ODPs erfolgt mit Beendigung der Aktivierungsgruppe. Solange die Aktivierungsgruppe geöffnet ist, kann ein ODP wiederverwendet werden. Bei der Option *ENDMOD, wird der ODP komplett gelöscht, sobald die Ausführung des Moduls beendet wurde. Damit muss bei jedem Durchlauf ein FULL OPEN, also die komplette Optimierung erfolgen.

Übrigens: Auch das Ändern der Bibliotheksliste führt bei unqualifizierten Zugriffen auf physische Dateien/Tabellen mittels SQL zu einem Hard Close der ODPs.

Birgitta

[Dirschl]

Dynamisches SQL aus einem ganz einfachen Grund:

In der View befinden sich 46 Felder aus 6 physischen Dateien, die - je nach Anforderung - in unterschiedlichsten Kombinationen selektiert werden.

Die View beinhaltet dzt 1,8 Millionen Sätze.

Die Ablehnung der Indexe erfolgt übrigens immer mit Code '4' - braucht zu viel Zeit - gleichzeitig wird ein Index mit den gleichen Feldern immer wieder empfohlen.

Bei der Maschine handelt es sich um eine 570 mit 4 Prozessoren und 64 GB Hauptspeicher - also kein langsamer Rechner.

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

[BenderD]

Hallo,

der Knackpunkt sind nicht die Felder, sondern die Join Bedingungen, where Klauseln und order by Kriterien. Wie kommst du bei 6 Joins auf die 1,8 Millionen Sätze? wenn das tatsächlich die relevante Größenordnung ist, dann ist das nicht viel. Bei neuerem Release könnte auch die ach so famose neue Query Engine das Problem sein (manchmal helfen PTFs)

Dieter Bender

Dynamisches SQL aus einem ganz einfachen Grund:

In der View befinden sich 46 Felder aus 6 physischen Dateien, die - je nach Anforderung - in unterschiedlichsten Kombinationen selektiert werden.

Die View beinhaltet dzt 1,8 Millionen Sätze.

Die Ablehnung der Indexe erfolgt übrigens immer mit Code '4' - braucht zu viel Zeit - gleichzeitig wird ein Index mit den gleichen Feldern immer wieder empfohlen.

Bei der Maschine handelt es sich um eine 570 mit 4 Prozessoren und 64 GB Hauptspeicher - also kein langsamer Rechner.

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