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 03, 2011 3:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 4:00 AM
Points: 1,969, Visits: 98
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 03, 2011 3:11 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
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 03, 2011 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 03, 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 03, 2011 3:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 4:00 AM
Points: 1,969, Visits: 98
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 03, 2011 5:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
Missed it. Thanks for the question.

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 1,625, Visits: 2,034
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 03, 2011 6:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 1,625, Visits: 2,034
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 03, 2011 7:05 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: Friday, April 11, 2014 7:54 AM
Points: 519, Visits: 678
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 03, 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, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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 03, 2011 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 5,472, Visits: 23,534
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