SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

UDF Overhead – A simple example

When Microsoft first announced that in SQLServer 2000 they were introducing user defined functions,  I thought “Excellent, that will really help my system”.  However,  it soon became apparent that there is a big overhead in calling them.  Here’s a really simple example of how to waste some system resources. 

Here is a simple udf

Create function Sales.CalcCommission(@Price Money)
returns money
@Commission money
@Commission = (@Price/100.000)*5
return @Commission

Nothing special there, it just calculates a sale persons commission at a rate of 5%.  So lets apply that to the whole of the SalesOrderDetail table.

select UnitPrice,Sales.calcCommission(UnitPrice)  From Sales.SalesOrderDetail;

Job done,  report written, push the code live and even better you have a function that can be re-used in different pieces of code many times over.  But what cost has that come as ?  Using ‘SET STATISTICS TIME’ is pretty graphic.

select UnitPrice,(UnitPrice/100.000)*5  From Sales.SalesOrderDetail
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 784 ms.
select UnitPrice,Sales.calcCommission(UnitPrice)  From Sales.SalesOrderDetail;
SQL Server Execution Times:
CPU time = 1625 ms, elapsed time = 1795 ms.

Yes, its takes over twice as long using the udf as not,  and just look at the CPU time.  So what are your alternatives ?  An inline function ?

Create Function Sales.InlineCalcCommission(@Price Money)
returns table
return select
(@Price/100.000)*5 as Commission;
select UnitPrice,Commission.Commission
From Sales.SalesOrderDetail cross apply Sales.InlineCalcCommission(UnitPrice) as Commission
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 984 ms.

Which is much better,  or my own personal favourite (call me old fashioned)

Create View SalesWithCommission
UnitPrice,(UnitPrice/100.000)*5 From Sales.SalesOrderDetail
Add this to the fact that the execution costs of functions are hidden in an execution plan,  this is why they are my penultimate port of call.  The last being a cursor.

Update 24Nov2009 : Please see my follow up blog post at http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/11/24/the-observer-effect-in-action.aspx


Dew Drop – October 15, 2009 | Alvin Ashcraft's Morning Dew said:

Pingback from  Dew Drop – October 15, 2009 | Alvin Ashcraft's Morning Dew

# October 15, 2009 1:19 PM

My Weekly Bookmarks for October 16th | Brent Ozar - SQL Server DBA said:

Pingback from  My Weekly Bookmarks for October 16th | Brent Ozar - SQL Server DBA

# October 16, 2009 10:59 PM

SQL and the like said:

I’ve put my hand up to to a quick 10 - 15 minute slot at the London user group, so I was getting my scripts

# November 24, 2009 10:16 AM

Tony Rogerson's ramblings on SQL Server said:

This Thursday 26th is the London SQL User Group and also the Edinburgh UG; in London we have Tony Rogerson

# November 24, 2009 12:58 PM

Handy SQL Server knowledge nuggets « C# Hacker – The Rambling Coder said:

Pingback from  Handy SQL Server knowledge nuggets «  C# Hacker – The Rambling Coder

# January 25, 2010 4:41 AM