Re: iNext Revealed As IBM i 7.3

[Peter Dow <petercdow@xxxxxxxxx>]

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
>
> Would be neat to have an SQL function return a result set.  That way you
> > could join to the SQL function.
> >     select   a.custName, b.ordnum, b.orderDate
> >     from    custMaster a
> >    join     GetCustomerOrders( a.custNum ) b