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