Different execution timing RRS feed

  • Question

  • 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?


    Friday, November 10, 2006 3:59 PM

All replies

  • 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.
    Saturday, November 11, 2006 12:13 AM