View Full Version : Embedded SQL - Performance-Probleme
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
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.
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.
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
Ü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).
@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 = <tablename> 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).
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.
@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?
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
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