Re: iNext Revealed As IBM i 7.3

Hi Peter,

With the table function in a CTE, I don't see a way to pass input
parameters to the table function from the portion of the query outside the
CTE, hence I've never tried that.

In cases where there was no need to pass input parameters to the UDTF from
outside the CTE, that might help, but I'm not too optimistic about it
making a difference. I'm curious about it though. I'll tinker with that
over the next day or two and see if it makes a difference.

Mike

date: Thu, 25 Feb 2016 12:57:58 -0800
from: Peter Dow <petercdow@xxxxxxxxx>
subject: Re: iNext Revealed As IBM i 7.3

Hi Mike,

Would putting the table function alone in a CTE, then joining the result
of the CTE with a table perform any better? SQL might be able to join
that result better.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 2/24/2016 10:32 AM, Mike Jones wrote:

Hi Steve,

You can join to table functions now. I do a lot with table functions.
That said, it can be challenging to get good performance with table
functions and joins. Their results are a black box that return some

data,

and it is therefore difficult for the SQL optimizer to predict that
returned set and optimize it with joined data. As a result, I use them
more for performing actions against the database in the middle of set
processing. I also use a table function to call CL programs in the

middle

of set processing. You can get good performance with fetching data via
table functions and joins, but it tends to take more engineering effort

to

make that happen versus a query with joins that does not involve table
functions.

Mike