2

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.

Improve this question
3
  • Will subsequent executions with different parameters likely need to read different data in from disc? What if you try DBCC DROPCLEANBUFFERS (on a development instance). Back to 5 seconds again?
    – Martin Smith
    Commented Nov 14, 2011 at 14:51
  • Also - how much memory is your machine? How many rows are we talking returned for each execution? I'm thinking what @MartinSmith and JNK are thinking - data access more than query compilation.
    – Mike Walsh
    Commented Nov 14, 2011 at 15:57
  • I would also run and output time statistics when you run this query. Run it specifying "SET STATISTICS TIME ON" before the query in that same session in SSMS. Paste the output here.
    – Mike Walsh
    Commented Nov 14, 2011 at 20:08

2 Answers 2

Reset to default
2

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?

Improve this answer
3

It's unlikely it's related to the query plans. I've only every seen very complicated queries take more than 1 second for the plan to get created, much less 4 seconds as your describe.

It sounds like it's page caching - basically SQL Server keeps recently used data in memory. The first run with each set of parameters you are paying the (normally expensive) cost of physical disk access.

Subsequent executions can access the data pages which are already stored in memory without waiting for the disk subsystem.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.