SQL Server Home
User defined function performance is unacceptable
1/14/2010 11:22:39 AM
Primary Feedback Item:
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.
SQL Server 2008 SP1
Operating System Language
Steps to Reproduce
Create a user defined function
Run a query using that against a million row table
Compare to doing the same inline
Performance is orders of magnitude worse
Comparabel performance to using system functions inline
to post a comment.
Please enter a comment.
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.
* 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)
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
on 4/7/2011 at 10:52 PM
Here is the link https://connect.microsoft.com/SQLServer/feedback/details/273443/
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?
on 1/19/2010 at 11:09 AM
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!
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().
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.
to post a workaround.
Please enter a workaround.
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