February 6, 2008
Warning About User Defined Functions in SQL Queries
I was looking at a query today that has an inline expression in it, something similar to
CASE WHEN myTable.myColumn < 5 THEN NULL ELSE myTable.myColumn END
This particular piece of business logic is used in many different places (well, not literally, since I made up a fake example), so I created a UDF to simplify the queries that use this statement. Suppose that UDF is called dbo.fnNulifyMyColumn.
Now let's suppose I originally had the following query:
SELECT CASE WHEN myTable.myColumn < 5 THEN NULL ELSE myTable.myColumn END FROM myTableAnd replaced it with:
SELECT dbo.fnNullifyMyColumn(myTable.myColumn) FROM myTable
The logic works perfectly. But I noticed that, in my real world scenario, the performance went down substantially. In fact, my query went from requiring 9 seconds to execute up to 27 seconds!
Turns out that there is a lot of overhead in using a function. I decided to see if Itzik Ben-Gan has anything to say about this in his book Inside Microsoft SQL Server 2005: T-SQL Programming. He does: "There are many benefits to using UDFs in terms of code simplicity and maintenance; though in terms of performance, typically you'll be better off if you manage to express your calculations as inline expressions inteh query and avoid using UDFs."
Comments