stuart.holloway (1/4/2013)
We had issues with Functions taking a long time to run when using parameters where the scope might change each time it's run.To resolve the issue, it was quicker and more efficient to let the Function perform the full query unfiltered, then add the parameters purely in the stored procedure that calls it.
Alternatively, unless you're using the Function many times in different SPs, just have the whole code in a stored procedure in full.
So you let a multi-statement UDF run without any filtering inside the UDF and then filtered the result set? How? As a WHERE clause in the calling statement? I'd be surprised if that radically improved performance. You're still dealing with the fact that the UDF uses table variables which don't have statistics. The filtering on the UDF would be scans on table variables that were loaded by scans against tables. If anything I'd expect to see performance degrade. Can you provide more details on this?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning