Hashing out hash tables (temp tables)

Temp tables use (or the lack of use) has a great impact on a lot of different aspects of the database.
Temp tables can cause a very negative impact on performance if used incorrectly and even if you use them in an efficient t manner you will still be taking a performance hit.
By using derived tables (Sub queries) you will hit your data in a more efficient way.
That being said sometimes temp tables can be the best solution for your current situation.
Some benefits of tem tables are
• They are sometimes much easier to create then trying to formulate the same result by using a sub query
• Makes production code cleaner; sub queries can get really messy.
• in data analysis tasks or in situations where the code will never hit a production database they are one of our greatest tools

This being said it is imposable to overlook the performance bottle neck that these little guys can inflict on a database. Let’s take a look at what is involved when SQL encounters a tem temp table
1) Lock tempdb database
2) CREATE the temporary table (write activity)
3) SELECT data & INSERT data (read & write activity)
4) SELECT data from temporary table and permanent table(s) (read activity)
5) DROP TABLE (write activity)
4) Release the locks
This represents a lot of activity under the hood for the database engine and will usually result in poorly performing chunks of code.
This being said I have come across many situations where I have used temp tables in my production code as a last resort. As with everything it depends on the situation.

One example of this is when the situation arises when you need to use a record set generated from one stored procedure in another stored procedure as this is the only way to do this without using a physical table.

A few tips for using temp tables in a efficient manner.
• Only include columns or rows you actually instead on using
• Do not use the select into statement; It places a lock on system objects
• Always delete the temp table yourself... that’s just a waste of resources
• If the temp db is not already on its own disk consider doing this This will help greatly
In closing I would like to say, I personally think that temp tables are a great tool and , if used correctly can be the best situation. As always we must understand the impact of these little guys before we start using them.

Comments, questions
McCannblogs@gmail.com
Or join our LinkedIn group here
http://www.linkedin.com/groups?gid=1856678&trk=hb_side_g

McCann OUT