这里有一个对于 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 - 运行极慢 - 我放弃了，五分钟都等不起
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
如果有人觉得这很重要：该函数的目的仅是选择任意数量位置在层次结构中的所有可能的祖先（父母、祖父母等）。
Node列是hierarchyid，dbo.GetAncestors是一个CLR函数，它简单地沿着路径向上遍历，不做任何数据访问。
UniqueIntTable是其名称的含义 - 它是一个用户定义的表类型，有一列Value int NOT NULL PRIMARY KEY。所有应该索引的内容都已索引 - 函数A的执行计划基本上只是两个索引查找和一个哈希匹配，就像函数B应该做的那样。甚至有些更奇怪的问题：
我甚至不能为使用函数B的简单查询获得预估的执行计划。看起来性能问题几乎与这个看起来简单的函数的编译有关。
如果我将函数B的"主体"取出来，只是将其插入到一个内联查询中，它会像函数A一样正常运行，性能相同。所以这似乎只是一个在UDF 内部使用CTE时才会遇到的问题，或者反过来，只有使用CTE的UDF才会遇到这个问题。
尝试运行B时测试机器上一个核心的CPU使用率飙升到100%。看起来没有太多I/O。
有什么想法吗？
[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脚本。