(Embedded) SQL Problem

[harkne]

Hallo zusammen,

leider kann ich es nicht spezifischer im Titel angeben, denn es ist einfach ein etwas größeres SQL Statement, welches auf der AS/400 mit STRSQL oder eben auch innerhalb des RPGs ca 4 Minuten braucht um ein Ergebnis zu bringen. Führe ich das mit einer 5250 Emulation über Client Access aus, habe ich das Ergebnis in 4-5 Sekunden. Rätsel über Rätsel.

Ich wollte eigentlich wissen ob jemand einen Grund kennt, warum es auf der AS400 direkt so lange braucht und dann über Client Access so schnell ist

Nachfolgend das SQL-Statement. Wenn da jemand Tipps hat wie man das schneller machen kann, bin ich ganz Ohr

SELECT CHNUMB, CHID, CHWGHT, CHWHCR, CHWHCU, CHLWHS, CHPAID, CHTRMT, CHTDICC, CHTDIYY, CHTDIMM, CHTDIDD, CHTDITM, CHDISC, CHDLRC, CHSTAT, CHSTAL, CHTYPE, CHCRCC, CHCRYY, CHCRMM, CHCRDD, CHCRTI, CHCRDT, CHSLPF, 'CHTRNP' AS FROMFILE
FROM CHTRNP AS CH
LEFT OUTER JOIN CHTTRNP ON CHID = CHTCHID AND CHWHCU = CHTDWHS,
DNPTRNP
WHERE
(CHDISC = '00' OR
(CHDISC = ' ' AND '00' IN
(SELECT T1.OHDISC FROM spefil.OHTRNP AS T1 INNER JOIN spefil.CDTRNP AS T2 ON T1.OHSODN = T2.CDSODN AND T1.OHODCC = T2.CDODCC AND T1.OHODYY = T2.CDODYY AND T1.OHODMM = T2.CDODMM AND T1.OHODDD = T2.CDODDD
WHERE (T2.CDCHID = CH.CHID OR (CH.CHBAID > 0 AND T2.CDCHID = CH.CHBAID)))))

AND CHPAID = 0
AND DNPDISC = '00'
AND DNPSIVN = 123456
AND DNPCHID > 0
AND DNPCHOI > 0
and (CHID = DNPCHID OR CHID = DNPCHOI OR CHBAID = DNPCHID OR CHBAID = DNPCHOI)

ORDER BY CHCRDT DESC, CHCRTI DESC;

[Fuerchau]

IN
(SELECT T1.OHDISC FROM spefil.OHTRNP AS T1 INNER JOIN spefil.CDTRNP AS T2 ON T1.OHSODN = T2.CDSODN AND T1.OHODCC = T2.CDODCC AND T1.OHODYY = T2.CDODYY AND T1.OHODMM = T2.CDODMM AND T1.OHODDD = T2.CDODDD
WHERE (T2.CDCHID = CH.CHID OR (CH.CHBAID > 0 AND T2.CDCHID = CH.CHBAID)))))

Hier stellt sich die Frage, warum du über die T1 an die T2 verknüpfst und dann ausschließlich aus T2 mit CH vergleichst.
Kannst du nicht direkt as der CH auf die T2 zugreifen oder wenigstens in der Where-Klausel einen Bezug zu CH herstellen?

Ein " abc in (select ...)" lässt sich häufiger und damit performanter durch "exists (select * from ...)" ersetzen. Beim "in" wird eine Liste ermittelt und dann durchsucht, beim Exists wird 1 Zugriff (am besten mit Index) gemacht.

Und ansonsten gilt dies, was hier schon vielfach diskutiert wurde:
STRSQL setzt Optimierungsoptionen die im Dialog das schnellste Ergebnis liefern sollen, da eher selten das gesamte Ergebnis gebraucht wird.
Embedded SQL macht dies genau anders herum.
Steuern kann man das im embedded per "optimize for first n rows" (o.ä.) und im STRSQL per F13->1.

[harkne]

