Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is a Temporary Table Really Necessary?


Is a Temporary Table Really Necessary?

Author
Message
Randy_Dyess
Randy_Dyess
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/temptabl.asp



Benjamin S Taylor-194434
Benjamin S Taylor-194434
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1

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
btaylor@sswug.org


Jules Bonnot
Jules Bonnot
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 409
"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?

www.sql-library.com
pokitlok
pokitlok
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8

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.





jwainz
jwainz
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 40
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.



sheepoo
sheepoo
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 34
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 .
einman33
einman33
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 512

"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.


Steve Rosenbach
Steve Rosenbach
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 206

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





Stephen E. Cook
Stephen E. Cook
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 818
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
Pedro R. Lopez
Pedro R. Lopez
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 140

agree with pokitlok I always do the same. but some times I use temp table , must of the time to avoid cursors.



Pedro R. Lopez
http://madurosfritos.blogspot.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search