代码之家 › 专栏 › 技术社区 › Aaronaught

奇怪的性能问题:内联用户定义函数中的公共表表达式

common-table-expression user-defined-functions sql-server-2008 performance sql-server

Aaronaught · 技术社区 · 16 年前

这里有一个SQL家伙的大脑扭曲-有人能想到为什么第一个函数执行得很好,第二个函数运行得很慢的原因吗?

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

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

如果有人决定了这一点:这个函数的目的只是挑选一个层次结构中任意数量位置的所有可能的祖先(父母、祖父母等)。这个 Node 列是 hierarchyid ,和 dbo.GetAncestors 是一个简单沿着路径运行的clr函数,它不执行任何数据访问。

UniqueIntTable 它的含义是-它是一个用户定义的表类型,只有一列, Value int NOT NULL PRIMARY KEY . 这里所有应该被索引的东西都被索引了——函数A的执行计划本质上只是两个索引查找和一个哈希匹配,就像函数B一样。

对于这个奇怪的问题,有些甚至是陌生的方面:

我甚至无法使用函数b为一个简单的查询获得一个估计的执行计划。它看起来几乎与性能问题有关。汇编这个简单的功能。
如果我将“body”从函数b中取出,并将其插入到一个内联查询中,它将正常运行,与函数a的性能相同。因此,它似乎只是CTE的一个问题。 在UDF内部 或者相反,只有使用CTE的UDF。
当我尝试运行b时,测试机上一个内核的CPU使用率一直飙升到100%。似乎没有太多的I/O。

我只想把它当作一个SQL Server错误,使用版本A,但我总是试图保持规则1( “选择未中断” )记住,我担心函数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 原稿中的脚本。

5 回复 | 直到 15 年前

Peter Radocchia 16 年前

哈哈,试试这个:

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

谁会猜到呢?

Peter Radocchia 16 年前

我复制了SQL 2008 SP1上的行为,用SQL UDF替换clf udf dbo.get祖先。我尝试了表值函数和内嵌函数;两者都没有区别。

我还不知道发生了什么,但是其他人的好处,我将在下面包括我的定义。

-- try a recursive inline UDF...
CREATE FUNCTION dbo.GetAncestors(@hierarchyid hierarchyid)
RETURNS TABLE AS RETURN (
WITH recurse AS (
    SELECT @hierarchyid AS Ancestor
    WHERE @hierarchyid IS NOT NULL
    UNION ALL
    SELECT Ancestor.GetAncestor(1) FROM recurse
    WHERE Ancestor.GetAncestor(1) IS NOT NULL
    )
SELECT * FROM recurse
)

-- ...or a table-valued UDF, it makes no difference
CREATE FUNCTION dbo.GetAncestors(@hierarchyid hierarchyid)
RETURNS @return TABLE (Ancestor hierarchyid) 
AS BEGIN
    WHILE @hierarchyid IS NOT NULL BEGIN
        INSERT @return (Ancestor)
        VALUES (@hierarchyid)
        SET @hierarchyid = @hierarchyid.GetAncestor(1)
    END             
    RETURN
END

选择上面的一个定义,然后运行它来观察它挂起:

DECLARE @IDs UniqueIntTable 
INSERT @IDs SELECT ID FROM Hierarchy
RAISERROR('we have inserted %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
SELECT * FROM dbo.GoodFunction(@IDs) a
RAISERROR('we have returned %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
GO

DECLARE @IDs UniqueIntTable 
INSERT @IDs SELECT ID FROM Hierarchy
RAISERROR('we have inserted %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
SELECT * FROM dbo.BadFunction(@IDs) a
RAISERROR('we have returned %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
GO

第二批从来没有开始 . 它已经过了解析阶段,但似乎在绑定和优化之间迷失了方向。

两个函数的主体在函数包装器之外编译为完全相同的执行计划:

SET SHOWPLAN_TEXT ON
GO
DECLARE @IDs UniqueIntTable 
INSERT @IDs SELECT ID FROM Hierarchy
SELECT p.ID, p.Node, p.Name, p.[Level]
FROM
(
    SELECT DISTINCT a.Ancestor AS Node
    FROM Hierarchy c 
    CROSS APPLY dbo.GetAncestors_IF(c.Node.GetAncestor(1)) a
    WHERE c.ID IN (SELECT Value FROM @IDs)
) np
INNER JOIN Hierarchy p
ON p.Node = np.Node

;WITH Ancestors_CTE AS
(
    SELECT DISTINCT a.Ancestor AS Node
    FROM Hierarchy c
    CROSS APPLY dbo.GetAncestors_IF(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


-- both return this:

    |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[Node]))
         |--Compute Scalar(DEFINE:([p].[Level]=[Scratch].[dbo].[Hierarchy].[Level] as [p].[Level]))
         |    |--Compute Scalar(DEFINE:([p].[Level]=[Scratch].[dbo].[Hierarchy].[Node] as [p].[Node].GetLevel()))
         |         |--Index Scan(OBJECT:([Scratch].[dbo].[Hierarchy].[IX_Hierarchy_Node] AS [p]))
         |--Top(TOP EXPRESSION:((1)))
              |--Filter(WHERE:([Recr1005]=[Scratch].[dbo].[Hierarchy].[Node] as [p].[Node]))
                   |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[Node]))
                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Value]))
                        |    |--Clustered Index Scan(OBJECT:(@IDs))
                        |    |--Clustered Index Seek(OBJECT:([Scratch].[dbo].[Hierarchy].[PK_Hierarchy] AS [c]), SEEK:([c].[ID]=[Value]) ORDERED FORWARD)
                        |--Index Spool(WITH STACK)
                             |--Concatenation
                                  |--Compute Scalar(DEFINE:([Expr1011]=(0)))
                                  |    |--Constant Scan(VALUES:(([Scratch].[dbo].[Hierarchy].[Node] as [c].[Node].GetAncestor((1)))))
                                  |--Assert(WHERE:(CASE WHEN [Expr1013]>(100) THEN (0) ELSE NULL END))
                                       |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1013], [Recr1003]))
                                            |--Compute Scalar(DEFINE:([Expr1013]=[Expr1012]+(1)))
                                            |    |--Table Spool(WITH STACK)
                                            |--Compute Scalar(DEFINE:([Expr1004]=[Recr1003].GetAncestor((1))))
                                                 |--Filter(WHERE:(STARTUP EXPR([Recr1003].GetAncestor((1)) IS NOT NULL)))
                                                      |--Constant Scan

非常有趣。在Microsoft Connect上提交错误报告,让他们告诉你发生了什么。

Peter Radocchia 16 年前

这是一个猜测,只是一个猜测,但也许它与优化器如何很好地猜测最佳执行计划有关,但并没有对其进行详尽的搜索。

因此,查询执行的工作方式如下:

分析->绑定->优化->执行

两个查询的解析树肯定是不同的。绑定树是可能不同。我对绑定阶段还不太了解,无法确定地声明这一点,但是假设绑定树是不同的是,它可能需要不同数量的转换才能使A和B将树绑定到相同的执行计划。

如果需要两个额外的转换才能将查询B转换为~5ms计划,优化器可能会在发现它之前说“足够好”。而对于查询A,~5ms计划可能正好在搜索成本阈值内。

Erik A. Brandstadmoen 16 年前

在第一个语句中,您的联接是

np INNER JOIN Hierarchy p
    ON p.Node = np.Node

你的第二句话是

Ancestors_CTE a
INNER JOIN Hierarchy p
ON p.Node = a.Node

但是,a也用作ct中dbo.getAncestors(c.node.getAncestor(1))的别名。试交换 Ancestors_CTE a 例如 Ancestor_CTE acte ,以确保优化器不会与重复使用a作为别名混淆。

也就是说,我不确定SQL Server在创建CTE时应用正确索引的能力有多强。我以前对此有过一些问题,并且使用了表变量,取得了很大的成功。

-1

Shane Collinsworth 15 年前

正如我理解的,在批量使用CTE时,您必须以“;”结束语句。它与with子句的解释有关。试试这个:

IF OBJECT_ID('_HappyFunction' ) IS NOT NULL DROP FUNCTION _HappyFunction  
IF OBJECT_ID('_NowHappyFunction') IS NOT NULL DROP FUNCTION _NowHappyFunction  
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 _NowHappyFunction (@IDs _UniqueIntTable READONLY)  
RETURNS @Table TABLE
(
Value INT
)
BEGIN
  ;WITH CTE AS (SELECT Value FROM @IDs)
  INSERT INTO @Table
  SELECT Value FROM CTE
  RETURN
END
GO

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

-- this will no longer hang and will also return an empty record set 
DECLARE @IDs _UniqueIntTable   
SELECT * FROM _NowHappyFunction(@IDs)  
GO