UDF performance issue

older
Merry Christmas and Happy 2017

imad hajj chahine

21 Dec 2016 21 Dec '16

1:40 p.m.

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.

Attachments:

attachment.html (text/html — 2.9 KB)

Show replies by date

Alberto Ferrari

21 Dec 21 Dec

2:26 p.m.

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 :

...

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

Ying Zhang

3:11 p.m.

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

imad hajj chahine

3:24 p.m.

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

Mark Raasveldt

4:09 p.m.

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

Ying Zhang

4:17 p.m.

...

On 21 Dec 2016, at 16:24, imad hajj chahine wrote:

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 mean the faster version of the function took 1.1 sec? How long did the slower one take?

...

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.

I’m sorry that I’m not a python expert. But I saw Mark has already answered. Regards, Jennie

...

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 :

...
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

imad hajj chahine

5:45 p.m.

Thank you Mark and Jennie, The purpose of this function is to create date brackets, like month and quarter brackets not only to convert date to string. I handled null values and now its working and returning Nat for null values. Any idea on how to return null instead NaT to stay in the SQL standards? I also noticed that i cant aggregate over the python function, for instance if i create a view with a column referring the Python UDF and i execute select count(columnPythonUDF) from table1 an error occured. Am I excepting too much from a Python UDF to behave like a normal SQL UDF, as I didnt face this error when aggregating over an SQL UDF. *CREATE FUNCTION pyDayBracket(d DATE) RETURNS STRING LANGUAGE PYTHON {* * import pandas as pd* * return pd.to_datetime(d, coerce=True).strftime('%Y-%m-%d')* *};* as for the execution time: 1. directly invoking str_to_date in select query took 0.73 sec 2. UDF with concat date parts tool 1.1 sec 3. UDF calling str_to_date took 24 sec Thank you. On Wed, Dec 21, 2016 at 6:17 PM, Ying Zhang wrote:

...

...
On 21 Dec 2016, at 16:24, imad hajj chahine wrote:

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 mean the faster version of the function took 1.1 sec? How long did the slower one take?

...
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.

I’m sorry that I’m not a python expert. But I saw Mark has already answered.

Regards, Jennie

...
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 <

...
...
Hi,

I have a weird behavior when i package some sql code in a function,

imad.hajj.chahine@gmail.com>: 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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

imad hajj chahine

7:36 p.m.

Hi Jennie, I also have the same problem with this date related function. *CREATE FUNCTION DateAdd(pdate date, pdays int) returns date* *BEGIN* * return select pdate + cast(pdays as interval day);* *end;* On Wed, Dec 21, 2016 at 7:45 PM, imad hajj chahine < imad.hajj.chahine@gmail.com> wrote:

...

Thank you Mark and Jennie,

The purpose of this function is to create date brackets, like month and quarter brackets not only to convert date to string. I handled null values and now its working and returning Nat for null values. Any idea on how to return null instead NaT to stay in the SQL standards? I also noticed that i cant aggregate over the python function, for instance if i create a view with a column referring the Python UDF and i execute select count(columnPythonUDF) from table1 an error occured. Am I excepting too much from a Python UDF to behave like a normal SQL UDF, as I didnt face this error when aggregating over an SQL UDF.

*CREATE FUNCTION pyDayBracket(d DATE) RETURNS STRING LANGUAGE PYTHON {* * import pandas as pd* * return pd.to_datetime(d, coerce=True).strftime('%Y-%m-%d')* *};*

as for the execution time:

1. directly invoking str_to_date in select query took 0.73 sec 2. UDF with concat date parts tool 1.1 sec 3. UDF calling str_to_date took 24 sec

Thank you.

On Wed, Dec 21, 2016 at 6:17 PM, Ying Zhang wrote:

...
...
On 21 Dec 2016, at 16:24, imad hajj chahine < imad.hajj.chahine@gmail.com> wrote:

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 mean the faster version of the function took 1.1 sec? How long did the slower one take?

...
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.

I’m sorry that I’m not a python expert. But I saw Mark has already answered.

Regards, Jennie

...
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 < 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!)

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,

imad.hajj.chahine@gmail.com>: 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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Ying Zhang

22 Dec 22 Dec

2:12 p.m.

...

On 21 Dec 2016, at 18:45, imad hajj chahine wrote:

Thank you Mark and Jennie,

The purpose of this function is to create date brackets, like month and quarter brackets not only to convert date to string. I handled null values and now its working and returning Nat for null values. Any idea on how to return null instead NaT to stay in the SQL standards? I also noticed that i cant aggregate over the python function, for instance if i create a view with a column referring the Python UDF and i execute select count(columnPythonUDF) from table1 an error occured. Am I excepting too much from a Python UDF to behave like a normal SQL UDF, as I didnt face this error when aggregating over an SQL UDF.

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

as for the execution time: • directly invoking str_to_date in select query took 0.73 sec • UDF with concat date parts tool 1.1 sec • UDF calling str_to_date took 24 sec

If you TRACE your queries, you shall be able to see where the time went. Also, the Tomograph tool (https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph) might give you some explanations… Jennie

...

Thank you.

On Wed, Dec 21, 2016 at 6:17 PM, Ying Zhang wrote:

...
On 21 Dec 2016, at 16:24, imad hajj chahine wrote:

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 mean the faster version of the function took 1.1 sec? How long did the slower one take?

...
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.

I’m sorry that I’m not a python expert. But I saw Mark has already answered.

Regards, Jennie

...
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 :

...
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

_______________________________________________ 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

imad hajj chahine

27 Dec 27 Dec

6:49 p.m.

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 <

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

Ying Zhang

30 Dec 30 Dec

3:53 p.m.

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

imad hajj chahine

21 Dec 21 Dec

3:14 p.m.

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=[0]:bat[:int] := bat.new(nil:oid,nil:int); 15 X_15=[0]:bat[:str] := bat.new(nil:oid,nil:str); 4 X_31=[1]:bat[:int] := bat.append(X_22=[1]:bat[:int],0:int); 5 X_23=[1]:bat[:str] := bat.append(X_15=[1]:bat[:str],"db.L":str); 5 X_20=[0]:bat[:int] := bat.new(nil:oid,nil:int); 13 X_43=[140179]:bat[:oid] := sql.tid(X_1=0:int,"db":str,"table1":str,0:int,4:int); 3 X_29=[1]:bat[:int] := bat.append(X_20=[1]:bat[:int],10:int); 10 X_19=[0]:bat[:str] := bat.new(nil:oid,nil:str); 10 X_18=[0]:bat[:str] := bat.new(nil:oid,nil:str); 5 X_27=[1]:bat[:str] := bat.append(X_19=[1]:bat[:str],"char":str); 4 X_25=[1]:bat[:str] := bat.append(X_18=[1]:bat[:str],"daybracket_Expiry_Date":str); 400 (X_52=[0]:bat[:oid],X_53=[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=[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=[140179]:bat[:date] := sql.projectdelta(X_43=[140179]:bat[:oid],X_48=[140179]:bat[:date],X_52=[0]:bat[:oid],X_53=[0]:bat[:date]); 5221 barrier X_87=false:bit := language.dataflow(); 13 X_107=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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=[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
...
: 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

2991

Age (days ago)

3000

Last active (days ago)

List overview

Download

11 comments

4 participants

participants (4)

Alberto Ferrari
imad hajj chahine
Mark Raasveldt
Ying Zhang

UDF performance issue

tags

participants (4)