MS SQL Server - Temp Tables vs. Table Variables

Author

Temp Tables vs. Table Variables

Larry Menzin

2006-03-27, 1:23 pm

Is there any performance advantage to using table variables in scripts and
SPs versus using temp tables? I know that both reside in Tempdb. When using
tools like execution plan and Index Tuning wizard, would table variables get
more meaningful results?

Also, how important is it to put tempdb on its own disk array on servers
that are heavily loaded? If I had only one spare array, would it be better to
use it for tempdb or for all the non-clustered indexes?

--
Larry Menzin
American Techsystems Corp.

Andy Price

2006-03-27, 8:23 pm

Table variables are in-memory structures, but a table structure will also be
created in tempdb. Table variables will be faster than #temp tables. Also
they go out of scope after you finish your batch, so you dont have to do any
clean-up such as dropping them.

--
Andy Price,
Sr. Database Administrator,
MCDBA 2003

"Larry Menzin" wrote:

> Is there any performance advantage to using table variables in scripts and
> SPs versus using temp tables? I know that both reside in Tempdb. When using
> tools like execution plan and Index Tuning wizard, would table variables get
> more meaningful results?
>
> Also, how important is it to put tempdb on its own disk array on servers
> that are heavily loaded? If I had only one spare array, would it be better to
> use it for tempdb or for all the non-clustered indexes?
>
> --
> Larry Menzin
> American Techsystems Corp.

Rich Wood

2006-03-27, 8:23 pm

I have found table variables to be much, much slower than #temp tables in
many cases in SQL Server 2005. In these cases, there was much less of a
difference in performance in SQL Server 2000, although the #temp tables were
still faster. The fact that SQL Server 2005 keeps statistics on #temp tables
but not table variables is likely the source of the performance difference.
If you do use table variables, depending on how you use them, defining a
primary key can result in much faster performance.

Rich Wood

"Andy Price" wrote:
[color=darkred]
> Table variables are in-memory structures, but a table structure will also be
> created in tempdb. Table variables will be faster than #temp tables. Also
> they go out of scope after you finish your batch, so you dont have to do any
> clean-up such as dropping them.
>
>
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
>
> "Larry Menzin" wrote:
>

Gert-Jan Strik

2006-03-27, 8:23 pm

If that is true, then a table variable that becomes larger than the
internal memory would first flush all cached data (forcing SQL Server on
its knees) and then fail after all, because of insufficient memory. In
short: it is not as simple as that. If you ask me, it is far from...

Do you have any proof (or even an indication) that table variables are
even a tiny bit more "in-memory" than temp tables are? Do you have an
example that proves that the use of a table variable is significantly
faster than a temp table? Because I have not seen any documentation that
supports such a difference.

Gert-Jan

Andy Price wrote:[color=darkred
]
>
> Table variables are in-memory structures, but a table structure will also be
> created in tempdb. Table variables will be faster than #temp tables. Also
> they go out of scope after you finish your batch, so you dont have to do any
> clean-up such as dropping them.
>
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Larry Menzin" wrote:
>

Mike Hodgson

2006-03-27, 8:23 pm

Can you clarify what you mean by an "in-memory" structure? Table
variables do not reside solely in memory. In terms of disk IO & memory,
they are no different to temp tables - they are backed by disk. Every
modification to a table variable is written to disk as well (just like
temp tables). To prove it, simple inspect the transaction log of tempdb
with ::fn_dblog() before & after writing a few rows to a table
variable. Modifications to table variables are definitely written to
disk. This is true for SQL 2000 & SQL 2005.

I did a little research on this topic last year (see
http://sqlnerd.blogspot.com/2005/09...-variables.html),
albeit a little naively, and found that performance was about the same
for table variables & temp tables up to sets of about 1-2 million rows.
Above that, temp tables started edging away (this was on SQL 2000).

The basic advantages of temp tables was the DRI you could define on them
(keys, indexes, constraints, etc.) and the fact that the query optimiser
would take indexes & statistics on columns into account when compiling
execution plans for queries involving temp tables, which is more
pertinent in larger sets. The advantages of table variables include the
fact that they can be returned from, and modified within, table-valued
functions and the scope of transactions involving table variables is
shorter resulting in less locking & logging than temp tables (and fewer
recompiles in general when used in stored procs). While the scope of a
table variable is no better defined than the scope of a temp table, it
is a little "tighter" and some folk tend to find this more convenient
(probably the same people who think automatic garbage collection in
recent 3GLs (eg. the .NET framework) is the best thing since sliced
bread). There are many limitations to table variables that do not apply
to temp tables (that are documented in BOL).

The bottom line is in most situations they perform roughly the same
(although for larger sets temp tables usually exhibit better
performance) and the onus is on the developer to thoroughly load test
their code. Basically use a temp table or table variable where the
limitations of the situation preclude the use of one or the other and in
case where you could legally use either, test, test, test, and pick the
solution that performs better (which is often a matter of personal
preference since performance is often virtually identical).

--
*mike hodgson*
http://sqlnerd.blogspot.com

Andy Price wrote:

>Table variables are in-memory structures, but a table structure will also be
>created in tempdb. Table variables will be faster than #temp tables. Also
>they go out of scope after you finish your batch, so you dont have to do any
>clean-up such as dropping them.
>
>
>
>

Sponsored Links