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 |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-06-09 : 18:03:14
|
| Hi,We were using select ..into clause to insert into table with database recovery model to bulk logged and then insert more data into that table. This is just part of big procedure where we are populating temporary tables. Since only 1st insert was minimally logged and not rest of inserts, we were evaluating table variables and it seems initially that they are doing minimal logging as they we very fast for normal inserts. Today we are trying to populate table variable with 1 million rows and it is taking much more time and even more than normal table. When I check tempdb it seems that insert into table variable is not minimal logged but in fact it is doing more logging than a normal tables. So I was just curious if anyone else also have some performance issues while using table variables?What do you recommend - temporary tables or table variables for performance?Thanks--Harvinder |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 18:09:01
|
| Table variables are only recommended for small tables, which according to the article that I read is less than 10,000 rows.I'll see if I can find the MS article that I am referring to.Tara |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-09 : 18:12:36
|
| http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2kTara |
|
|
|
|
|
|
table variables vs temporary tables for performace