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 11:17 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 1:44 AM
Points: 162, Visits: 520

Hi To all

This is nice article , i worked lot on Table Variable and know all it's + and -

there is a restriction on Table Variable that we cannot use EXEC and string query on table variable.

Can any one give me the right Alternative how i insert Dynamic query result into Table Variable . I don't Want to Use # Table or ## table.

my problem is like that.

DECLARE @SHASHI(PRIMARY_KEY INT NOT NULL, NAME VARCHAR(50))

DECLARE @SSQL AS STRING, @CHECK AS INT

SET @CHECK = 1

SET @SSQL = 'SELECT ID , NAME FROM MY_TABLE'

IF @CHECK = 1

SET @SSQL = @SSQL + 'WHERE ID >25'

ELSE

SET @SSQL = @SSQL + 'WHERE ID <25'

INSERT INTO @SHASHI

----------------------HOW I USE THE DYNAMIC QUERY TO INSERT VALUES INTO MY @SHASHI TABLE. CAN ANY ONE EXPERT SOLVE MY PROBLEM WITHOUT USING TEMP TABLE........

THANKS IN ADVANCE........HOPPING URGENT REPLY.........

Post #324817
Posted Wednesday, November 22, 2006 2:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 23, 2007 4:05 AM
Points: 97, Visits: 1

Hello All. First of all thank you Jambu for your article. Certainly got the yeah's and nay's going. A little experience I have had in the past on not so unrelated. Without going into too much detail, I had an issue where a stored procedure was being excessively recompiled. Did the usual analysis and ended up logging a call to MS. A order number and revision were passed to the SP and then updates took place. Because we are a very high OLTP site, MS got me to modify the sp, to DECLARE local variables in the sp and assign the order number and revision passed to the SP to these variables. The variables took the place of the parameters thruout the SP. And guess what, the excessive recompilations stopped. So just a little experience I had with local variables. Hope this might help one of you someday. Derek.

Post #324834
Posted Wednesday, November 22, 2006 10:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:22 PM
Points: 727, Visits: 265

Nice article covering some basic points, but here are 2 important points to hightlight:

A lot of my work day-to-day is fining previously written procedures that are slow and re-writing them more to be more efficient.  I often end up converting all the table variables to temp tables and making indexes on the columns that are used in JOINs or WHERE or GROUP BY clauses in subsequent queries.  This is especially germane when the row sizes are large.  When I bench mark and compare the two methods, I'd say that 75% of the time the #temp tables are considerable faster especially when number of rows are great (100,000 or higher)

However, in the case of user-defined functions you have no choice but to use a table variable.

- Paul



- Paul

http://paulpaivasql.blogspot.com/
Post #325004
Posted Wednesday, November 22, 2006 11:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 8, 2007 12:55 AM
Points: 2, Visits: 1
Guys,

is it possible to use table variabel in dynamic sql, for example how to make this query works ?

DECLARE @xyz TABLE (
CustID varchar(50),
CustName varchar(50)
)

insert into @xyz (CustID, Custname) VALUES ('1','Name 1')
insert into @xyz (CustID, Custname) VALUES ('2','Name 2')
insert into @xyz (CustID, Custname) VALUES ('3','Name 3')

EXEC sp_executesql N'SELECT * FROM @xyz'
Post #325135
Posted Thursday, November 23, 2006 7:05 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

yes, it is POSSIBLE, but only by having the declaration of the variable, population of the table variable, and usage of the table variable, ALL INSIDE THE DYNAMIC SQL.

not something you want to be doing, though.....

Post #325168
Posted Thursday, November 23, 2006 7:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 8, 2007 12:55 AM
Points: 2, Visits: 1
i see. thanks. im using temp table instead.
Post #325271
Posted Monday, September 22, 2008 10:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 22, 2013 10:13 AM
Points: 79, Visits: 200
Table variables are indeed very slow when processing even moderately sized result sets. I have a proc that produces, in one use case, about 2200 rows. This table variable needs to be joined(or updated) with another couple of tables which can be large to get the desired results. The table variable with umpteen hours of tuning still gets the job done in about 16 seconds and the temp table version of the exact same proc gets it done in 2 seconds. Go figure ...
Post #573674
Posted Tuesday, September 23, 2008 2:52 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
Guru Nagabhushan (9/22/2008)
Table variables are indeed very slow when processing even moderately sized result sets. I have a proc that produces, in one use case, about 2200 rows. This table variable needs to be joined(or updated) with another couple of tables which can be large to get the desired results. The table variable with umpteen hours of tuning still gets the job done in about 16 seconds and the temp table version of the exact same proc gets it done in 2 seconds. Go figure ...


That seems to tie in to my heuristic:
Wanderer (11/21/2006)
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 #574132
Posted Friday, August 28, 2009 2:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:55 AM
Points: 80, Visits: 189
It is better to try both(temp tables and table variable) before deciding. A stored procedure I am working on requires two temporary structures. However, one of them needs rows to be inserted using a dynamic query and since table variables cant be used in a dynamic query ( uncless offcourse you create the table variable itself as a part of the dynamic query) I chose to use temporary table for that one while I used table variable for the other. But i did test both separately and inspite of the large number of rows, table variable gave me a lower processing time ( for about 5 million rows)
Post #778937
Posted Wednesday, March 10, 2010 3:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 4:53 PM
Points: 1, Visits: 8
Here's a puzzling table variable speed behavior that I can't explain.

I have a user-defined table function that returns a small resultset (~200 rows).
The user-defined table function employs a GROUP BY in a query of a very large table
(say, 1,000,000 rows).

When I merely select from the user-defined table function,
the small resultset of ~200 rows is returned in ~5 seconds.

--psuedocode
SELECT myColumn
FROM udtReturningSmallResultsetFromGroupByOverLargeNumberOfRecords
(@myDateVariable)

However, when I try to insert this small resultset into a table variable,
the query below takes about 30 seconds

--psuedocode
DECLARE @tblVariable TABLE(myColumn varchar(50) NOT NULL)

INSERT @tblVariable(myColumn)
SELECT myColumn
FROM udtReturningSmallResultsetFromGroupByOverLargeNumberOfRecords
(@myDateVariable)

Does anyone have any ideas why inserting such a small resultset into a table variable (~200 records)
would cause a 6-fold slowdown? Lacking a good explanation, I can only guess that the presence of the table variable insert is somehow throwing off the optimizer for the user-defined table function select.
Post #880574
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse