UDF and Performance

  • Article

This was one of the thread entry in SQL discussion by Umachandar who is from SQL Product Team, Microsoft.    Take a look a the SCHEMABINDING information on blog site.  

I think it is a question of when to use UDFs. You should simply avoid UDFs in SELECT statements or DMLs since there are lot of performance implications. For example, if you use scalar UDFs in UPDATE statement the query optimizer can introduce additional spools in the plan which will be very expensive. The other problem is the lack of statistics for scalar UDFs and multi-statement table-valued functions. In SQL Server 2005, you can use the SCHEMABINDING option on scalar UDFs to give better hints to the query optimizer as to the nature of the UDF (does it do data access or not for example). But you will always get the best performance by inlining the expression in the UDF for example. So it is best to avoid using scalar UDFs in cases where you are trying to simplify usage of an expression multiple times in a SELECT statement. This can really prove to be a major performance problem. To summarize:

1. Use scalar UDFs in SELECT or DMLs sparingly (in predicates or select list or SET clause of UPDATE)

2. Using scalar UDFs to avoid repeating simple or even complex expressions that are easily inlineable in queries or DMLs will cause performance problems

3. Use multi-statement table-valued functions carefully. The lack of statistics means that the query optimizer often makes a guess as to the cardinality of the rows returned and plan choice can be bad

4. Using SCHEMABINDING option for UDFs in SQL Server 2005 can provide some performance advantages. See https://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx

5. Using scalar UDFs in procedural code to simplify logic is better than using those in set-based statements

6. Avoid doing data access or external process activation from within UDFs since they can cause performance problems or unexpected results also depending on their usage

7. If there are built-ins available to do a particular function then use those in queries or DMLs as opposed to wrapping them in a TSQL scalar UDF

8. Consider replacing TSQL scalar UDFs that perform complex computations with SQLCLR UDFs. See https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp