September 9, 2011 at 11:57 am
Having the same problem.
Apparently there is a hotfix for SQL 2008:
Seems to be a known issue. You can try switching the Compatibility level back to 2005 to confirm
September 9, 2011 at 12:00 pm
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.
October 10, 2011 at 6:15 am
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
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy