User Defined Function Investigation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

User Defined Function Investigation

Author

Topic

cfederl
Starting Member

26 Posts

Posted - 2005-12-04 : 07:24:09

I am in the process of investigating User Defined Functions including developing an alternative single SQL statement solution and then comparing the performance of the UDF vs SQL solution for the duration, logical reads and CPU resources used. I hope to write an article regarding this investigation I any meaningful conclusions are reached.

Matthew L. Wigdahl wrote an article titled "Are UDFs Harmful to SQL Server Performance?" which performs such an comparison. See http://www.sql-server-performance.com/mw_sql_server_udfs.asp

You assistance is needed to insure that as many solutions using UDFs are investigated. If you know of a situation where a UDF is superior, please provide the situation, preferable re-producible using the Northwind schema.

One requirement that can only be done with UDFs, at least under SQL Server 2000, is concatenate column values from multiple rows into a single columns in a single row at described at http://www.aspfaq.com/show.asp?id=2529

The conclusion I am reaching is why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard.

Below is an example of a UDF and the Alternative with performance statistics captured thru a trace. Note that using "set statistics" for IO or time does not capture the resources used by the UDF, hence the use of a trace.

UDF SQL
Duration 15665 4106
Reads 56133 20934
CPU 4806 1002

create function dbo.Customers_OrderCount
( @CustomerID nchar (5)
)
RETURNS INTEGER
AS
BEGIN
RETURN (SELECT COUNT(*) from dbo.Orders where CustomerId = @CustomerID)
END
go

declare @loop int
set @loop = 1
while @loop < 100
begin
set @loop = @loop + 1
select Customers.CustomerID
, SUM(CASE when Orders.CustomerID is null then 0 else 1 end )
from dbo.Customers
LEFT OUTER JOIN dbo.Orders
on Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID
end
declare @loop int
set @loop = 1
while @loop < 100
begin
set @loop = @loop + 1
select Customers.CustomerID
, dbo.Customers_OrderCount( Customers.CustomerID )
from dbo.Customers
end

Carl Federl

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-12-04 : 11:56:10

I am doing a similiar study of kitchen appliances. I have placed equal amounts of raw chicken in my refrigerator, and my oven. After 45 minutes, the chicken in the oven is done, but the chicken in the refrigerator is still inedible. I have therefore concluded that my refrigerator is useless, and I am going to padlock it to prevent all of my family members from using it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-04 : 12:25:30

Better keep scalpels out of the hands of surgeons too, they only just cut people with them.

Seriously, you're taking one bad example and classifying all UDFs as useless and dangerous?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-04 : 13:16:17

Someone made a chicken with a UDF? Huh??? What did I miss here? Oh....I'm so confused.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

cfederl
Starting Member

26 Posts

Posted - 2005-12-04 : 13:46:34

"I am doing a similiar study of kitchen appliances. I have placed equal amounts of raw chicken in my refrigerator, and my oven. After 45 minutes, the chicken in the oven is done, but the chicken in the refrigerator is still inedible. I have therefore concluded that my refrigerator is useless, and I am going to padlock it to prevent all of my family members from using it."

Very amusing.

I also did a study of kitchen appliances: After a week the chicken in my oven was in-edible but the chicken in my refrigerator was fine.

The question is what is the correct usage for UDFs? Here is an example of a correct usage:

CREATE FUNCTION Factorial
( @number int )
RETURNS bigint
AS
BEGIN
DECLARE @i int, @factorial bigint
IF @number > 20 RETURN NULL
SET @i = 1
SET @factorial = 1
WHILE @i <= @number
BEGIN
SET @factorial = @factorial * @i
SET @i = @i + 1
END
RETURN @factorial
END
GO
select dbo.Factorial(7)

Carl Federl

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-12-04 : 13:56:52

so what's the incorrect usage?

Go with the flow & have fun! Else fight the flow

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-04 : 16:02:55

You made the assertion that "...you should never, ever, under any circumstances use UDFs...". Then you made the statement “The question is what is the correct usage for UDFs? Here is an example of a correct usage:…”.

The statements seem to be at odds with each other. Both statements are highly questionable, but there is really nothing for anyone to rebut here, since both statements are unsupported by any evidence.

If you are going to make either of these contradictory statements, you should support them with some line of reasoning, or some evidence.

