sql-server – 多语句TVF与内联TVF性能

比较 Palindrome question上的一些答案(仅限10k用户,因为我删除了答案),我的结果令人困惑.

我提出了一个multi-statement,schema-bound TVF,我认为它比运行标准功能更快.我也认为多语句TVF会被“内联”,虽然我错了,但你会在下面看到.这个问题是关于这两种风格的TVF的性能差异.首先,您需要查看代码.

这是多语句TVF：

IF OBJECT_ID('dbo.IsPalindrome') IS NOT NULL
DROP FUNCTION dbo.IsPalindrome;
GO
CREATE FUNCTION dbo.IsPalindrome
(
    @Word NVARCHAR(500)
) 
RETURNS @t TABLE
(
    IsPalindrome BIT NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @IsPalindrome BIT;
    DECLARE @LeftChunk NVARCHAR(250);
    DECLARE @RightChunk NVARCHAR(250);
    DECLARE @StrLen INT;
    DECLARE @Pos INT;
    SET @RightChunk = '';
    SET @IsPalindrome = 0;
    SET @StrLen = LEN(@Word) / 2;
    IF @StrLen % 2 = 1 SET @StrLen = @StrLen - 1;
    SET @Pos = LEN(@Word);
    SET @LeftChunk = LEFT(@Word,@StrLen);
    WHILE @Pos > (LEN(@Word) - @StrLen)
    BEGIN
        SET @RightChunk = @RightChunk + SUBSTRING(@Word,@Pos,1)
        SET @Pos = @Pos - 1;
    END
    IF @LeftChunk = @RightChunk SET @IsPalindrome = 1;
    INSERT INTO @t VALUES (@IsPalindrome);
    RETURN
END
GO

内联TVF：

IF OBJECT_ID('dbo.InlineIsPalindrome') IS NOT NULL
DROP FUNCTION dbo.InlineIsPalindrome;
GO
CREATE FUNCTION dbo.InlineIsPalindrome
(
    @Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    WITH Nums AS
    (
      SELECT
        N = number
      FROM
        dbo.Numbers
    )
    SELECT
      IsPalindrome =
        CASE
          WHEN EXISTS
          (
            SELECT N
            FROM Nums
            WHERE N <= L / 2
              AND SUBSTRING(S,N,1) <> SUBSTRING(S,1 + L - N,1)
          )
          THEN 0
          ELSE 1
        END
    FROM
      (SELECT LTRIM(RTRIM(@Word)),LEN(@Word)) AS v (S,L)
);
GO

上述函数中的Numbers表定义为：

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL 
);

注意：数字表没有任何索引,也没有主键,并且包含1,000,000行.

试验台临时表：

IF OBJECT_ID('tempdb.dbo.#Words') IS NOT NULL
DROP TABLE #Words;
GO
CREATE TABLE #Words 
(
    Word VARCHAR(500) NOT NULL
);

INSERT INTO #Words(Word) 
SELECT o.name + REVERSE(w.name)
FROM sys.objects o
CROSS APPLY (
    SELECT o.name
    FROM sys.objects o
) w;

在我的测试系统上,上面的INSERT导致将16900行插入到#Words表中.

为了测试这两种变化,我设置了STATISTICS IO,TIME ON;并使用以下内容：

SELECT w.Word,p.IsPalindrome
FROM #Words w
    CROSS APPLY dbo.IsPalindrome(w.Word) p
ORDER BY w.Word;


SELECT w.Word,p.IsPalindrome
FROM #Words w
    CROSS APPLY dbo.InlineIsPalindrome(w.Word) p
ORDER BY w.Word;

我期望InlineIsPalindrome版本明显更快,但是以下结果不支持该假设.

多语句TVF：

Table ‘#A1CE04C3’. Scan count 16896,logical reads 16900,physical reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0,logical reads 0,lob read-ahead reads 0.
Table ‘#Words’. Scan count 1,logical reads 88,lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1700 ms,elapsed time = 2022 ms.
SQL Server parse and compile time:
CPU time = 0 ms,elapsed time = 0 ms.

内联TVF：

Table ‘Numbers’. Scan count 1,logical reads 1272030,lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 137874 ms,elapsed time = 139415 ms.
SQL Server parse and compile time:
CPU time = 0 ms,elapsed time = 0 ms.

执行计划如下：

在这种情况下,为什么内联变量比多语句变量慢得多？

在回应@AaronBertrand的评论时,我修改了dbo.InlineIsPalindrome函数,以限制CTE返回的行与输入字的长度相匹配：

CREATE FUNCTION dbo.InlineIsPalindrome
(
    @Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    WITH Nums AS
    (
      SELECT
        N = number
      FROM
        dbo.Numbers
      WHERE 
        number <= LEN(@Word)
    )
    SELECT
      IsPalindrome =
        CASE
          WHEN EXISTS
          (
            SELECT N
            FROM Nums
            WHERE N <= L / 2
              AND SUBSTRING(S,L)
);

正如@MartinSmith建议的那样,我已经在dbo.Numbers表中添加了一个主键和聚簇索引,这肯定有助于并且更接近人们期望在生产环境中看到的内容.

现在重新运行上面的测试会产生以下统计信息：

交叉申请dbo.IsPalindrome(w.Word)p：

(17424 row(s) affected)
Table ‘#B1104853’. Scan count 17420,logical reads 17424,logical reads 90,lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1763 ms,elapsed time = 2192 ms.

dbo.FunctionIsPalindrome(w.Word)：

(17424 row(s) affected)
Table ‘Worktable’. Scan count 0,lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 328 ms,elapsed time = 424 ms.

交叉申请dbo.InlineIsPalindrome(w.Word)p：

(17424 row(s) affected)
Table ‘Numbers’. Scan count 1,logical reads 237100,lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 17737 ms,elapsed time = 17946 ms.

我在SQL Server 2012 SP3,v11.0.6020,Developer Edition上测试了这个.

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!