Why does this query become drastically slower when wrapped in a TVF?

I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten minutes without ending. The only change is replacing two date variables (initialized with date literals) with date parameters:

Runs in Seven Seconds

DECLARE @StartDate DATE = '2011-05-21'
DECLARE @EndDate   DATE = '2011-05-23'

DECLARE @Data TABLE (...)
INSERT INTO @Data(...) SELECT...

SELECT * FROM @Data

Runs for At Least Ten Minutes

CREATE FUNCTION X (@StartDate DATE, @EndDate DATE)
  RETURNS TABLE AS RETURN
  SELECT ...

SELECT * FROM X ('2011-05-21', '2011-05-23')

I had previously written the function as a multi-statement TVF with a RETURNS @Data TABLE (...) clause, but swapping that for the inline structure has not made a noticeable change. The long run time of the TVF is the actual SELECT * FROM X time; actually creating the UDF just takes a few seconds.

I could post the query in question, but it's a bit long (~165 lines) and, based on the success of the first approach, I suspect something else is going on. Skimming through the execution plans, they appear to be identical.

I've tried breaking the query into smaller sections, without change. No single section takes more than a couple seconds when executed alone, but the TVF still hangs.

I see a very similar question, https://stackoverflow.com/questions/4190506/sql-server-2005-table-valued-function-weird-performance, but I'm not sure that the solution applies. Perhaps someone has seen this problem and knows a more general solution? Thanks!

Here's the dm_exec_requests after several minutes of processing:

```
session_id 59
request_id 0
start_time 40688.46517
status running
command UPDATE
sql_handle 0x030015002D21AF39242A1101ED9E00000000000000000000
statement_start_offset 10962
statement_end_offset 1

I isolated the problem to one line in the query. Keeping in mind that the query is 160 lines long, and I'm including the relevant tables either way, if I disable this line from the SELECT clause:

COALESCE(V.Visits, 0) * COALESCE(ACS.AvgClickCost, GAAC.AvgAdCost, 0.00)

...the run time drops from 63 minutes to five seconds (inlining a CTE has made it slightly faster than the original seven-second query). Including either ACS.AvgClickCost or GAAC.AvgAdCost causes the run time to explode. What makes it especially odd is that these fields come from two subqueries which have, respectively, ten rows and three! They each run in zero seconds when run independently, and with the row counts being so short I would expect the join time to be trivial even using nested loops.

Any guesses as to why this seemingly-harmless calculation would throw off a TVF completely, while it runs very quickly as a stand-alone query?

StackExchange Database Administrators Q#12189, answer score: 4