Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
teddyhoosevelt
Starting Member
2 Posts |
 Posted - 2011-10-20 : 00:20:00
|
| I created a stored procedure in SQL Server Express 2008 on my home PC. It has a single BIT parameter, and includes WITH RECOMPILE, SET ARITHABORT ON and SET NOCOUNT ON statements. Temp tables are not used, but common table expressions are, along with a number of deletes and inserts.I set up a new query to execute the stored procedure, and did so seven consecutive times, that is, as soon as it completed, I clicked Execute again. Run times (in seconds) are as follows:5,32,33,4,32,32,4I've found all kinds of answers for inconsistent run times in SSMS vs external apps, but nothing for this situation. Any idea why SSMS is behaving this way? Frustrated!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-20 : 04:32:21
|
| whats the proc doing? were you using same parameter value always? also why are setting WITH RECOMPILE option? any specific reason? do you need what it will result in?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
teddyhoosevelt
Starting Member
2 Posts |
Posted - 2011-10-20 : 09:37:25
|
| The proc pulls selected data from a large table, and through a series of insert and delete commands, populates a smaller table with summarized results. Yes, same parameter each time. The parameter is simply a switch to purge the output table. If 1, I purge, if 0, I don't.Initially, the proc was taking a very long time to run via VB. After research, I added WITH RECOMPILE to eliminate parameter sniffing. I am now testing in SSMS alone. I just commented out the WITH RECOMPILE statment and ran it 6 consecutive times. Execution times (in seconds): 34,33,5,33,32,4.If you look the run times in both of my posts, there's a repeated pattern. slow, slow, fast. |
|
|
|
|
|
|
|
|
Inconsistent Run Times for SSMS Stored Proc