Query Performance and UDFs

There is a reason why UDFs kill performance

Saw a great blog post [1] on the MSDN blogs regarding query tuning with UDFs; specifically the problem that was addressed is one that many of us have had to deal with, which is having to overcome the effects of a UDF in the WHERE clause of a TSQL statement.  In this situation, the SQL statement is probably guaranteed to skip any and all indexes, and interrogate many if not all rows from the table or join of tables. The author added:

>>The function is invoked for each row in the set, and each invocation has a fixed and not insignificant cost, which is in addition to the cost of the statements in the function body.

>>The optimizer cannot accurately estimate the cost of the function (which could vary greatly), therefore it may come up with less than optimal query plans.

This is oftentimes evident when the function is on the "left side" of the operator, such as … dbo.fn() = value.  Looking at a query plan from a query similar to his, you can see that the optimizer has placed the filter of the UDF before the join was reduced. This essentially goes against the logical query processing phase that tells us that FROM, ON, OUTER, come before WHERE. Figure 1 shows us that the filter is between the two tables that lead to our join.

Figure 1.  The optimizer moves UDF before row reduction

Now notice in Figure 2 how, in this case an aggregate suggested by a reader’s comment, and not a CROSS JOIN, reduces the result set first and then applies the UDF after. In this case my example tables evaluated 225,000 rows rather than over 450,000 rows for which the filter must run against.  The time for the query run was roughly 33% faster using no indexes and 1MM rows in dbo.Table1 and 500,000 rows in dbo.Table2.

Figure 2.  Using an aggregate to push the filter back

While you can’t see the rows affected, you can see that Filter has for sure been moved back in the sequence of events, which is then exposed to considerably fewer rows, and results in a faster running query.

Lee

----------------------------
What we're dealing with here is a complete lack of respect for the law

[1] Query Performance, scalar UDFs, and predicate pushdown. Retrieved 12/4/2009 from http://blogs.msdn.com/dfurman/archive/2009/12/02/query-performance-scalar-udfs-and-predicate-pushdown.aspx.