User defined function performance is unacceptable
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.
Upvotes: 196<=-=Jan 17 2010 9:00AM=-=>
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.<=-=Jan 19 2010 12:49AM=-=>
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().<=-=Jan 19 2010 11:09AM=-=>
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!
If there is a duplicate, could you post a link to the actual item, instead of just inserting the number?<=-=Apr 7 2011 10:52PM=-=>
Here is the link https://connect.microsoft.com/SQLServer/feedback/details/273443/
It is still not fixed and it’s also not closed as a duplicate of
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)