Now, I am a great fan of functions, but I know that they are not compiled like stored procedures, so I am weary of using them for complex SQL queries. This query was very complex. I've got the function's code below, but it is based on a much more complex view, which joins 5 tables. There is a picture of the function execution above. See how the execution time is only 125ms, while the parse and compile time is 6638ms! The obvious solution is to convert this function to a stored procedure, which will enable SQL to store the plan.
However, this would involve changes to .NET code, which would involve a lot of testing and change control. I found another solution, which enabled us to fix the Function without having any change in .NET code, permitting a quick fix to production. The fix was to alter the function so that it has multiple lines! This way SQL Server will treat the 'multistatement table-valued function' as a stored procedure and enable it to be cached. You can see from the execution results below that their is no parse and compile time, and the execution time is the same 125ms.
Note, SQL Server is able to cache Adhoc queries, so that your execution of a function with a particular parameter can be cached. Don't let this cloud your analysis, unless your OLTP environment is going to execute the function with exactly the same Adhoc text. You will see these 'Adhoc' cached plans above with objtype='Adhoc'
Note, there is documentation that explains this behaviour, although you might have missed it. See http://msdn.microsoft.com/en-us/library/cc293623.aspx and read the text near 'multistatement table-valued functions'.
What does this tell us? Firstly, it reminds us that compile time is not always trivial. Secondly, it reminds us that we should treat Functions as different to Procedures, and only use Functions where they are appropriate. I find them most useful where they are doing something like string manipulation and optimisation is not appropriate.
Old function (with 6.8 second compile time every execution)
create function MyFunction
RETURNS TABLE AS
from (select max(t4.TheDate) MyDate,
from vBranchCount as t3
left outer join (SELECT *
WHERE vBranchCount.Status = 'Applied') AS t4
ON t3.TranCode = t4.TranCode
AND t3.BranchNo = t4.BranchNo
AND t3.Cash1 = t3.Cash1
AND t3.Cash2 = t4.Cash2
AND t3.Cash3 = t4.Cash3
AND t3.Cash4 = t4.Cash4
AND t4.TheDate < t3.TheDate
where t3.StockCountPlaceId = @Parameter1
group by t3.BranchNo,t3.TranCode,t3.Cash1,t3.Cash2,t3.Cash3, t3.Cash4) as t1
inner join vBranchCount as t2
on t1.BranchNo = t2.BranchNo
and t1.TranCode = t2.TranCode
and t1.MyDate = t2.CashDate
and t1.Cash1 = t2.Cash1
and t1.Cash2 = t2.Cash2
and t1.Cash3 = t2.Cash3
and t1.Cash4 = t2.Cash4 )