I have a multi statement table value UDF. That builds a temp table then returns it as the result.
The first time its executed it takes 5 seconds, subsequent executions with the same parameters take 1 second. If I change the parameters it then takes 5 seconds the first time then it goes back to 1 second. If I then change the parameters back to the first ones it still takes one second.
I cant work out what's causing it to take longer the first time a new parameter is used. Do UDF's compile a separate query plan each time different parameters are used? Can I monitor this in Profiler? I can see the sp_Recompile event but I'm not sure what event is fired when a UDF compiles.
Unfortunately I cant post the code but am hoping there is enough information for someone to give me some sort of explanation.
Best Answer
Every statement in SQL Server has a plan. The fact of the statement being in a UDF or proc or adhoc doesn't matter usually
In this case, I suspect it is cache misses causing the delay. You can test by DBCC DROPCLEANBUFFERS (caution: will affect all processes on that box). Compile time would be for every change of parameter, not just to an unused one.
One thought though: Is the UDF processing or churning lots of data, enough to cause cache misses?