June 6, 2009 at 3:11 pm
Hi all,
I have very strange issue with one complex procedure.
Normal duration for that procedure is about 1 minute,but when I recompile it or free proc cache, execution took about 12 minutes which means that recompiling took about 11 minutes.
In that situation in sysprocesses view (CMD column) i see READTEXT like a command for that procedure.
What is that mean, do you now for any problem with compiling in SQL SERVER 2005???
Thanks in advance!
June 15, 2009 at 2:32 am
It's very difficult to say what's going on unless you reproduce the stored procedure here for review. Do that, and then perhaps we can help 🙂
June 15, 2009 at 2:48 pm
That is ok.... what is your issue, dont recompile or free cache. its not always good specially on sql server 2000 its batch recompile where as on sql server 2005 statement level recompile happen.
HTH.
Vin
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
June 16, 2009 at 7:48 am
In the mean time I resolved this issue...
Problem was with "no join predicate" which was the reason that compiling of procedure took about 30 minutes.
After I added hint (force order) into procedure execution time is now about 20 secundes 😉
Other option is that I find the reason for no join predicate and then to rewrite code but procedure is too complex (30 complex views...) and I don't have a time for this right now.
June 16, 2009 at 11:17 am
Perhaps you could reconstitute the procedure into a series of temporary tables as opposed to using that many views, or use indexed views. How much data are you dealing with here?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply