Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Unanswered: Stored procedure efficiency

    I have a stored procedure which executes several queries within it, assigns results to variables and then returns the variable values as a resultset (without going into too much detail, we have a list of people in a company and their spouse/dependents, each spouse/dependent has a status and type, the SP is doing analytics on this for a particular company).

    In large cases the procedure is taking 15+ seconds to execute so I commented out each query in it 1 by 1 and found that one of the queries when removed took the execution time down to 1 second. However, when I pull this query out verbatim including the variable declarations and run it by itself, it executes almost immediately with the desired result.

    I can post the query if needed but I wasn't sure if this is a common symptom of another problem. TIA.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    have you done a SHOW PLAN on both?

    Maybe you need to force a recompile...have any of the tables or views used in the sproc changed since it was originally compiled?

    Are you using temp tables

    post the sproc and the table DDL, with it's indexes
    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
  •