#temp table vs. @temp table variable in stored proc?

I have a complex stored procedure (it's the strip-packing one, Dan) that
uses several #temp tables along the way. There are typically 1000 to 50000
rows inserted into them during use. At the end of the procedure the final
results end up inserted into permanent tables. Concurrency is not a factor
here because the procedure is typically run only once a month.
My question is, could any performance improvement be gained by changing
them from #temp tables to @.temp table variables?http://www.aspfaq.com/2475
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jy8ydt3440yb$.dlg@.rpresser.invalid...
> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to
50000
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a
factor
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?|||Ross,
It is hard to tell you without knowing what kind of operations are you doing
with the temporary tables. SQL Server does not create statistics for table
variables, so for small number of rows this is a good option. The best way o
f
knowing this, is giving it a try and comparing performance results.
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/defaul...7&Product=sql2k
AMB
"Ross Presser" wrote:

> I have a complex stored procedure (it's the strip-packing one, Dan) that
> uses several #temp tables along the way. There are typically 1000 to 5000
0
> rows inserted into them during use. At the end of the procedure the final
> results end up inserted into permanent tables. Concurrency is not a facto
r
> here because the procedure is typically run only once a month.
> My question is, could any performance improvement be gained by changing
> them from #temp tables to @.temp table variables?
>|||Thank you both (Aaron, Alejandro) for the links. Given the large amount of
data, I think I'll stick with my #temp tables in this instance.|||yes, My experience is that with Small rowsets, the Table Variables are the
way to go.
TempTables, you can add indexes to, etc.
For large Rowsets, Temp Tables performed better in our situations.
Greg Jackson
Portland, OR