Are Table Variables a bad thing?
I would very much like the experts advice on the subject of Table Variables and Temporary tables. What should be the recommendation on their use?
I wrote an article on Simple-Talk recently called Temporary Tables in SQL Server in which I said in the conclusion *"I'm always wary of providing over-generalized advice, but I always prefer my databases to use Table Variables, and TVPs wherever possible, They require less resource, and you're less likely to hold onto them when you're finished with them. I like to use them to the max, with column and table checks and constraints. You may find times when they run out of steam, especially when table sizes get larger. In cases like this, or where it isn't practical to use table variables because of their restricted scope, then I'll use local temporary tables.*"
In answer to a comment, I said "*I usually do the development work on a local table (#) and get a set of timings using a good hunk of data. I then change to table variables (@), being careful to put in constraints on the rows I use to join.I then run the tests again to make sure performance is the same. It usually is. If I hit a performance problem, I'll try option (recompile), on the assumption that it can't generate a good query plan. If I still can't get equivalent performance then I'll go back to using a local temporary table (#)"*
Kevin Boles, who knows a great deal on the subject, has commented at the end of the article *"Sorry Phil, but your advice to use table vars over real temp tables is completely contrary to proper guidance. There are optimization limitations (especially the lack of statistics) that very frequently lead to horrible query plans. A SINGLE ROW table variable can get you a suboptimal plan compared to the same query using a temp table, so the very common "use table vars if you have less than NNN rows" advice is flawed. I also don't understand your statement about resource differences between the two.*
*There are 2 reasons where table vars are appropriate and they are VERY infrequently found in the wild: 1) extremely high-call-frequency code where recompilations from temp table activity are a problem a 2) audit scenarios where you need to keep information after a transaction rollback has occurred.*
*I will also take exception to the advice to use constraints, checks, indexes, etc. In 15+ years of relational engine consulting I have eliminated those things (especially indexes) from temp tables to improve performance at least an order of magnitude more frequently than I have added them.'*
I'm puzzled that I'm getting strong advice from more than one MVPs whose advice I respect, that one should not use such an important part of SQL Server as table variables and TVPs. I'm aware of their limitations (I covered them in the article) but my feeling is that the compromises made by Microsoft SQL Server Database Engine team in designing TVs and TVPs have been sensible for the general usage cases of table variables. Lack of Parallelism, for example, isn't necessarily a disaster for performance, for smallish tables surely? I'd always felt that if the database developer was aware of the risks and issues, then it was reasonable for them to judge when and where in their work it is better to use a Table Variable rather than a temporary table.
So. What do you think? Obviously, if I've got it wrong, I'll get the article amended, as I'd hate to think of anyone being misled by my advice!
I'm curious about the removing indexes from temp tables, for me, on larger temp tables indexes have saved the day but usually I write once and read many from these tables.
I too have found them to be extremely problematic. The issue comes down to the optimizer and how it treats them and the results of that treatment. The optimizer always assumes a single row. That's fairly well known and I think you mentioned it in the article. But, the question is, what happens when there are two or more rows? Generally, my advice has always been, if it's less than 100 rows (about, give or take, which direction is the window blowing), then you're probably OK to use TV. But, it's that word, "use" that digs the hole. Generally, I use them as temporary place holder and I avoid any actions on them other than insert & select if I can at all help it. As soon as you JOIN on them... things break down very quickly. I've recently seen execution plans that had 3-5 rows in a TV in a LEFT JOIN that produced horror show execution plans and substituting temp tables fixed the issue.
It's nuts, but I actively attempt to avoid their use unless I'm forced into it or I can limit it so that their advantages (of which there are a couple) come to the fore without their disadvantages (of which there are many) overwhelm them.
Grant, please refer to the original thread on Simple-Talk to see an example where a single row in a table variable gets you a horribly bad plan due to data distribution skew and the lack of statistics available to table variables. This goes beyond just the usual (and misguided) mantra of 'less than NNN rows use table variables'.
True, but even that mantra, when chanted appropriately, had a long list of caveats. I don't want to through the baby out with the bath water, despite the fact that the baby has a third eye & flipper hands. Table variables have their uses. But it has to be with careful application and a full understanding of what they do.
I suspect that the more we can understand under what conditions the query optimizer gridlocks on a bad strategy, the more explicit we can make advice to developers. I'm puzzled that I've used TVs a great deal over the years, with only rare problems where I've had to revert to a temp table. I've certainly come across some horror table variables but usually there are other design problems with the code. I wonder if some basic assumptions are being made about the good normalization of the data by the good people at Microsoft doing the Query Optimiser.
Similar to Kevin and Grant, I also have seen very poor performance from table vars, and just simply changing to a temp table usually fixes the issue. In fact, in was when I was investigating the reasons behind this that gave me the question "What is so different between a temp table and a table var to cause this kind of performance issue?", which led me to creating my Comparing Table Vars and Temp Tables article and subsequently create a presentation off of that which I now present at a lot of SQLSaturday events. While I haven't seen an issue at one row like Kevin has, I have seen a query that joined to a table var with only 48 rows (and appropriately indexed) return the results in over 3 minutes... just changing to a temp table returns results in under 10ms. It's been my experience that as soon as you either join a table var to any other table, or apply a where clause to a column in a table var, that performance takes a deep dive. Subsequently, if I'm doing a join or a where clause, I now only use temp tables. Since this is 99% of stuff I do, I essentially don't use table vars except in those places where SQL forces me to (table-valued functions or parameters). The only time I would choose to use them now is if I wanted to leverage their ability to be immune to explicit transaction rollbacks.
Here is a simple test to explore the differences between Table Variables and temporary tables in handling a simple join. This is a variation of the test that Kevin Boles used to show '6668 IO BAD PLAN'. I've compared the four possible combinations of using a small table joining to a much larger. When I ran it, (and you might find different results) there was a difference in the parallelism but no significant difference in the four query plans. All ran in under 3 Ms. SQL Server 2008 r2.
dbcc freeproccache
--SET STATISTICS IO on
DECLARE @Words TABLE (Word varchar(40) PRIMARY KEY)
CREATE table #Words (Word varchar(40) PRIMARY KEY)
SET STATISTICS PROFILE ON
INSERT INTO @Words (word) --no parallelism inserted 60387 words
SELECT CommonWords.string FROM CommonWords
INSERT INTO #Words (word) --yes parallelism inserted 60387 words
SELECT CommonWords.string FROM CommonWords
SET STATISTICS PROFILE off
/* so now we have a table variable of around 60,000 words and a
temporary table with 60,000 words*/
DECLARE @WordsToMatch TABLE (Word varchar(40))
--create a sample of words to join with the list of 60,000 common words
--and put them in a table variable
inSERT INTO @WordsToMatch (word) values ('Temporary'),('tables'),('are'),('used'),('by'),('every'),('DB'),('developer,'),('but'),('they''re'),('not'),('likely'),('to'),('be'),('to'),('adventurous'),('with'),('their'),('use'),('or'),('exploit'),('all'),('their'),('advantages'),('They'),('can'),('improve'),('your'),('code'),('performance'),('and'),('maintainability'),('but'),('can'),('be'),('the'),('source'),('of'),('grief'),('to'),('both'),('developer'),('and'),('DBA'),('if'),('things'),('go'),('wrong'),('and'),('a'),('process'),('grind'),('away'),('inexorably'),('slowly'),('We'),('asked'),('Phil'),('for'),('advice'),('thinking'),('that'),('it'),('would'),('be'),('a'),('simple'),('explanation')
CREATE table #WordsToMatch (Word varchar(40))
INSERT INTO #WordsToMatch SELECT word FROM @wordstomatch
--and also put them in a table variable
/* now we'll run all four possible combinations of temporary table
and table variable and see what sort of plan is being generated
in each case */
SET STATISTICS PROFILE ON
SELECT COUNT(*) FROM @words commonwords
INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word
SELECT COUNT(*) FROM #words commonwords
INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word
SELECT COUNT(*) FROM @words commonwords
INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word
SELECT COUNT(*) FROM #words commonwords
INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word
SET STATISTICS PROFILE OFF
/* now we'll run a simple set of timings just to see how long each join took (in Milliseconds) */
dbcc freeproccache
DECLARE @log TABLE (
Log_Id INT IDENTITY(1, 1),TheeVent VARCHAR( 2000 ),
DateAndTime DATETIME DEFAULT GetDate()) ;
INSERT INTO @log (TheEvent) SELECT 'both table variables';
SELECT COUNT(*) FROM @words commonwords
INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word
INSERT INTO @log (TheEvent) SELECT 'large table variable joined to small temp table';
SELECT COUNT(*) FROM #words commonwords
INNER JOIN @wordsToMatch intro ON commonWords.word=intro.word
INSERT INTO @log (TheEvent) SELECT 'Large temp table small table variable';
SELECT COUNT(*) FROM @words commonwords
INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word
INSERT INTO @log (TheEvent) SELECT 'Both temporary tables';
SELECT COUNT(*) FROM #words commonwords
INNER JOIN #wordsToMatch intro ON commonWords.word=intro.word
INSERT INTO @log (TheEvent) SELECT 'end';
SELECT
TheStart.TheeVent + ' took '
+ CAST( DatedIff( ms, TheStart.DateAndTime, Theend.DateAndTime ) AS VARCHAR( 10 ))
+ ' Ms.'
FROM @log TheStart
INNER JOIN @log Theend
ON Theend.Log_Id = TheStart.Log_Id + 1;
DROP table #WordsToMatch
DROP table #Words
1) guidance from Microsoft is varied and problematic. All one need do is examine the database structures and code of Sharepoint to understand that there are lots of substandard things going on SQL Server wise within Microsoft. Oh, take a gander at the myriad of issues that come along with TSQL UDF usage too. :-) And how about the hugely outdated 1MB growth fragment size default for database files (and more importantly lack if exceptionally stressed guidance to NOT keep that for any database), 5 for cost threshold for parallelism, 1 file per cpu core for tempdb guidance, etc, etc. 2) Voiding parallelism for DML activity involving table vars has essentially nothing to do with the TVP itself. It is with that massive 6 table 50M row hitting SELECT statement that is populating it. THAT is where the perf problem comes into play. 3) But the simplest thing to keep in mind is that you are GUARANTEED to get SEVERAL bad things happening some/most of the time when you use table vars over temp tables as I have said and demonstrated. So the guidance should always be if you are not seeing the two reasons I said they should be used for (recompilation perf issues or the very esoteric auditing need for rolled-back transactions) then don't use them. You just have the guidance backwards is all. 4) BTW, I am surprised and disappointed that a tech reviewer for the article didn't call you out on some of the issues I brought up.
We'll talk about the tech review at the Summit.
Your routine is interesting. It is great to start at the point where things have gone wrong and work outwards.
If, for example, you change the Select * to Select count(*), then the plans are identical good index seek nested loop join plans. If you add a bit more variation in the key column by doing ...
select number/10000, replicate(' ', 500)
...then both plans are identical.
Similarly, by deskewing a bit by doing ...
insert #BigTest
select number/10, replicate(' ', 500)
from tallyNumbers
where number between 2 and 1000
... then both plans are identical.
It shows, I guess, what can go wrong occasionally when there aren't distribution statistics for one or both tables, and the other one has a skewed distribution; but it also shows that the skew has to be pretty extreme, and normally, if the developer is 'with it', the temporary table will be an extract or aggregation from the table data, and so unlikely to be anything quite as skewed. However, real data does occasionally cause problems. We've all experienced Perfectly Poisonous Table Variables in real live data and occasionally I haven't been able to puzzle out an explanation.
I have come across a few instances where TVs have been employed in code and have cringed when I see the est row count going into them. Usually re-writing the code to use a #table has brought significant improvements. I cant say every time as i havent recorded the occurrences.
I now recommend in the office to write options using both and compare but to expect anything with more than a handful of records in a TV to be the slower/worse option.
Christian Bolton was speaking at our user group a few weeks ago and in his parallelism and CXPacket wait session he suggested that seeing performance gains with TVs comes when they are used for few records - OK for months of the year and so on but when the record count goes up the performance drops of and the #table is favoured.
"There are 2 reasons where table vars are appropriate and they are VERY infrequently found in the wild: 1) extremely high-call-frequency code where recompilations from temp table activity are a problem" I don't see how this is unlikely at all. If you write code that powers a modestly traffic'ed website, you'll run into this all the time. The recompilations from temp table activity will severely outweigh the performance loss from the lack of statistics on a table variable. As long as you're dealing with a rowset under a couple thousand rows table variables are the way to go for high volume code. Only when table variables get too big do they really start causing problems. In my experience, this has been somewhere north of 3000 rows.
Personally, I use them quite frequently and happily *under certain circumstances.*
I generally find the code involved when using a table variable seems cleaner, and there are no concerns about whether the table variable was dropped in a procedure (especially if that procedure results in an error during the development process rather than terminating cleanly.) I find that clean, readable code is a very valuable thing. And, in the very limited testing I have done, a table variable generally gets performance that is just as good as a temp table when dealing with small data sets that are being manipulated in simple ways.
With that said, it is important to note that I use them when data sets are small and the processes are simple. If I will need to use a large data set or use complex joins I will use a temptable.
Finally, regarding the:
> "I will also take exception to the
> advice to use constraints, checks,
> indexes, etc. In 15+ years of
> relational engine consulting I have
> eliminated those things (especially
> indexes) from temp tables to improve
> performance at least an order of
> magnitude more frequently than I have
> added them."
comment, I have to say that in circumstances I have dealt with I have seen dramatic improvements by placing indexes on *large* temp tables. If Kevin Boles says that they often reduce performance, then I will defer to his experience, but I will say I have certainly seen examples of times when indexes definitely do improve the performance with a temp table. That may be a case where it makes sense to test both ways.
This is another helpful article on the subject of temp tables vs table variables:
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/18/11-0-temporary-tables-table-variables-and-recompiles.aspx I have often tried to use table variables, but usually end up switching to temp tables because I need indexes. On that note: I've read a number of places that indexes on a temp table should be considered only in the case of a large data set. This is a little misleading if you are joining to the temp table: In my experience, even a small temp table when joined to a large dataset usually benefits from an index. Regarding recompiles, I found I had to be careful with temp tables to avoid recompiles in SQL 2000, but in SQL 2005 and later I haven't had noticeable problems with recompiles...even if I do DDL in places other than at the start of the procedure. My bottom line: I like the idea of table variables, but in practice they have turned out to be of limited use for me.
So what should I do in this case when I need to get the identity of last row inserted? Microsoft recommended here to use TV...
http://support.microsoft.com/kb/2019779 nh
You should as this as a new question instead of presenting it as an answer to an old question.
One other consideration on the issue of temp tables vs. TVs is scoping: The scope of a TV is limited to the span from declaration to the end of the batch or stored procedure, AND is limited to that batch or procedure. A temp table on the other hand is scoped to the session (SPID). This is a big difference in the case of nested procedure calls: If Procedure A calls Procedure B, and both procedures create a temp table of the same name but with different column definitions, a conflict arises. According to [BOL][1]: "...and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure." With temp tables therefore you need to take care that nested calls do not create temp tables with the same name but with different column definitions. With TVs, this is not a concern, because the scope of the TV does not allow the TV to be visible from inside a nested procedure. Note that this condition of two different tables on the session but with different column definitions result in a confusing error message when inside a transaction under SNAPSHOT isolation: the error indicates that DDL is not allowed within the transaction, even though no DDL statements were actually issued. And of course you can use TVs inside UDFs, while it is not possible to use temp tables inside UDFs. I have run into performance problems using TVs, and generally try to avoid them in favor of temp tables...but sometimes TVs are unavoidable, as in scenarios like I described above. [1]:
http://technet.microsoft.com/en-us/library/ms174979.aspx
