× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.