UDFs Endanger Performance

Three of my last five performance-tuning clients faced problems associated with user-defined functions (UDFs). As SQL Server 2000's customer base matures and becomes more comfortable with the product's advanced features, more people are using UDFs without recognizing the problems they might cause. Many customers operate under a false sense of security regarding UDF I/O efficiency because Query Analyzer's SET SHOW_STATISTICS I/O option doesn't report I/Os associated with a UDF. Last May, I wrote about UDFs' insidious row-by-row nature, calling them cursors in sheep's clothing (see "Beware Row-by-Row Operations in UDF Clothing"). This week, I want to revisit the topic and raise awareness about potential UDF performance problems.

Many of you know that ANSI T-SQL cursors are evil and must be avoided at all costs (unless writing very slow and inefficient T-SQL code is your primary goal). You know that cursors are row-by-row operations, as opposed to efficient, set-based operations. However, few people understand the subtle ways that UDFs can cause a set-based operation to act like a row-by-row operation. Let's look at a simple example to illustrate the problem. Imagine you have an Employee table with 100,000 rows, a Department table with 50 distinct values, and a ranking system that assigns an employee annual-review grade derived from data stored in other database tables. Your boss wants a query that will return the average annual-review grade—avg(AnnualReviewGrade)—for each department. Writing the query would be simple if AnnualReviewGrade were a column in the table, but it isn't. So your lead developer writes a UDF called GetAnnualReviewGrade that accepts an EmployeeId and returns the grade.

Let's think through the UDF's row-by-row implications. Say that SQL Server can process the UDF in a modest 15 logical I/Os. The query will execute the UDF once for each row that needs to be evaluated—in this case, once for each employee (100,000 times total). That means the UDF alone adds 1.5 million logical I/Os to the query's processing cost. Now, the UDF looks expensive. I've seen conceptually similar cases in which a query's processing time dropped from 15-20 seconds to less than 500ms by replacing complex UDFs with join processing. True, the queries became more complex and the clients had to code business logic in more than one place, but dropping 15-20 seconds off a query's execution time might be worth the effort.

The UDF performance problem is obvious when laid out in an example like this. However, real-world problems are typically much more difficult to spot, and you usually catch them when moving from development to production. The UDF that worked great for a 1,000-row result set in development might become a performance pig on a 1 million-row production result set. Replacing UDF logic with joins (and other set-based techniques) after the fact can be difficult and costly if the development team used UDFs extensively.

Compounding the problem, Query Analyzer doesn't report I/O from the UDF as part of the query cost when DBCC SHOW_STATISTICS I/O is enabled. You can test this assertion by running a query with and without a UDF in the SELECT clause. You'll see that the I/O that DBCC SHOW_STATISTICS I/O reports doesn't change when you add or remove the UDF. This omission leads developers to underestimate the query cost. For the record, SQL Server Profiler does capture I/Os associated with a UDF.

UDFs aren't always bad. UDFs are powerful T-SQL tools that I use regularly when I understand the performance implications. However, generally you should avoid using UDFs in a SELECT clause that returns a large number of rows. Also, I rarely use a UDF that accesses a table directly within the UDF. Chain saws are powerful tools and perfect for certain jobs, but you can do serious damage if you're not careful. The same goes for UDF usage. UDFs might seem like a convenient and simple way to write set-based T-SQL code, but if you're not careful, you'll open an expensive, row-by-row can of worms.