November 20, 2006 at 9:14 pm
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
November 21, 2006 at 3:39 am
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.
November 21, 2006 at 4:16 am
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.
November 21, 2006 at 4:30 am
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
November 21, 2006 at 4:45 am
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
November 21, 2006 at 5:27 am
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'
November 21, 2006 at 5:35 am
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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 21, 2006 at 5:41 am
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
November 21, 2006 at 6:27 am
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
November 21, 2006 at 6:34 am
Mark,
I've found that UDF's that return table variables, or larger data sets tend to be awful slow, since the returned 'table' ends up not using any indices. If you can avoid a UDF that returns a dataset (table) I would do so!
Mark
November 21, 2006 at 7:01 am
There is an initiative here to get rid of the UDFs and rewrite them and make them stored procedures. I can thankfully say this app was before my time here at my current position.
November 21, 2006 at 7:05 am
Another consideration when using Table Variables is parallelism: Temp Tables support it, Table Variables don't. This can be significant in some environments.
November 21, 2006 at 7:27 am
one clarification:
this is what I meant to say. You cannot use this construct to get the output of a stored procedure into a table variable.
declare @table_variable table(f1 varchar(100), f2 varchar(100))
INSERT INTO @table_variable EXEC sp_depends 'anyTableName'
Server: Msg 197, Level 15, State 1, Line 2
EXECUTE cannot be used as a source when inserting into a table variable.
sorry for the confusion.
jambu
November 21, 2006 at 7:46 am
Colin,
You are completely correct that table variables are justly famous for big improvements on small resultsets, especially in savings on re-compliations on SPs. The question always seems to remain - what is the threshold at which you move from one to the other? Should we always write our queries in both forms, then performance test, and use the better version - I don't know.
At the moment, the hueristic I use is > 1000 rows, I look at using temp tables first, <= 1000 I look at table variables first. After that, if it is a frequently a case of deciding if it is an ad hoc query, or something that will be used with regularity.
I'm not sure why you think that temporary table, or table variables, wouldn't be used in complex queries, but that's a completely seperate thing.
Cheers
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply