Thanks for the article, Mike!
Just curious: Why have not you used WITH RECOMPILE option? I believe one has more flexibility with this option if specified in a procedure call statement. Alternatively a procedure can be created with this option.
And the join hints can tell SQL to use a particular join every time, just in case
You're on the money with your detective work.
However, I agree with Alex that you should have considered the WITH RECOMPILE option with your create procedure statement. That's probably in line with your intent.
I'm curious, did you call the sp_recompile on your stored procedure from within the stored procedure itself?
-Larry
Great!! Recently one of the stored procedure was behaving some what in the similar way as mentioned, I did use sp_recompile which made it much faster, but didnt knew the reason behind..!!
The Great article for explaning the behaviour of sp..!!
Thank you for posting this useful article, Mike. I also had the same experience in the past with the same cause (sp_recompile).
Regards, Vincent
I am a novice and don't know much about the SQL server engine but Microsoft documentation claims that "Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so."
I found this claim at http://msdn2.microsoft.com/en-us/library/aa238892(SQL.80).aspx
Not sure what is it. Could some expert explain?
Nice. I agree with others, you should use "with recompile" statement . And more, if you are using MS SQL 2k5, you may use "with recompile" on a statement rather on the batch.
And some comments about the network level in .NET. If you would be having problem with that layer, I guess you would see a waiting status for your spid with something like 'NETWORKIO'.
Best Regards,
Igor.
A little off topic. I have seen similar results (slow .NET access but fast in Query Analyzer) that seemed to be solved with "SET ARITHABORT ON". Maybe by doing that I am forcing a recompile and it is the same problem. Of course you need to be careful that the actual procedure doesn't have the potential for an actual error that ARITHABORT will falsely handle.
Hope that helps someone else. I would welcome any other feedback on a relationship between how SQL Server and .NET are actually handling these differently at a code level. Maybe there is some other solution that is more appropriate.
Enjoy,
Brian
