#Temp Tables vs. Table Variables 4

[WoodyGuthrie]

I apologize in advance if I'm stepping back too far. Looks like it's been a while for this topic: Temp Tables or Table Variables...

I have a stored procedure that uses 6 tables that are each a unique subset of a master table. The master table is about 600K rows and each of the other tables has between 300K and 500K rows.

The query joins each table to the master table. While each table is a relatively large percentage of the master table, each is only a single column and a relatively low percentage of the rows are used at any given run. The tables are TRUNCATEd every evening and then updated with with a selection from the master table to re-populate each subset.

The obvious advantage for me would be a query that could potentially scan 6 tables of 300-500K rows each or 6 temp tables/variables of 10-20K rows each depending on the date range selected.

...Change the stored procedure to to eliminate these tables and replace them with temp tables or table variables or... leave it as is?

There are no udf's so that's not an issue.

Clean up is really not a problem with either method, however, I do wonder how SQL Server handles session uniqueness for #TempTables..??

According to BOL..: table variables used in stored procedures cause fewer recompilations of stored procedures than with temp tables. Also BOL : Table variables require less locking because transactions last only for the duration of an update to the variable.---logging overhead??

I know temporary tables support indexes which can help. As an interim measure I've created indexes over the 6 tables that are currently in place and that greatly improved performance.

Thanks for your input.

 

[gmmastros]

>> I do wonder how SQL Server handles session uniqueness for #TempTables..??

This is not a problem. Each session can have the same temp table. Meaning... each session could create a temp table, have different data in it, and it wouldn't affect the temp table of another session.

Logging overhead? Yeah. Using real tables, like you are currently doing puts a load on your transaction log. Temp tables are written to the TempDB, so there are transaction log implication there as well. Table variables, on the other hand, are stored completely in memory so there are no transactions that get logged.

>> I know temporary tables support indexes which can help. As an interim measure I've created indexes over the 6 tables that are currently in place and that greatly improved performance.

This is true. You can put indexes on real tables AND temp tables. Most people don't realize it, but you can also put an index on a table variable. Unfortunately, you can only have one index and, even worse, it can only be implemented through a primary key constraint. Depending on your query, this may be good enough.

In my experience, table variables almost always outperform temp tables. This is not always true, so some experimentation may be necessary.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I also suggest you read this: faq183-1100

Not covered in the FAQ's is how to create an index/primary key on the table.

It's like this....

[tt][blue]
Declare @TableVariable
Table (Col1 Int,
Col2 Int,
Col3 varchar(20)
[!]Primary key (Col1,Col2)[/!])
[/blue][/tt]

Or, if you have just one column as the primary key...
[tt][blue]
Declare @TableVariable
Table (Col1 Int [!]Primary Key[/!],
Col2 Int,
Col3 varchar(20) )
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WoodyGuthrie]

Thanks George. Good Stuff.

I wondered about the specifics of the temp table session uniqueness ... I know table variables are unique via the sessionID. I wonder if temp table structure includes the sessionID in the TempDB or exactly how SQL Server handles it? No big deal...just curious.

 

[mrdenny]

Temp tables are not stored completely in memory. They are stored partially in memory, and partially in tempdb. This article explains more.

http://searchsqlserver.techtarget.c...get.com/tip/0,289483,sid87_gci1267047,00.html

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[gmmastros]

mrdenny,

Thank link was... uh... weird. I think this one works a little better.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1267047,00.html

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

mrdenny,

Thank you for the link. I learned something valuable from it. Can you please elaborate on...

Table variables are partially stored on disk and partially stored in memory.

I always assumed that table variables were completely stored in memory unless you exceeded the physical memory of the server, in which case the windows swap file would come in to play. Somehow, I suspect this is NOT what you were referring to. Can you please clarify this for me?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

See new post Table Variables Use Tempdb.

 

[mrdenny]

Sorry, My google bookmark has a bunch of referring info in it for some reason. Must have been how I got to the tip that day.

Something else to fix. Goodie.

I always assumed that table variables were completely stored in memory unless you exceeded the physical memory of the server, in which case the windows swap file would come in to play.

That was what I thought as well until I started digging into table variables for the article.

Fire up an instance that isn't used for anything. Create a table variable, load a little data into it and check the space usage of tempdb. You'll see that the amount of space that data is taking has increased. Add more records and you'll see that the amount of space will increase again. End that session and you'll see the amount of space drop.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog