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

Table variable vs temp table Expand / Collapse
Author
Message
Posted Monday, October 3, 2011 3:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:29 AM
Points: 2,158, Visits: 106
Very interesting question
In my case on SQL 2008 R2 i receive follow result:
- table variable - 29290 ms
- temp table - 240 ms
Post #1184361
Posted Monday, October 3, 2011 3:11 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:29 AM
Points: 1,484, Visits: 1,965
dgvozdetsky (10/3/2011)
Very interesting question
In my case on SQL 2008 R2 i receive follow result:
- table variable - 29290 ms
- temp table - 240 ms


Cool. The comment block in the start of the question have both 2005 and 2008 reference so didnt really know if that would effect anything. And since i dont have a 2008 (let alone an R2). Hard to test.

I gave it a try on 2000 and that was faster for me (like 70ms +/- 10ms). Strange effect on 2008 i must say.

/T
Post #1184366
Posted Monday, October 3, 2011 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 3, 2011 3:20 AM
Points: 23, Visits: 3
The site which i referred regarding this says that table variables are stored only in memory. so the access time for a table variable can be faster than the time it takes to access a temporary table.

Note: http://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

but when i tried to execute the query it results both as same....

So which one is the correct answer...
Post #1184375
Posted Monday, October 3, 2011 3:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:29 AM
Points: 2,158, Visits: 106
tommyh (10/3/2011) Strange effect on 2008 i must say.

/T

As known, Microsoft was changed work with tempDB since SQL 2005, so it is possible, that time dimensions in SQL 2000 differ with time dimensions in SQL 2k5, 2k8, 2k11 etc.

Excuse my bad english
Post #1184378
Posted Monday, October 3, 2011 5:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Missed it. Thanks for the question.

http://brittcluff.blogspot.com/
Post #1184430
Posted Monday, October 3, 2011 6:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:43 AM
Points: 1,925, Visits: 2,205
I got it wrong, so I tested it on my own system. (2008 R2 - 2005 not available)

Results for table variable, in ms: 80800, 80203, & 79523

Results for temp table, in ms: 463, 450, 470

So, now I'm a bit confused. Just when am I supposed to use table variables??


Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1184456
Posted Monday, October 3, 2011 6:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:43 AM
Points: 1,925, Visits: 2,205
Oops, where are my manners. Thank you sir, may I please have another? (Question, that is.)

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1184458
Posted Monday, October 3, 2011 7:05 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 636, Visits: 801
The Inserts both execute at about the same speed. It is the "Select where not in" statement that is considerably slower with the table variable. So I think the point of the question was not how fast the insert runs but how fast the select runs.

Tony
------------------------------------
Are you suggesting coconuts migrate?
Post #1184499
Posted Monday, October 3, 2011 7:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
You guys really need to learn english. There was rarely, if ever, a better written question.


It clearly stated to execute 3 batches separately with begin / end points in the code.



I just would like to add to the short explaination that the recompile speeds up the query because the row estimation is better which gives a different & better plan to execute.

Table variables estimate to 1 row which is way wrong in this case and causes the "bad" plan.
Post #1184509
Posted Monday, October 3, 2011 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
yogesh.balasubramanian (10/3/2011)
I see a relavent article and it seems like table variable is much faster than temp table.
Ref: http://sqlserverperformance.idera.com/uncategorized/performance-comparison-temp-tables-table-variables-sql-server/

Confused on which one is better?


One item that the question hoped to illustrate is that like most things in SQL SERVER (T-SQL) "IT DEPENDS"

For example - additional testing using SQL Server 2008
                     Processing Time (in milliseconds)
#Rows Table Variable Temp Table
20,000 68,773 386
2,000 633 70
200 33 23
20 16 20

Add in further modifications to the Temp Table, such as an index, and the results change again. So in the real world, do not depend upon
assumptions, or what others say is the best method. Test YOUR solution and test it again and again to insure it is the better solution before placing it in a production DB


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1184513
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse