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

Comparing Table Variables with Temporary Tables Expand / Collapse
Author
Message
Posted Friday, June 8, 2012 3:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
Thanks to Wayne for an awesome article! And thanks others for great discussion.

There's one point that no one picked up that I find inaccurate:

Note that the estimated execution plan for both table variables and temporary tables will show 1 row;


Agreed, the cardinality for the table variable is 1 row (without recompilation) but the temporary table??

Execute:

select 1 id into #obs
union select 2
union select 3
union select 4
union select 5
go

and then examine the estimated execution plan for:

select * from #obs;
go

The full table scan of #obs has cardinality 5, not 1, even before the query is executed and the plan cached. Given that statistics are created for temporary tables this is exactly what I'd expect so am confused why you would suggest otherwise. What have I missed?
Post #1313005
Posted Friday, June 8, 2012 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 7,125, Visits: 12,722
Very well done. I especially like the comparison table you produced. I have plans to refer some developers to read this article as the misuse of Table Variables is rampant in the current shop.

In the article:

Microsoft recommends using table variables (in Reference 4).


What would be unfortunate is if the initial adoption of such practices were due to a poorly worded recommendation from Microsoft so I would like to read their actual recommendation. I visited Reference 4 as shown as T-SQL BOL (SQL 2008), CREATE TABLE however I must be missing it. I would like to have my bases covered before I bring the issue for discussion with the group. Can you point me to the recommendation made by Microsoft, or restate it here?

Thanks again for a great read.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1313066
Posted Friday, June 8, 2012 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 4:23 PM
Points: 2, Visits: 93
I wish I could rate it more than 5 stars. Excellent article. Concise and precise.
Post #1313266
Posted Friday, June 8, 2012 9:14 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 25, 2012 7:39 AM
Points: 1,682, Visits: 210
Really good article.
I will read it more in details.
Thanks Wayne!
Post #1313464
Posted Friday, June 8, 2012 9:40 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:17 PM
Points: 197, Visits: 459
Very good article, thanks.

And apparently I wasn't the only one who must have missed it the first time around, so it is also a good illustration of the value of resurfacing older articles.
Post #1313465
Posted Saturday, June 9, 2012 8:06 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
I know it's a reprint but I have to say it again... I consider this to be the definitive article on the differences between Table Variables and Temp Tables. It has opened the eyes of several people that I know, especially about the myth that Table Variables are "memory only" and that Temp Tables are "disk only".

Very well done, Wayne.


--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 #1313488
Posted Sunday, June 10, 2012 1:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 360, Visits: 720
Superb article. Well worth the read.
Post #1313616
Posted Monday, June 11, 2012 11:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 5,364, Visits: 8,949
opc.three (6/8/2012)
Very well done. I especially like the comparison table you produced. I have plans to refer some developers to read this article as the misuse of Table Variables is rampant in the current shop.

In the article:

Microsoft recommends using table variables (in Reference 4).


What would be unfortunate is if the initial adoption of such practices were due to a poorly worded recommendation from Microsoft so I would like to read their actual recommendation. I visited Reference 4 as shown as T-SQL BOL (SQL 2008), CREATE TABLE however I must be missing it. I would like to have my bases covered before I bring the issue for discussion with the group. Can you point me to the recommendation made by Microsoft, or restate it here?

Thanks again for a great read.


The way that MS does their SQL BOL links, that link now points to the SQL 2012 BOL entry. Check out the SQL 2008 (not R2) link at: http://msdn.microsoft.com/en-us/library/ms174979%28v=sql.100%29.aspx - it's in the "Temporary Tables" section, and it has been removed from the SQL 2008R2 / 2012 sections.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1313973
Posted Monday, June 11, 2012 11:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 5,364, Visits: 8,949
sparky-407434 (6/8/2012)
I had been using temp tables in a large import process and made the change to table variables and saw a big improvement in performance. However, the server was later moved to a virtual server (VMWare) and the processes ran much slower (x40 slower). I reverted back to temp tables and the process improved again (though not to the original speed on physical server using table variables)
I think we need more discussion when looking at how different methods interact with disk / memory, as to how they compare across both physical and virtual environments. In my case what is good for physical was bad for virtual and vice versa. Or perhaps there are things which the VM administrators need to do to overcome this problem.


And this highlights one very important thing... always test in your environment. Your results may be different, and may change over time. Thanks for bringing this up Sparky!


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1313982
Posted Monday, June 11, 2012 12:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 5,364, Visits: 8,949
Andrew Diniz (6/8/2012)
Thanks to Wayne for an awesome article! And thanks others for great discussion.

There's one point that no one picked up that I find inaccurate:

Note that the estimated execution plan for both table variables and temporary tables will show 1 row;


Agreed, the cardinality for the table variable is 1 row (without recompilation) but the temporary table??

Execute:

select 1 id into #obs
union select 2
union select 3
union select 4
union select 5
go

and then examine the estimated execution plan for:

select * from #obs;
go

The full table scan of #obs has cardinality 5, not 1, even before the query is executed and the plan cached. Given that statistics are created for temporary tables this is exactly what I'd expect so am confused why you would suggest otherwise. What have I missed?


Andrew, I do believe that you are right... you found something that was missed the first time around. I'll have to look into this tonight and respond.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1313984
« Prev Topic | Next Topic »

Add to briefcase «««1314151617»»

Permissions Expand / Collapse