Data I/O Model 29B Universal Programmer (circa 1984)

Are scalar User-Defined Functions Ever a Good Idea?

In SQL Server, you have the ability to create a user-defined function that can perform an operation directly inline as a part of a query.

For example, consider an ordering system, which might have a function IsInStock(), that checks whether the inventory level for a product is below some required threshold (for our hypothetical business, we never want to sell the last widget in case a very important customer calls.)

CREATE FUNCTION IsInStock(@productid INT)
DECLARE @CurrentInventory INT
SELECT @MinStock = min_stock FROM products WHERE product_id = @productid
SELECT @CurrentInventory = COUNT(*)
FROM inventory
WHERE product_fk = @productid

IF @MinStock < @CurrentInventory

This function could be consumed with this SQL statement:

SELECT product_id, product_name, dbo.IsInStock(product_id)
FROM products

Now, on the surface, this seems to be a very nice way to code operations that are used in multiple places.  Unfortunately, SQL Server does not internally figure out that this query could be optimized and the result is that, for every row in the products table, our function will run and each run of the function hits the database twice.  This query would run much faster using joins:

SELECT product_id, product_name,
CASE WHEN COUNT(product_fk) > min_stock THEN 1 ELSE 0
FROM products
LEFT OUTER JOIN inventory ON product_fk = product_id
GROUP BY product_id, product_name, min_stock

Interestingly, found that performance suffers even when using a function that doesn’t do anything.  This calls into question the utility of even functions that don’t hit a database table, for example, expanding abbreviations of state names using a CASE statement.

Microsoft’s own advice for UDFs is, “performance can be adversely affected when the rowset is very large”.  Writing code that you know will not scale well is generally a bad idea – just because your database is small today does not mean that it will stay small forever.

Want to talk more database programming? Visit our eBusiness Solutions team today.

image credit: Michael Holley

Comments are closed.