Table variable performance vs. temp table

Hi,

I've got an identical query, one with temp tables and one with table
variables that results in two completely different execution times.
The plans look virtually the same but execution time is around 2
minutes with the variables and less than a second with the temp table.
The plan shows that most of the time with the variable scenario is
inserting into @c. Any ideas what could cause this?

Table Variable:

declare @c table (
kerberos nvarchar(20),
inv nvarchar(20)
)

insert into @c (kerberos)
select distinct kerberos from prod_users where isdisabled <> 'yes'

update @c set inv = 'yes'
where kerberos in (select kerberos from prod_radius_membership)

select kerberos, inv
from @c


Temp Table:

create table ##c (
kerberos nvarchar(20),
inv nvarchar(20)
)

insert into ##c (kerberos)
select distinct kerberos from prod_users where isdisabled <> 'yes'

update ##c set inv = 'yes'
where kerberos in (select kerberos from prod_radius_membership)

select kerberos, inv
from ##c

drop table ##c

There can be a number of reasons why a temp table can be faster than a table
variable and a number of reasons why temp tables can be slower then table
variables. If you have a case where the temp table is significantly faster
than the table variable, three common reasons are 1) SQL won't create
statistics on table variables, 2) SQL won't use parallelism on inserts to
table variables and 3) the indexes that can be created on a table variable
are limited. Since you aren't using indexes and you think the time is being
spent in the insert, my guess would be reason number 2.

You might want to look at
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-ta...-variab
for more info.

But it is often difficult to tell why the difference exists (at least for
me). I usually don't worry too much about why when I find a difference. I
just pick either a table variable or a temp table when designing the code.
Which one I pick depends on several things. The more data that goes into
the table and the more complex the usage of the table, the more likely I am
to pick a temporary table. Then if the performance when testing the choice
I made with real world sized data is good, great. If not, it's easy to
change the code to try the other way.

I realize that the code you have given may be just a sample demonstrating
the problem. But if it is your real code, I would have two comments.
First, if all you do with a temporary table is create it, load and process
data, and then drop the table and the table is never used by some other
connection, a local temporary table (#c) would be sufficient, you don't need
and therefore shouldn't use a global temporary table (##c). Also, if this
is all you are doing, loading this data into the table and then selecting it
and then dropping the table, it may be faster yet to just do this in one
select, something like

select distinct u.kerberos,
case when m.kerberos is not null then 'yes'
else null end as inv
from prod_users u
left outer join prod_radius_membership m on u.kerberos = m.kerberos
where u.isdisabled <> 'yes'

Tom

wrote in message

> Hi,
>
> I've got an identical query, one with temp tables and one with table
> variables that results in two completely different execution times.
> The plans look virtually the same but execution time is around 2
> minutes with the variables and less than a second with the temp table.
> The plan shows that most of the time with the variable scenario is
> inserting into @c. Any ideas what could cause this?
>
> Table Variable:
>
> declare @c table (
> kerberos nvarchar(20),
> inv nvarchar(20)
> )
>
> insert into @c (kerberos)
> select distinct kerberos from prod_users where isdisabled <> 'yes'
>
> update @c set inv = 'yes'
> where kerberos in (select kerberos from prod_radius_membership)
>
> select kerberos, inv
> from @c
>
>
> Temp Table:
>
> create table ##c (
> kerberos nvarchar(20),
> inv nvarchar(20)
> )
>
> insert into ##c (kerberos)
> select distinct kerberos from prod_users where isdisabled <> 'yes'
>
> update ##c set inv = 'yes'
> where kerberos in (select kerberos from prod_radius_membership)
>
> select kerberos, inv
> from ##c
>
> drop table ##c
>