Stored Procedure runs long the first run, then runs faster

I have a Stored Procedure that does the following: Creates 2 Temp Tables Inserts into first Temp Table Inserts into second Temp Table from 20 Tables, plus joining to the first Temp Table and has ...

monktarbello

Copper Contributor

May 23, 2025

the first time the SP doesn't have an execution plan cached. it has to compile the SP. On next run, it found a reusable plan not data necessarily. The plan cached makes it faster. Recompile the SP and run it, you will observe the same behavior.

thanks

RandyLP7

Copper Contributor

May 24, 2025

My understanding the following SQL shows execution plan in cache.

SELECT *
FROM syscacheobjects
WHERE ObjType = 'Proc'
AND SQL LIKE '%{StoredProcName}%'

On previous days, before I run the process I confirmed the execution plan was in cache.

As an additional test today (Saturday and the Server is not very busy), I ran the Stored Procedure for results B, which is a small result set. This run should ensure the execution plan is in cache and I used the above SQL which still shows the execution plan in cache.

Ran the Stored Procedure for results A, a larger result set, and the first run took 23 seconds. But every run for results A after that runs in 8 seconds.

From these tests I do not think the execution plan in cache is the issue.

Thanks for the theory......it was worth testing/confirming.

Forum Discussion

Stored Procedure runs long the first run, then runs faster

Resources