Verknüpfen von Tabellen mit SQL

[BenderD]

Hallo,

wobei natürlich ein geblockter Fetch in eine DS mit dim signifikant schneller ist...

mfg

Dieter Bender

Unabhängig davon ob du mit einem Fetch 1 Zeile oder N Zeilen abrufst, intern wird deswegen trotzdem geblockt.
Man sieht es ganz gut über "DSPJOB->Auswahl 14" (Systemanfrage 3=>14) dass die Anzahl der Leseoperationen durchaus erheblich kleiner sein kann als die Anzahl Fetch die man macht.
Blocken ist eine automatische Systemfunktion !

Anders bei native I-O. Hier wird bei RPG zusätzlicher Code ausgeführt, der das Blocken intern übernimmt.
Dadurch kommt es ja bei O-Dateien zu "seltsamen" Datenverlusten, da ggf. der interne RPG-Puffer noch nicht ausgegeben werden konnte.

[alfredo]

Von der Theorie zur Praxis:
Lesen von 1.500.000 Sätzen aus einer PF mit Order auf bestehende LF.
OPNQRYF 7.000 ms
SELECT * (25 Felder) 21.000 ms
SELECT 9 Felder 19.000 ms
Mehrfachfetch ?

Für Dialogprogramme ist das natürlich nebensächlich.
lg

[B.Hauser]

Hallo Alfredo,

wenn Du SQL z.B. mit native I/O vergleichst, evt. auch mit OPNQRYF darfst Du niemals nach der ersten Ausführung gehen.

Bei der ersten Ausführung mit SQL läuft der komplette Optimierungs-Prozess, mit Erstellung/Validierung der Access Plans, Prüfen der vorhandenen Zugriffs-Wege und Aufbau/Öffnen des verwendeten Daten Pfades (ODPs). Nach der ersten Ausführung wird der ODP wieder geschlossen. Beim zweiten Durchlauf wird der Access Plan nochmals geprüft und der ODP erneut aufgebaut. Erst nach dem zweiten Aufruf bleibt der ODP geöffnet und kann immer wieder verwendet werden. Und erst nach diesem Aufruf kann SQL mit native I/O verglichen werden. Und erst ab dem 3. Aufruf wird SQL schneller als native I/O.

Wie Dieter bereits gesagt hat ist auch das Fetchen eines einzelnen Satzes signifikat langsamer als ein Fetch in eine Mehr-Dimensionale-Datenstruktur (vor V5R3) oder in eine Array-Datenstruktur (ab V5R3). Ebenso kann durch die Angabe von FOR READ ONLY oder FOR FETCH ONLY eine Performanceverbesserung erreicht werden, da in diesem Fall auf alle Fälle geblockt gelesen wird. Select-Statement, die über Cursor verarbeitet werden und nicht von Natur aus READ ONLY sind (z.B. durch Join-Anweisungen, Order By oder Group By-Anweisungen), sind updatefähig. Diese Update-Fähigkeit verhindert u.U. ein geblocktes Lesen.

Ich weiß jetzt nicht auf welcher Basis Du Deine Tests gemacht hast, aber solltest Du die logische Datei im SQL angegeben haben, muss ein rerouting von der SQE (SQL-Quera-Engine) zur CQE (Classic Query Engine) erfolgen, was bis zu 10% Performance kosten kann. OPNQRYF dagegen kann die logische Datei direkt verarbeiten, da OPNQRYF eine alte Technolgie und kein SQL ist.

Deine Ergebnisse in Ehren, aber ohne genauere Informationen, wie die Statements aussehen, bzw. aufgerufen wurden, kann man dazu nichts sagen. Weiterhin würde ich ihnen ohne detaillierte Analyse über DBMON keinen Glauben schenken.

Birgitta

[alfredo]

Na gut:
Select * from PF
order by A,B
for fetch only

Für den Vergleich wurde jede Variante 3x hintereinander aufgerufen und der letzte Aufruf verglichen.
lg

[BenderD]

Hallo,

