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
as
begin 
  Declare @Commission money
  Select @Commission = (@Price/100.000)*5
  return @Commission
end

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
as
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
as
select 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.