Using Temp tables in calculations 1

[manmaria]

What are the drawbacks for using the temp tables in calculation in stored procedures? Are they going to be a performance issue?

Thanks

 

[jrsansom]

Hi Manmaria,

Personally I find that the spec of the server hosting your database has a very strong influence but the overall consensus seems to be to try to avoid using temporary tables where possible:

either by using derived table:

http://www.sql-server-performance.com/jg_derived_tables.asp

or by using the table variable when appropriate(i.e. smaller data sets):

http://www.odetocode.com/Articles/365.aspx

SQL Server Temp Table Performance Tuning Tips

http://www.sql-server-performance.com/temp_tables.asp

In my experience when working with very large data sets, using a physical table offers more performance (even though in theory a temp table is a physical table ).

Hope this helps.

cheers,