October 3, 2011 at 9:28 am
Lynn Pettis (10/3/2011)
SanDroid (10/3/2011)
Nice question. I heard this was supposed to change in 2008.Not suprised it did not.
Becuase of this we always remove table variables from any code we get.
Table Variables have a use and need in some functions, but in a stored procedure they have little to no use.
I'll agree to disagree. I think it really depends on what they are being used for in the stored procedure. If you are trying to capture information using the OUTPUT clause of an INSERT/UPDATE/DELETE/MERGE operation regardless of a commit or rollback of the operation, then you do need the table variables.
There may also be other reasons, but it really comes down to testing, testing, and more testing.
I have to agree on the test and more testing part. I have yet to find a test case where table variables are faster on average.
I am sure there are times when using a table varible is needed.
Does anyone know what the difference would be between using a WITH statement and a TEMP table?
October 3, 2011 at 9:37 am
Nice question. We rarely use table variables so I am always interested in questions regarding performance using them versus using a temp table. Personally I have seen few instances where the table variable outperforms the temp table. As others mentioned though, they do have their place and testing is the best way to tell if you should use one or not.
October 3, 2011 at 9:41 am
Thanks for the question.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
October 3, 2011 at 9:54 am
tks for the question. I ran into a similar issue to that reported on the reference article when migrating code from 2000 to 2005/2008. :crazy:
agree with you Lynn -context is an important consideration before we declare table variables dead. 😉
October 3, 2011 at 9:58 am
abhiraami.kasilakshmi (10/3/2011)
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...
Table variables being stored only in memory is a myth. Check out the link posted already for Gail Shaws article debunking that myth.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2011 at 10:02 am
SanDroid (10/3/2011)
Lynn Pettis (10/3/2011)
SanDroid (10/3/2011)
Nice question. I heard this was supposed to change in 2008.Not suprised it did not.
Becuase of this we always remove table variables from any code we get.
Table Variables have a use and need in some functions, but in a stored procedure they have little to no use.
I'll agree to disagree. I think it really depends on what they are being used for in the stored procedure. If you are trying to capture information using the OUTPUT clause of an INSERT/UPDATE/DELETE/MERGE operation regardless of a commit or rollback of the operation, then you do need the table variables.
There may also be other reasons, but it really comes down to testing, testing, and more testing.
I have to agree on the test and more testing part. I have yet to find a test case where table variables are faster on average.
I am sure there are times when using a table varible is needed.
Does anyone know what the difference would be between using a WITH statement and a TEMP table?
Do you mean as in using a CTE or subquery?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2011 at 10:03 am
Thanks for the question Ron.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 3, 2011 at 10:07 am
That was a great question!
Got it wrong, as usual.
However, I think the reason behind the slowness of the table variable is the poor cardinality estimation more than the recompile itself. Had it been recompiled to an accurate plan, it would have performed just as fast as the temp table.
-- Gianluca Sartori
October 3, 2011 at 10:16 am
Lynn Pettis (10/3/2011)
... There may also be other reasons, but it really comes down to testing, testing, and more testing.
I agree with you!!!!!
October 3, 2011 at 11:38 am
The explanation:
The reason is that the table variable causes a recompilation.
...is entirely backwards, as reading the explanatory link shows. An unfortunate typo, perhaps?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 3, 2011 at 11:48 am
Thomas Abraham (10/3/2011)
So, now I'm a bit confused. Just when am I supposed to use table variables?
Whenever they suit the requirement better than a temporary table 🙂
Wayne Sheffield wrote an excellent SSC article comparing the two: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
The skilled database professional is aware of the benefits of each type of temporary structure, and uses each as appropriate. Anyone who says one is always better than the other is wrong.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 3, 2011 at 12:25 pm
SQL Kiwi (10/3/2011)
Thomas Abraham (10/3/2011)
So, now I'm a bit confused. Just when am I supposed to use table variables?Whenever they suit the requirement better than a temporary table 🙂
Wayne Sheffield wrote an excellent SSC article comparing the two: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
The skilled database professional is aware of the benefits of each type of temporary structure, and uses each as appropriate. Anyone who says one is always better than the other is wrong.
From the source referenced above
So, what should I use?
Microsoft recommends using table variables (in Reference 4). If the number of rows that you are going to be inserting into the table is very small, then use a table variable. Most of the “internet gurus” will tell you to have less than 100 records as a guideline, as this is where the lack of statistics start to mess up a query – but they will also tell you to test your specific needs against both methods. Some people will only use table variables within user-defined table functions (which require them). If you can use an index from either a PRIMARY KEY or UNIQUE constraint on a table variable, then you could get excellent performance from table variables that contain tens of thousands of records. This is primarily true if you don’t have to join the table variable to another table. When you start joining the table variable to other tables, then the cardinality error frequently results in a bad execution plan, which gets worse the more complex the query is, and the larger the row count is.
That's about right for a "rule of thumb" - one that I will, of course, not assume is always correct. Thanks for the reference!
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
October 3, 2011 at 12:28 pm
Fantastic QOD. Thank you!
For ease of reading here are what I considered the salient points in the MSDN posting. First, the important difference between table variables and temp tables:
While the “Estimated” and “Actual” query plans are same in most cases, there are some exception. If a table (temporary or permanent) is created in the same batch with the query, SQL Serve has to recompile the query since the query definition is unknown when the batch is compiled the first time. This is not the case for table variables (and that was the main reasons SQL Server 2005 introduce them – to reduce recompilations when unnecessary).
Then, why this makes a difference in the sample situation:
You will see that the actual plan for script 1 is the same as estimated plan and took substantially longer than the script 2 to complete. The actual execution plan for Script 2 is very different from its estimated plan. The actual execution plan for script 2 uses Hash Match to execute the Anti Semi Join at the top of the plan. There is a table scan of the temporary table #Tmp1 at the top of the plan building the hash table. If you examine the properties of this scan you will find out that the number of Estimated rows is 20000 (was 1 in the Estimated plan as well as it is 1 for the table variable plan).
Finally, the bottom-line advice, which is that generally temp tables are preferred to table variables, but which I also interpret as pretty much what some others have said here as "look at the ACTUAL execution plan and TEST, TEST, TEST":
When examining query plans be careful to consider possible plan change during the batch execution due to recompiles. Bear in mind that while table variables are not causing recompiles, you may need just the opposite to get the best query plan. You should always use temporary tables in your queries unless you intentionally want to avoid query recompilation and you are confident query variable will give you a good plan. Introduction of statement level recompilation in SQL Server 2005 substantially lowered the need to use table variables in your queries.
October 3, 2011 at 12:29 pm
SQLRNNR (10/3/2011)
SanDroid (10/3/2011)
......Does anyone know what the difference would be between using a WITH statement and a TEMP table?
Do you mean as in using a CTE or subquery?
Sorry... I mean using a CTE statement...
October 3, 2011 at 1:02 pm
stewartc-708166 (10/3/2011)
Interesting question, Ron.Thanks
Yes, it's a good and interesting question
Something to bear in mind: There are numerous factors that influence the choice between table variables and/or temp tables, inter alia:
> Data set size (number of columns and rows) - larger datasets work better with temp tables (mainly because these can be indexed);
> Availalbe memory vs available drive space (file swapping etc);
> IO's;
> Persistence (using the data set in other stored procedures)
> UDF - one can only use table variables in UDF's
If this is the sort of message that people take home from this question, that's extremely bad news, because two of the five points are incorrect and a third may be misleading:
1)Both temporary tables and table variables can be indexed.
2)Memory and drive space usage are the temp table and the table variable live in tempdb
3)The point about more IOs is correct because the creation of the temp table and modifications to it have to be logged (to allow for rollback), but all non-log IO is identical for a temp table and for a table variable.
The other two points are indeed valid.
However, the question is trying to show people that the forced recompilation when a temp table is used can make a difference - and if people take that lesson away it will be good news.
However, I do feel that writing it as one batch with instructions to run it as three batches buried in comments so that the apparent duplicate variable declaration is not in fact a duplicate and won't cause a problem makes it to some extent a trick question (it didn't catch me, but I read comments, probably more carefully that I read code).
Another lesson that people could perhaps take away if they thought about how slow these queries are going to be as written is that they should avoid having tables and table variables with no primary key and avoid using heaps (whether for tables or for table variables) when there is a clearly useful cluster structure. Changing the declarations to take account of those simple best practise ground rules - so that "(ID int)" becomes "(ID int primary key clustered)" in all three declarations - will speed up query 1 quite a bit, and result in query 2 and query 1 each taking about the same time (the query with the table variable may be faster that the one with the temp table if the saving on logging is significant; I guess there will be something like a 5 or 10 times speed up on query 1 and maybe a couple of thousand times speed up on query 2, but it's likely to be somewhat hardware dependent). Oh, the wonders of using sensible indexing and clustering!
Tom
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply