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