May 19, 2004 at 8:32 am
I have a long, complex stored procedure that uses many declared table variables to hold intermediate results. The procedure takes userid as a parameter and then based on the userid, looks up how much data to process (ex. userid can be associated with 1 account or many accounts). My problem is that the procedure is no running endlessly for many accounts. (ex. 200 accounts should be < 20 secs, I am killing process after minutes) I have also seen the read count at 80 million and growing when it should only be 1 million. I have tried dropping and recreating the procedure and executing with recompile without any success. However when I change one of the declared table variables to a temp table the procedure runs < 20 secs. Later I changed the code back to use a declared table variable and it still runs < 20 secs.
Can anyone explain what might be happening or what I should look at to get more information? I am able to workaround the problem but this happens sporadically with other procedures (using same userid parameter to lookup #accounts to process) and I would love to get to the underlying cause.
Thank you.
May 19, 2004 at 10:23 am
It would be much easier to help if you posted the code. I have seen a similar problem in SQL 7 where an sp runs quickly then starts to hang and just making a change and re-saving the sp has returned it to faster execution. I have not been able to determine the reason for this behavior. I also have an sp that hangs when run from an asp page, but runs in less than 10 seconds in QA.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply