UDF in UPDATE affect performance ???

Question

Post reply

UDF in UPDATE affect performance ???

karthik M

One Orange Chip

Points: 29139
More actions
November 2, 2010 at 6:43 am

#242481

All,
will UDF in an UPDATE statement affect the performance? I hope so.
UPDATE #Result
SET DataSourceID = dbo.fnGetAsset(AssetID, AssetType_Code)
Assume #Result table has 400000 records.
Innputs are welcome!
karthik

Viewing 15 posts - 1 through 15 (of 19 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Scalar UDFs are generally slow.

Can you post DDL statements for your UDF and involved tables?

-- Gianluca Sartori

karthik M One Orange Chip Points: 29139 More actions · Answer 2

CREATE TABLE #Results(

,Issue VARCHAR(500)

,AssetID INT

,AssetName VARCHAR(1000)

,AssetType_Code INT NULL

,AssetTypeName VARCHAR(1000)

,DataSourceID INT NULL

)

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 3

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

karthik

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Scalar UDFs generally speaking don't handle well this kind of situation.

Consider rewriting it as an ITVF or code the update directly in the calling SQL:

UPDATE R

SET DataSourceID =

CASE UPPER(dbo.fnGetValueAndType(R.AssetType_Code, 'AssetType'))

WHEN 'HFUND' THEN H.DataSourceId

WHEN 'INDEX' THEN I.DataSourceId

ELSE R.DataSourceID

END

FROM #Result AS R

LEFT JOIN (

SELECT ID, DataSourceID

FROM tfund

WHERE IsActive = 'Y'

AND IsDeleted = 'N'

) AS H

ON H.ID = R.AssetID

LEFT JOIN (

SELECT ID, DataSourceID

FROM tIndex

WHERE IsActive = 'Y'

AND IsDeleted = 'N'

) AS I

ON I.ID = R.AssetId

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Not only that but the nested UDF of fnGetValueAndType , could be doing a monstrous amount of work too.

Clear Sky SQL
My Blog[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Agreed. Since I don't have the definition for that UDF I didn't include it in the rewrite.

I suggest comparing the exec plans with and without the UDFs.

-- Gianluca Sartori

karthik M One Orange Chip Points: 29139 More actions · Answer 7

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE FUNCTION [dbo].[fnGetValueAndType]

(

@Value int

,@Typevarchar(100)

)

RETURNS VARCHAR(1000)

AS

BEGIN

DECLARE @Name VARCHAR(1000)

SELECT @Name = [Name]

FROM tCodeLookup

WHERE Type = @Type

AND [Value] = @Value

AND IsActive = 'Y' AND IsDeleted = 'N'

RETURN @Name

END

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 8

I have also seen the below code

WHERE ABS(Round(@AVG - @Return,2)) >= @TSTTData

I hope this one also lead performance issue.

Let me take a deep look into the query.

karthik

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

That should be in an if statement,

It doesnt reference ANY table data

Clear Sky SQL
My Blog[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Performance will likely be horrible here. You simply MUST refactor the code to inline the UDFs into the update itself. Even if you need to use interim temp tables to do so it will be MUCH faster.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

tfifield SSCrazy Eights Points: 9655 More actions · Answer 11

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

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

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.

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

karthikeyan-444867 (11/2/2010)
All,
will UDF in an UPDATE statement affect the performance? I hope so.
UPDATE #Result
SET DataSourceID = dbo.fnGetAsset(AssetID, AssetType_Code)
Assume #Result table has 400000 records.
Innputs are welcome!

Karthik... you and I go a long way back and I've never understood why you ask questions like this. Why don't you just test it and see for yourself? 😉

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

Jeff Moden (11/4/2010)
karthikeyan-444867 (11/2/2010)
All,
will UDF in an UPDATE statement affect the performance? I hope so.
UPDATE #Result
SET DataSourceID = dbo.fnGetAsset(AssetID, AssetType_Code)
Assume #Result table has 400000 records.
Innputs are welcome!
Karthik... you and I go a long way back and I've never understood why you ask questions like this. Why don't you just test it and see for yourself? 😉

? No Faith ?