Table Variables

  • 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

  • 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.

  • 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