Embedded SQL - Performance-Probleme

[MrBonZai]

Hallo,

ich habe ein Schnittstellen-Programm geschrieben, das komplett dynmische SQL-Statements aufbaut und ausführt.

Eine Performance-Messung hat ergeben, dass eine der Engpässe die Ausführung der SQL-Statements ist.
Pro Update werden ca. 3 millisekunden und bei Insert bis zu 6 ms benötigt werden (4-6 ms)

Da mehrere Insert auf diverse Dateien durchgeführt werden, läppert sich das ganz schön zusammen... und es vergehen z.B. bis zu 15 Sekunden und das obwohl keine übermässige Menge an Daten geschrieben werden...

des SQL selbst wird mittels folgendem Befehl ausgeführt:
c/EXEC SQL
c+ EXECUTE IMMEDIATE :sqlcmd
c/END-EXEC

die SQL-Statements die ich ausführe sehen z.b. so aus:
update NC6000 set BWOT ='490' where FIR =100 and FIL =100 and DKNR =100

insert into NC6130 (FIR,FIL,DKNR,KORR,WPNR,PSZE,PSLFNR) (select 100,100,1051,0,1,'DE001006',CASE WHEN (MAX(PSLFNR)+1 IS NULL) THEN 1 ELSE MAX(PSLFNR)+1 END from NC6130 where FIR =100 and FIL =100 and DKNR =00000000001051 and KORR =0 and WPNR =1)

Pro Aufruf der Schnittstelle wird nur ein Update und alle folgenden Operationen mittels Insert ausgeführt.

Kennt ihr Tipps wie ich die Performance steigern kann?
Gibt es Tricks, die man bei Insert beachten muss bzw. beim Dynamischen SQL?
oder sonstige Einstellungen die man beachten sollte?

gruss Alex

[Fuerchau]

Hierzu muss gesagt werden, dass beim dynamischen SQL jedesmal die Syntax überprüft, die Zugriffswege analysiert und optimiert werden müssen.
Besser ist es immer, mit statischen SQL's und Parametern zu arbeiten wenn die Anzahl der Felder immer konstant sind.

Also mit
c/exec sql
c+ update NC6000 set BWOT =:BOWT where FIR =:FIR and FIL =:FIL and DKNR =:DKNR
c/end-exec

Dieses Statement wird prepared (vorbereitet) und ist direkt ausführbar.

Deinen Insert würde ich in 2 Teile aufteilen.
Die Vergabe von PSLFNR sollte über Trigger gelöst werden, so dass kein Select erforderlich ist:

CREATE TRIGGER NC6130_BEFORE_INSERT
BEFORE INSERT ON NC6130
REFERENCING NEW ROW AS NR
FOR EACH ROW MODE DB2ROW
SET OPTION SQLPATH = *LIBL
BEGIN
SET NR.PSLFNR = (SELECT VALUE(MAX(PSLFNR), 0) + 1
FROM NC6130);
END

Dann kannst du deinen Insert mit
/exec sql
+insert into NC6130 (FIR,FIL,DKNR,KORR,WPNR,PSZE,PSLFNR)
+values (:FIR, :FIL, :DKNR, :KORR, :WPNR, :PSZE, 0)
/end-exec

durchführen.

[B.Hauser]

Hi,

es ist meistens besser und einfacher statisches SQL zu verwenden, da die Syntaxprüfung zur Compile Zeit erfolgt und je nachdem, wie die OPTION CLSSQLCSR im Compile Befehl oder in einer SET OPTION Anweisung angegeben wurde, wird der ODP (open data path) wieder verwendet.

Es gibt noch viele andere Aspekte, die die Performance beeinflussen können. Darüber gibt es auch diverse REDBOOKS.

Leider gibt hat man nicht immer die Möglichkeit statisches SQL zu verwenden.

Wenn der EXECUTE IMMEDIATE Befehl ausgeführt wird, wird zunächst der alphanumerische Befehl in ein ausfürhrbares SQL statement konvertiert, anschliessend wird der optimale Zugriffsweg ermittelt und dann das Statement ausgeführt.
Der EXECUTE IMMEDIATE Befehl ist die Kombination der Befehle PREPARE und EXECUTE.

Nutzt man diese beiden Befehle, braucht das SQL statement nur einmal konvertiert zu werden. Die Ausführung kann beliebig oft erfolgen, d.h. der Optimizer braucht nicht bei jedem Aufruf den Zugriffs-Pfad zu ermitteln.

Man kann im SQL-String Parameter Marker (?) für Variablen setzen, die bei der Ausführung dann durch Host Variablen Werte ersetzt werden können. Diese Werte werden im EXECUTE statement über USING :Var1, :Var2 angegeben.

