Eliminate the Use of Temporary Tables For HUGE Performance Gains
| By : Justin Gunther May 30, 2002 |
As queries become more complex, temporary tables are used more and more. While temporary table may sometimes be unavoidable, they can often be sidestepped by using derived tables instead. In brief, a derived table is the result of using another SELECT statement in the FROM clause of a SELECT statement. By using derived tables instead of temporary tables, we can boost our application's performance. Let's find out more.
How the Use of Temporary Tables Affect Performance
Temporary tables slow performance dramatically. The problem with temporary tables is the amount of overhead that goes along with using them. In order to get the fastest queries possible, our goal must be to make them do as little work as possible. For example, with a SELECT statement, SQL Server reads data from the disk and returns the data. However, temporary tables require the system to do much more.
For example, a piece of Transact-SQL code using temporary tables usually will:
1) CREATE the temporary table
2) INSERT data into the newly created table
3) SELECT data from the temporary table (usually by JOINing to other physical tables) while holding a lock on the entire tempdb database until the transaction has completed.
4) DROP the temporary table
This represents a lot of disk activity, along with the potential for contention problems. And all of this adds up to poor performance.
Eliminate A Few Steps!
The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance.
Here are the steps when you use a temporary 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
Compare the above to the number of steps it takes for a derived table:
1) CREATE locks, unless isolation level of "read uncommitted" is used
2) SELECT data (read activity)
3) Release the locks
As is rather obvious from this example, using derived tables instead of temporary tables reduces disk I/O and can boost performance. Now let’s see how.
Using Derived Tables
Derived tables are essentially SELECT statements within SELECT statements. Let's look at a very simple example:
Take a look at this simple query where we SELECT data from a table:
USE northwind
GO
SELECT * FROM categories
Now, instead of selecting data from the categories table, let’s select our data from a derived table. For example:
USE northwind
GO
SELECT * FROM (SELECT * FROM categories) dt_categories
This is all there is to derived tables. Remember, a derived table is just the result of using another SELECT statement in the FROM clause of another SELECT statement. Simply put the query in parenthesis and add a table name after the query in the parenthesis.
Both of the above examples produce the exact same results. This example is designed to show you what a derived table is, and how easy they are to create. In the real world, if you needed to write a query like the one above, you would of course use the simpler of the two examples. But if your query is complex, consider using a derived table instead of a temporary table, as we will see in the following real world example.