Início / dba / Perguntas / 137775
Accepted
Hannah Vernon
Asked: 2016-05-07 06:08:16 +0800 CST

Desempenho de TVF com várias instruções vs. TVF em linha

  • 772

Comparando algumas das respostas na pergunta do Palindrome (mais de 10 mil usuários apenas, desde que excluí a resposta), estou obtendo resultados confusos.

Propus um TVF com várias instruções e vinculado ao esquema, que pensei que seria mais rápido do que executar uma função padrão, o que é. Também tive a impressão de que o TVF com várias instruções seria "embutido", embora eu esteja errado nessa contagem, como você verá abaixo. Esta pergunta é sobre a diferença de desempenho desses dois estilos de TVF. Primeiro, você precisa ver o código.

Aqui está o TVF de várias declarações:

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

O TVF em linha:

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

A Numberstabela na função acima é definida como:

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

Observação: a tabela de números não possui nenhum índice nem chave primária e contém 1.000.000 linhas.

Uma mesa temporária de teste:

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;

No meu sistema de teste, os INSERTresultados acima em 16.900 linhas sendo inseridas na #Wordstabela.

Para testar as duas variações, eu SET STATISTICS IO, TIME ON;e uso o seguinte:

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;

Eu esperava que a InlineIsPalindromeversão fosse significativamente mais rápida, porém os resultados a seguir não suportam essa suposição.

TVF multi-estado:

Tabela '#A1CE04C3'. Contagem de varredura 16896, leituras lógicas 16900, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Table 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 88, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server:
tempo de CPU = 1700 ms, tempo decorrido = 2022 ms.
Tempo de análise e compilação do SQL Server: tempo
de CPU = 0 ms, tempo decorrido = 0 ms.

TVF em linha:

Tabela 'Números'. Contagem de varredura 1, leituras lógicas 1272030, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 88, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server:
tempo de CPU = 137874 ms, tempo decorrido = 139415 ms.
Tempo de análise e compilação do SQL Server: tempo
de CPU = 0 ms, tempo decorrido = 0 ms.

Os planos de execução se parecem com:

insira a descrição da imagem aqui

insira a descrição da imagem aqui

Por que a variante inline é muito mais lenta do que a variante de várias instruções, neste caso?

Em resposta a um comentário de @AaronBertrand, modifiquei a dbo.InlineIsPalindromefunção para limitar as linhas retornadas pelo CTE para corresponder ao comprimento da palavra de entrada:

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, N, 1) <> SUBSTRING(S, 1 + L - N, 1)
          )
          THEN 0
          ELSE 1
        END
    FROM
      (SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);

Como sugeriu @MartinSmith, adicionei uma chave primária e um índice clusterizado à dbo.Numberstabela, o que certamente ajuda e estaria mais próximo do que se esperaria ver em um ambiente de produção.

Executar novamente os testes acima agora resulta nas seguintes estatísticas:

CROSS APPLY dbo.IsPalindrome(w.Word) p:

(17424 linhas afetadas)
Tabela '#B1104853'. Contagem de varredura 17420, leituras lógicas 17424, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Table 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 90, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server:
tempo de CPU = 1763 ms, tempo decorrido = 2192 ms.

dbo.FunctionIsPalindrome(w.Word):

(17424 linhas afetadas)
Tabela 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 90, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server:
tempo de CPU = 328 ms, tempo decorrido = 424 ms.

CROSS APPLY dbo.InlineIsPalindrome(w.Word) p:

(17424 linhas afetadas)
Tabela 'Números'. Contagem de varredura 1, leituras lógicas 237100, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela 'Worktable'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.
Tabela '#Words'. Contagem de varredura 1, leituras lógicas 90, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server:
tempo de CPU = 17737 ms, tempo decorrido = 17946 ms.

Estou testando isso no SQL Server 2012 SP3, v11.0.6020, Developer Edition.

Aqui está a definição da minha tabela de números, com a chave primária e o índice clusterizado:

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL 
        CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
);

;WITH n AS
(
    SELECT v.n 
    FROM (
        VALUES (1) 
            ,(2) 
            ,(3) 
            ,(4) 
            ,(5) 
            ,(6) 
            ,(7) 
            ,(8) 
            ,(9) 
            ,(10)
        ) v(n)
)
INSERT INTO dbo.Numbers(Number)
SELECT ROW_NUMBER() OVER (ORDER BY n1.n)
FROM n n1
    , n n2
    , n n3
    , n n4
    , n n5
    , n n6;
sql-server sql-server-2012

1 respostas

  1. Best Answer

    Sua tabela de números é uma pilha e potencialmente está sendo totalmente verificada a cada vez.

    Adicione uma chave primária em cluster Numbere tente o seguinte com uma forceseekdica para obter a busca desejada.

    Tanto quanto eu posso dizer, esta dica é necessária, pois o SQL Server apenas estima que 27% da tabela corresponderá ao predicado (30% para o <=e reduzido para 27% pelo <>). E, portanto, só terá que ler 3-4 linhas antes de encontrar uma que corresponda e possa sair da semi-junção. Portanto, a opção de digitalização tem um custo muito baixo. Mas, na verdade, se algum palíndromo existir, ele terá que ler a tabela inteira, portanto, esse não é um bom plano.

    CREATE FUNCTION dbo.InlineIsPalindrome
(
    @Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    WITH Nums AS
    (
      SELECT
        N = number
      FROM
        dbo.Numbers WITH(FORCESEEK)
    )
    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

    Com essas mudanças, ele voa para mim (leva 228ms)

    insira a descrição da imagem aqui

    • 12

relate perguntas