View Full Version : SQL Performance
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)?
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.
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)?
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
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
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.
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.
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.
@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.
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 ...