June 24, 2014 at 2:11 pm
Came into new dba position, found several SP's with table variables that were taking hours to run. Started changing them to true temp tables and cut time by up to 95%. I have heard a few speakers refer to them as the evil empire and evern words that MS may start to depracate them. Any one else seeing this?
June 24, 2014 at 2:39 pm
I haven't heard this, but I also know that used properly they have a very good use. I have used them to capture data that needed to survive a transaction rollback for one significant use.
I have done thins in some production code as well as to verify changes during destructive data changes that were then rolled back instead of committed.
June 24, 2014 at 2:41 pm
this was for a warehouse load, it is clear and from what i have heard that if you have lots of records in the table variable joins to large tables can be a disaster.
June 24, 2014 at 2:43 pm
Table variables don't ever get statistics.
Be sure to specify the appropriate clustering key on either table variables for temp tables. Than can make a vast difference in the performance of those tables.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 24, 2014 at 2:44 pm
You are correct, for large amounts of data table variables are not a good choice. As with everything else in SQL Server it comes down to using the right tools for the job at hand.
June 24, 2014 at 2:48 pm
I don't believe that table variables are set to be deprecated, though they do have different use cases than, say, a temp table.
Table variables don't have optimization statistics on them, and they can't have indexes (EDIT: after creation; during creation, they can, with either a UNIQUE constraint or key), so if you're holding large volumes of data and sorting through them based on specific values, they can be a performance hog, from what I understand.
On the other hand, as Lynn points out, they aren't affected by ROLLBACK statements, so they can be useful for testing, and on smaller data sets, they may actually have performance benefits compared to temp tables, as discussed in this[/url] article.
As usual, in SQL Server, "it depends"! Testing is the best way to determine whether a table variable will sink a given bit of code or not. It sounds like you've done the testing in this case to disprove their usefulness, but disregarding them outright should be reserved until testing shows it to be necessary 🙂
- 😀
June 24, 2014 at 2:59 pm
Andrew Kernodle (6/24/2014)
I don't believe that table variables are set to be deprecated, though they do have different use cases than, say, a temp table.Table variables don't have optimization statistics on them, and they can't have indexes, so if you're holding large volumes of data and sorting through them based on specific values, they can be a performance hog, from what I understand.
On the other hand, as Lynn points out, they aren't affected by ROLLBACK statements, so they can be useful for testing, and on smaller data sets, they may actually have performance benefits compared to temp tables, as discussed in this[/url] article.
As usual, in SQL Server, "it depends"! Testing is the best way to determine whether a table variable will sink a given bit of code or not. It sounds like you've done the testing in this case to disprove their usefulness, but disregarding them outright should be reserved until testing shows it to be necessary 🙂
Careful about saying you can't have indexes on table variables, you can. You have create them as a primary key or unique key constraint while declaring the table variable. What you can't do is create indexes on table variables after they are declared.
They still won't have statistics on them, but they can have indexes.
June 24, 2014 at 3:03 pm
actually she is right about the indexes however it does not look the the engine uses them correctly. I had a table variable with 200 rows an and index, and a temp table with the same data. Did a join another large table and did a comparison on performance, the table variable took 222,000 logical reads to get the data, the temp tables 112.
June 24, 2014 at 3:06 pm
Don't use a simple UNIQUE constraint. You have to define a primary key, and the only way it's really useful to your query is if the first column in the key is joined/compared. Of course, that's true for any index: you have to use the first column to be eligible for a seek vs. a scan.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 24, 2014 at 3:09 pm
thats what was used, made no difference, I have actually seen several examples of this inside other stored procedures. even removed index statement from the table variable and the temp table, still 95% faster with the temp table. And in all cases the logical reads is insane. I have seen this on different servers, different databases, same results
June 24, 2014 at 3:10 pm
tcronin 95651 (6/24/2014)
actually she is right about the indexes however it does not look the the engine uses them correctly. I had a table variable with 200 rows an and index, and a temp table with the same data. Did a join another large table and did a comparison on performance, the table variable took 222,000 logical reads to get the data, the temp tables 112.
Not a she, I'm a he.
June 24, 2014 at 3:12 pm
my bad
June 24, 2014 at 3:13 pm
Lynn Pettis (6/24/2014)
Andrew Kernodle (6/24/2014)
I don't believe that table variables are set to be deprecated, though they do have different use cases than, say, a temp table.Table variables don't have optimization statistics on them, and they can't have indexes, so if you're holding large volumes of data and sorting through them based on specific values, they can be a performance hog, from what I understand.
On the other hand, as Lynn points out, they aren't affected by ROLLBACK statements, so they can be useful for testing, and on smaller data sets, they may actually have performance benefits compared to temp tables, as discussed in this[/url] article.
As usual, in SQL Server, "it depends"! Testing is the best way to determine whether a table variable will sink a given bit of code or not. It sounds like you've done the testing in this case to disprove their usefulness, but disregarding them outright should be reserved until testing shows it to be necessary 🙂
Careful about saying you can't have indexes on table variables, you can. You have create them as a primary key or unique key constraint while declaring the table variable. What you can't do is create indexes on table variables after they are declared.
They still won't have statistics on them, but they can have indexes.
Gah, my mistake :-P. I literally just came back from reading Phil's article that I linked, in a moment of "hey, it's been awhile since I read this, let me read it again", and read about the key/constraint method. Edited my post accordingly; thanks for the catch!
- 😀
June 24, 2014 at 3:15 pm
I didn't say that the indexes would improve performance only that table variables CAN have indexes, you just have to create them when declaring the table variable. The optimizer will normally treat a table variable as having only one row regardless of the actual number of rows contained in the table variable. This is why performance degrades when using them as the volume of data increases.
The reason I say normally is I seem to recall reading somewhere that this is not always the case but I don't remember in what circumstances this could change and I haven't researched more on my own at this time.
June 24, 2014 at 8:54 pm
tcronin 95651 (6/24/2014)
actually she is right about the indexes however it does not look the the engine uses them correctly. I had a table variable with 200 rows an and index, and a temp table with the same data. Did a join another large table and did a comparison on performance, the table variable took 222,000 logical reads to get the data, the temp tables 112.
It will create an index and it will take into account that the index is unique as part of the optimization process, but because it still doesn't have statistics, as was stated above, it doesn't behave in the same way through the optimizer. That's the fundamental issue for table variables. It's what makes them useful and useless. They don't have stats, so no recompiles, which can be awesome, depending. But, they don't have stats, so really horrific execution plans, which can be really problematic.
There is a change in the cardinality estimation in 2014 for those who are interested which might make some table variables somewhat less problematic.
"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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply