I second that the DETERMINISTIC clause should make a difference if you're not already using it. I'm also wondering how you're using this returned value. Is it simply in the select portion of the statement, or are you using it in the where clause, where you weren't doing that in your comparison statement. I ask, because when you say "longer than an index-based select statement", it implies either an advanced index, or, you're not using the right term.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Salsman, Michael
Sent: Friday, May 10, 2019 10:13 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: UDF bad performance
Try defining the function as DETERMINISTIC.
Michael Salsman
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Justin Taylor
Sent: Friday, May 10, 2019 07:30
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: UDF bad performance
I have this UDF that returns an integer:
BEGIN
RETURN CEILING ( LENGTH * WIDTH * HEIGHT / 166 ) ; END
Calls to this UDF take a lot longer than I expect (about 21x longer than an index-based SELECT statement). I checked all the parameters, and the data types match verbatim so there shouldn't be any implicit conversions.
Any suggestions?
TIA
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=BEYrPw2fcoEGBjXw4NBdHt1LVNU_cTqq0v1BUOEwcJw&s=AOPPA4PHkGkmlBABSJUfUfTXDqL2I2S9yFaQF5HwfBw&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=BEYrPw2fcoEGBjXw4NBdHt1LVNU_cTqq0v1BUOEwcJw&s=Qr0s-4_s8uF1I0paOGM4zJGSWG0J72irhG2likUsFPM&e= .
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=BEYrPw2fcoEGBjXw4NBdHt1LVNU_cTqq0v1BUOEwcJw&s=St6QlqY8NesgfCU_1fjF81Jl5iPxjYa5TspZyIAVI-o&e=
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
