Are temp tables better than table variables

Question

Post reply

Are temp tables better than table variables

Ashok Jebaraj

Ten Centuries

Points: 1215
More actions
March 16, 2008 at 10:38 pm

#184155

Hi All,
We are getting huge XML data from an external source system which is passes as an input to one of our stored procedures. The XML is read and stored into 5-6 table variables and the data is manipulated in the table variables and then inserted/updated into the actual database tables.
The procedure was taking a very long time to run. When er modified the procedure to use temp tables (#), the procedure ran really fast. The performance was huge around 3 minutes between the table variables and # temp tables.
Can I know when would a table variable perform bad and when we should not be using them?
Note: All the table variables have atleast 15-20 columns.
Thank you,
Ashok Jebaraj

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

One major difference between temp tables and table variables is that you can index your temp tables.

Generally when you have a large dataset you would use temp tables and for smaller datasets you can use table variables.

"Keep Trying"

Ashok Jebaraj Ten Centuries Points: 1215 More actions · Answer 2

Thank you for the reply.

In my case the dataset are really small.

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 3

Temp tables and table variables both start out in memory and spill over to disk if they get large enough.

There's a whole lot of minor differences but the big difference is, table variables do not and cannot be made to use statistics.

You might want to read the folowing URL... read it all but pay particular attention to Q3/A3 and Q4/A4.

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff Moden (3/17/2008)
There's a whole lot of minor differences but the big difference is, table variables do not and cannot be made to use statistics.

Yup. And that, along with the lack of indexes, makes them fairly inappropriate for larger data sets (over a couople hundred rows) especially if you're querying and joining.

My usual recommendation to the developers - try both ways and see which is better.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ashok Jebaraj Ten Centuries Points: 1215 More actions · Answer 5

Thank you all for your responses. I think the issue was statistics and the volume of data. I was thinking any thing within 10000 should perform the same way with or without indexes.

Thank you,

Ashok Jebaraj

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 6

if you can avoid temp table and table variables

i personally prefer to make a view if i can and use that instead of the above

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

GilaMonster (3/17/2008)
Jeff Moden (3/17/2008)
There's a whole lot of minor differences but the big difference is, table variables do not and cannot be made to use statistics.
Yup. And that, along with the lack of indexes, makes them fairly inappropriate for larger data sets (over a couople hundred rows) especially if you're querying and joining.
My usual recommendation to the developers - try both ways and see which is better.

...with its inability to use NON-CLUSTERED indexes.....

A table var can use a clustered index. but still - it is limited because of the stats.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

SQL Noob (3/18/2008)
if you can avoid temp table and table variables
i personally prefer to make a view if i can and use that instead of the above

Unless you're talking indexed views - views do exactly squat for you performance-wise.

Interestingly enough - indexed views essentially ARE temp tables (since the values are "materialized" into a hidden table, and then indexed).

Unindexed views are simply a keystroke saver - they aren't compiled or optimized on their own at all.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Matt Miller (3/18/2008)
GilaMonster (3/17/2008)
Jeff Moden (3/17/2008)
There's a whole lot of minor differences but the big difference is, table variables do not and cannot be made to use statistics.
Yup. And that, along with the lack of indexes, makes them fairly inappropriate for larger data sets (over a couople hundred rows) especially if you're querying and joining.
My usual recommendation to the developers - try both ways and see which is better.
...with its inability to use NON-CLUSTERED indexes.....

If you wanna get technical... 😉

A table var can have a single unique index (clustered or nonclustered) created as part of the primary key. It cannot have any other indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

GilaMonster (3/18/2008)
Matt Miller (3/18/2008)
GilaMonster (3/17/2008)
Jeff Moden (3/17/2008)
There's a whole lot of minor differences but the big difference is, table variables do not and cannot be made to use statistics.
Yup. And that, along with the lack of indexes, makes them fairly inappropriate for larger data sets (over a couople hundred rows) especially if you're querying and joining.
My usual recommendation to the developers - try both ways and see which is better.
...with its inability to use NON-CLUSTERED indexes.....
If you wanna get technical... 😉
A table var can have a single unique index (clustered or nonclustered) created as part of the primary key. It cannot have any other indexes.

I knew there was a definitive answer somewhere! absolutely right....Point, Set, Match to Gail!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 11

If you really want technical... scroll back up to the early stages of this thread and look at the URL I recommended... :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

The last 3 consecutive clients I have engaged with for performance tuning work have had the exact same problem: excessive use of table variables leading to poor performance. Here is the most common scenario: Optimizer can't have stats on table var, so when you look at the query plan you see an estimated row count from table var of 1 (often). This leads the optimizer to choose nested-loop-type plans. And then there is 1K, 10K or more rows in the table var - and that nested-loop plan leads to millions or even hundreds of millions in some cases reads. Absolute performance nightmare.

In every case what the tuner needs to do is evaluate if the table var has the POTENTIAL to have a large number of rows for a given sproc input. If so, it will almost certainly be more efficient to use a temp table, for which the optimizer will KNOW (probably due to a statement level recompile after populating the temp table) how many rows are in the temp table, and thus be able to choose the most efficient plan. Even paying for the overhead of temp table plus the recompile the total cost for lots of executions will be significantly less because for those situations where you do hit the nested-loop-for-large-numbers-of-rows the query cost is extraordinarily high.

YMMV

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 13

I've had a great many similar experiences especially with poor performance due to Table Variable usage. If you add in the fact that TempTables persist in Query Analyzer whereas Table Variables are no longer available at the end of a partial run of one or more queries in a spoc during trouble shooting, the only time I ever use a Table Variable is in a UDF and that's only because it won't allow me to use a Temp Table. If you consider that both live in memory and spill into TempDB when they get large enough, and the fact that a TempTable can be used in scope deeper than when it was created, and the fact that TempTables automatically drop when the proc completes, then I see no practical reason for ever using TableVariables. I'm sorry that Microsoft ever added them to the mix. I'd much rather they had allowed the use of Temp Tables in UDF's especially since you can make a TempTable using SELECT/INTO and you can't with a Table Variable. But, that's just an opinion... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

yup....What they both said....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?