Search

User defined function performance is unacceptable by Simon Sabin

Active

110
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)
10
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 jeff-42 on 8/2/2012 at 11:35 AM
The issue does not happen consistently for us. The same query will often perform at expected speeds (fast), but sometimes several orders of magnitude more slowly (for example, 3 milliseconds versus 650 ms, as observed in SQL Server Profiler). Restarting the SQL Server service always restores performance for a span of several hours to several days.

The UDF in question is used in a single SQL text query in an SSRS subreport (so the query is executed multiple times with one varying parameter). SQL Server and SSRS are on the same server.

The UDF:

* Always performs as expected when the query is executed in SSMS
* Is always the last step in query execution (SSMS "Actual Execution Plan")
* Consists of calls to several other UDFs which include string concatenation, REPLACE function, CONVERT function, HASHBYTES function, UPPER function, LEFT function (i.e. there is *no* table access in the UDF)
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).
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement