SQL-Laufzeit durch Funktion erheblich länger?

[cbe]

Hallo liebe SQL-Spezialisten,

ein Kollege hat eine nette Funktion programmiert, das aus 3 numerischen Feldern für JJJJ, MM, TT ein Zeichenfeld im ISO-Format macht. Also z.B. JMT2DATE(2011, 4, 1) würde '2011-04-01' liefern.

Das funktioniert auch prima, nur die Laufzeit ist katastrophal schlecht.



with x as (select JMT2DATE(TSL10J, TSL10M, TSL10T) from SSLTST)
select count(*) from x


braucht für 100000 Sätze ca 1 Minute, während


with x as (select digits(TSL10J) concat '-' concat digits(TSL10M) concat '-' concat TSL10T from SSLTST)
select count(*) from x


deutlich unter 1 Sekunde benötigt!
(Das "with x as ..." habe ich nur eingebaut, um die Laufzeit prüfen zu können)


Haben Funktionen so einen Overhead, oder ist die vielleicht nur ungeschickt programmiert?
Vieleicht könnt Ihr mal einen Blick auf die Funktion werfen:


CREATE FUNCTION MHK/JMT2DATE(p_JJ dec(4, 0),
p_MM dec(2, 0),
p_TT dec(2, 0))
RETURNS CHAR(10)
LANGUAGE SQL
SPECIFIC JMT2DATE1
func1_lab:
BEGIN
DECLARE s_date CHAR(10);
DECLARE InvalidDate CONDITION FOR '22007';
DECLARE EXIT HANDLER FOR InvalidDate
BEGIN
RETURN '1900-01-01';
END;
IF p_JJ IS NULL OR p_jj = 0 THEN
RETURN '1900-01-01';
END IF;
IF p_JJ < 50 THEN
SET p_JJ = 2000 + p_JJ;
END IF;
IF p_JJ < 100 THEN
SET p_JJ = 1900 + p_JJ;
END IF;
SET s_date = trim(char(p_JJ)) concat '-'
concat trim(char(p_MM)) concat '-'
concat trim(char(p_TT)) ;

RETURN s_date;
END

drop specific function mhk/JMT2DATE2
CREATE FUNCTION MHK/JMT2DATE(p_JJ INTEGER,
p_MM INTEGER,
p_TT INTEGER )
RETURNS CHAR(10)
LANGUAGE SQL
SPECIFIC JMT2DATE2
func2_lab:
BEGIN
DECLARE s_date CHAR(10);
DECLARE InvalidDate CONDITION FOR '22007';
DECLARE EXIT HANDLER FOR InvalidDate
BEGIN
RETURN '1900-01-01';
END;
IF p_JJ IS NULL OR p_jj = 0 THEN
RETURN '1900-01-01';
END IF;
IF p_JJ < 50 THEN
SET p_JJ = 2000 + p_JJ;
END IF;
IF p_JJ < 100 THEN
SET p_JJ = 1900 + p_JJ;
END IF;
SET s_date = trim(char(p_JJ)) concat '-'
concat trim(char(p_MM)) concat '-'
concat trim(char(p_TT)) ;

RETURN s_date;
END

drop specific function mhk/JMT2DATE3
CREATE FUNCTION MHK/JMT2DATE(p_Date INTEGER)
RETURNS CHAR(10)
LANGUAGE SQL
SPECIFIC JMT2DATE3
func3_lab:
BEGIN
DECLARE s_date CHAR(10);
DECLARE InvalidDate CONDITION FOR '22007';
DECLARE EXIT HANDLER FOR InvalidDate
BEGIN
RETURN '1900-01-01';
END;
IF p_Date IS NULL OR p_Date = 0 THEN
RETURN '1900-01-01';
END IF;
SET s_date = substr(digits(p_Date), 1, 4) concat '-' concat
substr(digits(p_Date), 5, 2) concat '-' concat
substr(digits(p_Date), 7, 2);

RETURN s_date;
END

drop specific function mhk/JMT2DATE4
CREATE FUNCTION MHK/JMT2DATE(p_Date decimal(8, 0) )
RETURNS CHAR(10)
LANGUAGE SQL
SPECIFIC JMT2DATE4
func3_lab:
BEGIN
DECLARE s_date CHAR(10);
DECLARE InvalidDate CONDITION FOR '22007';
DECLARE EXIT HANDLER FOR InvalidDate
BEGIN
RETURN '1900-01-01';
END;
IF p_Date IS NULL OR p_Date = 0 THEN
RETURN '1900-01-01';
END IF;
SET s_date = substr(digits(p_Date), 1, 4) concat '-' concat
substr(digits(p_Date), 5, 2) concat '-' concat
substr(digits(p_Date), 7, 2);

RETURN s_date;
END



Fällt Euch dazu was ein?
Mit Funktion ist es schon besser lesbar, aber so arg die Laufzeit strapazieren darf es auch nicht.


Gruß,
Christian

[BenderD]

100.000 Aufrufe in 60 Sekunden sind 1600 in der Sekunde, d.h. die Function liegt unter einer Millisekunde, das ist´durchaus akzeptable.

Im übrigen ist deine Benchmark wahrscheinlich nicht korrekt, bei deinem 2. SQL wird die concat Operation zur Ausführung nicht benötigt.

D*B

[cbe]

stimmt:


with x as (select digits(TSL10J) concat '-' concat digits(TSL10M)
concat '-' concat TSL10T as d from SSLTST)
select max(d) from x

braucht immerhin 3 Sekunden.

Leider ist die Funktion immer noch mehr als 10x langsamer.

Wenn das so ist, dann ist das halt so, ist aber schade.

[Fuerchau]

So ist er nun mal, der Optimizer.
Was effektiv nicht gebraucht wird, wird weggeschmissen oder intern sogar umcodiert.

Du kannst deinen Benchmark nur vergleichen, in dem du den SQL etwas aufborst:


with x as (select digits(TSL10J) concat '-' concat digits(TSL10M) concat '-' concat TSL10T
from SSLTST where TSL10J <> 0) select count(*) from x
</PRE>

[BenderD]

... an der function kann man sicher noch etwas optimieren; zuerst sollte man die Option DETERMINISTIC und RETURNS NULL on NULL INPUT angeben, ALLOW PARALLEL könnte auch noch was bringen - ob da in den Details noch was steckt, habe ich mir nicht angesehen...

D*B

[andreaspr@aon.at]

Die Option NOT FENCED könnte auch helfen, dadurch wird die Funktion nicht in einem seperatem Thread ausgeführt. Das wären dann 100.000 Threads die nicht erstellt und bereinigt werden müssten.

[Fuerchau]

Das wäre nur das Einsparen eines Threads, da nicht je Satz ein Thread gestarte wird!

[cbe]

danke für die Tips + Infos,
ich habe es mal ausprobiert mit
DETERMINISTIC, RETURNS NULL on NULL INPUT, ALLOW PARALLEL und NOT FENCED

Es ist tatsächlich ca. 1/4 schneller geworden - aber trotzdem noch deutlich langsamer als der einfache digits + concat.

Mein Kollege lässt es jetzt bei der Version ohne Funktion.

Gruß, Christian

[BenderD]

... kann man natürlich auch in eine View reinnageln, wg. dem Comfort.

Das grundsätzlich problematische an der Einbindung von Functions in Select Statements ist, dass der Optimizer nicht weiß, was die Function da zurück liefert, da fängt man sich leicht einen Fulltable scan ein.

D*B

[cbe]

... kann man natürlich auch in eine View reinnageln, wg. dem Comfort.
genau das hat der Kollege dann auch gemacht


Das grundsätzlich problematische an der Einbindung von Functions in Select Statements ist, dass der Optimizer nicht weiß, was die Function da zurück liefert, da fängt man sich leicht einen Fulltable scan ein.
ich muss gestehen, dass der Optimierer eh ein wenig Voodoo für mich ist.:rolleyes: Die meisten Sachen, die ich brauche macht er ganz gut, manchmal muss ich ihn ein wenig in die richtige Richtung drängeln...
Aber das ist ein ausuferndes Thema, was ich hier gar nicht weiter vertiefen will - Jetzt ist Feierabend + Feiertag:D