View Full Version : SQL-Optimizer ist einfach blöd
optimize for n rows:
Alle meine Versuche, die selben Antwortzeiten bzgl. des 1. Satzes wie mit STRSQL zu erreichen, sind kläglich gescheitert.
Egal ob ich ODBC oder embedded SQL verwende, den Weg, den STRSQL geht kann ich leider nicht nachvollziehen.
Ein SQL in STRSQL ist häufig sehr schnell, die Debug-Nachrichten werden untersucht, ggf. angewendet.
Spätestens im embedded SQL läuft da irgendwas anders, so dass diese Antwortzeiten einfach nicht erreichbar sind.
Alle dynamischen SQL-Interfaces oder Abfragen (dazu gehören natürlich auch interaktives SQL oder embedded dynamic SQL) werden per Default mit dem Optimierungsziel *FIRSTIO ausgeführt. Es wird also so optimiert, dass die erste Seite so schnell wie möglich ausgegeben wird.
Statisches Embedded SQL wird dagegen mit dem Optimiuerungsziel *ALLIO ausgeführt. Es wird also so optimiert, dass die komplette Abfrage so schnell wie möglich ausgeführt wird.
Diese Unterscheidung ist vor allem dann wichtig, wenn es darum geht einen Index-Zugriff oder einen Table Scan zu verwenden. *FIRSTIO tendiert dazu doch lieber einen Index zu nehmen, während *ALLIO zum Table Scan tendiert.
Um beim intraktiven SQL mit dem gleichen Optimierungsziel wie beim Statischen embedded SQL zu arbeiten, sollte im interaktiven SQL am Ende des Select-Statements OPTIMIZE FOR *ALL ROWS angegeben werden.
Noch besser ist es, Du gibts im embedded SQL das Optimierungsziel immer am Ende eines SELECT-Statements an und übernimmst dann dieses Ziel, wenn Du die Abfrage interaktiv ausführst.
@Dieter
FORCE_JOIN_ORDER default *NO favorisiert SQE
IGNORE_DERIVED_INDEX default *NO favorisiert CQE
IGNORE_DERIVED_INDEX war deshalb auf *NO geblieben, um (voraussehbare) Probleme mit der SQE zu minimieren, bzw. um zu verhindern dass einem wichtige Zugriffswege unter den Füßen weggezogen wurden. Da die meisten alten Anwendungen logische Dateien mit SELECT/OMIT-Anweisungen haben, wurden relativ wenige Abfragen mit der SQE ausgeführt.
Nur wer eine einigermaßen saubere SQL-Datenbank mit Referentiellen Integritäten und sauberen SQL-Indices hat, hat das Vergnügen sich mit der SQE herumzuschlagen. (Deshalb auch mein Kommentar von gestern, dass in den meisten Fällen, in denen auf die SQE geschimpf wurde und wird, die CQE die Abfragen ausführt.)
Das böse Erwachen wird für viele mit Release 6.1 kommen. Mit Einführung der Derived Indices unter Release 6.1 hat IBM beschlossen den Default-Wert für IGNORE_DERIVED_INDEX in der QAQQINI in der Bibliothek QSYS von *YES auf *NO zu ändern. Wurden also die DDS beschriebenen logischen Dateien nicht durch Derived Indices ersetzt werden bei vielen Abfragen wichtige Zugriffwege nicht mehr gezogen.
Übrigens die SQE ist mit Release 6.1 abgeschlossen, d.h. was jetzt nicht von der SQE verarbeitet werden kann, wird es auch nie werden können.
@Baldur
Die CQE wird genauso verschwinden wie die /36-Umgebung und der RPGIII-Compiler ... also uns noch einige Zeit erhalten bleiben.
Birgitta
@optimize Klausel:
soweit ich das sehe ist das kein SQL Standard! genauso wie irgendwelches rumbasteln an SQL Statements um die Macken von Query engines auszubügeln, für SQL gilt immer: entscheidend ist, was hinten rauskommt.
@sauber:
ich mache seit Jahren nichts anderes und genau daher kommen meine negativen Wertungen zur SQE. Weiterentwicklung der DB2/400 muss am SQL Ende stattfinden und nicht an der Altlastenfront, wenn IBM die Büchse weiter halten will.
@Perspektiven CQE:
ich glaube nicht, dass IBM in dieser Frage Herrn Fürchau, Frau Hauser oder mich konsultieren wird!!!
D*B
Noch besser ist es, Du gibts im embedded SQL das Optimierungsziel immer am Ende eines SELECT-Statements an und übernimmst dann dieses Ziel, wenn Du die Abfrage interaktiv ausführst.
Nur wer eine einigermaßen saubere SQL-Datenbank mit Referentiellen Integritäten und sauberen SQL-Indices hat, hat das Vergnügen sich mit der SQE herumzuschlagen. (Deshalb auch mein Kommentar von gestern, dass in den meisten Fällen, in denen auf die SQE geschimpf wurde und wird, die CQE die Abfragen ausführt.)
Die CQE wird genauso verschwinden wie die /36-Umgebung und der RPGIII-Compiler ... also uns noch einige Zeit erhalten bleiben.
Birgitta
Ich habe mich bei unserer BI-Anwendung damals für die von Dieter angegebenen Firebird-Datenbank entschieden.
Gerade was den Optimizer angeht, könnte IBM doch von dieser OpenSource-DB viel lernen.
Dieses gehuddel mit "Optimize for" kann ich deshalb nicht nachvollziehen.
Wieso verändert STRSQL meinen eingegebenen Select mit einer eigenständigen Optimize-Klausel ?
Das gehört verboten!
Wie soll man denn da vernünftige Entscheidungen bezgl. des SQL's treffen ?
Ich baue viele SQL's erst mal per STRSQL bevor ich mich der Mühsal des Programmtestens unterziehe.
Den OpsNav verwende ich da nicht, da mir das einfach zu langsam geht und tatsächlich nervt.
Der Vorteil von embedded SQL's insbesonders dem Einbinden von Servicemodulen (auch in OPM-Konzepten durchaus vorhanden), die sowohl in Batch als auch Dialog aufgerufen werden ist somit dahin.
Ich muss also in den Programmen abfragen, ob es ein Dialog- oder Batchprogramm ist um den entsprechenden statischen SQL aufzurufen ?
Der Jobstatus alleine reicht da nun mal nicht mehr aus, da es auch Batch-Services gibt, die eigentlich auf Dialog ausgerichtet sind (Web-Services).
Und was passiert, wenn man mal per RDB auf eine AS/400 durchgreift, die den Optimize for noch nicht kennt ?
Also alles nicht so schön, wie IBM immer beschreibt.
Da kann ich nun langsam die Anwender verstehen, dass sie zu SQL-Server oder Oracle wechseln, wenn es um SQL-Anwendungen geht (nicht nur wegen der bunten Oberflächen).
PS:
Und was die Compiler angeht, so ist ja das Lizenzmodell ab V6 erschreckend, dass man nun 2 Compilergruppen hat:
1. preiswerte ILE-Compiler
2. teure OPM-Compiler mit Tendenz zum Verschwinden
Bei tausenden OPM-Anwendungen, die sicherlich auch noch nach V6 gewartet und auch als OPM erweitert werden müssen, ist das kein gutes Zeichen.
Ansonsten gilt halt, bestimmte neue SQL-Funktionen sind in der CQE nicht mehr implementiert, so dass die SQE zwangsläufig verwendet werden muss.
Andersherum gilt auch, dass es in der SQE noch nicht implementierte Funktionen gibt, so dass eben doch die CQE genommen wird.
Kann es SQL-Anweisungen geben, die sowohl neue in der CQE nicht mehr implementierte als auch alte in der SQE noch nicht implementierte SQL-Funktionen beinhalten, und wie werden diese dann ausgeführt?
Nein, das kann es nicht geben.
Neue Funktion müssen ja irgendwie per SQL verwendet werden.
Aber wie Birgitta ja sagt, man kann ggf. per CREATE INDEX irgendwas erstellen, was der Optimizer aber noch nicht nutzen kann.
Zwar kann man Indices auch per Native-IO (als Datei in ILERPG/COBOL) verwenden, da sie i.W. wie normale LF's funktionieren.
Was das dann allerdings mit SQL zu tun hat, weiß ich auch nicht.
Wahrscheinlich ist den Entwicklern nun DDS nicht mehr weitreichend genug, so dass man eher auf Freeform-Syntax des SQL zurückgreift.
Und irgendwan soll der Optimizer diese Indices ja auch nutzen können (wie oben erwähnte Firebird).
@Baldur,
welches Optimierungsziel per Default verwendet wird, hängt nicht davon ab, ob ein SQL Statement interaktiv oder im Batch ausgeführt wird, sondern ob das SQL-Statement statisch oder dynamisch ist.
Statisch heißt es ist in einem (Service-)Programm hinterlegt und wird zur Laufzeit nur durch verschiedene Variablenwerte verändert. Dynamsich heißt, das SQL-Statement muss zur Laufzeit zunächst aus einem String in ein lauffähiges SQL-Statement konvertiert werden.
Wenn Dir das nicht gefällt kannst Du immer noch die Option OPTIMIZATION_GOAL in der QAQQINI entweder auf *ALLIO oder *FIRSTIO setzen, dann werden alle Abfragen unabhängig davon ob statisch oder dynamisch per Default mit dem angegebenen Optimierungsziel ausgeführt.
@Pickachu
Abfragen, die an die CQE geroutet werden müssen, jedoch neue Funktionen (z.B. OLAP-Ranking-Funktionen) beinhalten werden nicht ausgeführt. Fehlermeldung wird SQL0255 sein:
Funktion nicht für die Abfrage unterstützt.
Warum MUSS etwas an die CQE geroutet werden, wenn nur die SQE dieses ausführen Kann ?
D.h., ich muss wissen, wer was kann, eine entsprechende QAQQINI wählen, damit ich kein SQL0255 bekomme ?
Also, wenn ich in einem Programm diverse SQL's habe, sollten alle vom selben Optimizer ausgeführt werden.
Sonst müsste ich mich ja disconnecten, die QAQQINI ändern und reconnecten.
Unter Commit/Control einfach unmöglich.
Hier sollte es mal generelle Richtlinien geben, dass ICH mich für eine BESTIMMTE Methode entscheiden kann und kein Automatismen, die keiner versteht, Anwendung finden.
SQL0255 gehört in die Tonne und dürfte nie auftreten, das ist meine Meinung.
du musst wissen wer was kann und darfst dann gewisse Kombinationen nicht in einem Statement verwenden, sonst kriegst du eine SQL0255 um die Ohren, mit QAQQINI kannst du da nix ausrichten.
Ich würde da noch einen Schritt weiter gehen als du:
- SQL0255 ist ein Armutszeugnis für DB2/400
- SQL0101 (statement too long or too complex) ist der Offenbarungseid von DB2/400
- wieviele Query engines es gibt will mich nicht interessieren müssen, wenn ich mir Gedanken über Zugriffswege machen wollte, dann würde ich RLA programmieren!!!
D*B
Warum MUSS etwas an die CQE geroutet werden, wenn nur die SQE dieses ausführen Kann ?
D.h., ich muss wissen, wer was kann, eine entsprechende QAQQINI wählen, damit ich kein SQL0255 bekomme ?
Also, wenn ich in einem Programm diverse SQL's habe, sollten alle vom selben Optimizer ausgeführt werden.
Sonst müsste ich mich ja disconnecten, die QAQQINI ändern und reconnecten.
Unter Commit/Control einfach unmöglich.
Hier sollte es mal generelle Richtlinien geben, dass ICH mich für eine BESTIMMTE Methode entscheiden kann und kein Automatismen, die keiner versteht, Anwendung finden.
SQL0255 gehört in die Tonne und dürfte nie auftreten, das ist meine Meinung.
Eigentlich kann es nur noch schlimmer kommen.
Beide Optimizer unterstützen ja keine derived Indices.
Der ein sagt, da sind welche vorhanden, also nimm den anderen (es sei denn QAQQINI definiert was anders), und der andere ignoriert diese sowieso.
Warum denn dann nicht gleich.
Ausserdem kann es dann passieren (wie mir eben), dass laufende Programme plötzlich kein Ergebnis liefern.
Desweiteren stelle ich mir vor, dass es da ein Tableset (PF+LF's) ohne derivied Indices gibt, alles läuft wie geschmiert.
Plötzlich überlegt sich jemand, da könnte man doch eben eine LF bzw. derived Index anlegen und laufende Programme werden plötzlich umgeroutet, liefern SQL0255 oder ganz anderes.
Inzwischen sind Anwendungen so komplex geworden, dass man sich ja gerade deswegen für SQL entscheidet.
Anwendungen weisen heute einen Mix aus OPM/ILE/RLA/SQL auf, was ja i.W. funktioiert.
Es darf aber einfach nicht passieren, dass sich bei existierenden SQL's mal so mal so entschieden wird.
Hier wäre ggf. ein Set Option hilfreich, um diesen Problemen aus dem Weg zu gehen. Schließlich enthält ein Programm ja meist doch viele SQL's, Actgrp's nicht zu vergessen, Commitzyklen über mehrere Programmaufrufebenen u.v.m.
Da darf man sich nicht von einer QAQQINI abhängig machen, die man auch noch je Job spezifizieren muss.
Ich müsste dann auch noch bei jedem SBMJOB, Dialogjob, ggf. vorgeschaltete CLP's einen CHGQRYA einbauen.
Ich habe auch Anwendungen mit Schnittstellen zu anderen Anwendungen, die per CALL aufgerufen werden.
Ändere ich nun für meine Anwendung die QAQQINI kann diese schon für das Fremdprogramm vollkommen falsch eingestellt sein.
So langsam sollte sich die IBM da was einfallen lassen.
- Wegfall der CQE (ich sehe da keinen Grund zur Erhaltung, spätestens ab V6R2)
- Wegfall der QAQQINI !!!
die Indexe mit der where Klausel könnten sich wieder mal als zweitbeste Idee entpuppen, ich empfehle hier die Finger davon zu lassen.
set option ist auch kein Allheilmittel, immerhin können in derselben ACTGRP mehrere Module mit kollidierenden settings innerhalb einer Transaktion laufen.
ich vermeide generell globale settings (nicht nur hier!!!) und verwende Einstellungen der qaqqini nur zu Testzwecken.
den CHGQRYA könnte man eventuell noch in einer connect routine per stored procedure machen, die Idee reißt mich aber auch nicht vom Hocker.
Perspektivisch muss man von dem Mix runter und alles per SQL machen und sich konsequent an ANSI SQL halten, ich habe in den letzten Jahren genau das versucht und viele Empfehlungen in das OPNCLO versenkt und bin damit ganz gut gefahren.
D*B
Eigentlich kann es nur noch schlimmer kommen.
Beide Optimizer unterstützen ja keine derived Indices.
Der ein sagt, da sind welche vorhanden, also nimm den anderen (es sei denn QAQQINI definiert was anders), und der andere ignoriert diese sowieso.
Warum denn dann nicht gleich.
Ausserdem kann es dann passieren (wie mir eben), dass laufende Programme plötzlich kein Ergebnis liefern.
Desweiteren stelle ich mir vor, dass es da ein Tableset (PF+LF's) ohne derivied Indices gibt, alles läuft wie geschmiert.
Plötzlich überlegt sich jemand, da könnte man doch eben eine LF bzw. derived Index anlegen und laufende Programme werden plötzlich umgeroutet, liefern SQL0255 oder ganz anderes.
Inzwischen sind Anwendungen so komplex geworden, dass man sich ja gerade deswegen für SQL entscheidet.
Anwendungen weisen heute einen Mix aus OPM/ILE/RLA/SQL auf, was ja i.W. funktioiert.
Es darf aber einfach nicht passieren, dass sich bei existierenden SQL's mal so mal so entschieden wird.
Hier wäre ggf. ein Set Option hilfreich, um diesen Problemen aus dem Weg zu gehen. Schließlich enthält ein Programm ja meist doch viele SQL's, Actgrp's nicht zu vergessen, Commitzyklen über mehrere Programmaufrufebenen u.v.m.
Da darf man sich nicht von einer QAQQINI abhängig machen, die man auch noch je Job spezifizieren muss.
Ich müsste dann auch noch bei jedem SBMJOB, Dialogjob, ggf. vorgeschaltete CLP's einen CHGQRYA einbauen.
Ich habe auch Anwendungen mit Schnittstellen zu anderen Anwendungen, die per CALL aufgerufen werden.
Ändere ich nun für meine Anwendung die QAQQINI kann diese schon für das Fremdprogramm vollkommen falsch eingestellt sein.
So langsam sollte sich die IBM da was einfallen lassen.
- Wegfall der CQE (ich sehe da keinen Grund zur Erhaltung, spätestens ab V6R2)
- Wegfall der QAQQINI !!!