October 3, 2009 at 8:28 pm
Paul White (10/3/2009)
Elliott W (9/23/2009)
As far as logical reads of a table variable.. Keep in mind that if they are not too big they will probably reside fully in memory and the reads are much cheaper...There's really no difference between the two in this regard.
I think there are a lot of factors.. Size being a major one. If it can stay in memory then it won't have to do a physical read which will be cheaper.
CEWII
October 4, 2009 at 12:43 am
Elliott W (10/3/2009)
Paul White (10/3/2009)
Elliott W (9/23/2009)
As far as logical reads of a table variable.. Keep in mind that if they are not too big they will probably reside fully in memory and the reads are much cheaper...There's really no difference between the two in this regard.
I think there are a lot of factors.. Size being a major one. If it can stay in memory then it won't have to do a physical read which will be cheaper.
CEWII
Paul's point is that there's no difference between Table Variables and Temporary Tables in this respect. There is no circumstance where a table variable would stay in memory when a similarly designed temporary table would not.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 4, 2009 at 8:56 am
I thought I was agreeing..
But I was also making a larger point about there being lots of factors that controls how it will perform..
As the table grows is less obvious which is the better performer..
CEWII
October 4, 2009 at 11:19 am
Elliott W (10/4/2009)
...As the table grows is less obvious which is the better performer..
No, as the table grows, it becomes clearer and clearer that temporary tables outperform table variables. In fact the only normal(*) case where table variables outperform temporary tables on SQL Server 2005 & higher is for very small tables and that is only because table variables have slightly less create/drop overhead, but this is quickly lost in the noise as the number of rows grows.
The reason why temp tables win in almost all other cases is because they have statistics, just like a regular table (this is also one of the places that that tiny bit of extra overhead is coming from), but table variables do not have statistics. That means that the optimizer always sees table variables as having exactly one (1) row and so almost always puts them in nested loops, and on the "small table" side, plus when joining other temp tables, it frequently puts filtering, etc. after the join that would normally go before (because 1*1=1 is less than 1+1=2). In short, it makes poor query plans.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 4, 2009 at 11:53 am
RBarryYoung (10/4/2009)
Elliott W (10/4/2009)
...As the table grows is less obvious which is the better performer..
No, as the table grows, it becomes clearer and clearer that temporary tables outperform table variables. In fact the only normal(*) case where table variables outperform temporary tables on SQL Server 2005 & higher is for very small tables and that is only because table variables have slightly less create/drop overhead, but this is quickly lost in the noise as the number of rows grows.
The reason why temp tables win in almost all other cases is because they have statistics, just like a regular table (this is also one of the places that that tiny bit of extra overhead is coming from), but table variables do not have statistics. That means that the optimizer always sees table variables as having exactly one (1) row and so almost always puts them in nested loops, and on the "small table" side, plus when joining other temp tables, it frequently puts filtering, etc. after the join that would normally go before (because 1*1=1 is less than 1+1=2). In short, it makes poor query plans.
Barry,
I have to disagree. I have personal experience indicating in a particular case that I got better performance with table variables. This was found through testing of both methods. Now, I admit that this was a particular case. But in the vase majority of cases you are right..
CEWII
October 4, 2009 at 1:38 pm
Elliott W (10/4/2009)
RBarryYoung (10/4/2009)
Elliott W (10/4/2009)
...As the table grows is less obvious which is the better performer..
No, as the table grows, it becomes clearer and clearer that temporary tables outperform table variables. In fact the only normal(*) case where table variables outperform temporary tables on SQL Server 2005 & higher is for very small tables and that is only because table variables have slightly less create/drop overhead, but this is quickly lost in the noise as the number of rows grows.
The reason why temp tables win in almost all other cases is because they have statistics, just like a regular table (this is also one of the places that that tiny bit of extra overhead is coming from), but table variables do not have statistics. That means that the optimizer always sees table variables as having exactly one (1) row and so almost always puts them in nested loops, and on the "small table" side, plus when joining other temp tables, it frequently puts filtering, etc. after the join that would normally go before (because 1*1=1 is less than 1+1=2). In short, it makes poor query plans.
Barry,
I have to disagree. I have personal experience indicating in a particular case that I got better performance with table variables. This was found through testing of both methods. Now, I admit that this was a particular case. But in the vase majority of cases you are right..
CEWII
There's a reason that I left that qualification above(*) in. I have seen cases where table variables outperformed temporary tables even when a significant number of rows was involved, but they all had the same thing in common. In each of these cases, the presence of better information(statistics) from temp tables was actually causing the optimizer to pick *worse* plans than it did with less information. Now it certainly happens that sometimes SQL Server does not pick a very good plan (maybe 5-15% of the time?), and in some of those cases, it would pick a better plan if it actually knew less (maybe half of those cases?), but it's definitely unusual, and best treated as an exception to the rule.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 4, 2009 at 4:08 pm
Thanks Barry! You made the points exactly as I intended - apologies for the brief comment initially, but I don't have a lot of time at the moment.
There are indeed cases where a lack of statistics produces a better plan, and table variables can be extremely useful in those scenarios. Sometimes it is possible to achieve a comparable plan through careful use of hinting and row-goals, but often these solutions are more fragile and complex than the table variable equivalent.
My personal preference is for 'real' temporary tables by default, but there certainly are times when table variables make better sense.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 5, 2009 at 6:05 am
I agree with Barry and Paul on this one. The exceptions that I've seen are usually where, usually due to badly written code or poorly designed structures, the only possible operation against the temporary object is a scan. When it's a scan only, the overhead of creating statistics and the accompanying statement recompiles make the table variable faster, regardless of size. But note, the "faster" in this situation is just a matter of degree and it's usually completely negated when the query or structure are rebuilt appropriately.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2009 at 4:27 am
Hi friends
Thanks for very informative article..
But missing one thing if i am not wrong
want to add one thing
we can not use table variable in the
update table from @tablevariable
and for difference
We can use tablevariable inside functions but not the temporary tables.
Regards
Nitin
December 5, 2009 at 1:20 am
Nitin.S.Pathak (12/1/2009)
we can not use table variable in the update table from @tablevariable
Alias the table variable.
Nitin.S.Pathak (12/1/2009)
We can use table variable inside functions but not the temporary tables.
Correct.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 8, 2010 at 10:30 pm
Nice to see this article come around again. Great stuff.
April 8, 2010 at 10:48 pm
Tim Walker. (6/10/2009)
Excellent article. Very comprehensive about the theory!
I agree 🙂
Question: (and I wouldn't have even asked it if I hadn't read this first) is what is the point in defining a unique constraint on a table variable? Won't the 'optimized assuming one row in a table variable' render this unnecessary because the optimizer will consider the value in the column is going to be unique anyway?
There's an important difference between the optimizer estimating one row, and knowing that any rows produced will be unique...there are a number of important optimizations that are only available if uniqueness is guaranteed.
Comment: I've noticed that when more than one multi-row table variable is involved in a query the optimizer tends to table scan one and repeatedly table scan the other. This can get really expensive in CPU and is best avoided.
Although table variables do not support statistics, you can get a better plan at runtime if you include OPTION (RECOMPILE) - since the number of rows in the table variable (its cardinality) is then available to the optimizer. You may find a better plan is produced with this hint.
edit: quote tags...:(
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 9, 2010 at 3:19 am
Outstanding article, Wayne. I must have missed it when it was first published. Nice job.
Your article raised a doubt in my mind. You write
Temporary tables can have named constraints, indexes, etc. However, if two users are running the same procedure at the same time, the second one will get an error: “There is already an object named ‘<objectname>’ in the database”. You will need to build the object without a name, thus creating a system-assigned name which will be unique in the tempdb database.
I tried creating temp tables with named constraints in two different sessions and the duplicate object name error appears, but the same doesn't apply to indexes.
Am I missing something?
-- Gianluca Sartori
April 9, 2010 at 3:44 am
Gianluca Sartori (4/9/2010)
Outstanding article, Wayne. I must have missed it when it was first published. Nice job.Your article raised a doubt in my mind. You write
Temporary tables can have named constraints, indexes, etc. However, if two users are running the same procedure at the same time, the second one will get an error: “There is already an object named ‘<objectname>’ in the database”. You will need to build the object without a name, thus creating a system-assigned name which will be unique in the tempdb database.
I tried creating temp tables with named constraints in two different sessions and the duplicate object name error appears, but the same doesn't apply to indexes.
Am I missing something?
In BOL, under CREATE INDEX:
indexname : Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.
When dealing with the constraints, they must be unique within the database. That's why (when working with temporary tables), if possible, I create them un-named as part of the CREATE TABLE statement - the system will generate a unique name for them.
And as I finish writing this, I see your point. The first sentence of mine that you quoted does apply to indexes; the last two sentences do not.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 9, 2010 at 4:11 am
Thanks for clarifying Wayne. So, it works the same as permanent tables: I was fearing that a different behaviour could apply.
I wrote that because I use indexes on temp tables in some of my procedures ad I've never seen problems in concurrency, but that sentence in your article made me think.
-- Gianluca Sartori
Viewing 15 posts - 76 through 90 (of 163 total)
You must be logged in to reply to this topic. Login to reply