December 9, 2008 at 8:58 pm
Hello:
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.
http://support.microsoft.com/?kbid=305977
Any help on this would be appreciated.
Thanks,
Ramesh.
December 9, 2008 at 9:24 pm
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.
December 10, 2008 at 2:53 am
You may be able to decide this after reading this article.
http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html
Regards | Enbee
December 10, 2008 at 7:46 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2010 at 5:52 am
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!
February 12, 2010 at 6:04 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply