SQL and the like

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

October 2009 - Posts

Another T-SQL Challenge

Phil Factor has posted up another SQL challenge.  Its a goodie and a $60 amazon voucher is up for grabs. 

Give it a try here

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.

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