Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

table variable issues Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 3:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 20,900, Visits: 32,939
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.



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)
Post #1585695
Posted Tuesday, June 24, 2014 3:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:44 AM
Points: 68, Visits: 121
my bad
Post #1585696
Posted Tuesday, June 24, 2014 3:13 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 598, Visits: 7,235
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 . 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!




-
Post #1585698
Posted Tuesday, June 24, 2014 3:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 20,900, Visits: 32,939
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.



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)
Post #1585699
Posted Tuesday, June 24, 2014 8:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 14,208, Visits: 28,537
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
Post #1585727
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse