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 1234»»»

Table Variables Expand / Collapse
Author
Message
Posted Friday, November 03, 2006 12:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 30, 2013 11:07 AM
Points: 195, Visits: 75
Comments posted here are about the content posted at temp
Post #320432
Posted Monday, November 20, 2006 9:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 07, 2008 10:54 PM
Points: 12, Visits: 4

Table variables are represented in the temp database as tables.  Have you tried loading 10 million rows in a table variable?  SQL Server has no way of knowing how many rows will be loaded in a table variable, so it must create an object in temp.  So table variables are the same as temp tables, apart from naming conventions.

I'm sure we have had this article before in this site

Greg

 

 




Post #324499
Posted Tuesday, November 21, 2006 3:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 17, 2013 1:19 PM
Points: 29, Visits: 397

I suggest to look in http://support.microsoft.com/kb/305977, which states:  "Table variables are created in the tempdb database similar to temporary tables", so if the article says: "This indicates while the table variable has no internal representation in sysobjects, a temporary table does" this just means that Table variables are stored differently in tempdb.

The article says, "You cannot use ...: insert into @table_variable select * from myTable". Well, I do this everyday. Probably the statement should be like: "You cannot use ...: INSERT INTO table_variable EXEC stored_procedure".

My opinion: I like to use table variables very much. I believe the arguments in the above (Q305977) article without hesitation, in my own words: "lighter vehicle, more performance".
The article, although nicely written, falls short in some details. I agree with Greg, that we have seen a similar article before. That's not a reason not to post such an article, but I am expecting and looking for an article that surpasses the earlier ones. For myself: I am already convinced to use table variables whenever I feel they are appropriate (I never have to process 10 millions rows, but 50 thousand was no problem). What's beyond my way of working is inspecting recompilation details. The above (Q305977) article also leads us to (Q243586) "Troubleshooting stored procedure recompilation", which provides more details on recompilations.

Post #324527
Posted Tuesday, November 21, 2006 4:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:22 AM
Points: 1,052, Visits: 862

Another feature of table variables is that they do not get statistics.... which results in some incredibly poor performance when they are misused (particularly by those who think they live in 'memory' not tempdb...) for instance parallelism can be effectively reduced to nil by the judicial use of table variables.

 

 

 




Post #324534
Posted Tuesday, November 21, 2006 4:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, July 28, 2013 3:59 PM
Points: 86, Visits: 255

Just to add in on this

- table variables cannot be indexed, while temp tables can.

-Table variables cannot be trunacted (afaik), temp tables san.

After a certain size (who knows what that size is, tho), table variables can HUGELY degrade performance. I recently changed some reports which hung after a certain size, converted to using temp tables, and all was done in about a minutes

As a quantative example: I say that having just converted a developer's query that used table variables which ran 15 minutes on a SQL2005 SP 1 64bit, 8way, 16GB box box, with fairly decent SAN setup, to use temp tables. That was the only change I made. Since this is a reporting server that doesn't have much usage yet, I ran DBCC DROPCLEANBUFFERS, re-ran the SQL script using #temp tables, and got results in 20 seconds.

A quote from an email I sent of to some of my developer's on the topic a while ago:

Contrary to the popular belief that table variables exists in memory, KB Article 305977 states that "A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)."

A nice discussion on this topic: http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html

Post #324536
Posted Tuesday, November 21, 2006 4:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:22 AM
Points: 1,052, Visits: 862

If you have a minute check the 2 procs - do you get paralellism on the table variable one?  I never did, and believe its due to their lack of stats:

Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

http://www.support.microsoft.com/kb/305977

 




Post #324542
Posted Tuesday, November 21, 2006 5:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701

Here's how you can prove tables variables use tempdb:

1. Open a connection and run the sample code in this article adding a WAITFOR command:

declare @tbl_Var1 table(f1 int, f2 varchar(10))
--Wait 10 seconds
waitfor DELAY '000:00:10'

2. Open another connection and run this query whichs lists space used by temp objects. You should see a strangely named object, something like #67F75FA9. It appears to be a randomly generated name. If you insert rows into the table variable you'll notice the row count matches.

SELECT object_id   = a.id,
       object_name = a.name,
       rows        = isnull( b.rows, 0 ) +
                     isnull( c.rows, 0 ),
       reserved_KB = ( ( isnull( b.reserved, 0 )
                       + isnull( c.reserved, 0 )
                       + isnull( d.reserved, 0 )
                     ) * v.low ) / 1024
from   tempdb..sysobjects a,
       tempdb..sysindexes b,
       tempdb..sysindexes c,
       tempdb..sysindexes d,
       master.dbo.spt_values v
where  a.type = 'U'
and    a.id *= b.id
and    a.id *= c.id
and    a.id *= d.id
and    b.indid = 0
and    c.indid = 1
and    d.indid = 255
and    v.number = 1
and    v.type = 'E'

 




Post #324546
Posted Tuesday, November 21, 2006 5:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695

This discussion keeps coming up and lots of misconceptions will no doubt be cast far and wide.

Note that the points raised concerning table variables were not covered in the article, which was, I thought, pretty good at explaining table variables.

Both temp tables and table variables may be created in memory or tempdb, according to data set size, resource etc. You can apply a primary key to a table variable so in effect you can index a table variable, you don't get any performance benefit from a PK but you do get sorting. Small datasets are good in table variables, much better than temp tables, large datasets are not good in any type of temporary data structure.

Why on earth anyone could imagine using a parallel plan against a table variable would be a good thing I just can't think ?  Parallelism is good but infers a complex query .. not really where you'd want to be using temporary tables of any type surely?

Yup we all know table variables don't do stats - with small data sets it doesn't matter.

Finally I've been able to obtain some dramatic performance improvements by replacing #temp tables with table variables - but I'm talking about using small data sets as befits an oltp application.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #324549
Posted Tuesday, November 21, 2006 5:41 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711

What I've seen in the past is that table variables tend to be a little faster with smaller data sets than temp tables.  has anyone else noticed the same thing?

I haven't written a test (yet) to quantify it or figure out if it's always the case.

Mark

Post #324553
Posted Tuesday, November 21, 2006 6:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:56 PM
Points: 209, Visits: 349

We have an application that was written by a consulting firm; they wrote a ton of UDFs that utiliize Table Variables.  We had one report in particular that was really slow and caused some blocking on our database.  The only change I made was to use Temp Tables and get rid of the Table variable.  It cut the processing time by 70-80% and got rid of the blocking.  I'm not suggesting one over the other; what I am suggesting is to test both before deciding one over the other.

Mark

Post #324564
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse