RE: UDF bad performance

I'll check out your links. Thank you



-----Original Message-----
From: Sue Romano [mailto:slromano@xxxxxxxxxx]
Sent: Friday, May 10, 2019 12:16 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: re: UDF bad performance

This simple function should be eligible for inlining. You will need to remove the BEGIN and END from the function definition and make sure it is defined as DETERMINISTIC. A function that is inlined is rerwritten into the query that uses it by the optimizer, removing the overhead of calling the function completely.

Detailed information about inline functions (applies to both scalar and table functions) can be found at the following link and in the SQL Reference.

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Improved%20Inlining%20support%20for%20SQL%20Scalar%20User%20Defined%20Functions%20(UDFs)

Sue Romano
IBM i SQL development

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?