I was tuning a SQL Server 2000 query this afternoon which had a function inside the WHERE clause. The query was well indexed, and the only reason for the performance problems was due to the WHERE clause (additionally, there were six other functions being called in the SELECT clause, but as no rows were being returned, they weren’t a problem). Additionally, the function in the WHERE clause called another function. As there were 4,500 rows to be processed in the WHERE clause, that’s 9000 function calls.
In SQL Server 2005, the engine would cache those calls, so the function would only need to be compiled/looked up in the proc cache once per query. However, in SQL Server 2000, this is not the case, and the function is compiled/looked up every time it’s called – in this case, 9000 recompilations of functions.
There are a number of things that can be tried to improve this performance – such as removing the function calls and replacing them with in-line SQL code (depending on the complexity of the function). The downside to this is the query becomes less maintainable, which is probably the reason a function was added in the first place! Another option is to process the code from the function for the entire set of possible data (such as in a view) and then join this onto the row set, restricting it to the rows you want. This minimises function calls, but may not be as flexible as needed.
The final option is to upgrade to SQL Server 2005.
TIP: When tuning queries with a function call, try running the query without the function call and compare the duration. This allows you to quickly see if the function call is responsible for the performance issue, or if it’s something else.