UDF in UPDATE affect performance ???

Question

UDF in UPDATE affect performance ???

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

Lynn Pettis (11/4/2010)
tfifield (11/4/2010)
UDF's don't necessarily hurt performance. I write UDF's all the time to do string manipulation type actions. These aren't much slower than built in functions like SUBSTRING(). The problem comes when the UDF has to select something from a table. This means a separate select for each row returned from the main query and is horrible RBAR in most cases.
I've seen this go so far as to select something in the UDF from a row in a table that was already selected in the main query that called the UDF. The optimizer didn't know that the row was already selected in the main query and so the UDF selected it again - very inefficient.
When the UDF has to select something from a table it is much better to do an In Line Table function (not multi-statement as these are just about as bad as scalar UDF's on performance) when possible and compile it with SCHEMABINDING. I've found these to perform quite well.
Todd Fifield
Really? Have you looked at the this blog post, Comparing Hardcoded functions, In-Line TVF's, and Scalar Functions? You may be slightly surprised.

I believe you and Todd are saying the same thing. Todd DIDN'T say "scalar functions". In fact, he went out of his way to say to use iTVF's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

karthikeyan-444867 (11/3/2010)
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[fnGetAsset]
(
@AssetIDINT
,@AssetType_CodeINT
)
RETURNS INT
AS
BEGIN
DECLARE @DataSourceIDINT
DECLARE @AssetTypeNameVARCHAR(100)
SET @AssetTypeName = dbo.fnGetValueAndType(@AssetType_Code, 'AssetType')
IF @AssetID IS NULL OR @AssetType_Code IS NULL
SET @DataSourceID = NULL
ELSE
BEGIN
IF UPPER(@AssetTypeName) = 'HFUND'
SELECT @DataSourceID = DataSourceID
FROM tfund
WHERE ID = @AssetID
AND IsActive = 'Y'
AND IsDeleted = 'N'
ELSE IF UPPER(@AssetTypeName) = 'INDEX'
SELECT @DataSourceID = DataSourceID
FROM tIndex
WHERE ID = @AssetID
AND IsActive = 'Y'
AND IsDeleted = 'N'
END
RETURN @DataSourceID
END

You have a scalar function being called by a scalar function. Put your set-based hat on and write just one inline Table Valued Function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Jeff Moden (11/4/2010)
Lynn Pettis (11/4/2010)
tfifield (11/4/2010)
UDF's don't necessarily hurt performance. I write UDF's all the time to do string manipulation type actions. These aren't much slower than built in functions like SUBSTRING(). ...
I believe you and Todd are saying the same thing. Todd DIDN'T say "scalar functions". In fact, he went out of his way to say to use iTVF's.

Actually, it is this particular part of his quote I was addressing. If you check out the code I used in my blog you'll see it is just calling a built-in function like he indicates he uses in a scalar function. I was surprised to see the results I had. I really didn't think it would have such a significant impact calling a built-in function in a scalar function, but it did.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Ah... understood. Thanks, Lynn.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)