Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variables


Table Variables

Author
Message
SDB15
SDB15
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 114
Having the same problem.

Apparently there is a hotfix for SQL 2008:
http://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1

Seems to be a known issue. You can try switching the Compatibility level back to 2005 to confirm
SDB15
SDB15
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 114
FredFlintst0ne (3/10/2010)
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.


I should be more specific. If I run a similar query to the above in 2005, it returns in under 1 second. When I ran it in 2008, it took over a minute and a half. It seems to be a known issue and hopefully there will be a fix soon.
Eyck Troschke
Eyck Troschke
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 46
I had the same problem under sql server 2008r2 SP1 (both 32 and 64 bit)!
Start parameter -T4130 seems to solve the problem.
But i am wondering, because that hotfix (and so the trace flag 4130) is only documented for sql server 2008!?!

Or do i have overseen something?

Regards

Eyck
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search