Weil T1.OHDISC den Wert liefert
eine Zeile darüber AND '00' IN (SELECT T1.OHDISC ...

Im Embedded SQL lese ich auch Satz für Satz, deshalb verhalten Sie sich wohl auch gleich schnell/langsam.

Das mit exists ist noch eine Idee die ich probieren werde.

Was allerdings nicht erklärt warum das SQL über Client Access so schnell ist.

[Fuerchau]

"STRSQL setzt Optimierungsoptionen die im Dialog das schnellste Ergebnis liefern sollen".
Darüber schweigt sich die IBM im Wesentlichen aus.
Ich habe da auch schon versucht über QAQQINI, "optimize for ..."-Anweisung bei embedded SQL und Dialog SQL das selbe Ergebnis zu erzielen, wie es STRSQL tut, habe es aber nie geschafft. STRSQL ist grundsätzlich schneller als embedded SQL.
Frag mich nicht warum.
Das hat auch nichts mit längeren Ergebnistabellen zu tun. Selbst wenn ich nur 1-10 Sätze habe, ist STRSQL häufig schneller.

[harkne]

Weiß jemand was genau der Unterschied ist zwischen

declare c sensitive scroll cursor for s
zu
declare c cursor for s

und anschließend

fetch next from c into

Ich bin immer davon ausgegangen, dass beim sensitive scroll cursor Satz für Satz eingelesen wird, soll heißen er holt sich nicht die gesamte Datenmenge sondern immer nur den nächsten Satz wenn dieser über fetch next angeforder wird

Im Gegensatz dazu wird im zweiten Fall die komplette Datenmenge eingelesen, trotzdem aber Satz für Satz per fetch next in meine Felder übertragen.

Lieg ich da richtig ?

Meine komplette Datenmenge beträgt ca 90 Mio Datensätze
Eine Subfileseite beinhatet 15 Sätze.
Deshalb habe ich auch gedacht ich nehme den sensitive scroll cursor. Denn nach 15 Datensätzen soll dem Benutzer die Subfile angezeigt werden und erst beim Blättern die nächsten Datensätze ermittelt werden.

Ich habe jetzt festgestellt.
Sind nur 3 Datensätze anzuzeigen läuft mein Programm seltsamerweise bis zu 9 Minuten (muss alle Datensätze einlesen)
Lass ich den Sensitive Cursor weg, ca 10 Sekunden

Wähle ich dann eine riesige Datenmenge aus (z.B. 100000 Datensätze) dann ist der Sensitive Cursor schneller als ohne. Da er anscheinend ja schnell 15 Sätze zum Anzeigen findet, ist er dann auch schnell bei der Anzeige. Die nächsten 15 holt er sich dann beim Blättern.

Jetzt aber meine Frage.

Was das Einlesen über FETCH NEXT betrifft, macht es doch keinen Unterschied ob ich SENSITIVE SCROLL CURSOR verwende oder nicht (ich meine nicht die Schnelligkeit sondern die Daten die ich bekomme) ?

Weil ich würde jetzt das Programm einfach so ändern dass er nur noch einen "normalen" und keinen sensitive scroll Cursor mehr hat. Die Daten sind immer noch die gleichen die ich bekomme ?

Viel geschrieben, aber am wichtigsten wäre mir ein ja auf die letzte Frage ;-)

Viele Grüße harkne

[andreaspr@aon.at]

Kurz gesagt: Nein.
Wenn du Sensitive angibst werden die Daten bei jedem Fetch aktualisiert.
D.h. du kannst bei einem Fetch Daten angezeigt bekommen obwohl dieser Satz beim Open oder beim vorherigen Fetch noch gar nicht existiert haben.

lg Andreas

[Fuerchau]

Des weiteren werden Optimierungen ausgeschaltet da ja immer auf die Aktualität der Daten Rücksicht genommen werden muss, was bei komplexeren Joins dann eher kritisch wird.
Wenn man dies mit .NET vergleicht, gibt es dort die Möglichkeit überhaupt nicht. Deshalb wird ja unterschieden zwischen einem Reader (der kann nur Next) und einem Table-Objekt.
Ohne Sensitve gehts meist erheblich schneller, da man eher selten "fetch previuos" benötigt.
Je nach zu erwartender Datenmenge ist ein Refresh dann immer noch besser, Stichwort Subfile+F5.
Beim Blättern gibt es häufiger Verwirrung wenn ständig Daten verschwinden oder hinzukommen.

[BenderD]

Meine komplette Datenmenge beträgt ca 90 Mio Datensätze
Eine Subfileseite beinhatet 15 Sätze.
Deshalb habe ich auch gedacht ich nehme den sensitive scroll cursor. Denn nach 15 Datensätzen soll dem Benutzer die Subfile angezeigt werden und erst beim Blättern die nächsten Datensätze ermittelt werden.

Ich habe jetzt festgestellt.
Sind nur 3 Datensätze anzuzeigen läuft mein Programm seltsamerweise bis zu 9 Minuten (muss alle Datensätze einlesen)
Lass ich den Sensitive Cursor weg, ca 10 Sekunden

... 10 sec. für 15 Sätze ist jenseits von Gut und Böse, da hast Du ein anderes Problem wie scrollable oder nicht!!!
Das lesen von 10 Sätzen muss im Bereich von hundertstel Sekunden liegen!!! Das sieht eher nach einem fehlenden Index aus. Nimm den Job mal unter Debug und sieh dir das Joblog genau an, was es da so treibt.

