- Advantages of UDFs
- UDF Limitations
- Misuses of UDFs
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Misuses of UDFs
You have seen some advantages and disadvantages of UDFs. In my previous article I also showed you a few UDFs that can be helpful in your applications. Just because you can write a UDF does not mean that it is the best tool in every situation. In this section I'll go over a couple of instances where UDF is a poor choice.
I mentioned earlier that you can join the output of the UDFs to other tables. Be aware that scalar UDFs are logically executed once per row. So, if you join the output of a scalar function to a table with a million rows, your UDF is likely to be executed 1,000,000 times. Therefore, a scalar UDF must be as efficient as possible.
One of the worst things you can do is reinvent the wheel. As it applies to UDFs, this means writing a user-defined function when there are system functions available to do the same. Hence, there is the need to know system-supplied functions well.
For instance, the following UDF uses a conditional statement to determine whether the royalty is NULL and replaces the NULL value with 0:
CREATE FUNCTION dbo.bad_udf_royalty (@royalty INT)
RETURNS INT
AS
BEGIN
DECLARE @royalty1 INT
IF @royalty IS NULL
BEGIN
SELECT @royalty1 = 0
END
ELSE
SELECT @royalty1 = @royalty
RETURN @royalty1
END
You can use this UDF to replace NULL royalty values in the output of the following query:
SELECT title_id, dbo.bad_udf_royalty(royalty) FROM titles
Everything looks cool until you check out the execution time: 30 milliseconds for 18 rows of output. Instead of doing such nonsense, you could easily use the ISNULL system-supplied function to accomplish the same thing, as follows:
SELECT title_id, ISNULL(royalty, 0) FROM titles
This query runs in less than a millisecond. Now if the performance is 30 times better for 18 rows, imagine what would happen to a query involving millions of rows! One of my consulting engagements optimized a query involving a poor scalar UDF executed repeatedly across many queries. Replacing that single poor UDF with built-in functions resulted in 120 times more efficient application.
Other UDFs that typically don't work well are the ones that call other UDFs, which in turn call other UDFs, and so forth. It is true that UDFs bring the capability to encapsulate some logic into a module that can be called from within a query. However, just because you can rewrite your stored procedure with numerous UDFs doesn't necessarily guarantee performance improvement. Poorly written code and poor indexes on your tables are not mitigated by UDFs. Instead, you should examine the causes of the performance issue at stake and try to change your physical model or the offending block of code.