Forum Discussion

RandyLP7's avatar
RandyLP7
Copper Contributor
May 23, 2025

Stored Procedure runs long the first run, then runs faster

I have a Stored Procedure that does the following:

  1. Creates 2 Temp Tables
  2. Inserts into first Temp Table
  3. Inserts into second Temp Table from 20 Tables, plus joining to the first Temp Table and has 10 subqueries.
  4. Inserts into second Temp Table from 18 Tables.
  5. Inserts into second Temp Table from 14 Tables.

The first time the SP is run for the day, or after it has not run for a few hours, it sure 40+ seconds.

The runs after that come in between 7 – 20 seconds.

 

The SP takes three parameters which can change, but for testing we are using the same three parameters.

The SP is in cache before the first run.

I have checked all the Indexes are in place and not overly fragmented.

I do not see any CPU, Memory, or Drive issues.

 

I have gone through the Execution Plan more times that I can count.

 

I do not understand why the first run takes long, but the following runs are always short.

 

I did see this in another post:

"A result set is never cached in SQL Server, however, the underlying data pages containing the result rows are. All this means is that the response time for subsequent queries may be lower since the underlying data is re-read from memory, with only any variations read from storage."

If that is the issue, is there a way to "fix it"?

 

Any ideas?

3 Replies

Sort By
  • monktarbello's avatar
    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's avatar
      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.

Resources