11.0 Temporary Tables, Table Variables and Recompiles
Published Mar 23 2019 04:49 AM 1,636 Views
Microsoft
First published on MSDN on Jan 18, 2007

<br/><!--<br/> p.MsoNormal<br/> {mso-style-parent:"";<br/> margin-bottom:.0001pt;<br/> font-size:12.0pt;<br/> font-family:"Times New Roman";<br/> margin-left:0in; margin-right:0in; margin-top:0in}<br/> table.MsoNormalTable<br/> {mso-style-parent:"";<br/> font-size:10.0pt;<br/> font-family:"Times New Roman";<br/> }<br/>--><br/>


11.1 Temporary Tables versus Table Variables



In order to determine if table variables or temporary tables is the best fit for your application, let us first examine some characteristics of table variables and temporary tables:


1.             Table variables have a scope associated with them. If a table variable is declared in a stored procedure, it is local to that stored procedure and cannot be referenced in a nested procedure.


2.             The transaction semantics for table variables is different from temporary tables. Table variables are not affected by transaction rollbacks. Every operation on a table variable is committed immediately in a separate transaction.


3.             It is not possible to create indexes on table variables except by specifying them as constraints (primary key and unique key). In other words, no DDL is possible on table variables. On the other hand, it is possible to create non key indexes on temporary tables. This is especially useful if the data set is large.


4.             Temporary tables can also be referenced in nested stored procedures and may be the right fit if the object needs to be available for a longer duration (not just scoped to the batch like table variables).


5.             Both temporary tables and table variables are kept in memory until their size reaches a certain threshold after which they are pushed to disk.



Let us examine the caching impact of temporary tables and table variables. There are no statistics based recompiles for table variables. The general rule of thumb is to use temporary tables when operating on large datasets and table variables for small datasets with frequent updates. Consider the example below where the table test_table has 10 rows of data and 100K rows are inserted into a table variable. Then a join is performed on the table variable and test_table.



create procedure table_variable_proc


as


begin


declare @table_variable table(col1 int, col2 varchar(128));


declare @i int;


set @i = 0;


while (@i < 100000)


begin


insert into @table_variable values(@i, convert(varchar(128), @i));


set @i = @i + 1;


end


select * from @table_variable tv join test_table on tv.col1 = test_table.col1;


end


go



exec table_variable_proc


go



Now let us rewrite the same example with temporary tables to compare and contrast the two approaches:



create procedure temp_table_proc


as


begin


create table #table_name(col1 int, col2 varchar(128));


declare @i int;


set @i = 0;


while (@i < 100000)


begin


insert into #table_name values(@i, convert(varchar(128), @i));


set @i = @i + 1;


end


select * from #table_name join test_table on #table_name.col1 = test_table.col1;


end


go



exec temp_table_proc


go



Now query the DMVs to get the query plan and average CPU time:



select total_worker_time/execution_count as avg_cpu_time,


substring(st.text, (qs.statement_start_offset/2) + 1,


((case statement_end_offset


when -1


then datalength(st.text)


else


qs.statement_end_offset


end


- qs.statement_start_offset)/2) + 1) as statement_text


, cast(query_plan as xml)


from sys.dm_exec_query_stats qs


cross apply sys.dm_exec_sql_text(qs.sql_handle) st


cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset)


order by total_worker_time/execution_count desc;


go




Avg_


Cpu_


Time


Statement_


Text


Query_plan


208412


select * from @table_variable tv join test_table on tv.col1 = test_table.col1;


<ShowPlanXML xmlns="http://


schemas.microsoft.


com/sqlserver/2004/07/showplan"


Version="1.0" Build="9.00.0000.00">


<BatchSequence>


<Batch>


<Statements>


<StmtSimple StatementText=


"create procedure


table_variable_proc


&#xD;&#xA;as


.


.


<QueryPlan CachedPlanSize="8"


CompileTime="2" CompileCPU="2"


CompileMemory="120">


<RelOp NodeId="0"


PhysicalOp="Nested Loops"


LogicalOp="Inner Join"


EstimateRows="1" EstimateIO="0"


.


.


</ShowPlanXML>


51978


select * from #table_name join test_table on #table_name.col1 = test_table.col1;


<ShowPlanXML xmlns="http://


schemas.microsoft.


com/sqlserver/2004/07/


showplan" Version="1.0"


Build="9.00.0000.00">


<BatchSequence>


<Batch>


<Statements>


<StmtSimple StatementText=


"&#xD;&#xA;create procedure


temp_table_proc


.


.


<QueryPlan CachedPlanSize="21"


CompileTime="322"


CompileCPU="203"


CompileMemory="104">


<RelOp NodeId="0"


PhysicalOp="Hash Match"


LogicalOp="Inner Join"


EstimateRows="10"


.


.


</ShowPlanXML>



The temporary tables query outperforms the table variables query. Notice that the query plan for the table variables query estimates 1 row at compile time and therefore chooses a nested loop join. In the temporary tables case, however, the query plan chosen is a hash join which leads to better query performance. Since the query plan for table variables always estimates the number of rows at compile time to be zero or one, table variables may be more suitable when operating on smaller datasets.



11.2 Recompiles Based on Temporary Tables



