Temp Table Recompile - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Temp Table Recompile

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-09-12 : 14:01:22

Hi folks,
I am using temp tables in stored procedures to perform some data processing. is there a table hint I can use to prevent the stored procedures from recompiling since I am using temp tables.
Ramdas

Ramdas Narayanan
SQL Server DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-12 : 14:32:09

Did you specify the RECOMPILE option when the stored procedure was created or altered?

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-12 : 19:57:43

If the temp table is created in the SP then it shouldn't recompile.
If the temp table is created and then the SP called then it has to compile on every run to resolve the object.

==========================================
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.

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-09-13 : 17:16:48

In fact SQL Server will recompile procedures because Temp tables in tw situations:

1) If you interleave DDL (Creation of temp or perm tables, indexes, etc) with DML. If you create a Temp Table, then issue an INSERT, the proc will be recompiled. To avoid it, move all DDLs to the header of your proc.

2) If Temp tables data is modified more than six times, the proc will be recompiled.

To minimize this sort of recompilations, change all of temporary tables for Table Datatype.

see the following links:

http://www.idisoft.com/products/sample/sprecompile/sprecompile_overview.htm

http://www.sql-server-performance.com/stored_procedures.asp

http://www.databasejournal.com/features/mssql/article.php/2218451

The following article from Microsoft is outstanding in helping to resolve proc recompilations. I've used some time ago to resolve this issue:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp

regards,

Sérgio Monteiro
Trust in no Oracle

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-14 : 17:08:46

>> 1) If you interleave DDL (Creation of temp or perm tables, indexes, etc) with DML. If you create a Temp Table, then issue an INSERT, the proc will be recompiled. To avoid it, move all DDLs to the header of your proc.

Think that would refer to permanent tables. As long as you decalare the temp table in the SP (and don't do anything like drop it if it already exists) then I don't think declarations anywhere will cause a recompile.

>> 2) If Temp tables data is modified more than six times, the proc will be recompiled.
Don't know where this comes from - may be something to do with statistics.

Try this
drop procedure spcomp
go
create procedure spcomp
as
create table #a (i int)
insert #a select id from sysobjects
update #a set i = i+1
update #a set i = i+1
update #a set i = i+1
update #a set i = i+1
update #a set i = i+1
update #a set i = i+1
update #a set i = i+1
update #a set i = i+1
update #a set i = i+1

select * from #a where i = 10
create table #b (i int)
insert #b select id from sysobjects
exec spcomp2
select * from #a where i = 1
go
drop procedure spcomp2
go
create procedure spcomp2
as
insert #a select 1
go
exec spcomp
If you set up the profiler to monitor recompilations you should see both SPs recompiling on first run then spcomp2 compiling on every run (due to having to resolve the temp table) but spcomp not recompiling.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources