Re: UDF performance issue

21 Dec 2016

      Thank you Alberto,

In my case i am using the same syntax as in a select query, so there must
be something other the calculation in the function itself.
As martin proposed to write a C or Python language, I tried this path with
Python with no luck.

Checking the query trace:

120633 profiler.starttrace();
2 X_32=0@0:void := querylog.define("trace\nselect
db.daybracket(\"Expiry_Date\")from \"db\".\"table1\" limit
1\n;":str,"default_pipe":str,93:int);
7 X_1=0:int := sql.mvc();
13 X_22=[0]:bat[:int] := bat.new(nil:oid,nil:int);
15 X_15=[0]:bat[:str] := bat.new(nil:oid,nil:str);
4 X_31=[1]:bat[:int] :=
bat.append(X_22=[1]:bat[:int],0:int);
5 X_23=[1]:bat[:str] :=
bat.append(X_15=[1]:bat[:str],"db.L":str);
5 X_20=[0]:bat[:int] := bat.new(nil:oid,nil:int);
13 X_43=[140179]:bat[:oid] :=
sql.tid(X_1=0:int,"db":str,"table1":str,0:int,4:int);
3 X_29=[1]:bat[:int] :=
bat.append(X_20=[1]:bat[:int],10:int);
10 X_19=[0]:bat[:str] := bat.new(nil:oid,nil:str);
10 X_18=[0]:bat[:str] := bat.new(nil:oid,nil:str);
5 X_27=[1]:bat[:str] :=
bat.append(X_19=[1]:bat[:str],"char":str);
4 X_25=[1]:bat[:str] :=
bat.append(X_18=[1]:bat[:str],"daybracket_Expiry_Date":str);
400 (X_52=[0]:bat[:oid],X_53=[0]:bat[:date]) :=
sql.bind(X_1=0:int,"db":str,"table1":str,"Expiry_Date":str,2:int,0:int,4:int);
4105 X_48=[140179]:bat[:date] :=
sql.bind(X_1=0:int,"db":str,"table1":str,"Expiry_Date":str,0:int,0:int,4:int);
237 X_60=[140179]:bat[:date] :=
sql.projectdelta(X_43=[140179]:bat[:oid],X_48=[140179]:bat[:date],X_52=[0]:bat[:oid],X_53=[0]:bat[:date]);
5221 barrier X_87=false:bit := language.dataflow();
13 X_107=[0]:bat[:str] := bat.new(nil:oid,nil:str);
236 barrier (X_111=0@0:oid,X_112="2016-02-12":date) :=
iterator.new(X_60=[140179]:bat[:date]);
4 sql.mvc();
1 calc.str("pdate":str);
0 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
1 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
2 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
50 function user.daybracket(Apdate:date):str;
63 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
2 bat.append(X_107=[1]:bat[:str],X_114="2016-02-12":str);
217 redo (X_111=1@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
3 sql.mvc();
1 calc.str("pdate":str);
0 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
1 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
45 function user.daybracket(Apdate:date):str;
55 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
1 bat.append(X_107=[2]:bat[:str],X_114="2016-02-12":str);
295 redo (X_111=2@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
1 sql.mvc();
1 calc.str("pdate":str);
1 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
1 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
44 function user.daybracket(Apdate:date):str;
58 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
1 bat.append(X_107=[3]:bat[:str],X_114="2016-02-12":str);
232 redo (X_111=3@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
4 sql.mvc();
1 calc.str("pdate":str);
0 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
1 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
46 function user.daybracket(Apdate:date):str;
55 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
2 bat.append(X_107=[4]:bat[:str],X_114="2016-02-12":str);
214 redo (X_111=4@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
4 sql.mvc();
0 calc.str("pdate":str);
0 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
1 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
44 function user.daybracket(Apdate:date):str;
53 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
1 bat.append(X_107=[5]:bat[:str],X_114="2016-02-12":str);
243 redo (X_111=5@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
4 sql.mvc();
0 calc.str("pdate":str);
0 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
1 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
45 function user.daybracket(Apdate:date):str;
54 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
3 bat.append(X_107=[6]:bat[:str],X_114="2016-02-12":str);
214 redo (X_111=6@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
3 sql.mvc();
1 calc.str("pdate":str);
1 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
1 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
44 function user.daybracket(Apdate:date):str;
53 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
2 bat.append(X_107=[7]:bat[:str],X_114="2016-02-12":str);
212 redo (X_111=7@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
3 sql.mvc();
1 calc.str("pdate":str);
1 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
0 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
46 function user.daybracket(Apdate:date):str;
54 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
2 bat.append(X_107=[8]:bat[:str],X_114="2016-02-12":str);
240 redo (X_111=8@0:oid,X_112="2016-02-12":date) :=
iterator.next(X_60=[140179]:bat[:date]);
3 sql.mvc();
1 calc.str("pdate":str);
0 X_6="%Y-%m-%d":str := calc.str("%Y-%m-%d":str);
0 X_8="2016-02-12":str :=
mtime.date_to_str(X_5="2016-02-12":date,X_6="%Y-%m-%d":str);
1 X_9="2016-02-12":str :=
calc.str(4:int,0:int,0:int,0:int,X_8="2016-02-12":str,10:int);
44 function user.daybracket(Apdate:date):str;
53 X_114="2016-02-12":str := user.daybracket(X_112="2016-02-12":date);
2 bat.append(X_107=[9]:bat[:str],X_114="2016-02-12":str);

On Wed, Dec 21, 2016 at 4:26 PM, Alberto Ferrari <
aferrari@starconnecting.com> wrote:
...
Imad, we had the same problem (you can find our mail in list with
subject "Functions in Monetdb", date nov/22), and had no satisfactory
solution yet.
I think monetDb is not good for UDF yet... (though is great db engine!)
2016-12-21 10:40 GMT-03:00 imad hajj chahine 
...
:
Hi,
I have a weird behavior when i package some sql code in a function, the
performance deteriorate.
The same code running directly in select statement is blinking fast.
ex:
select date_to_str("Expiry_Date",'%Y-%m-%d') from table: exec time is
0.72s
if i create a function:
CREATE FUNCTION DayBracket(pdate date) returns char(10)
BEGIN
    return date_to_str(pdate,'%Y-%m-%d');
end;
grant execute on function DayBracket to public;
and execute select DayBracket("Expiry_Date") from table: exec time is 24s
even when i set the limit to 1 its taking the same time, so i guess it
should be something related to loading the function.
The following function perform much better:
CREATE FUNCTION DayBracketOpt(pdate date) returns char(10)
BEGIN
    return extract(year from pdate) || '-' || lpad(extract(month from
pdate),2,'0') || '-' || lpad(dayofmonth(pdate),2,'0');
end;
grant execute on function DayBracketOpt to public;
I also tried to create a Python function:
CREATE FUNCTION pyDayBracket(pdate date) returns string
LANGUAGE PYTHON {
from time import strftime, strptime
return [strftime("%Y-%m-%d", strptime(pdt,"%Y-%m-%d")) for pdt in pdate]
};
it is taking too long and throwing an exception:buffer size mismatch
PS: the pdate array type is detected in pythons as string not as date;
Also how to delete a function with dependency, I need to replace some
functions which are used in others functions and views. Any flags to turn
off dependency check before dropping?
Thank you.
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list