Die ist jedoch nur möglich für Felder. Muss die Datei variabel gesetzt werden, muss das PREPARE statement immer ausgeführt werden.

Birgitta.

[BenderD]

Hallo Alex,

ob das schnell oder langsam ist, hängt zuerst von der Hardware ab, für das, was Du da machst sieht das garnicht so langsam aus. Bei 5 ms werden da immerhin 200 Operationen pro sec ausgeführt, das wären dann bei 15 sec. ja 3000 Datenbankoperationen???
Generell wird das Ganze bei static SQL zwar schneller, aber Wunder würde ich hier nicht erwarten, es sieht mir nicht so aus, dass der prepare das Problem darstellt, sondern eher die Menge der Operationen.
Zu dem Mechanismus mit dem MAX habe ich Kopfweh (auch bei Baldur), das funktioniert nur fast und ist Quelle von Sperrproblemen im konkurrierenden Zugriff. Ein SELECT MAX(...) sperrt bei entsprechendem Sperrlevel die gesamte Table ohne dass damit überhaupt garantiert wäre, dass die so gefundene Nummer frei ist (wenn jemand über einen anderen Weg, oder mit dirty read schneller ist).
Ein primärer Kandidat ist bei sowas auch immer der (Group) PTF Stand; bei V5R3 wackelt die Datenbank immer noch, selbst bei relativ neuen Ständen.
Generell gilt hier immer: schneller ist es dann, wenn man das messtechnisch nachweisen kann, Patentrezepte gibt es hier (fast) keine.

mfg

Dieter Bender

Hallo,

ich habe ein Schnittstellen-Programm geschrieben, das komplett dynmische SQL-Statements aufbaut und ausführt.

Eine Performance-Messung hat ergeben, dass eine der Engpässe die Ausführung der SQL-Statements ist.
Pro Update werden ca. 3 millisekunden und bei Insert bis zu 6 ms benötigt werden (4-6 ms)

Da mehrere Insert auf diverse Dateien durchgeführt werden, läppert sich das ganz schön zusammen... und es vergehen z.B. bis zu 15 Sekunden und das obwohl keine übermässige Menge an Daten geschrieben werden...

des SQL selbst wird mittels folgendem Befehl ausgeführt:
c/EXEC SQL
c+ EXECUTE IMMEDIATE :sqlcmd
c/END-EXEC

die SQL-Statements die ich ausführe sehen z.b. so aus:
update NC6000 set BWOT ='490' where FIR =100 and FIL =100 and DKNR =100

insert into NC6130 (FIR,FIL,DKNR,KORR,WPNR,PSZE,PSLFNR) (select 100,100,1051,0,1,'DE001006',CASE WHEN (MAX(PSLFNR)+1 IS NULL) THEN 1 ELSE MAX(PSLFNR)+1 END from NC6130 where FIR =100 and FIL =100 and DKNR =00000000001051 and KORR =0 and WPNR =1)

Pro Aufruf der Schnittstelle wird nur ein Update und alle folgenden Operationen mittels Insert ausgeführt.

Kennt ihr Tipps wie ich die Performance steigern kann?
Gibt es Tricks, die man bei Insert beachten muss bzw. beim Dynamischen SQL?
oder sonstige Einstellungen die man beachten sollte?

gruss Alex

[Fuerchau]

Über die Triggerlösung geht das ja ganz gut, da ja bei jedem Insert (auch bei Recordlevel-Access) der Trigger auf jeden Fall aufgerufen wird.
Man sollte natürlich einen Zugriffspfad auf diese Nummer legen, so dass SQL hier so optimieren kann, dass tatsächlich nur 1 Zugriff erfolgt.
Ist das nicht der Fall (das könnte auch hier das Problem sein), dauert der Select natürlich relativ lange.

Achja:
"MAX(PSLFNR)+1 is null " ist falsch !!!
"MAX(PSLFNR) is null" wäre richtig, da die Operation +1 im NULL-Fall zum SQL-Fehler führt und die ganze Operation nicht durchgeführt wird.
Um MAX eben nur 1 Mal durchzuführen ist VALUE (COALESCE) schon besser (siehe oben).

[BenderD]

@Baldur,

