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 a
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