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!
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?
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.