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.