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=<tmp_30374>[0]:bat[:int] := bat.new(nil:oid,nil:int); 15 X_15=<tmp_35032>[0]:bat[:str] := bat.new(nil:oid,nil:str); 4 X_31=<tmp_30374>[1]:bat[:int] := bat.append(X_22=<tmp_30374>[1]:bat[:int],0:int); 5 X_23=<tmp_35032>[1]:bat[:str] := bat.append(X_15=<tmp_35032>[1]:bat[:str],"db.L":str); 5 X_20=<tmp_42074>[0]:bat[:int] := bat.new(nil:oid,nil:int); 13 X_43=<tmp_24353>[140179]:bat[:oid] := sql.tid(X_1=0:int,"db":str,"table1":str,0:int,4:int); 3 X_29=<tmp_42074>[1]:bat[:int] := bat.append(X_20=<tmp_42074>[1]:bat[:int],10:int); 10 X_19=<tmp_35141>[0]:bat[:str] := bat.new(nil:oid,nil:str); 10 X_18=<tmp_25467>[0]:bat[:str] := bat.new(nil:oid,nil:str); 5 X_27=<tmp_35141>[1]:bat[:str] := bat.append(X_19=<tmp_35141>[1]:bat[:str],"char":str); 4 X_25=<tmp_25467>[1]:bat[:str] := bat.append(X_18=<tmp_25467>[1]:bat[:str],"daybracket_Expiry_Date":str); 400 (X_52=<tmp_2031>[0]:bat[:oid],X_53=<tmp_4241>[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=<tmp_35357>[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=<tmp_37213>[140179]:bat[:date] := sql.projectdelta(X_43=<tmp_24353>[140179]:bat[:oid],X_48=<tmp_35357>[140179]:bat[:date],X_52=<tmp_2031>[0]:bat[:oid],X_53=<tmp_4241>[0]:bat[:date]); 5221 barrier X_87=false:bit := language.dataflow(); 13 X_107=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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=<tmp_37213>[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=<tmp_24574>[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 <imad.hajj.chahine@gmail.com
: 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