Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    15

    Smile Unanswered: Naughty SP ruining my day!?!

    Hi!

    Running a SQL2K-server on a Win2K(adv) I have sort of an enigma (to me atleast!):

    One of my SPs sometimes seems to hang. Arrgh. This is of course not the total extent of my problem - naah, I've had/created my share of faulty SPs which hang on a regular basis - no, the enigma is that the code inside my SP *never* hangs when executed from the Analyser? The test is done using ctrl-a/copy&paste - remove 'Create...' and putting a 'Declare in front of the only parm, then inserting a select parm='value_of_parm'. As far as my knowledge goes this 'running code from SP in Query Analyser' should introduce no extra room for slack (which in turn could help me explain why the code works in Query Analyser)?

    Btw, when I say 'hang' it's not the whole truth - using the debugger I can see the code (mostly a bunch of updates on one table) being executed - but extremely slow. The code takes approx. 1 min in Query Analyser but hasn't finished after 60 mins when executing the SP.

    A little info on the SP:
    The user runs another SP which gathers some data from an Oracle DB and inserts these data into a SQL2000 table. Then the user activates the above flamed SP in vain hope of marking the newly fetched data with a statusid.
    The problem allways arises when the user tries to let the SP engage newly fetched data - and rarely when engaging old (already marked, hence 'remarking') data. I suspect this could be a clue for anyone with some insight?

    I'm looking for 1) a solution, 2) a finger pointing out the bug(s) 3) any suggestions on how to do further research into this evil SP!

    Thanx in advance!
    Ult

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To clarify,

    Running the raw code from Query Analyzer is fine, but executing the Stored Procedure from Query Analyzer runs slow?

    Check and compare the execution plans for both methods and see where they are different.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    15
    >Blindman: Exactly!

    ...I'll try checking the executionplan!

    Thanx
    Ult

  4. #4
    Join Date
    Sep 2003
    Posts
    15

    Unhappy

    Hmmm...

    After having compared execution plans (and they were quite different) I haven't found any obvious mistakes in the way the SP is handled by SQL2000.

    My relentless executing of the code did reveal something: The SP runs only seems to hang (or slow to almost complete halt) on 'fresh' data. When I have executed the code from the SP a couple of times 'directly' in the Query Analyser the SP subsequently has no trouble running on the same data?

    Any ideas?

  5. #5
    Join Date
    Sep 2003
    Posts
    15
    Further info:

    Even when I 'reset' the data fetched (as to simulate 'fresh' data) the SP runs smooth now. (The reset is just setting 'statusid' = null).

    But on 'true' fresh data the SP is sloooooow.

    The SP consists of a number of updates, and the table beeing updated has a clustered index on it. I wonder if this could be relevant?

    ....I also wonder if being left alone in my own tread has made me turn introvert and perhaps triggered my habit of talking to myself?!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have run into cases where executing code as a stored procedure causes the optimizer to use table scans (which are slow) that it does not use when executed through query analyzer.

    I don't know why.

    The problem was solved after simplifying my query by moving some of the sub-querys into separate statements that stored their results in table variables.

    Also, what kind of value is your new data flag? If it is a BIT value it cannot be indexed. You might try changing it to an INT or CHAR value and indexing it. Whether this speeds up the process depends on the cardinality of the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How big is the code? Can you attach it or paste it?

    What do you mean by Fresh Data?
    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
  •