功能A – 通常在〜5 ms内完成

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.Level
    FROM Ancestors_CTE ac
    INNER JOIN Hierarchy p
    ON p.Node = ac.Node

下面我会解释一下这个功能的作用,但是在介绍之前,我想指出,我不认为这很重要,因为据我所知,这两个功能是完全一样的！唯一的区别是一个使用CTE,一个使用一个子查询; A中的子查询的内容和B中的CTE相同.

如果任何人决定这个事情：这个功能的目的只是挑选层次结构中任意数量的位置的所有可能的祖先(父母,祖父母等). Node列是一个层次结构,dbo.GetAncestors是一个CLR函数,它只是走上路径,它不会进行任何数据访问.

UniqueIntTable是什么意思 – 它是一个用户定义的表类型,带有一列,Value int NOT NULL PRIMARY KEY.应该索引的所有东西都被索引 – 函数A的执行计划本质上只是两个索引寻找和哈希匹配,因为它应该是函数B.

有些甚至陌生的方面,这个奇怪的问题：

>我甚至不能使用函数B获得一个简单查询的估计执行计划.它几乎看起来像性能问题与编译这个简单的功能有关.
>如果我将“body”从函数B中取出,并将其粘贴到一个内联查询中,它的运行通常与函数A的性能相同.所以它似乎只是UDF内的CTE的一个问题,或者相反地,与UDF使用CTE.
>当我尝试运行B时,测试机上一个核心上的cpu使用率一直高达100％.似乎没有太多的I / O.

我想把它作为一个sql Server错误,并使用版本A,但我总是尝试保持规则#1(“选择不破”),并担心,从功能A的好结果以某种方式是局部化的流感,它将“失败”与B在不同服务器上的方式相同.

有任何想法吗？

更新 – 我现在包含一个完整的自包含的脚本来重现.

GetAncestors函数

[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脚本.