Search

User defined function performance is unacceptable by Simon Sabin

Active

100
1
Sign in
 to vote
Type: Bug
ID: 524983
Opened: 1/14/2010 11:22:39 AM
Access Restriction: Public
Primary Feedback Item: 273443
1
Workaround(s)
7
User(s) can reproduce this bug
Scalar user defined functions are the logical implementation unit for repeated code. However unlike most languages the use of scalar user defined functions in SQL Server can cripple performance.

This is especially true in case of reporting style queries that process many rows. The time taken for processing the function is proportional to the number fo rows, unlike most other SQL functions where the are optimisations for doing large bulk processing.

Whats more the user of UDFs blocks the use of parallel plans which also hurts reporting style applications.

There needs to be a way for these to be compiled and not to have such a detrimental impact on performance.

http://sqlblogcasts.com/blogs/simons/archive/2008/11/03/TSQL-Scalar-functions-are-evil-.aspx
Details (expand)
Product Language
English

Version

SQL Server 2008 SP1

Category

SQL Engine

Operating System

Not Applicable
Operating System Language
Not Applicable
Steps to Reproduce
Create a user defined function
Run a query using that against a million row table
Compare to doing the same inline
Actual Results
Performance is orders of magnitude worse
Expected Results
Comparabel performance to using system functions inline

Platform

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Arnoud van Bers on 6/23/2011 at 3:08 AM
It is still not fixed and it's also not closed as a duplicate of
https://connect.microsoft.com/SQLServer/feedback/details/273443/the-scalar-expression-function-would-speed-performance-while-keeping-the-benefits-of-functions#tabs
Posted by IsmailS on 4/7/2011 at 10:52 PM
@Bob Sovers1!
Here is the link https://connect.microsoft.com/SQLServer/feedback/details/273443/

Posted by Bob Sovers1 on 8/19/2010 at 1:06 PM
If there is a duplicate, could you post a link to the actual item, instead of just inserting the number?
Posted by Microsoft on 1/19/2010 at 11:09 AM
Hi Simon,

Thanks for your feedback, we are aware of this issue and it is infact already tracked by connect item 273443. I'll go ahead and close this one as a duplicate.

Thanks for your help!
- Tobias
Posted by TechVsLife2 on 1/19/2010 at 12:49 AM
This is a well-know problem. The workaround is to use an inline table function instead that returns one row and one value (select val from yourtablefunction().
Posted by Matt Brazier on 1/17/2010 at 9:00 AM
Perhaps I'm not a very good sql coder, but I do not see a big performance problem when using UDFs. I see performance problems when I have not written the sql code very well, or if I have not designed my database very well.

I'll admit, I have not read your "scalar functions are evil". I will read it when I get a chance. Perhaps your blog will explain to me why some of my millions of records tables perform pretty well with UDF's, both scalar and table.
Sign in to post a workaround.
Posted by TechVsLife2 on 1/19/2010 at 12:50 AM
Use an inline table function instead that returns one row and one value
set %var = (select val from yourinlinetablefunction(param) )
rather than set %var = yourscalarfunction(param).
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement