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 |
|
shan
Yak Posting Veteran
84 Posts |
 Posted - 2010-01-11 : 14:05:09
|
| Which one is faster, Table variable or temp table. I created a ETL process and i tested with both #temp table and table variable and i see process runs faster while I use #temp table.ETL process basically cleans from downloaded from 4 sources the data and have lots of formulas (pretty complicated) and finally populates in the reporting table which is in dimensional model.Pls let me know your thoughts about temp table and table variable with respect to perfomance.-Shan |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-11 : 14:08:38
|
| You should only use table variables if the data in it will be very small such as less than 100 rows.We ran into a major performance issue with table variables when we assumed our data set was small. We switched to a temporary table and got a huge performance gain. The reason why table variables are bad for performance is because there are no statistics associated with them which means it assumes you only have one row in the table. We have made it a standard to always use temporary tables due to our experience as well as discussing it with a MS SQL Server engineer.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
shan
Yak Posting Veteran
84 Posts |
Posted - 2010-01-11 : 14:19:24
|
| Thanks for your reply. I too noted a significant performance gain using #temp instead of @tempAlso Will there be difference in performance if I create temp using into clause (select col1 into #temp from table)or expect creation of #temp using create table #temp.-Shan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2010-01-11 : 14:55:39
|
I agree, as a rule of thumb use a #Tmp table instead of a variable unless you have no other option (i.e. a Function). It doesn't mean it's always a performance increase, but I have yet to see it hurt performance.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shan
Yak Posting Veteran
84 Posts |
Posted - 2010-01-11 : 15:03:52
|
| Thanks for your suggestion.-Shan |
|
|
|
|
|
|
#temp table vs @table variable