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]

 


Published Thursday, January 14, 2010 7:21 PM by simonsabin

Comments

Friday, January 15, 2010 11:44 AM by Dave Ballantyne

# re: User defined function performance is awful, Micorosft please sort this out

Personally, I think that if the Parser sees

Select * from table where UDF(val) = X

or (worse)

Select * from scalartableUDF()

       where X=Y

It should error with "Dont be a twat"

Select udfFunc(val) from table

is terrible but not quite as bad

Friday, January 15, 2010 3:31 PM by SqlServerKudos

# User defined function performance is awful, Micorosft please sort this out

Kudos for a great Sql Server article - Trackback from SqlServerKudos

# Twitter Trackbacks for User defined function performance is awful, Micorosft please sort this out - Simon Sabin UK SQL Consultant's [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 User defined function performance is awful, Micorosft please sort this out - Simon Sabin UK SQL Consultant's         [sqlblogcasts.com]        on Topsy.com

Saturday, January 16, 2010 10:37 PM by TheSQLGuru

# re: User defined function performance is awful, Micorosft please sort this out

Gotta disagree Dave.  Select udfFunc(val) from table can be just as bad if the function has ancillary data access and causes row-by-row processing under the covers.

# Something for the weekend: SQL Server Links for the week 14/01/10 | John Sansom - SQL Server DBA in the UK

Pingback from  Something for the weekend: SQL Server Links for the week 14/01/10 | John Sansom - SQL Server DBA in the UK