Re: UDF performance issue

21 Dec 2016

      Hey Imad,

Not sure what the problem is; your function works fine for me. Note that it won't be extremely efficient either, because you are using a loop in Python and hence calling a function for every value as well. Maybe the pandas version of this function would work better for you:

CREATE FUNCTION pydate(d DATE) RETURNS STRING LANGUAGE PYTHON {
    import pandas as pd
    return pd.to_datetime(d).strftime('%Y-%m-%d')
};

Note that if you just want to convert dates to strings you can use the following Python UDF as well (but then there's not much point in using a Python UDF at all):

CREATE FUNCTION pydate(d DATE) RETURNS STRING LANGUAGE PYTHON { return d };

I'm not sure what you mean with "Python doesn't recognize the date type from MonetDB". Dates aren't a fully supported type in Python UDFs; and any not fully supported type is converted to a string as a fallback. You can input and return dates from Python UDFs but they will be converted to/from strings when passed between MonetDB and Python.

Hope that helps,

Mark

----- Original Message -----
From: "imad hajj chahine" 
To: "users-list" 
Sent: Wednesday, December 21, 2016 4:24:26 PM
Subject: Re: UDF performance issue

Hi Jennie,

Thank you for the answer, the unexpected version of the function take 1.1
sec to execute, the table contains 500k values.

You mentioned Bulk functions, I tried to achieve this with python with no
luck, can you please check my Python function syntax and let me know the
problem.
*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]*
*};*

Throws a buffer size mismatch exception.
Also why python don't recognize the date type from monetdb?

Thank you.

On Wed, Dec 21, 2016 at 5:11 PM, Ying Zhang  wrote:
...
Hai
...
On 21 Dec 2016, at 15:26, Alberto Ferrari 
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!)
Mostly, it’s because monetdb can’t automatically bulk execute user defined
functions.
So for UDFs, the overhead of a function call is multiplied by the number
of values to process.
While for built-in functions, they all have a bulk version, so that they
are only called once with all values to process.
...
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.
Using LIMIT in the outer query doesn’t help, your UDF is till called with
every value.
The final tuple is only filtered after that.
...
...
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;
Hmm, a bit unexpected.  How much better is it?  How many values are
processed?
...
...
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?
I think you’ll have to drop the depending functions and views first.
Regards,
Jennie
...
...
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
_______________________________________________
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

Re: UDF performance issue

Mark Raasveldt