Is a Temporary Table Really Necessary?
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/temptabl.asp
-
Nicely written article.
I too have found temporary tables to be invalueable. In fact, I learned the technique by reading the query plans. When I saw SQL Server creating a work table I understood that to be a way of breaking down the work.
There are times when I know better (go figure) than the query optimizer how the data is distributed. In those cases I have found that temporary tables can radically outperform any other method available. Indeed, I have even found that temporary tables can out-perform table variables.
Most of the topics for the links you referenced were for obscure issues with temporary tables. I have not found contention or radically reduced issues since SQL 2k with the creation of temp tables. Especially since MS fixed the SELECT INTO bug. The other issues dealing with Identity columns or BCP into a temp table are rather esoteric.
I say use them if they work...and many times they work well.
Cheers,
Ben Taylor
-
"now and not multiple queries that have to researched and changed. I use this technique quite often and it often forces me to use a temporary table to hold the results of those stored procedures since Transact-SQL does not allow the results of a stored procedure to be used as a table"
Have you considered using table valued fucntions instead? This would allow modular code without forcing you to use temp tables.
What you think?
-
I have some kinds of this experieces.
For your query,
SELECT table1.numCustID, table2.strPhoneNumber, table3.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN dbo.table2 table2
ON table1.numBillID = table2.numBillID
INNER JOIN dbo.table3 table3
ON table2.numBillDtlID = table3.numBillDtlID
WHERE table1.numCustID = '5555'
AND table2.strPhoneNumber = '5555555555'
AND table3.strPhoneNumberCalled = '1234561234'
ORDER BY table3.dtmCalled DESC
I would rewrite it to:
SELECT t1.numCustID, t2.strPhoneNumber, t3.strPhoneNumberCalled
FROM
(Select * from dbo.table1 WHERE dbo.table1.numCustID = '5555'
) as t1
INNER JOIN
(Select * from dbo.table2 WHERE dbo.table2.strPhoneNumber = '5555555555'
) as t2
ON t1.numBillID = t2.numBillID
INNER JOIN
(Select * from dbo.table3 WHERE dbo.table3.strPhoneNumberCalled = '1234561234') as t3
ON t2.numBillDtlID = t3.numBillDtlID
ORDER BY table3.dtmCalled DESC
So, no temp table needed but performance is ok.
-
Slicing up problems with temp tables really helps with performance. And you've identified ways to improve upon that basic approach by narrowing the number of columns and filtering the rows.
I was surprised that you failed to mention a more effective method of improving performance without the penalties caused by temp tables - Table Variables. Table Variables are more performant than temp tables because they don't use tempdb. I regularly replace the use of temp tables with table variables when maintaining code. And have recently discovered they can be returned from Functions.
Thanks for the article, temp tables are better than cursors and they can improve performance.
-
Nice article!
I have been forced to use temporary tables because of the fact that most of our code is very reusable in the form of Stored Procedure and we do sometime use INSERT INTO #temp_table. In fact, there is a piece of code which uses CURSOR to create and drop a temporary table. May not sound very elegant but it works
. -
"Table Variables are more performant than temp tables because they don't use tempdb."
From my experience I have seen table variables still built into tempdb. Do some simple testing and you will see the same results. You are accurate as Table variables do seem to perform better than classic table vars.
-
I've been using temporary tables in SQL Server for years, and they have helped me solve a lot of problems. Please don't tell Joe Celko about this, as he will have me excommunicated as a SQL developer for my heresey (a quote from J.C. - "...In 20 years, I have found that it is always possible to come up with a subquery, derived table expression or a VIEW instead of a temp table.")
One thing I have noticed, though, is that using temporary tables inside of a transaction can be tricky - I often experience very poor performance or even have the whole process come to a halt. These are often cases where the same procedure *without* the transaction will run very quickly.
The problem seems to be because a much larger number of locks are created in the case of temp tables inside a transaction. In many cases, I've solved the problem by replacing the temp table with a derived table.
I haven't done any real work with SQL Server 2005 yet, but it looks to me as if I can use Common Table Expressions (CTE) in many cases where I would have otherwise use a temporary table.
Thanks for the great article, Randy.
Eric - I like your icon - the first computer I owned was a TI-99/A in 1982!
Best regards,
SteveR
-
I have a related question:
In a stored procedure, is it better to create and later drop a "normal" table, instead of using a temp table?
-- Stephen Cook
-
agree with pokitlok I always do the same. but some times I use temp table , must of the time to avoid cursors.
-
Good article, but I tend to disagree on the "roll your own" cursors. I have seen several situations where using a cursor vastly outperformed the solution you seem to advocate. This is particularly true where you are using explicit transactions for some reason.
Over the years I have come to like, and rely on, derived tables and subqueries. These are generally my "go to" option. Then if I need to I'll use table variables. Temp tables are generally the last thing I look at using and usually it's in situations where putting an index on them really helps. Finally, using an indexed view can sometimes be a good alternative to temp tables.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I've index table variables many times:
declare @table table (id int not null primary key . . .
-
While you can set some basic constraints on a table variable you cannot delcare indexes per se, and from my experience the unique or PK constraints don't seem to help query performance the way that an index on a table would.
From BOL "Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables."
Without statistics, the PK or unique constraints cannot be used by the optimizer to boost query performance.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Hi Stephen,
The problem with creating and dropping a "normal" table in a stored procedure is that two users might try to run the same procedure at the same time and step all over each other, trying to access the "real" table.
A local temporary table (prefixed by # as opposed to ##), on the other hand, is local to a connection, so two users won't interfere with each other provided they are using 2 different connections to the database.
What happens with local temporary tables is that behind the scenes, a "real" table is created in tempdb with a lot of underscores and some hex digits added on to the the name you see. If my friend Sam and I both run a stored procedure that creates a temp table #Foo, what happens is that Sam creates a table in tempdb that will be called something like #Foo________________________________________________________________________________________________________________000000017D5D
while I will create, for example, one called #Foo________________________________________________________________________________________________________________000000029A4F
(if you scroll all the way to the right, you'll see that the last several digits in the name are different)
Regards,
SteveR
-
All of the links at the end of the article are broken.....
Viewing 15 posts - 1 through 15 (of 93 total)
You must be logged in to reply to this topic. Login to reply