sql-server-2008 - SQL 服务器 (T-SQL) : Avoid call scalar function multiple times

Question

我有一个存储过程，它执行 SELECT 以返回一些行。在 SELECT 中，我需要检查条件以便为某些列返回正确的值。此条件包含一个标量函数。对于正在处理的行，所有时间都使用相同的参数调用标量函数，见下文:

SELECT
    Id,
    Name,
    Surname,
    CASE WHEN (dbo.GetNumTravels(Id) >= 50)
        THEN 10
        ELSE 99
    END as Factor1,
    CASE WHEN (dbo.GetNumTravels(Id) >= 50)
        THEN 15
        ELSE -1
    END as Factor2,
    CASE WHEN (dbo.GetNumTravels(Id) >= 50)
        THEN 30
        ELSE 70
    END as Factor3
FROM 
    Employees
WHERE 
    DepartmentId = 100

我担心性能，我的意思是，我不喜欢多次调用标量函数 dbo.GetNumTravels，那么如何避免这种情况并且只调用一次然后一直使用它我需要它吗？

Answer 1

标量用户定义函数因性能不佳而臭名昭著。如果您可以将其转换为内联表值函数，您有望看到性能提升。

如果您将标量函数转换为内联表值函数，您可以使用 cross apply() 为每一行调用一次，如下所示:

select
     Id,
     Name,
     Surname,
     case when x.NumTravels >= 50
        then 10
        else 99
     end as Factor1,
     case when x.NumTravels >= 50)
        then 15
        else -1
     end as Factor2,
     case when x.NumTravels >= 50
        then 30
        else 70
     end as Factor3
from Employees
  cross apply dbo.GetNumTravels_itvf(e.Id) x
where DepartmentId = 100

引用:

• When is a sql function not a function? "If it’s not inline, it’s rubbish." - Rob Farley
• Inline Scalar Functions - Itzik Ben-Gan
• Scalar functions, inlining, and performance: An entertaining title for a boring post - Adam Machanic
• tsql User-Defined Functions: Ten Questions You Were Too Shy To Ask - Robert Sheldon