über den Trigger bekommt man das Sperrproblem auch nicht in den Griff: wenn mit dirty read gelesen wird, können zwei gleichzeitig dieselbe Nummer ziehen und der zweite rennt beim schreiben auf den Hammer. Lese ich mit Repeatable read sperrt das die ganze Table, lässt trotzdem den insert zu und schert sich einen Dreck um den Zugriffspfad.
Besser als die MAX Arie ist es in jedem Fall sich die letzte vergebene Nummer in einer Tabelle zu merken und von dort zu holen und wegen der Sperre mit UPDATE SET maxnbr = maxnbr + 1 where table = sofort zu sperren. Als SQL Function gibt es da was auf meiner Freeware Seite.
Null hat seine eigene Logik: NULL verknüpft mit irgendebbes (wie man in Hessen sagt) ist immer noch NULL und solange man 1 auf das Feld addieren darf, hat SQL nix dagegen.

mfg

Dieter Bender

Über die Triggerlösung geht das ja ganz gut, da ja bei jedem Insert (auch bei Recordlevel-Access) der Trigger auf jeden Fall aufgerufen wird.
Man sollte natürlich einen Zugriffspfad auf diese Nummer legen, so dass SQL hier so optimieren kann, dass tatsächlich nur 1 Zugriff erfolgt.
Ist das nicht der Fall (das könnte auch hier das Problem sein), dauert der Select natürlich relativ lange.

Achja:
"MAX(PSLFNR)+1 is null " ist falsch !!!
"MAX(PSLFNR) is null" wäre richtig, da die Operation +1 im NULL-Fall zum SQL-Fehler führt und die ganze Operation nicht durchgeführt wird.
Um MAX eben nur 1 Mal durchzuführen ist VALUE (COALESCE) schon besser (siehe oben).

[Fuerchau]

OK, mit der NULL gebe ich dir recht.

Aber ich bleibe bei der Triggerlösung.
Die Wahrscheinlichkeit des genau gleichzeitigen Inserts ist sehr gering.
Wenn ich denn einen SQL-Fehler "Doppelter Schlüssel" bekomme, muss ich den Insert nur wiederholen, da ein 2. gleichzeitiger oder sogar 3. immer unwahrscheinlicher wird.
Ich spare mir damit (den manipulierbaren) externen Zähler.

[BenderD]

@Baldur

das mit der Wahrscheinlichkeit, it depends on...
und wenn es denn passiert, dann ist das Unangenehme daran, dass der Wait Record (im default 60 sec.) abgewartet wird und dann der gewinnt. der später kommt.

Dieter

Ich spare mir damit (den manipulierbaren) externen Zähler.

PS: ich wusste garnicht, dass der jetzt extra Lizenzgebühren kostet, oder wird der bei CFINT mitgezählt?

[MrBonZai]

Bei 5 ms werden da immerhin 200 Operationen pro sec ausgeführt, das wären dann bei 15 sec. ja 3000 Datenbankoperationen???

Warum werden in 5ms 200 Operationen durchgeführt?
das ist ein einziger Insert der 5ms braucht, oder habe ich da jetzt etwas falsch verstanden?
d.h. habe ich 30 Datensätze die geschrieben werden müssen (was ja ein wohl ein witz ist oder?) brauche ich 30 x 5 ms = fast 15 Sekunden!!! Das ist für mich und den Benutzer komplett unzumutbar...

Den SQL-String kann ich nicht statisch programmieren, da ich weder weiss welche Datei noch welches Feld verwendet werden soll.
Dies kann der User über eine andere Datei selbst konfigurieren.

Die Datei könnte ich noch rausfinden oder "hart" verdrahten, aber die Felder die ich ansprechen und füllen muss, werden definitiv vom User vorgegeben.
Gibt es da evtl. eine Möglichkeit?

Danke & Gruss
Alex

[BenderD]

Hallo Alex,

für mich waren bisher ms Millisekunden, sprich 1/1000 sec. Wenn Du für 30 Schreiboperationen 15 sec. brauchst, dann sind das pro update 0,5 sec. sprich 1/2 sec., das ist allerdings schon viel und deutet auf fehlende Zugriffspfade oder auf ernsthafte Ressourcenengpässe hin.
Du musst Dir allerdinmgs darüber im klaren sein, dass Dein Anwendungsdesign ebenfalls ein Wackelkandidat ist; wenn ich in eine Datei was x beliebiges reinschreiben kann, was dann zu einem SQL String führt, dann sträuben sich mir die Nackenhaare und die haben zuweilen Recht, aber vielleiicht habe ich noch nicht zu Ende verstanden, was Du da vor hast.

mfg

Dieter Bender

d.h. habe ich 30 Datensätze die geschrieben werden müssen (was ja ein wohl ein witz ist oder?) brauche ich 30 x 5 ms = fast 15 Sekunden!!! Das ist für mich und den Benutzer komplett unzumutbar...

Den SQL-String kann ich nicht statisch programmieren, da ich weder weiss welche Datei noch welches Feld verwendet werden soll.
Dies kann der User über eine andere Datei selbst konfigurieren.

