当另存为内联表值函数时，T-SQL代码非常慢 [英] T-SQL code is extremely slow when saved as an Inline Table-valued Function

问题描述

我似乎无法弄清楚为什么将我的代码包装到ITVF中时SQL Server采取完全不同的执行计划。当自己在ITVF内部运行代码时，查询将在5秒钟内运行。如果将其另存为ITVF，它将运行20分钟，并且不会产生结果。我希望将其放在ITVF中以便代码重用。为什么将代码另存为ITVF会导致严重的性能问题？

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
)

编辑：

好...我想我已经解决了问题...但是我不明白。可能我应该发布一个全新的问题，让我知道您的想法。这里的问题是，当我用文字调用函数时，它真的很慢...当我用变量调用它时它很快。

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')

有什么想法吗？

推荐答案

使用文字时，SQL Server可以看起来在列统计信息中估算出将返回多少行，并根据该假设选择适当的计划。当您使用变量时，值在编译时是未知的，因此它会落在猜测上。

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

这篇关于当另存为内联表值函数时，T-SQL代码非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！