Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Posts
    25

    Unanswered: stored procedure taking more time than sql query

    Hi

    I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.

    What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Since your using a temp table, the stored proc will be recompiled at the time of execution. It is at this time that it may be generating a different incorrect plan.

    I just went through this.

    Look up the query hint KEEPPLAN.

    And go buy Kalen Delaneys "Inside SQL Server"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •