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 «««678910»»

Is a Temporary Table Really Necessary? Expand / Collapse
Author
Message
Posted Friday, May 30, 2008 5:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
jmapledoram (5/30/2008)
But correct me if I'm wrong, doesn't a Table Variable get stored in ram rather than the tempdb? You will want to consult your DBA before writting a lot of these. You could just be transfering work load from one area to antoher without the hardware to support it.


Yes and no... if a table variable fits in memory, then it does just like a derived table does... it uses memory. If it doesn't fit in memory, it will use TempDB, just like a derived table does.

Now, here's the surprise for some folks... where does a Temp Table live? If you said TempDB, you're only half right because, just like a Table Variable, if it fit's in memory, it lives in memory. Both will have an "entry" in TempDB and a derived table can appear as a "work" table in TempDB... same holds true for CTE's.

Don't take my word for how both Table Variables and Temp Tables live in either memory or TempDB depending on their size though... I've posted it several times before on this very thread... if you haven't done so already, PLEASE read Q3/A3 and Q4/A4 in the following URL...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

For those that think SELECT/INTO isn't logged in a database with a SIMPLE recovery mode... EVERY action in a database is logged... SELECT/INTO, if it meets certain conditions, is MINIMALLY logged and will still blow away an INSERT into a new table even if the database is in the FULL recovery mode.

Again... don't take my word for it... run the following in each recovery mode... notice the number of logical reads on the INSERT INTO example...

--===== Create and populate a 10,000 row test table.
-- This is the "controlled" source for further tests
SELECT TOP 10000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
GO
----------------------------------------------------------------------------------------
-- The INSERT/INTO test
----------------------------------------------------------------------------------------
--===== Create a table for the Insert/Into test
CREATE TABLE Table1 (SomeID INT,SomeInt INT, SomeLetters2 CHAR(2))

--===== Do the test while capturing some statics information
SET NOCOUNT ON
PRINT '===== INSERT INTO ====='
SET STATISTICS IO ON
SET STATISTICS TIME ON
INSERT INTO Table1
SELECT * FROM dbo.JBMTest
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',78)
GO
----------------------------------------------------------------------------------------
-- The SELECT/INTO test
----------------------------------------------------------------------------------------
--===== Do the test while capturing some statics information
SET NOCOUNT ON
PRINT '===== SELECT INTO ====='
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT *
INTO Table2
FROM dbo.JBMTEST

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',78)
GO

--===== Simple house keeping
DROP TABLE dbo.Table1, dbo.Table2, dbo.JBMTest



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #509422
Posted Sunday, June 1, 2008 7:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:33 PM
Points: 85, Visits: 260
pduplessis (5/29/2008)
I agree that in most cases you can avoid the use of temp tables by structuring your query slightly differently, but for sure, sometimes they are unavoidable.
[snip]...
Firstly, ANYTHING going into a where statement, I queried the dimensions first and got the respective keys for (example, only certain products are deemed to be "activating" products). Popped these into a temporary products table, which I now could use in an inner join on my monster fact
[snip]...


By forcing the temporary table in effect you are saying before you merge join hash/match whatever, that creating a single set of index seeks to produce a subset of records to join in a query can be more performant than a repeating series of clustered or even covering non-clustered index seeks each time the subset is referenced. Even though the number of index pages scanned is logarithmically related to the cardinality of the table it can still be non-trivial for large tables. Copying a small amount of data into a new table at some point is more efficient that certain amount of index seeks.

Or in other words I know that there are a couple of (generally key always indexed) columns in this table and I know that if I restrict my working table to these before I start evaluating the rest of the query I can eliminate a whole pile of index page reads because the smaller number of rows I am interested in can be pre-fetched and much easier to index and join over as it is a much smaller range than via the index on the much large entire table.

But the converse where the predicate is not a good indicator of selectivity (ie accounts < 100,000) the pre restriction wont help because the subset wont be much smaller than the original table (if indeed there are 110,000 accounts) and creating an indexed temporary table will be a waste of effort. For a given database design,row cardinality and list of selection predicates you can tell whether this method is going to be a benefit or not.

Its forcing the data access design back into procedural programming (well 2 steps) away from a logical set definition of SQL (ie desired result and leaving the determination of the best method for evaluation to the query optimiser).

The fact that this in practice this is necessary is a pity but I guess until you get large sets it gives no benefit and a DB never really goes back to being smaller. Whether this method is better is really a property of how selective a predicate is on an index eg: account = 123456789 on the product_sales table.
Post #509623
Posted Monday, June 2, 2008 4:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
Nice article......


Post #509740
Posted Thursday, June 5, 2008 9:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
one of our analysts sent me a bunch of queries they run daily for some reports. each one is a select into a temp table, then another select into another temp table and joining data from the previous temp table and other tables.

didn't feel like writing a crazy query with a bunch of where statements so i took the lazy way out. i created views for each of the temp tables. and running a select from a view calls the 2 other views automatically.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #512217
Posted Friday, June 6, 2008 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 7,056, Visits: 6,818
Out of the blue... off the cuff remark...

Wouldn't it be nice to create a temp table and be able to specify the database in which is created :D



Far away is close at hand in the images of elsewhere.

Anon.

Post #512870
Posted Friday, June 6, 2008 7:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
what would be the point?

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #512895
Posted Friday, June 6, 2008 7:50 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Might be good if you had multiple filegroups and multiple temp tables you were gonna join?;)
Post #512898
Posted Friday, June 6, 2008 7:53 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: Monday, July 22, 2013 11:59 AM
Points: 509, Visits: 719
CREATE TABLE [temporaryTable]...

LOL


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #512905
Posted Friday, June 6, 2008 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 7,056, Visits: 6,818
Control disk space and where it is used
Reduce interference from other apps/databases ie locks
Minimise effect of rogue apps/databases filling tempdb

I did state ir was off the cuff.... didn't say it was a perfect idea



Far away is close at hand in the images of elsewhere.

Anon.

Post #512911
Posted Friday, June 6, 2008 7:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
you can always put tempdb on multiple spindles using multiple files

I think MS did it this way because it would be a nightmare if apps created temp tables in user databases that couldn't be deleted until the next restart. this way all the junk is in the one database you rarely look into


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #512915
« Prev Topic | Next Topic »

Add to briefcase «««678910»»

Permissions Expand / Collapse