I was facing this issue for a while that I have created a Stored Procedure in SQL Server 2005 Database that performs a search on a table (according to the given parameters). It was happening that when this procedure was executed from ADO.net (my front-end Visual Basic Application) Timeout exception was occurring. But at the same time when we execute this procedure from SQL Server Management Studio it executes immediately.
The Timeout in Visual Basic Application was NOT coming immediately it was coming after some time when users execute the procedure many times. Whenever time out occurred, I went in the SQL Server Management Studio and just did ALTER (without changing a single thing in procedure). And the problem resolved for some time NOT permanently.
So I put this question on MSDN Forums and a member told me to put the WITH RECOMPILE in the procedure. He told me that
"I think the problem is with the cache. When you ALTER the procedure from Management Studio it is recompiled and the problem is fixed. this is my guess."
It sure fixed my problem and the Timeout never came afterwords. Here is the link to that post.
http://social.msdn.microsoft.com/Forums/en-US/...
The Timeout in Visual Basic Application was NOT coming immediately it was coming after some time when users execute the procedure many times. Whenever time out occurred, I went in the SQL Server Management Studio and just did ALTER (without changing a single thing in procedure). And the problem resolved for some time NOT permanently.
So I put this question on MSDN Forums and a member told me to put the WITH RECOMPILE in the procedure. He told me that
"I think the problem is with the cache. When you ALTER the procedure from Management Studio it is recompiled and the problem is fixed. this is my guess."
It sure fixed my problem and the Timeout never came afterwords. Here is the link to that post.
http://social.msdn.microsoft.com/Forums/en-US/...
4 comments:
I was having the same problem before.. and it works for me as well.. thanks a lot :)
This problem has to do with "Parameter Sniffing"
Yes maybe "Parameter Sniffing" can fix this issue also, we can try. But the problem is that the procedure never slows down when it is executed from SQL Server Management Studio. The exception is thrown in application (which is obviously thrown by ADO.net)
This issue is not limited to ADO.Net using VB.Net. I had the same thing happening in an all C# app with a C# DAL. However the "WITH RECOMPILE" still fixed it. I'm thinking that it might have had something to do also with the sorting that's available on the page, requiring to many executions of the procedure returning the same result set. So I'll probably cache the results and use the cached results for the sorting...
Post a Comment