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 |
|
scottpt
Posting Yak Master
186 Posts |
 Posted - 2004-07-02 : 22:06:08
|
| Win 2000 SQL 2000 sp3a w/ hotfixI have a app that uses a stored proc with a temp table. THe temp table causes a recompile every time the proc is run. I cannot remember wht the command it to use the plan cache. Something like Create procedure keep plan or somthing??? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-02 : 22:24:30
|
| SET FORCEPLAN ONCan you replace the temp table with a table variable though? How many rows are we talking here?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-03 : 15:12:10
|
| Do you create the temp table in the app then call the SP. If so there's nothing you can do about it.You can move all the temp table access into dynamic sql or another SP to make the recompiles quicker if you wish.Or make it a permanent table accessed via the spid.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Stored procedure Recompiles