Reusing T-SQL Code - Scalar UDFs and Performance

Hopefully, the examples so far have demonstrated that laying out code in simple reusable modules can simplify maintenance, and reduce the chance of bugs when requirements change.

The reason is simple: careless code reuse can seriously hurt performance. For example, in some cases scalar UDFs may perform very poorly, and I will provide an example that demonstrates this, for SQL Server 2005 and 2008. Of course, in future versions of SQL Server the relative performance of the different flavors of UDFs may change, so it's essential that you always benchmark the performance impact of code refactoring, and rerun these benchmarks when you upgrade to a new SQL Server version.

For this example, we'll need to create a test table with a reasonable number of rows, so let's first set up a 128K-row helper table, Numbers, as shown in Listing 1 which we can use to populate the test table.

These helper tables are a must-have in database development and, in fact, you will have already created an almost-identical 1-million row Numbers table . If so, or if you already have your own version that suits the same purpose, then feel free to use that instead.

Listing 1. Creating and populating the Numbers helper table.

Next, in Listing 2, we create the sample Packages table and populate it using our Numbers helper table.

Listing 2. Create the Packages table and populate it with test data.

Suppose that the cost of shipping for a package is $1 if it weighs less than 5 pounds and $2 if it weighs 5 pounds or more. Listing 3 shows how to implement this simple algorithm, both as a scalar and as an inline UDF.

Listing 3. Calculating the shipping cost using a scalar UDF, GetShippingCost, and an inline UDF, GetShippingCost_Inline.

Now, we are ready to examine the comparative performance of each function, using the simple benchmark shown in Listing 4.

Listing 4. A simple benchmark to compare the performance of the scalar and inline UDFs vs. the performance of the copy-and-paste approach.

Although both functions implement exactly the same algorithm, the performance is dramatically different. When we run this benchmark on SQL Server 2005 or 2008, the query that uses our scalar UDF runs dramatically slower. Also, in this particular case, the query which uses the inline UDF performs very well, although not as fast as the query that does not use any UDFs at all, as shown in Listing 5. Of course, when you run these benchmarks on your system, you may get different results.

Listing 5. The performance of the query using our scalar UDF is dramatically slower than the performance of other equivalent queries.

I am not saying that using inline UDFs never incurs any performance penalties; blanket statements do not belong in database programming, and we always need to consider the performance of each particular case separately. However, in many cases, inline UDFs perform very well.