Share via

11.0 Temporary Tables, Table Variables and Recompiles

  • Article

 

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

&#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="https://

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.

Comments

  • Anonymous
    January 29, 2007
    Does anyone know if the limitation of not being able to use a table variable as an input parameter to a stored procedure is going to be resolved in a future version? This would be NICE functionality, and a legitimate extension to the table variable data structure.

  • Anonymous
    January 29, 2008
    The comment has been removed

  • Anonymous
    January 29, 2008
    The comment has been removed