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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-09-03 : 08:28:58
|
| Max writes "I have a complex query, so I created a procedure out of it. While testing I hard coded a value within a local variable so when I call the procedure to execute, it looks like this:exec temp_spexecution time is about 2 seconds. Happy with the result i parameterized the stored proc, and now passing a value to it like this.exec temp_sp 361execution time went to about 15 seconds.Please explaint why? and how do I fix this?Sincerely,Max Z" |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-03 : 08:41:48
|
| Yeah... We would probably have to see your query to answer something like that. However, it is most likely an index issue with regards to the column that the parameter restricts.Show us more and we'll tell you moreCorey |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-09-04 : 09:52:21
|
| More likely a poor execution plan "stuck" in memory. Try dropping and recreating the procedure. Or try recompiling the stored proc: sp_recompile myproc. Also, if this is not on your production server try executing DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. These statements should flush the procedure cache and force a new query plan to be created.OS |
|
|
|
|
|
|
Query Performance