By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Resources used by UDF

P: n/a
Ray
I know that a query inside a called UDF does not show up when displaying the
estimated query plan, but are the resources (CPU, DiskIO) used by a UDF that
is call from within an SProc or embedded in a SQL statement included in
sp_who2 and Profiler BatchCompleted?

Also how performance draining is it to do something like the following. I
assume for every row returned the udf will be called causing 8000 table
scans on DateTable?

Create Functionu udf_GetCountDates (@StartDate datetime, @EndDate datetime)
Returns Int
begin
Return(
select count(*) from DateTable
where ColDate > @TestDate and Col1Date <
)
end

--DateTable has 5,000 rows

Create proc Test
@TestDate datetime
As
select Col1, Col2, udf_GetCountDates(@TestDate, Col2)
From OTable

--OTable has 8,000 rows
Go

Thanks,
Ray
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Ray (so*****@nowhere.com) writes:
I know that a query inside a called UDF does not show up when displaying
the estimated query plan, but are the resources (CPU, DiskIO) used by a
UDF that is call from within an SProc or embedded in a SQL statement
included in sp_who2 and Profiler BatchCompleted?
I think so, but I am not going to swear on it.
Also how performance draining is it to do something like the following. I
assume for every row returned the udf will be called causing 8000 table
scans on DateTable?


Yes. So would a direct subquery in the SELECT list to, but there is
quite some overhead for a scalar UDF in SQL 2000. Generally be careful
with scalar UDF, and benchmark before using them. There can be drastic
performance penalty, as the UDF more or less transform the query into
a cursor internally.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.