quote:
Originally posted by cfederl
...The question is what is the correct usage for UDFs? Here is an example of a correct usage:...

CODO ERGO SUM

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-04 : 16:40:17

>> You assistance is needed to insure that as many solutions using UDFs are investigated.

If you are serious about this..

1) Deterministic vs Non-deterministic UDF performance in SELECT statements
2) Deterministic/Non-deterministic UDF performance vs other solution in SELECT statements
3) Deterministic vs Non-deterministic UDF performance when intergrated into CHECK constraints and there impact on INSERT and UPDATE statements.
4) UDF performance vs VIEWs for complex constraint enforcement.

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-04 : 18:31:12

quote:
Originally posted by cfederl
One requirement that can only be done with UDFs, at least under SQL Server 2000, is concatenate column values from multiple rows into a single columns in a single row at described at http://www.aspfaq.com/show.asp?id=2529

....snip....

The conclusion I am reaching is why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard.

As Michael says, that's pretty big contradiction you've got there. You're saying we should never ever use UDF's but at the same time telling us a situaton in which UDF's offer a superior solution?

Now, if you say "you should never use UDF's to perform table lookups or other operations that can be replaces with joins" then you are on to something.

cfederl
Starting Member

26 Posts

Posted - 2005-12-04 : 20:59:38

The condition I have identified so far:

1. Deterministic UDFs vs In-Line SQL in the SELECT section of SQL statement
2. Non-Deterministic UDFs vs In-Line SQL in the SELECT section of SQL statement
3. Deterministic UDFs vs In-Line SQL in the WHERE section of SQL statement
4. Non-Deterministic UDFs vs In-Line SQL in the WHERE section of SQL statement
5. Deterministic UDFs vs In-Line SQL as a CHECK Constraint
6. Non-Deterministic UDFs as CHECK Constraint vs In-Line SQL in a Trigger

DavidM
What would be a test case for “UDF performance vs Views for complex constraint enforcement”?

Jsmith:

“You're saying we should never ever use UDF's but at the same time telling us a situaton in which UDF's offer a superior solution?

Now, if you say "you should never use UDF's to perform table lookups or other operations that can be replaces with joins" then you are on to something.”

This is what I am trying to determine with an objective benchmark where the results are reproducable.

Carl Federl

cfederl
Starting Member

26 Posts

Posted - 2005-12-04 : 21:04:12

Some additional information for refernces that I am using:
Ken Henderson's "The Guru's Guide to SQL Server Stored Procedures, XML and HTML" which offers some interesting test cases.

Joseph Gama's "TSQL functions" at http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5 These UDFs appear to be all deterministic.

Andrew Novick's UDF library at http://www.novicksoftware.com/

Carl Federl

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-04 : 21:38:54

>>What would be a test case for “UDF performance vs Views for complex constraint enforcement”?

Given Northwind..

Rule: Only allowed to sell a product if there is enough of that product.

Option 1: Use a View and only allow insertion to the view
Option 2: Create a UDF and implement as CHECK constraint.


use northwind
go
create view Products_InStock
as
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount 
FROM dbo.[Order Details] OD
WHERE EXISTS(SELECT 1 FROM dbo.Products P where P.ProductID = OD.ProductID and P.UnitsInStock >= OD.Quantity)
with check option
go
create function ProductIsInStock
(
@ProductID int,
@Quantity int
)
returns bit
as
begin
declare @result bit
set @result = 0
if exists(SELECT 1 FROM dbo.Products P where P.ProductID = @ProductID and P.UnitsInStock >= @Quantity)
	set @result = 1
return @result
end
go

EDIT: Opps.. swapped "less than" to "greater than" operator

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-04 : 21:41:02

quote:
Originally posted by cfederl
Jsmith:

“You're saying we should never ever use UDF's but at the same time telling us a situaton in which UDF's offer a superior solution?

Now, if you say "you should never use UDF's to perform table lookups or other operations that can be replaces with joins" then you are on to something.”

This is what I am trying to determine with an objective benchmark where the results are reproducable.

Carl Federl

Then why the heck would you make a statement like

"The conclusion I am reaching is why you should never, ever, under any circumstances use UDFs. e.g. DENY CREATE FUNCTION to public should be standard."

???

You aren't making any sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources