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 Tuesday, November 21, 2006 6:34 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

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

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

Post #324566
Posted Tuesday, November 21, 2006 7:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:53 PM
Points: 209, Visits: 367
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. 
Post #324577
Posted Tuesday, November 21, 2006 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 6, 2007 6:48 AM
Points: 1, Visits: 1

Another consideration when using Table Variables is parallelism:  Temp Tables support it, Table Variables don't.  This can be significant in some environments.

 

Post #324579
Posted Tuesday, November 21, 2006 7:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:54 AM
Points: 195, Visits: 80

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

Post #324592
Posted Tuesday, November 21, 2006 7:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:14 AM
Points: 89, Visits: 260
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
Post #324605
Posted Tuesday, November 21, 2006 7:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 29, 2010 8:26 AM
Points: 21, Visits: 6

I have found both temporary tables and variable tables to be useful. I have found this article to have a good explanation of when to use each.

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

 

 

Post #324611
Posted Tuesday, November 21, 2006 8:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 31, 2007 8:22 AM
Points: 92, Visits: 1
Thank you for taking the time to share with us, Jambu. Maybe you were not able to help the geniuses, but it was very helpful to the rest of us.
Post #324629
Posted Tuesday, November 21, 2006 8:31 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

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

Jambu,

Thanks for sharing your info - this topic sparked a great discussion on the topic!

Mark

Post #324635
Posted Tuesday, November 21, 2006 9:33 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:56 PM
Points: 136, Visits: 624

Contradiction:

Nothing comes without caveats! You cannot use Table Variables in the following situations:

select xxx into @table_variable
or
(here) insert into @table_variable select * from myTable

For example if you want to get the output of a Stored Procedure into a table variable, you cannot do so without circumvention. Here is a sample piece of code which gets the output of a Stored Procedure in to a temporary variable. The only way to get the output of a stored procedure into a table variable is by using an intermediary table, for example using a temporary table.

set nocount oncreate table #sp_depends (  t1		nchar(128),  t2		nchar(128)  )insert into #sp_depends execute sp_depends myTabledeclare @t1 table (ta varchar(200), tb varchar(200))
(here) insert into @t1 select * from #sp_depends
drop table #sp_dependsselect * from @t1


Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Post #324652
Posted Tuesday, November 21, 2006 9:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:14 AM
Points: 89, Visits: 260
Quite a nice article, but, as has been pointed out here, a table variable 'does' create in tempdb. In fact, I tested this, and was able to show a growth with just 1 row into an INT only table.

quote:'A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

So we can be explicit now that table tavriables are created on tempdb, albeit in a splightly different manner.

--something bizarre is happening with the formatting of this message
Post #324661
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse