Different execution timing

I'm using SQL 2k.

I wrote a query using the query analizer and tested it before turning it into a Stored Procedure. It worked fine an the execution time was acceptable (25 secs, since there was a lot of data to analize)

When I executed the recently created stored procedure, the execution time happened to be three or four times higher. (1 min, 38 secs)

It was the exact same code, i was logged in the same database server, the parameters were the same in btoh cases. So, my question is as follows:

Why is it that executing a script and executing a stored procedure with the exact same script differ so much in timing



Answer this question

Different execution timing

  • Kevin Rodgers

    Can you post some sample code Most probably you parameterized the values in the WHERE clause and you are getting a different plan or preventing optimizer from doing parameter sniffing. Anyway, to understand more about plan caching and impact of variables on query plans refer to the white paper below:
    Most of the information in the white paper is relevant to SQL Server 2000 also and the differences are explained based on context.


  • Different execution timing