scrollable cursor brauchst Du, wenn Du rückwärts lesen, oder frei positionieren willst. Ohne sensitive wird der cursor dann read only und es könnte eine lokale Kopie erlaubt sein.

Was man auch noch überlegen könnte, wäre mehrere Seiten in einem Block fetch zu lesen und bei einer update Operation das Subfile komplett neu aufzubauen.

D*B

[harkne]

Naja, dann habe ich wohl den falschen Ansatz gehabt. Ich dachte der SENSITVE SCROLL Cursor wird gebraucht, damit er nicht die komplette Datenmenge einliest, sondern immer nur soviel liest wie er braucht. Mit STRDBG habe ich bereits gesehen welchen und dass er einen Index verwendet. Wir haben nur festgestellt das die Problemtatik überwiegend beim embedded SQL vorkommt. Ich hab das SQL über Client Access ausgeführt und er hat noch nicht mal mit der Wimper gezuckt um mir die 3 Datensätze anzuzeigen. Mit dem Sensitive Scroll Cursor 9 Minuten !!! und mit STRSQL auch noch 30 Sekunden. Ohne den Sensitive Scroll Cursor braucht er jetzt noch 30 Sekunden. @Bender ... wenn Du dir das SQL-Statement angeschaut hast, dachte ich zunächst, dass das IN das Problem ist, durch die Komplexität dahinter, lass ich das weg wirds aber auch nicht schneller. Höchstwahrscheinlich liegt es am Join auf die DNPTRNP mit mehreren Feldern gemischt. Als ich das Sensitive Cursor weggelassen hatte war dann zwar dieses SQL-Statement schneller (nur noch 30 Sekunden) was mir persönlich immer noch zu lange dauert, allerdings wenn jemand dann nur den DIST auswählt braucht er ohne Sensitive Scroll viel länger als mit Sensitive Scroll. Ich hab das jetzt so angepasst, dass er, wenn er was eingibt, bei dem ich wenig Datensätze erwarte ohne Sensitive Scroll arbeitet und wenn ich viel Datensätze erwarte mit Sensitive Scroll. Somit ist alles "erträglich" aber weit weg von zufriendenstellend.

Vielen Dank für Eure Hilfe

[BenderD]

Mit STRDBG habe ich bereits gesehen welchen und dass er einen Index verwendet.


@Bender ... wenn Du dir das SQL-Statement angeschaut hast, dachte ich zunächst, dass das IN das Problem ist, durch die Komplexität dahinter, lass ich das weg wirds aber auch nicht schneller. Vielen Dank für Eure Hilfe

... wenn ein SQL Statement einigermaßen lesbar sein soll, dann sollte man für alle Tabellen einen kurzen alias (A, B, C oder T1 T2 T3) vergeben und alle Felder mit Herkunft prefixen, dann bräuchte man auch noch die Felddefinitionen und wäre immer noch nicht viel schlauer.

Die wesentliche Arbeit musst Du schon selber machen. Mag sein, dass da irgendwo, irgendwie ein Index genommen wird, spannend ist doch die Frage, was da unterschiedliches passiert. Dazu muss man halt beide Varianten unter Debug laufen lassen und dann die Joblogs vergleichen, wo der Unterschied liegt. Wenn da alpha Felder für join oder order by im Spiel sind, reicht da evt. schon eine andere CCSID Einstellung im Job - das kann man aus der Ferne nicht sehen.

D*B

[harkne]

Ich wollte auch nicht, dass mir jemand die Arbeit abnimmt. Meine ursprüngliche Frage lief darauf hinaus, dass ich nicht verstehen konnte warum über Client Access das ganze rasend schnell zu einem Ergebnis kommt und der STRSQL auf der AS400 bzw. das embedded SQL so lange braucht. Ich dachte dass es da ein bekanntes Problem oder ähnliches gibt. Das SQL wollte ich ursprünglich gar nicht posten, ich dachte nur dass jemandem vielleicht was auffällt und er/sie sagt, ja bei der oder der Art der Verknüpfung hat die AS/400 Probleme über Client Access geht es deshalb schneller weil ...
Wenn es dazu keine Antwort gibt, bin ich auch damit zufrieden, dann darf ich es vielleicht wieder mit einem altbackenen READE programmieren was aus meiner Sicht in bestimmten Fällen einfach die schnellere Lösung ist.

[BenderD]

... es gibt immer eine Antwort und die ist in diesem speziellen Fall unmittelbar aus den beiden Joblogs ersichtlich. Wie Du dieses SQL Statement mit einem "altbackenen READE" ablösen willst, das ist mir ein wenig schleierhaft, aber sei#s drum.
Was Dein SQL Statement angeht, ein where KONSTANTE in (select ...), das ließe sich auch in einen inner join auflösen, der nur das mitnimmt, was mit der KONSTANTE matched.

D*B