主页 / user-2065144

Nelson O's questions

Martin Hope
Nelson O
Asked: 2025-01-03 02:05:40 +0800 CST
  • 7

本质上，我的问题归结为观察这两个查询计划（来自 SQL Server 2019）：

  1. 使用 SQL 定义的标量函数进行计划。

SQL 标量函数查询计划

  1. 使用 CLR 定义的标量函数进行规划。

CLR 标量函数查询计划

我定义了一个标量函数，用于将 IP 地址的字符串表示解析为binary(16)IPv6 地址的值（将 IPv4 地址映射到 IPv6）。我首先在 SQL 中实现它，然后我也在 C# 中使用内置类IPAddress来解析该值。我试图使用此函数将包含 IP 地址字符串的表连接到包含解析出的 CIDR 块列表的表（聚集索引[Start]和[End] binary(16)值）。

我编写 SQL 查询的方法有两种：

  1. 直接在条件中使用标量函数JOIN。
SELECT
    *
FROM
    [dbo].[values]
    val
LEFT JOIN
    [dbo].[cidr]
    cidr
        ON [dbo].[fn_ParseIP](val.[IpAddress]) BETWEEN cidr.[Start] AND cidr.[End]
;
  1. APPLY在引用子句中的标量函数之前，先计算它JOIN。
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]
;

在我的测试中，[dbo].[values]包含 17 行（并且是使用实际表定义的VALUES而不是实际表）并且[dbo].[cidr]包含 986,320 行。

当使用 SQL 中定义的标量函数时，查询 1 大约需要 7.5 分钟才能运行，而查询 2 则需要不到 1 秒。

使用 CLR 标量函数时，两个查询大约需要 2.5 分钟才能运行，但查询 2 在查询计划中有一个额外的节点用于在连接后计算标量函数。

最终的区别在于，当引用 SQL 中定义的标量函数时，我可以让它生成第一个计划，该计划首先计算标量函数的结果，然后[dbo].[cidr]在执行连接时将这些结果用作查找谓词进入聚集索引。但是当使用 CLR 函数时，它总是将计算作为聚集索引查找（和过滤）的一部分来执行，因此它会更频繁地运行该函数来获取结果。

我的假设是，这种行为可能是由于查询规划器认为该函数是不确定的，但我已经使用以下属性实现了 CLR 函数：

    [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)
    { }

我希望可以依靠 .NET 标准库在 SQL 中帮我处理 IP 地址解析。目前，我们有一些流程仅适用于 IPv4 地址，我需要更新它们以使其也适用于 IPv6。在我们的一些大型数据库中，这种处理非常缓慢，因此我希望 .NET 中的解析逻辑更高效。看起来 CLR 函数本身比我的 SQL 实现更快，但对查询计划的影响明显更差。

我可以重写查询以首先使用临时表解析 IP 地址，这应该可以解决这个问题。定义一个等效的 CLR 表值函数（该函数仅返回一行）时，我也可以获得不错的结果。

但是，我想知道我是否遗漏了某些东西，以便更轻松地将 CLR 标量函数用作过滤谓词的一部分。这是否是个坏主意，我应该继续使用其中一些替代方案，或者我可以做些什么来更轻松地使用 CLR 函数作为 SQL 函数的替代品？

对于任何感兴趣的人，这里是使用 T N 的答案中给出的概念表现良好的最终查询。

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
;
sql-server