Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dcarva
Posting Yak Master
140 Posts |
 Posted - 2006-04-04 : 17:17:34
|
| I read an article on sqlmag.com stating that temporary tables are almost always faster than table variables. It went on to describe how table variables don't take advantage of execution plans, therefore SQL might choose a poor execution plan. Well, I did some tests against a 500,000 record table. Basically, I:1) Load the entire table (4 columns) into a temp table2) Select * from tempTable I do the same thing with a table variable:1) Load entire table into a table variable2) Select * from @TableVariableThe table variable was faster in most cases. Here are the times when run from Query Analyzer. (nothing else was running on this box)---------Temp table test run 5 times in a row00:1802:0002:0200:5501:42Average: 417 seconds---------Table variable test run 5 times in a row00:3200:4201:3300:4300:36Average: 246 secondsSo my question is, in what scenario are temporary tables faster? I ran several iterations of these tests, and the table variable seemed to outperform temporary tables in my tests.ThanksDanny |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-04 : 17:26:52
|
| According to everything that I have read, table variables should be faster as long as the number of rows in the table variable are less than 10,000 (approximately). BOL goes into some detail as to why table variables are faster and there is a good MSDN article that compares both. I'll see if I can find it.[EDIT] Found the article: http://support.microsoft.com/default.aspx?scid=kb;en-us;305977Tara Kizeraka tduggan |
 |
|
|
dcarva
Posting Yak Master
140 Posts |
Posted - 2006-04-04 : 17:36:25
|
| Good article. Thanks for help. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Temp Tables vs Table Variables