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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2006-10-25 : 17:19:05
|
| I have a somewhat complex stored procedure (has some dynamic SQL, nothing I can do about that) that takes at least 30 seconds to run the first time it's executed, but each time after that it runs instantaneously, whether in QA or in the ASP.NET form, though only as long as the parameters stay the same. As soon as any parameters change, it takes 30+ seconds again. Is there any way around this? |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2006-10-25 : 19:38:18
|
| I was able to speed up my proc significantly using an index (the slowness came from a large UPDATE with a subquery inside a CASE), but I'm still curious about this. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-10-26 : 02:25:05
|
| you didn't post the code so it's hard to say, but it may be a parameter sniffing issue. see this link:http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspxSqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
|
|
|
proc slow first time parameters change