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, September 09, 2011 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 25, 2012 10:16 AM
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
Post #1172764
Posted Friday, September 09, 2011 12:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 25, 2012 10:16 AM
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.
Post #1172767
Posted Monday, October 10, 2011 6:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 6:56 AM
Points: 1, Visits: 25
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
Post #1187857
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse