Table Variables are Faster Than Temp Tables - Fact or Fiction
 |
(Ben Taylor) I have read in numerous articles and postings that table variables are a preferred method for temporary storage in stored procedures. They state that the use of temporary tables in tempdb can not perform as quickly as a table variable in memory. This has been such a general statement without qualification that I feel I must contest. In fact, I have stored procedures that prove contrary. This article reviews one condition that I find common where a temporary table out performs a table variable 1,000%. |
|
| | Related Articles - For Members.
|
|
Key (Please note):
(R) - registration may be required for access at the target site
($) - target site may require paid membership for access to this or other content
Reader Comments: Post Your Comments/Feedback
RE: Table Variables are Faster Than Temp Tables - Fact or Fiction
(posted: 6/28/2005 11:57:25 AM)
> Always test your stored procedures for performance
Indeed, if in doubt test it out. As mentioned on various occasions, performance is largely a physical property that may be expected to vary with resources and utilization. HTH
RE: Table Variables are Faster Than Temp Tables - Fact or Fiction
(posted: 6/28/2005 12:26:46 PM)
declare @Filter TABLE (
CUST_ID INT NOT NULL,
INV_ID INT NOT NULL,
INV_START SMALLDATETIME NOT NULL,
INV_END SMALLDATETIME NOT NULL
unique clustered cust_id, inv_id)
This technique was covered beautifully by Tom Moreau in an article titled "How Table Variables Can Speed Up Your Queries".
ffoiii
Frank F. Owen III
RE: Table Variables are Faster Than Temp Tables - Fact or Fiction
(posted: 6/28/2005 12:39:23 PM)
There is a major flaw in your testing method. One reason that it often favorable to use table variables is that it doesn't cause frequent recompilation of procedures like temp tables do. By clearing the proc cache for each run, you've totally illiminated that factor from the testing. That would be like tying Michael Johnson's ankles together and then bragging because you beat him in a foot race.
Nonetheless, I agree that using table variables instead of temp tables is not always better.
RE: Table Variables are Faster Than Temp Tables - Fact or Fiction
(posted: 7/5/2005 12:12:16 AM)
Thanks for the comments. I took them seriously and tested using each of your recommendations. Here are the results:
Changed temp tables to temp memory variables declared with unique clustered. Ran multiple tests and did not clear any cache.
Temp tables performed an average of 4 times faster than table variables.
Cheers,
BST
|
