Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Table Variables versus Temp Tables versus Permanant Tables Expand / Collapse
Posted Tuesday, December 9, 2008 8:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 3, 2011 9:11 AM
Points: 64, Visits: 104
I have a dot net application which heavily uses XMLs. XML data is stored in Temp Tables during the processing. Data is huge and so are the xmls. Due to this, we are having some performance challenges. Can anyone suggest what is best when it comes to huge chunk of data? Should it be temp Table or Table Variable or a Permanant Table and do Insert/Truncates?

There is an article on MSDN for SQL 2000, however we are using SQL 2005.

Any help on this would be appreciated.

Post #616632
Posted Tuesday, December 9, 2008 9:24 PM



Group: Administrators
Last Login: Today @ 1:59 PM
Points: 34,363, Visits: 18,582
There isn't a lot of difference between temp tables and table variables. They'll both spill over into tempdb if the data sizes are large.

If you have a lot of inserts/updates, you can use a permanent table and it might save you some memory, but depending on how you do things, it's possible that your application should be architected to handle the load better.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #616645
Posted Wednesday, December 10, 2008 2:53 AM


Group: General Forum Members
Last Login: Monday, January 30, 2012 1:37 AM
Points: 159, Visits: 222

You may be able to decide this after reading this article.

Regards | Enbee
Post #616799
Posted Wednesday, December 10, 2008 7:46 AM



Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 17,172, Visits: 32,134
Is the data loaded through XML being filtered later in the query? If so, use either a permanent table or a temporary table. If not, if the data is doing scans against the temporary storage anyway, then table variables won't add overhead and could save time and recompiles. There are no hard & fast answers though because other factors could affect these decisions in addition to the simple ones I've listed here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #617033
Posted Thursday, February 11, 2010 5:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 11, 2010 5:59 AM
Points: 1, Visits: 1
In my experience the only sure fire way of deciding is to test alternative methods of your code with real life data volumes. Temp variables are better performers in simple scenarios.

However, whilst optimising a long running process recently I found that Temp tables beat temp variables by a factor of 10 when used in complex queries. reduced a 48:00 minute procedure run to 4:26 !!!

The consensus at the time was due to the query optmizer choosing a sub-optimal plan because it assumes temp variables have only 1 row! (I didn't prove this but initial query analyser execution plans showed it to be true)

My personal rule of thumb is assume temp tables are quicker if the scenario is complex, and use temp variables if a) you have to for other technical reasons, or b) where the subsequent use of the temp variable data is in simple queries (eg. zero joins).

I hope this helps!

Post #864018
Posted Friday, February 12, 2010 6:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 4, 2010 10:53 AM
Points: 6, Visits: 79
Actually it depends on the usage.

Table Variable: It can be used as long as you capture/store less records ie in 100s or <1000 records. It also usefull if you don't need to search any record in the Table variable or not joined with any other table(s). TV data or TV structure is like local to the process / procedure

Case you have mentioned need to process large volume of data. Hence table variable is not the ideal solution. Still you want to use this TV ensure you are not doing any search operation / no join with other table(s) and every tousand records you are clearing this TV.

Temp Table and Normal Table: Both serve the purpose what you are looking for. The only diff is TEMP table's data is available in the current session or instance ONLY.

Hope this would give some idea
Post #864562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse