Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Nov 14 '11 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 Nov 14 '11 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 Nov 14 '11 at 20:08
add comment

2 Answers 2

up vote 2 down vote accepted

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?

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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