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

[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. 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