Performance of T-Sql query inside scalar user-defined function

Question

I recently reviewed a colleague's SQL Server function (in SQL Server 2008) where he had put a T-SQL query within a scalar user-defined function. That function was then used in the Select clause of a query.

I thought that having a query inside a function would have terrible performance because I assumed each row returned would have to run the query - even though that query is optimised to query on an index.

Here's an example of what I'm talking about:

create table [PERSON] (
ID int primary key,
FIRSTNAME NVARCHAR(100),
MIDDLENAME NVARCHAR(100) null,
LASTNAME NVARCHAR(100))
GO

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (1, 'BOB', 'M', 'BLUE')

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (2, 'VALERIE', 'J', 'GREEN')

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (3, 'SIMON', 'D', 'RED')

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (4, 'LIONEL', 'W', 'BROWN')
GO

-- Scalar Function with T-SQL
CREATE FUNCTION dbo.fn_FormatNameFromId(
            @pnPersonId int
            )
Returns nvarchar(300)
AS
Begin
    return (Select FIRSTNAME + ' ' + MIDDLENAME + ' ' + LASTNAME
        From PERSON
        Where ID = @pnPersonId)
End
go

-- Scalar Function without T-SQL
CREATE FUNCTION dbo.fn_FormatNameFromValues(
            @psFirstName nvarchar(100),
            @psMiddleName nvarchar(100),
            @psLastName nvarchar(100)
            )
Returns nvarchar(300)
AS
Begin
    return (@psFirstName + ' ' + @psMiddleName + ' ' + @psLastName)
End
go

-- T-SQL within function
select dbo.fn_FormatNameFromId(ID)
from PERSON

-- Pass values directly
select dbo.fn_FormatNameFromValues(FIRSTNAME, MIDDLENAME, LASTNAME)
FROM PERSON

When comparing the execution plans for both these selects, I could find no difference in the cpu or memory performance of the two functions. Even when scaling up the test to return tens of thousands of rows, there was no performance difference.

Can anybody explain how or why the fn_FormatNameFromId is not causing a loss of performance?

Probably it is because of caching. You are loading all data from table and Sql Server load all pages into memory. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Nov 17, 2015 at 6:39
"I thought ... would have terrible performance" - it may have worse performance than an equivalent, but the first question you should be asking yourself is - is the current performance acceptable? That is, do you have performance goals, and have you measured the current performance? If it is performing acceptably, why are you spending time looking at it? If it's not performing acceptably, you already have your answer. And if you've not set goals and measured, how do you know you're spending time looking at where performance needs to be and can be improved? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Nov 17, 2015 at 7:38
@Damien_The_Unbeliever, Yeah, the performance turned out to be fine. I just wanted to understand how it worked so I could worry/not worry about it in the future. In terms of best-practice though we shouldn't rely on the optimiser. — antman
– antman, Commented Nov 18, 2015 at 0:18

Rodion · Accepted Answer · 2015-11-17 08:39:13Z

1

You can see the difference between Estimated Execution Plan and Actual Execution Plan

You are personally expecting select dbo.fn_FormatNameFromId(ID) from PERSON to work as shown on the screenshot: for every row from Query 1 (Clustered Index Scan) do Query 2 (Clustered Index Seek - or Subquery search by ID)

But sql-server sees the overall pattern of these queries and optimizes actual query plan to solely one simple Clustered Index Scan as sql-server does in your second query select dbo.fn_FormatNameFromValues(FIRSTNAME, MIDDLENAME, LASTNAME) FROM PERSON.

answered Nov 17, 2015 at 8:39

Rodion

88610 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2018-11-13 23:16:58Z

1

Personally i am not a geat fan of t-sql functions as far as PERFORMANCE is concern so i usally avoid them as much as possible in SQL SERVER,

YES i know there are many many advantages of user functions in programming.

lets go to your questions directly

When comparing the execution plans for both these selects, I could find no difference in the cpu or memory performance of the two functions

i usually dont compare executions plan to check which plan is better and which is worse on the bases of subtree/cpu costs because these costs are just based on estimated values.

Can anybody explain how or why the fn_FormatNameFromId is not causing a loss of performance?

because you are still dealing with little data i tested your both function like below:

declare @a int=1
Declare @name varchar(50)
while (@a<99999)
Begin 
-- T-SQL within function
select @name = dbo.fn_FormatNameFromId(ID)
from PERSON
SET @a =@a+1
END

it ran on my local system in 13 second

declare @a int=1
Declare @name varchar(50)
while (@a<99999)
Begin 

-- Pass values directly
select @name =dbo.fn_FormatNameFromValues(FIRSTNAME, MIDDLENAME, LASTNAME)
FROM PERSON
SET @a =@a+1
END

it ran on my local system in 9 secound

Ahhhh i said i am not great fan of functions so did run without functions

declare @a int=1
Declare @name varchar(50)
while (@a<99999)
Begin 

-- Pass values directly


Select @name= FIRSTNAME + ' ' + MIDDLENAME + ' ' + LASTNAME
        From PERSON


SET @a =@a+1
END

and it ran in 2 seconds in my system

please read the article below

T-SQL User-Defined Functions: the good, the bad, and the ugly

edited Nov 13, 2018 at 23:16

CommunityBot

11 silver badge

answered Nov 17, 2015 at 7:30

Neeraj Prasad Sharma

1,70514 silver badges16 bronze badges

3 Comments

antman Over a year ago

Whilst I agree in principal that functions can cause problems, when used in the right circumstances they are very useful. I'm not sure your example is really testing the T-SQL performance, rather the overhead required in producing the execution plan and executing the query. If you generated 99,999 rows in the PERSON table and ran the query, I think you will find no difference in performance (when using the functions). There might be a small performance gain in NOT using the function, but in my scenario, a function is better than repeating the code in hundreds of stored procedures.

Neeraj Prasad Sharma Over a year ago

@antman i have linked one atricle above please read out that.

J Brun Over a year ago

The tl;dr for the article is partly this, the cost of scalar functions, (CPU usage, etc.) don't show up in execution plans. That's another one of their drawbacks, they're a black box.

Collectives™ on Stack Overflow

Performance of T-Sql query inside scalar user-defined function

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related