Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Is a Temporary Table Really Necessary? Expand / Collapse
Author
Message
Posted Sunday, April 7, 2002 12:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 14, 2007 1:40 PM
Points: 42, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/temptabl.asp


Post #3450
Posted Tuesday, November 15, 2005 10:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 9, 2006 11:54 AM
Points: 2, 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

Post #237390
Posted Friday, June 2, 2006 3:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 2, 2013 7:04 AM
Points: 374, Visits: 408
"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
Post #284464
Posted Friday, June 2, 2006 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 11, 2011 8:52 PM
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.




Post #284485
Posted Friday, June 2, 2006 6:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 7, 2013 11:44 AM
Points: 51, 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.



Post #284506
Posted Friday, June 2, 2006 7:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 31, 2013 11:41 PM
Points: 121, 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 .
Post #284513
Posted Friday, June 2, 2006 7:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, 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.

 

Post #284518
Posted Friday, June 2, 2006 7:50 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:52 AM
Points: 91, Visits: 194

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

 




Post #284530
Posted Friday, June 2, 2006 8:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:08 AM
Points: 860, Visits: 807
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
Post #284562
Posted Friday, June 2, 2006 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 4, 2009 1:49 PM
Points: 160, 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/
Post #284567
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse