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.
Types of Temporary tables
There are two types of temporary data types. They are namely Local and global. Let me first give you and example to start the temporary table. Following example is taken from Books on Line of Microsoft® SQL Server™ 2000.
"The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server" 1
"For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect."2
Note: 1,2 above is taken from the book on line sub heading Creating and Modifying
Unlike in the access days, you do not have to delete these temporary tables manually, instead you can rely SQL Server to do it automatically.
No comments:
Post a Comment