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 |
|
paolo883
Starting Member
4 Posts |
 Posted - 2009-08-11 : 03:01:09
|
| Hi,I have a big stored procedure that is used for searching products on my web site. This stored procedure creates three temporary tables and at the end it drops them.Sometimes it happens that there are timeouts on this sp. It seems that something is locked but I don't know what. But if I alter the sp by renaming all temporary tables it works fine again.Does anyone know the reason of this behaviour?ThanksPaoloPaolo Costa |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-11 : 03:11:07
|
seems that you are using permanent table as temporary table. Why don't use just use temporary table ? Temporary table is created with the table name prefix with #create table #temp1( . . .)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
paolo883
Starting Member
4 Posts |
Posted - 2009-08-20 : 00:37:37
|
| No, it's not a permanent table, it's called #result.Now I realised that it's enough to call ALTER PROC on the sp without changing it to make it faster. So maybe it's not a matter of temp table. Strange, isn't it?PaoloPaolo Costa |
|
|
|
paolo883
Starting Member
4 Posts |
Posted - 2009-08-20 : 00:58:13
|
| Some more details:SP is slow (not always) when called by web site. If I call it from management studio with another user it's ok.If I call ALTER PROC without even changing it, it immediately becomes faster.SP is very big and contains a lot of IF .... THEN .... ELSE depending on different cases.My opionion is that SQL SERVER saves an execution plan that is good for a certain condition but not for all of them, but I'm not sure about it. Now I added a sp_recompile call at the end of sp to see what happens.Any help would be TRULY appreciated :-) PaoloPaolo Costa |
|
|
|
|
|
|
Problem with temporary tables