保存为内联表值函数时，T-SQL代码非常慢

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?

我似乎无法弄清楚为什么在将我的代码包装在ITVF中时,SQL Server采用完全不同的执行计划。在ITVF内部运行代码时,查询将在5秒内运行。如果我将其保存为ITVF,它将运行20分钟而不会产生结果。我更喜欢在ITVF中使用它来进行代码重用。将代码保存为ITVF的任何想法都会导致严重的性能问题?

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?

1 个解决方案

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

使用文字时,SQL Server可以查看列统计信息以估计将返回的行数,并根据该假设选择适当的计划。当您使用变量时,在编译时不知道这些值,因此它会回落到猜测上。

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

如果您打开了统计信息的自动更新,那么您可能会遇到此问题统计信息,行估计值和升序日期列

#1