User defined function performance is awful, Micorosft please sort this out
User defined functions looked like something great when
they were introduced in SQL 2005. They are a very logical step to make more code
reuseable by wrapping common code in functions. It is an approach that is in
development 101 and something everyone does and looks to doing.
So what is the problem with SQL Server.
Well the issue is that whilst there are great from a reducation of duplicated
code the performance sucks, and sucks big time. I wrote about this a year
ago TSQL Scalar
functions are evil and have
spoke about it at many usergroups over the years.
Almost every client I've seen over the past 2 years has implemented functions
because it is the logical thing to do. However they've then been bitten big
style by the performance issue. To make the matter worse use of scalar functions
also gets in the way of supporting code, try and do a stmt level profile or
something I found this week try and capture query plans in profiler. Because
each of these is considered a block of code it results in stmtCompeleted and
starting events to fire and for query plan event to fire.
At a recent client I was able to tune a query from 3 minutes to 3 seconds
using the tricks in "TSQL Scalar
functions are evil"
Scalar user defined functions are the worst thing in SQL
Server and something needs to be done about them. I feel this is a bug in the
product and have posted as such here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273443
If you have been bitten by this bug please vote for the item on connect go
here "Vote
for changing user defined functions so they perform better "
[SS 2010/01/19 I've been told my connect item is a duplicate and so has been
closed. SO please vote on this item instead https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273443 The
links above now point to this connect item]