June 10, 2017 at 4:45 pm
I understand that stored procedures are compiled on the first run, possibly making the runtime longer. If I create a new procedure and run it multiple times in a loop within the same script, would it still only take longer on the first iteration of the loop or the same amount of time as every iteration because its in the same batch of records I am going through. In other words, would there be any performance benefit of running the procedure once first before I run my script to call it again multiple times.
June 11, 2017 at 5:07 am
jonathanmarenus - Saturday, June 10, 2017 4:45 PMI understand that stored procedures are compiled on the first run, possibly making the runtime longer.
No, they're not.
Procedures get optimised, and an execution plan generated, if there's no valid plan for that procedure when it runs. It'll happen on the first run, and will happen again any time the procedure's plan is not in cache.
If I create a new procedure and run it multiple times in a loop within the same script, would it still only take longer on the first iteration of the loop or the same amount of time as every iteration because its in the same batch of records I am going through.
Any time the plan is not in cache.
In other words, would there be any performance benefit of running the procedure once first before I run my script to call it again multiple times.
Depends. Are you doing performance testing? If so, yes, run once, discard times, run again. If in production, no, just run it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply