T-SQL code is extremely slow when saved as an Inli

Question

I can't seem to figure out why SQL Server is taking a completely different execution plan when wrapping my code in an ITVF. When running the code inside of the ITVF on its own, the query runs in 5 seconds. If I save it as an ITVF, it will run for 20 minutes and not yield a result. I'd prefer to have this in an ITVF for code reuse. Any ideas why saving code as an ITVF would cause severe performance issues?

CREATE FUNCTION myfunction
(
    @start_date date, 
    @stop_date date
)
RETURNS TABLE 
AS
RETURN 
(
    with
    ad as (
        select [START_DATE]
              ,[STOP_DATE]
              ,ID
              ,NAME
              ,'domain1\' + lower(DOMAIN1_NAME)
               collate database_default as ad_name
        from EMP_INFO
        where DOMAIN1_NAME != ''
        union
        select [START_DATE]
              ,[STOP_DATE]
              ,ID
              ,NAME
              ,'domain2\' + lower(DOMAIN2_NAME)
               collate database_default as ad_name
        from EMP_INFO
        where DOMAIN2_NAME != ''
    )
    select ad.ID
          ,ad.NAME
          ,COUNT(*) as MONITORS
    from scores
    join users
        on (scores.evaluator_id = users.[user_id])
    join ad
        on (lower(users.auth_login) = ad.ad_name and
            scores.[start_date] between ad.[START_DATE] and ad.[STOP_DATE])
    where scores.[start_date] between @start_date and @stop_date
    group by ad.ID
            ,ad.NAME
)

EDIT:

Ok...I think I figured out the problem...but I don't understand it. Possibly I should post an entirely new question, let me know what you think. The issue here is when I call the function with literals, it is REALLY slow...when I call it with variables it is fast.

-- Executes in about 3 seconds
declare @start_date date = '2012-03-01';
declare @stop_date date = '2012-03-31';
select *
from myfunction(@start_date, @stop_date);

--Takes forever!  Never completes execution...
select *
from myfunction('2012-03-01', '2012-03-31')

Any ideas?

Answer 1

When you use literals SQL Server can look at the column statistics to estimate how many rows will be returned and choose an appropriate plan based on that assumption. When you use variables the values are not known at compile time so it falls back on guesses.

If the plan is better when it guesses than when it refers to the actual statistics then this indicates the statistics likely need updating.

If you have auto update of statistics turned on then you may well be hitting the issue here Statistics, row estimations and the ascending date column