SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


table variable issues


table variable issues

Author
Message
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
tcronin 95651
tcronin 95651
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 Visits: 551
my bad
Andrew Kernodle
Andrew Kernodle
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 8135
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 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!

- :-D
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39458 Visits: 32630
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search