Die Datei könnte ich noch rausfinden oder "hart" verdrahten, aber die Felder die ich ansprechen und füllen muss, werden definitiv vom User vorgegeben.
Gibt es da evtl. eine Möglichkeit?

Danke & Gruss
Alex

[Fuerchau]

Gut, schauen wir uns die Details mal an:

Dein Update scheint mir da weniger ein Problem zu sein, da 3 ms doch sehr schnell ist, also ca. 333 Updates pro Sekunde möglich sind.

Dass dein Insert ca. das doppelte benötigt ist auch verständlich, da dieser nicht nur den Insert sondern auch einen Select enthält. Jeder ca. 3 ms, entspricht immer noch ca. 166 Inserts bzw. 333 Operationen pro Sekunde.
Unter der Berücksichtigung, dass für jeden SQL folgendes abläuft:
1. Die Syntax geprüft
2. Die Datei über die LIBL ermittelt werden muss
3. Jedes Feld geprüft wird, ob es a) vorhanden ist und b) der Wert zum Typ passt
4. Jede Menge interne Call's (sind auch Disk-IO, falls gepaged wurde) ablaufen
Dann finde ich das immer noch verdammt schnell !

Nun betrachte, was auf deinem System noch so läuft:

Wieviele Job's sind gleichzeitig aktiv ?
Wievele Updates/Inserts laufen in diesen noch parallel ?

Dass der einzelne Job da RELATIV lange benötigt, heißt nicht, dass dieser ein Engpass ist !!!

Zur weiteren Analyse schau mal über WRKACTJOB mit F11 auf die Sicht "Abgelaufene Daten". In der Spalte "AuxIO" kannst du über den Messzeitraum (zwischen F10 und F5 ergibt oben die abgelaufene Zeit) sehen, wieviele IO's pro Job gelaufen sind.
Mit F16 kannst du die Spalte sortieren, so dass die größten Werte oben stehen.

Bei mir sehe ich z.B., dass ein Batchprogramm gerade 13000 IO's in einer Minute erzeugt.

Dann betrachte per WRKSYSSTS die Gesamtbelastung:
In den DB/Nicht-DB-Spalten siehst du die Anzahl IO's pro Sekunde mit jeweils 4KB (1 Block).
DB-Seiten sind Datenbankobjekte (PF's, LF's, usw.)
Nicht-DB-Seiten sind z.B. Programmobjekte.
Spalte "fehl." sind Seiten (4K), die aus dem Pool verdrängt und neu gelesen werden
Spalte "geles" sind Seiten (4K), die noch nicht im Pool waren.

Wenn das noch nicht ausreicht, schau auch noch WRKDSKSTS nach.
Zu jeder Spalte gibt es eine Hilfe-Beschreibung (ALT-F1).

Wer jetzt noch behauptet, dass 3ms für Update und 6 ms für Insert langsam sind, der sollte sich mal die Plattenphysik anschauen.
Durchschnittliche Zugriffszeit beträgt pro Platte immer ca. 9-11 ms (je nach Plattentyp).

Die Anzahl der Platten im System und nicht die Größe der Platten ist entscheidend !
Da es nun 8, 16 und auch nun auch 32GB-Platten gibt ist (was Disk-IO angeht) ein System mit 8 * 16GB mit 11ms schneller als mit 4 * 32GB mit 9ms (oder heute PC's mit 120, 160 oder gar 200GB).
Es stehen einfach mehr parallele Zugriffsarme zur Verfügung !

Ganz krass hatte ich einen Kunden, der früher mit 10 * 6GB nun mit 2 * 32GB über Disk-IO-Engpässe stöhnt. Dies ist nur mit mehr Platten oder extrem vergrößertem Hauptspeicher wieder zu beschleunigen, dass ist aber zu teuer, nun stöhnt er lieber.

[MrBonZai]

Wenn Du für 30 Schreiboperationen 15 sec. brauchst, dann sind das pro update 0,5 sec. sprich 1/2 sec., das ist allerdings schon viel und deutet auf fehlende Zugriffspfade oder auf ernsthafte Ressourcenengpässe hin.

du hast recht... war ein kompletter Irrtum meinerseits!
es sind 0.5 Sekunden und nicht 5 ms... sorry.

Benötigt ein INSERT einen speziellen "Zugriffspfad"?
oder kann ich einen speziellen Zugriffspfad erzwingen?

oder ist das nur bei SELECT so?

ein Update braucht derzeit ca. 0.3 Sekunden... ein Insert 0,5 bis 0,6 Sekunden.
wie kann ich denn bei Update/Select einen Zugriffspfad erzwingen so dass ich mir wenigstens diese Zeit sparen kann?