sql-server - 奇怪的性能问题: Common Table Expressions in inline User-Defined Function

Question

对于 SQL 人员来说，这是一个令人费解的问题 - 谁能想到为什么第一个函数执行良好，而第二个函数运行缓慢？

功能 A - 通常在约 5 毫秒内完成

CREATE FUNCTION dbo.GoodFunction
(
    @IDs UniqueIntTable READONLY
)
RETURNS TABLE
AS RETURN
    SELECT p.ID, p.Node, p.Name, p.Level
    FROM
    (
        SELECT DISTINCT a.Ancestor AS Node
        FROM Hierarchy h
        CROSS APPLY dbo.GetAncestors(h.Node.GetAncestor(1)) a
        WHERE h.ID IN (SELECT Value FROM @IDs)
    ) np
    INNER JOIN Hierarchy p
    ON p.Node = np.Node

功能 B - 运行速度极慢 - 5 分钟后我就放弃了

CREATE FUNCTION dbo.BadFunction
(
    @IDs UniqueIntTable READONLY
)
RETURNS TABLE
AS RETURN
    WITH Ancestors_CTE AS
    (
        SELECT DISTINCT a.Ancestor AS Node
        FROM Hierarchy c
        CROSS APPLY dbo.GetAncestors(c.Node.GetAncestor(1)) a
        WHERE c.ID IN (SELECT Value FROM @IDs)
    )
    SELECT p.ID, p.Node, p.Name, p.Level
    FROM Ancestors_CTE ac
    INNER JOIN Hierarchy p
    ON p.Node = ac.Node

我将在下面解释这个函数的作用，但在开始之前，我想指出我认为这并不重要，因为据我所知，这两个函数正是相同!唯一的区别是一个使用CTE，一个使用子查询； A 中的子查询内容和 B 中的 CTE 相同。

如果有人认为这很重要:此函数的目的只是挑选出层次结构中任意数量的位置的所有可能的祖先( parent ，祖 parent 等)。 Node 列是一个 hierarchyid，而 dbo.GetAncestors 是一个 CLR 函数，它只是沿着路径向上走，它不执行任何数据访问。

UniqueIntTable 就是它的含义 - 它是一种用户定义的表类型，只有一列，Value int NOT NULL PRIMARY KEY。这里应该索引的所有内容都已索引 - 函数 A 的执行计划本质上只是两个索引查找和一个哈希匹配，就像函数 B 应该做的那样。

这个奇怪问题的一些更奇怪的方面:

• 我什至无法使用函数 B 获得简单查询的估计执行计划。看起来性能问题与这个简单查询的编译有关-查找函数。

• 如果我从函数 B 中取出“主体”并将其粘贴到内联查询中，它会正常运行，性能与函数 A 相同。所以这似乎只是 CTE 的问题 在 UDF 内，或者相反，仅在使用 CTE 的 UDF 内。

• 当我尝试运行 B 时，测试机器上一个核心的 CPU 使用率一路飙升至 100%。似乎没有太多 I/O。

我想将其视为 SQL Server 错误并使用版本 A，但我总是尽力牢记规则 #1(“SELECT 不会损坏”)，并且我我担心函数 A 的良好结果在某种程度上是局部的侥幸，它会像 B 在不同的服务器上那样“失败”。

有什么想法吗？

<小时/>

更新 - 我现在包含一个完整的独立脚本来重现。

获取祖先函数

[SqlFunction(FillRowMethodName = "FillAncestor", 
    TableDefinition = "Ancestor hierarchyid", IsDeterministic = true,
    IsPrecise = true, DataAccess = DataAccessKind.None)]
public static IEnumerable GetAncestors(SqlHierarchyId h)
{
    while (!h.IsNull)
    {
        yield return h;
        h = h.GetAncestor(1);
    }
}

架构创建

BEGIN TRAN

CREATE TABLE Hierarchy
(
    ID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Hierarchy PRIMARY KEY CLUSTERED,
    Node hierarchyid NOT NULL,
    [Level] as Node.GetLevel(),
    Name varchar(50) NOT NULL
)

CREATE INDEX IX_Hierarchy_Node
ON Hierarchy (Node)
INCLUDE (Name)

CREATE INDEX IX_Hierarchy_NodeBF
ON Hierarchy ([Level], Node)

GO

INSERT Hierarchy (Node, Name)
    SELECT CAST('/1/' AS hierarchyid), 'Alice' UNION ALL
    SELECT CAST('/1/1/' AS hierarchyid), 'Bob' UNION ALL
    SELECT CAST('/1/1/1/' AS hierarchyid), 'Charles' UNION ALL
    SELECT CAST('/1/1/2/' AS hierarchyid), 'Dave' UNION ALL
    SELECT CAST('/1/1/3/' AS hierarchyid), 'Ellen' UNION ALL
    SELECT CAST('/1/2/' AS hierarchyid), 'Fred' UNION ALL
    SELECT CAST('/1/3/' AS hierarchyid), 'Graham' UNION ALL
    SELECT CAST('/1/3/1/' AS hierarchyid), 'Harold' UNION ALL
    SELECT CAST('/1/3/2/' AS hierarchyid), 'Isabelle' UNION ALL
    SELECT CAST('/1/4/' AS hierarchyid), 'John' UNION ALL
    SELECT CAST('/2/' AS hierarchyid), 'Karen' UNION ALL
    SELECT CAST('/2/1/' AS hierarchyid), 'Liam' UNION ALL
    SELECT CAST('/2/2/' AS hierarchyid), 'Mary' UNION ALL
    SELECT CAST('/2/2/1/' AS hierarchyid), 'Nigel' UNION ALL
    SELECT CAST('/2/2/2/' AS hierarchyid), 'Oliver' UNION ALL
    SELECT CAST('/2/3/' AS hierarchyid), 'Peter' UNION ALL
    SELECT CAST('/2/3/1/' AS hierarchyid), 'Quinn'

GO

CREATE TYPE UniqueIntTable AS TABLE 
(
    Value int NOT NULL,
    PRIMARY KEY (Value)
)

GO

COMMIT

GO

上述代码/脚本可用于创建 CLR 函数/DB 架构；在原始版本中使用相同的 GoodFunction 和 BadFunction 脚本。

Answer 1

哈哈，试试这个:

IF OBJECT_ID('_HappyFunction' ) IS NOT NULL DROP FUNCTION _HappyFunction
IF OBJECT_ID('_SadFunction'   ) IS NOT NULL DROP FUNCTION _SadFunction
IF TYPE_ID  ('_UniqueIntTable') IS NOT NULL DROP TYPE _UniqueIntTable
GO

CREATE TYPE _UniqueIntTable AS TABLE (Value int NOT NULL PRIMARY KEY)
GO

CREATE FUNCTION _HappyFunction (@IDs _UniqueIntTable READONLY)
RETURNS TABLE AS RETURN
  SELECT Value FROM @IDs
GO

CREATE FUNCTION _SadFunction (@IDs _UniqueIntTable READONLY)
RETURNS TABLE AS RETURN 
  WITH CTE AS (SELECT Value FROM @IDs)
  SELECT Value FROM CTE
GO

-- this will return an empty record set
DECLARE @IDs _UniqueIntTable 
SELECT * FROM _HappyFunction(@IDs)
GO

-- this will hang
DECLARE @IDs _UniqueIntTable 
SELECT * FROM _SadFunction(@IDs)
GO

谁能想到呢？