Recompiles of queries with temporary tables occur for several reasons and can cause poor performance if temporary tables are not used properly. Using examples we will look at the most common causes for recompiles based on temporary tables. Consider the example below:



create procedure DemoProc1


as


begin


create table #t1(a int, b int);


insert into #t1 values(1,2);


select * from #t1;


end


go



Enable the SP:Recompile and SP:StmtRecompile events in profiler.



exec DemoProc1


go




TextData


EventClass


EventSubClass


insert into #t1 values(1,2);


SP:Recompile


3 - Deferred compile


insert into #t1 values(1,2);


SQL:StmtRecompile


3 - Deferred compile


select * from #t1;


SP:Recompile


3 - Deferred compile


select * from #t1;


SQL:StmtRecompile


3 - Deferred compile



When the stored procedure DemoProc1 is compiled, the insert and select query are not compiled. This is because during initial compilation, the temporary table does not exist and the compilation of this query is deferred until execution time. A compiled plan for the stored procedure is generated, but is incomplete. At execution time, the temporary table is created, and the select and insert statement are compiled. Since the stored procedure is already in execution, this compilation of the select and insert query are classified as a recompilation. It is important to note that in SQL Server 2005, only the select and insert statement in the stored procedure are recompiled. In SQL Server 2000, the entire stored procedure is recompiled. Subsequent re-executions of this stored procedure do not result in any more recompiles since the compiled plan is cached. Notice that even though the temporary table is re-created each time the stored procedure is executed, we do not recompile the stored procedure each time. This is because the temporary table is referenced in the plan by name and not by ID if they are created in the same module. Since the temporary table is re-created each time with the same name, the same compiled plan is re-used. Now consider a case when the temporary table is referenced in a second stored procedure as below:



create procedure DemoProc1


as


begin


create table #t1(a int, b int);


insert into #t1 values(1,2);


exec DemoProc2;


end


go



create procedure DemoProc2


as


begin


select * from #t1;


end


go



Now enable the SP:Recompile and SP:StmtRecompile events in profiler.



exec DemoProc1


go



exec DemoProc1


go




TextData


EventClass


EventSubClass


insert into #t1 values(1,2);


SP:Recompile


3 - Deferred compile


insert into #t1 values(1,2);


SQL:StmtRecompile


3 - Deferred compile


select * from #t1;


SP:Recompile


1 – Schema Changed


select * from #t1;


SQL:StmtRecompile


1 – Schema Changed



Each execution of DemoProc1 leads to recompiles of the select statement. This is because the temporary table is referenced by ID in DemoProc2 since the temporary table was not created in the same stored procedure. Since the ID changes every time the temporary table is created, the select query in DemoProc2 is recompiled.



Now let us make a slight variation to DemoProc1 as illustrated below:



create procedure DemoProc1


as


begin


create table #t1(a int, b int);


insert into #t1 values(1,2);


exec DemoProc2;


exec DemoProc2;


end


go



create procedure DemoProc2


as


begin


select * from #t1;


end


go



exec DemoProc1


go



Notice that the second execution of DemoProc2 inside DemoProc1 causes no recompiles. This is because we already have the cached query plan the select query on the temporary table and it can be re-used because the temporary table ID is the same.



It is important to group together all DDL statements (like creating indexes) for temporary tables at the start of a stored procedure. By placing these DDL statements together unnecessary compilations due to schema change can be avoided. Some other common reasons for recompiles relating to temporary tables include: declare cursor statements whose select statement references a temporary table, or in an exec or sp_executesql statement.



One of the most common reasons for recompiles of queries with temporary tables is row count modification. Consider the example below:



create procedure RowCountDemo


as


begin


create table #t1 (a int, b int);


declare @i int;


set @i = 0;


while (@i < 20)


begin


insert into #t1 values (@i, 2*@i - 50);


select a from #t1 where a < 10 or ((b > 20 or a >=100) and (a < 10000)) group by a ;


set @i = @i + 1;


end


end


go



Before executing the stored procedure enable the SP:Recompile and SP:StmtRecompile events in profiler.



exec RowCountDemo


go



The trace event data is as follows:




TextData


EventClass


EventSubClass


select a from #t1 where a < 10 or ((b > 20 or a >=100) and (a < 10000)) group by a ;


SP:Recompile


2 - Statistics changed


select a from #t1 where a < 10 or ((b > 20 or a >=100) and (a < 10000)) group by a ;



SQL:StmtRecompile


2 - Statistics changed



After 6 modifications to an empty temporary table any stored procedure referencing that temporary table will need to be recompiled because the temporary table statistics needs to be refreshed.



The recompilation threshold for a table partly determines the frequency with which queries that refer to the table recompile. Recompilation threshold depends on the table type (permanent vs temporary), and the cardinality (number of rows in the table) when a query plan is compiled. The recompilation thresholds for all of the tables referenced in a batch are stored with the query plans of that batch.



Recompilation threshold is calculated as follows for temporary tables: n is the cardinality of the temporary table when the query plan is compiled.



If n < 6, Recompilation threshold = 6.



If 6 <= n <= 500, Recompilation threshold = 500.



If n > 500, Recompilation threshold = 500 + 0.20 * n.



For table variables recompilation thresholds do not exist. Therefore, recompilations do not happen because of changes in cardinalities of table variables.

Version history
Last update:
‎Mar 23 2019 04:49 AM
Updated by: