Query performance can be affected hugely by choice of temporary tables or table variables. Both have upsides and downsides. Faced with either type of table SQL Server will try to keep operations in memory, and both use the tempdb database.
The main differences are: temporary tables are written to the transaction log (so operations can be rolled back), you can create indexes on temporary tables and as such they often perform better when there are many records in the temp table and the query involves joins. Temporary tables however need more careful cleanup – the scope isn’t restricted to the procedure as with table variables. Finally, procedures with temporary tables may need to be recompiled frequently whereas procs with table variables can be pre-compiled.
If the recordsets for the tables are small, and the proc is executed very frequently then table variables might be the way to go. Try both flavours – certainly don’t assume that one is better than the other.
February 4, 2009 at 4:25 pm |
Thanks for the good info.