July 6, 2007 at 12:49 pm
Hi,
We have a stored procedure in production environment that executes under 2 seconds when we execute it through query analyzer.
The same procedure hangs when it's being called from the site by passing the same set of parameters.
After debugging, we commented out the last statement in the procedure that was dropping temp table ( drop table #<table_name> ), procedure started executing successfully in no time even when it is called from the website.
I need to give an explanation for this change and it is very hard to explain.
Is there any behaviour change in how the temp tables are dealt with in when we execute procedure through query analyzer and when it is called through webpage ( ado.net) ?
-N
July 6, 2007 at 1:20 pm
I would think ADO.NET maybe trying to drop a regular table which takes a long time, options see if you can use derived table or table variable. Another option wrap your ADO.NET code calling the stored procedure with what we call the second USING statement in C# both 1.1 and 2.0 and in VB.NET 2.0 that statement calls dispose for you automatically on classes that implement IDISPOSABLE. If the above does not change the behavior your other option is to implement what is called the DISPOSE pattern which may make you call DISPOSE BOOL which clears unmanaged resources like a temp table. Try the link below for more about dispose. Hope this helps.
http://msdn2.microsoft.com/en-us/library/b1yfkh5e(vs.71).aspx
Kind regards,
Gift Peddie
July 8, 2007 at 10:23 pm
When you see "hang" did you check using profiler about what is happening on SQL Server side.
Can you create stored procedure using "WITH RECOMPILE" clause and let me know how it executed from website (uncomment the relief for time being)
Hoping to hear further...
July 9, 2007 at 4:25 am
How many records where there in the temp table that you created. Was the drive having enough space.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 9, 2007 at 7:06 am
Worth checking to make sure all your SET params match, more than once I've see things go bad because there were differences between what QA used and production used.
July 10, 2007 at 3:19 pm
Thanks for all the replies.
Total number of rows being inserted in the temp table was max 530. We did recompile the procedure to see if it makes any difference but it didn't. It continued executing in no time vs never completed when called through the website.
We did use profiler to see what happens after submitting the parameters from the webpage. The procedure execution did not complete.
Finally in the trial and error mode, we commented out the drop table #<temp> and things were fine.
Setting on qa and production are exact same. Also, we have bunch of other procedures with the similar method and they all work fine.
Really weird !
July 11, 2007 at 11:02 am
Are you suffering a 'notational' foul? is the table name in some way a reserved word, or some other such feature that the remote execution could run afoul of? (yes, this is a VERRRRY long reach!)
July 11, 2007 at 1:44 pm
Have you checked SQL Server Error Log. There maybe issues with tempdb !?!
* Noel
July 12, 2007 at 5:56 am
when procedure was taking a longer time from web site, what was the waittype / waittime of that SPID in sysprocesses ?
was it blocked by some other SPID ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply