What are the negative performance hits from using table variables?

Question

What are the negative performance hits from using table variables?

John-250225

Mr or Mrs. 500

Points: 588
More actions
June 14, 2006 at 7:17 pm

#153042

Hi,
I have a sproc that has about 9 different update statements made to a table variable and 1 insert statement to the same table variable. Correct me if I'm wrong but table variables are better to use than temp tables cause you can run into tempdb locks where as a table variable is in memory. So i'm wondering if their are problems with using table variables? I'm trying to enhance the performance on a sproc.
Also, is their a way to find out what part of your code is creating the tempdb locks? Appreciate the help!

Viewing 6 posts - 1 through 5 (of 5 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>Correct me if I'm wrong but table variables are better to use than temp tables cause you can run into tempdb locks

Not necessarily. It depends on whether the #temp table is created via CREATE TABLE versus SELECT INTO. If you use SELECT INTO with a long-running SELECT, it locks the tempdb system tables.

Depending on your usage and how your 9 different updates are performed, a #temp table with appropriate indexes on it may outperfrom a @Table variable, particularly if you are joining other tables to the #temp table during the updates.

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 2

Depends on your usage of those variables. If you're planning to put large volumes of data in them (by which I mean several thousand rows or more), then I'd go for a #temp table every time. Use the CREATE TABLE ... INSERT syntax, rather than the SELECT INTO ... as PW recommends above. Also, you can create indexes on #temp tables.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

not all table variables are created in memory and not all #temp tables are created on disk. Depends upon available resource.

With large data sets you can run into problems with table variables, note that table variables are not subject to stats updates or maintaining statistics. You can create a PK on a table variable for use in ordering or enforcing uniqueness. ( it won't speed up the query )

Using table variables within multi table joins may not produce the best query plan, it's not a big deal - you just have to use join hints.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

mel-260965 SSC Enthusiast Points: 147 More actions · Answer 4

The big difference between @table and #table is not just indexing.

@tables cannot be referenced in embedded SQL scripts used by sp_executesql.

@tables cannot be rolled back.

Other than these issues, performance is very similar.

...Mel

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

SOMETIMES the performance is similar... SOMETIMES a table variable will absolutely kill performance... read the following to find out why... pay particular attention to "Q3" and "Q4"...

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)