SQL Server标量函数计算字段查询慢优化方案

• 核心瓶颈是你使用的多语句标量用户定义函数（UDF）：SQL Server 2019之前的版本，以及2019之后未开启智能查询处理的场景下，这类函数不会被优化器解析到主执行计划中，会为结果集的每一行单独执行一次函数逻辑。你的Client表有2500条记录，每条记录触发3次函数调用，累计产生7500次独立的Temporal表查询，仅函数调用的上下文切换开销就占了总耗时的90%以上，这也是你简化函数内查询条件后性能没有明显提升的核心原因——和查询条件复杂度无关，逐行调用的固定开销就足够拉低性能。
• 次要瓶颈是Temporal表缺失匹配查询模式的索引：当前Temporal表的聚簇索引建在自增Id上，每次函数查询都要通过全表扫描匹配记录，单次查询的IO成本很高。
• 额外开销：原函数定义返回值类型为NVARCHAR(255)，但实际返回INT类型值，计算列上又做了一次CONVERT转换，多余的类型隐式转换会进一步拖慢执行速度。

1. 替换多语句标量UDF为内联表值函数（iTVF），性能提升最显著 #

内联表值函数会被SQL Server优化器直接展开到主查询计划中，完全消除逐行调用的上下文切换开销，相比原多语句标量UDF通常有几十到上百倍的性能提升，且完全不需要改动Temporal表的历史存储逻辑、不影响未来时间生效的规则。

• 第一步：创建内联表值函数，修正原函数的返回类型不匹配问题

CREATE FUNCTION [dbo].[GetCurrentTemporalValue_iTVF]
(
    @clientId INT,
    @temporalType NVARCHAR(128),
    @queryTime DATETIME2(7)
)
RETURNS TABLE AS RETURN
(
    SELECT TOP(1) CAST(Value AS INT) AS CurrentValue
    FROM dbo.Temporal 
    WHERE ClientId = @clientId 
      AND TemporalType = @temporalType
      AND (ValidFrom <= @queryTime OR ValidFrom IS NULL) 
      AND (ValidTo >= @queryTime OR ValidTo IS NULL)
    -- 增加排序避免同一时点多条匹配记录时返回结果不确定
    ORDER BY ValidFrom DESC
)
GO

• 第二步：创建视图封装查询逻辑，保留原有直接查询Client维度当前值的使用习惯，不需要改动上层业务查询逻辑

CREATE VIEW [dbo].[Client_CurrentValue]
AS
SELECT 
    c.Id,
    c.Name,
    ISNULL(site.CurrentValue, 0) AS InternalSiteId,
    ISNULL(bs.CurrentValue, 1) AS BudgetingStatusId,
    ISNULL(bu.CurrentValue, 0) AS BusinessUnitId
FROM dbo.Client c
CROSS APPLY dbo.GetCurrentTemporalValue_iTVF(c.Id, 'Client_InternalSite', SYSUTCDATETIME()) site
CROSS APPLY dbo.GetCurrentTemporalValue_iTVF(c.Id, 'Client_BudgetingStatus', SYSUTCDATETIME()) bs
CROSS APPLY dbo.GetCurrentTemporalValue_iTVF(c.Id, 'Client_BusinessUnit', SYSUTCDATETIME()) bu
GO

后续业务查询直接访问Client_CurrentValue视图即可，2500条数据的全量查询耗时通常可以降到100毫秒以内。

2. 为Temporal表创建匹配查询模式的覆盖索引 #

无论是否替换函数，这个索引都可以将Temporal表的查询从全表扫描变为索引点查，大幅降低IO成本：

CREATE NONCLUSTERED INDEX IX_Temporal_ClientType_ValidPeriod
ON dbo.Temporal (ClientId, TemporalType, ValidFrom, ValidTo)
INCLUDE (Value)
-- 企业版可加ONLINE=ON选项，建索引时不阻塞业务读写
WITH (ONLINE = ON)
GO

注意查询时传入的TemporalType参数必须是NVARCHAR类型，避免隐式转换导致索引失效。

3. 高版本SQL Server可开启标量UDF内联，保留原有计算列逻辑 #

如果你使用的是SQL Server 2019及以上版本，不需要替换函数，只需要修改原函数定义开启内联属性，优化器会自动将标量函数逻辑展开到主查询计划中，消除逐行调用开销：

ALTER FUNCTION [dbo].[GetCurrentTemporalValue]
(
    @clientId INT,
    @temporalType NVARCHAR(128)
)
RETURNS INT
WITH INLINE = ON
AS
BEGIN
    DECLARE @at DATETIME2(7) = SYSUTCDATETIME()
    DECLARE @retVal INT
    SELECT @retVal = CAST(Value AS INT) FROM dbo.Temporal 
    WHERE ClientId = @clientId 
      AND TemporalType = @temporalType
      AND (ValidFrom <= @at OR ValidFrom IS NULL) 
      AND (ValidTo >= @at OR ValidTo IS NULL)
    RETURN @retVal
END
GO

*注意：因为GETUTCDATE()/SYSUTCDATETIME()是不确定性函数，无法基于这几个计算列创建持久化计算列，不需要浪费时间尝试持久化方案。

• 不要为了性能将三个维度字段改成固定存储的普通外键列，会直接破坏历史版本保留、未来时间生效的业务逻辑，上述优化方案完全不需要改动现有数据存储规则。
• 避免在生产环境直接SELECT *查询带非内联标量UDF计算列的表，只要返回结果行数超过百条，就会触发明显的性能问题。

内容的提问来源于stack exchange，提问作者user3057544