RE: Re: SQL UDF to return one column from a row -- MIDRANGE-L

The rule is, if it is no longer possible to do with an view or if you need
more than a single step, then create and use an UDTF.

BTW global variables can be included within views.
Before executing the view, just set the global variable(s) with an SQL SET
statement.
In this way it is even possible to replace UDTFs (which need only a single
step) with a view. For example for recursions where the starting point has
to be set in the recursive Common Table Expression (CTE).

On the other side, if an UDTF consists only of the RETURN statement, the
query optimizer can now (since one of the latest enhancements) handle the
UDTF like a View.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mark
Murphy/STAR BASE Consulting Inc.
Sent: Montag, 3. April 2017 18:03
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Re: SQL UDF to return one column from a row

I think in this case you are correct, but not in every case. I have a set of
UDTF's that calculate bill of materials for a part because using a view
performed exceedingly poorly. With the UDTF I can pass in a part that I want
the BOM generated for, using the View, it tries to calculate all possible
BOM's. The UDTF is way faster.

Mark Murphy
Atlas Data Systems
mmurphy@xxxxxxxxxxxxxxx

-----Mike Jones <mike.jones.sysdev@xxxxxxxxx> wrote: -----
To: midrange-l@xxxxxxxxxxxx
From: Mike Jones <mike.jones.sysdev@xxxxxxxxx>
Date: 03/31/2017 02:03PM
Subject: Re: SQL UDF to return one column from a row

To simplify queries and joins used repeatedly, views should be your primary
solution object of choice.

I dabbled a bit with UDFs that perform I/Os, and can confirm the often
dramatically worse performance, especially when large amounts of data are
present in the table(s) referenced. I would never again use a scalar UDF
that performs I/Os.

I have one or two, rarely used, table UDFs that perform I/Os over a small
table that I wrote a long time ago, that I used as a last resort. But, I
really should review them to see if I really needed a table UDF. They do
perform slowly.

Scalar UDFs that don't perform I/Os, thereby taking parameters inputs,
performing a calculation on them, and returning the result (without
performing I/Os), perform very well.

I agree 100% with Birgitta on this: "UDFs perform normally worse than
joins", but then again, I agree with her advice virtually every time.

Mike

date: Fri, 31 Mar 2017 07:27:06 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: SQL UDF to return one column from a row

What About:

Create View YourFirstView
As (Select a.*, yourText
From yourFile a Join YourTextFile on ....);

Create View NextView
As (Select *
From YourFirstView ....)

Then use the view you need (no need to code anything over and over again).

UDFs perform normally worse than joins. A UDF is for the query optimizer

black box.
Joins are visible to the query optimizer, independent whether they are
entered directly or whether they are located in even nested views.
In this way the query optimizer can find better solutions when using views
contrary to UDFs.

Example: If you have a UDF that returns the customer name.
With an UDF a "chain" must be performed for each row. If the same customer
is returned in multiple rows, the UDF is called for each row.
If a JOIN is used instead, the optimizer might be able to find an access
plan that reads the customer information only once.

Mit freundlichen Gr??en / Best regards

Birgitta Hauser