解决SQL Server中CLR标量函数导致的执行计划不佳问题

函数实现 #

我分别实现了两个标量函数，用于将IP地址字符串解析为IPv6的binary(16)类型值：

• SQL标量函数：[dbo].[fn_ParseIP]
• CLR标量函数：基于.NET的IPAddress类实现，函数定义及属性如下：

[SqlFunction(
    DataAccess = DataAccessKind.None,
    IsDeterministic = true,
    IsPrecise = true
)]
[return: SqlFacet(IsFixedLength = true, IsNullable = true, MaxSize = 16)]
public static SqlBinary fn_CLR_ParseIP([SqlFacet(MaxSize = 50)] SqlString ipAddress)
{ }

测试场景与查询写法 #

测试涉及两张表：

• [dbo].[values]：存储IP字符串，仅17行数据（通过VALUES定义）
• [dbo].[cidr]：存储解析后的CIDR块，共986320行，聚类索引建立在[Start]和[End]这两个binary(16)列上

我用两种写法关联两张表：

写法1：JOIN条件中直接调用函数 #

SELECT
    *
FROM
    [dbo].[values] val
LEFT JOIN
    [dbo].[cidr] cidr
        ON [dbo].[fn_ParseIP](val.[IpAddress]) BETWEEN cidr.[Start] AND cidr.[End]
;

写法2：先通过APPLY计算函数结果再关联 #

SELECT
    val.*, cidr.*
FROM
    [dbo].[values] val
CROSS APPLY
    (
        SELECT [ParsedIpAddress] = [dbo].[fn_ParseIP](val.[IpAddress])
    ) calc
LEFT JOIN
    [dbo].[cidr] cidr
        ON calc.[ParsedIpAddress] BETWEEN cidr.[Start] AND cidr.[End]
;

性能与执行计划差异 #

• SQL标量函数：写法1耗时约7.5分钟，写法2耗时不足1秒。执行计划显示写法2会先计算所有IP的解析结果，再以此为查找谓词对cidr表的聚类索引执行查找。
• CLR标量函数：两种写法均耗时约2.5分钟。写法2的执行计划中，函数会在聚类索引查找（过滤）阶段才计算，导致函数执行次数大幅增加——即使我已经为CLR函数设置了IsDeterministic=true等属性，查询优化器仍未生成预期的执行计划。

疑问与现有优化方案 #

我希望用.NET的高效解析逻辑替代SQL实现，以支持IPv6解析（原SQL函数仅支持IPv4），但CLR标量函数对执行计划的负面影响过大。目前我可以通过临时表或CLR表值函数实现优化，比如以下查询：

SELECT
    *
FROM
    [dbo].[values] val
CROSS APPLY
    (
        SELECT [IpAddress] = [dbo].[fn_CLR_ParseIP](val.[IpAddress])
    ) parsed
OUTER APPLY
    (
        SELECT TOP (1)
            *
        FROM
            [dbo].[cidr] _cidr
        WHERE
                _cidr.[range_start] <= parsed.[IpAddress]
            AND _cidr.[range_end]   >= parsed.[IpAddress]
        ORDER BY
            _cidr.[range_start] DESC
    ) cidr
;

但我想知道：是否有办法让CLR标量函数作为SQL标量函数的直接替代，避免执行计划问题？还是只能采用临时表、CLR表值函数这类现有替代方案？

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