Re: UDF performance issue

AFAIK, a lacking of time is the main/only reason. Whenever possible, we gradually add the missing bulk impl. for a function. Sometime ago (the first commit was on Dec 17, 2013), we added something called Manifold, which will try to automatically generate a bulk version for a function, if it doesn’t have an explicitly implemented bulk version. However, this may not be possible for every function. Moreover, the generated bulk version may not be as efficient as an explicit implementation.

On 27 Dec 2016, at 19:49, imad hajj chahine wrote:

Hi Jennie,

After examining the file /module/atoms/mtime.c, I understand why using the extract version perform much better from the date_to_str version, as the extract has a bulk version while the date_to_str doesn't. Any reason why you skipped the creation of a bulk version from date_to_str? Also the bulk version is missing for the dayofyear, dayofweek, weekofyear functions.

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 :

...

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