11.0 Temporary Tables, Table Variables and Recompiles
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="https:// 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 
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="https:// schemas.microsoft. com/sqlserver/2004/07/ showplan" Version="1.0" Build="9.00.0000.00"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementText= "
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.