udf vs query in sp - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

udf vs query in sp

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-07-19 : 12:49:10

I have 2 scenarios that runs a query and the query inside an UDF. I get the following stats. I see the query gets physical reads 3. However the UDF shows an elapsed time = 97 ms. Which one is faster?

--------------------------------------------------------------------------------------------------------------
DECLARE @UserID int
SET @UserID = (SELECT Users.UserID FROM sso.Users WITH (READUNCOMMITTED) WHERE Users.UserName = 'Grant328');
SELECT @UserID

--QUERY--
--------------------------------------------------------------------------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Users'. Scan count 0, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

-----------
1

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------------------------------------------------------------------------------
ALTER FUNCTION [dbo].[GetUserID]
(

@UserName VARCHAR (50))
RETURNS INT AS
BEGIN

Declare @UserID INT

SELECT @UserID = Users.UserID from sso.Users WITH (READUNCOMMITTED) WHERE Users.UserName = @UserName

Return @UserID
END

--UDF--
--------------------------------------------------------------------------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

-----------
1

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 97 ms.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-19 : 12:52:22

Avoid functions as much as possible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-19 : 14:24:41

quote:
I see the query gets physical reads 3. However the UDF shows an elapsed time = 97 ms. Which one is faster?

Let me rephrase this slightly:

I see a mountain goat with 4 legs. However the mountain lion weighs 200 kilos. Which one is faster?

You're comparing 2 completely different measurements, and not providing us any context (the query? the UDF?) where we can help.

Also, didn't we provide answers to this yesterday? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176910

What's wrong with that thread that you can't continue with it? It's very frustrating to provide answers and advice, which is then ignored, and to see the same question posted again.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-07-23 : 13:39:29

thanks, We decided to use inline sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources