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.