der Test sieht schon ok aus und die Ergebnisse entsprechen schon dem, was ich so aus eigenen Erfahrungen in Erinnerung habe; ich denke, dass ein Blockfetch von sagen wir mal 500 im SQL nochwas gut macht, aber das ist alles aus meiner Sicht nicht so ausschlaggebend. Im Dialog, ist wie du bereits angemerkt hast, das eh egal.
Was für SQL gegenüber OPNQRYF spricht, ist das einfachere Handling und die bessere Lesbarkeit, vergleiche ich Record Level Access mit den dynamischeren Varianten, dann ist es die größere Flexibilität und die im Schnitt (!!!) besseren Antwortzeiten von SQL, die daraus resultieren, dass der Programmierer sich im richtigen Leben zuweilen für die zweitbeste Lösung entscheidet.
In den Bereich des Marketings und des Wunschdenkens gehört, dass SQL per se schneller sei als Record Löffel Ekzem, letzteres ist bei entsprechender Optimierung bei über 90% der Fälle im Vorteil - apropos Wunschdenken, die SQE ist eine mittlere Katastrophe, nach jedem Database Group PTF halte ich die Luft an, was da wieder alles krumm ist und oft wird mehr langsamer als schneller wird!!!

Fazit: konsequenter Umstieg zu SQL, wegen besserer Programmierer Performance, was die Maschine angeht ist es SQL für heutige Hardware schnell genug, im Dialog liegt der Unterschied (bei entsprechendem Index Design) unter der Messbarkeits Schwelle und im Batch holt man Speed eh' über Parallelisierung zur Auslastung der vorhandenen Hardware und nicht über Hungerjobs.

mfg

Dieter Bender

Na gut:
Select * from PF
order by A,B
for fetch only

Für den Vergleich wurde jede Variante 3x hintereinander aufgerufen und der letzte Aufruf verglichen.
lg

[e_sichert]

Vielen Dank meine Herren für die detailierten Ausführungen.
Leider übersteigen diese (derzeit noch) meinen IT Horizont.

Trotzdem, nochmals vielen Dank.

[BenderD]

Hallo,

dann nochmal ganz pragmatisch:
OS400 Befehlszeile am Greenscreen:
CHGQRYA QRYTIMLMT(0)
STRSQL
select * from ... left outer join ...
jetzt commt eine Fehlermeldung ... Ausführungszeit dauert länger als...
mit C wie cancel beantworten

jetzt zeigt ein Blick ins Joblog warum das solange dauert und mit welchem Index das schneller geht

wenn dann noch unklar bitte konkret nachfassen

mfg

Dieter Bender

Vielen Dank meine Herren für die detailierten Ausführungen.
Leider übersteigen diese (derzeit noch) meinen IT Horizont.

Trotzdem, nochmals vielen Dank.

[e_sichert]

Hallo Herr Bender,

ich habe Ihre Empfehlung durchgeführt und bekomme folgendes Joblog.

> strsql
Geschätzte Abfrageverarbeitungszeit 1 überschreitet Limit 0 (C I)
? C
Alle Zugriffspfade wurden für Datei FSFRE00P berücksichtigt.
Zugriffspfad mit Hilfe von Schlüsseldatei TSZUS11P erstellt.
Datei FSFRE00P in Verknüpfungsposition 1 verarbeitet.
Datei TSZUS11P in Verknüpfungsposition 2 verarbeitet.
Voraussichtliche Abfragedauer 1 überschreitet Zeitlimit 0
Geschätzte Abfrageverarbeitungsdauer von 1 überschreitet Zeitlimit von 0.

Die erweiterte Nachrichtenanzeigt der Zeile "Alle Zugriffspfade wurden für Datei FSFRE00P ber... " zeigt folgendes an:

Ursachencode 0 gibt an, daß der Zugriffspfad zur Implementierung der Abfrage
verwendet wurde.
FSBD102FAS/FSFRE00P 6, FSBD102FAS/FSFRE80LTS 6, FSBD102FAS/FSFRE70L 6,
FSBD102FAS/FSFRE70LTS 6, FSBD102FAS/FSFRE60LTS 6, FSBD102FAS/FSFRE50L 6,
FSBD102FAS/FSFRE30L 6, FSBD102FAS/FSFRE00I 6, FSBD102FAS/FSFRE90LTS 6,
FSBD102FAS/FSFRE72LTS 6, FSBD102FAS/FSFRE10LTS 6, FSBD102FAS/FSFRE71LTS
6, FSBD102FAS/FSFRE60L 6.
Die Ursachencodes haben folgende Bedeutung:
1 - Der Zugriffspfad befand sich in einem ungültigen Status und das System

...und mehr...

Die erweiterte Nachrichtenanzeigt der Zeile "Datei FSFRE00P in Verknüpfungsposition 1 verarbeitet." zeigt folgendes an:

Ursache . . . . : Der Zugriff nach Eingangsfolge wurde zum Auswählen von
Sätzen aus Teildatei FSFRE00P der Datei FSFRE00P in Bibliothek FSBD102FAS
verwendet.
Handelt es sich bei Datei FSFRE00P in Bibliothek FSBD102FAS um eine
logische Datei, ist Teildatei FSFRE00P der physischen Datei FSFRE00P in
Bibliothek FSBD102FAS die tatsächliche Datei in Verknüpfungsposition 1.
Der Dateiname *N für die Datei gibt an, daß es sich um eine temporäre
Datei handelt.
Fehlerbeseitigung: Normalerweise kann die erzwungene Verarbeitung einer Datei
in Verknüpfungsposition 1 erreicht werden, indem nur für diese Datei eine
Sortierung nach Feld angegeben wird.

Was muß ich tun damit die Sätze der Datei FSFRE00P nicht nach Eingangsreihenfolge sondern nach einem Index abgerufen werden, sofern das dann schneller geht?

Ich habe für Datei FSFRE00P bereits einen Index für Feld OrdNum und einen Index für Feld OrdNum,OrdPos angelegt. Diese Indexe wurden allerdings auch nicht verwendet.

Hier nochmal mein SQL String:

Select * From TSZUSCHNIT/TSZUS11P T1 Join FSBD102FAS/FSFRE00P T2
ON T1.ZusFan = T2.OrdNum and T1.ZusPos = T2.OrdPos
Where T1.ZusLnr = 136

[Fuerchau]

Indizees können nur verwendet werden, wenn der Feldtyp der Verknüpfung identisch ist.
Ansonsten muss ein Casting durchgeführt werden, was zu einem Tablescan führt.
Überprüfe also die Felddeklarationen von ZusFan <> OrdNum und ZusPos<>OrdPos und ob du einen Index über ZusLnr hast.

Wenn die Datei klein genug ist, kann ggf. auch auf den Index verzichtet werden, was hier anscheinend der Fall ist.

[e_sichert]

Die Feldtypen von ZusFan und ZusPos sind in Tabelle TSZUS11P vom Typ DECIMAL (7/0 und 5/0) und von OrdNum und OrdPos vom Typ NUMERIC (7/0 und 5/0).
Sind die Typen dann verschieden oder gilt das nur für die Verknüpfung von Alphafeldern und nummerischn Felder?

Zur Dateigröße;
In Datei TSZUS11P befinden sich ca. 200 Sätze, wird aber nach und nach größer. In Datei FSFRE00P befinden sich ca. 250.000 Sätze.

[Fuerchau]

Decimal ist PACKED, Numeric ist ZONED.
Die Feldtypen sind also unterschiedlich.

Manchmal hilft auch ein eigenes Casting:

left join file b on decimal(a.key, n, m)=b.key ...

Also eine Typanpassung der linken Seite der Beziehung.
Dies gilt auch für Numerisch nach Char, umgekehrt nur bedingt, wenn das Zeichenfeld sicher nur numerische Werte enthält.

[e_sichert]

Hallo,

Das Casting im SQL Statement hat noch nichts gebracht.
Dann habe ich die DDS der Tabelle TSZUS11P in 7S 0 und 5S 0 geändert sodass die Felder vom Typ numeric sind, so wie in Tabelle FSFRE00P.

Jetzt wird ein Index verwendet und die Ausführungsgeschwindigkeit des SQL's ist x-mal schneller.So wie man es eben gewohnt ist.

Vielen Dank für Ihre Hilfe.

mfg.
Erwin Sichert