Multi-statement TVF vs Inline TVF Performance


17

Confrontando alcune delle risposte sul Palindrome question (solo 10k + utenti, poiché ho eliminato la risposta), sto ottenendo risultati confusi.

Ho proposto un multi-statement, schema-bound TVF che pensavo sarebbe stato più veloce di una funzione standard, che è.Avevo anche l'impressione che la TVF multi-statement sarebbe stata "in linea", anche se su questo punto mi sbaglio, come vedrai in seguito.Questa domanda riguarda la differenza di prestazioni di questi due stili di TVF.Per prima cosa, dovrai vedere il codice.

Ecco la TVF multi-statement:

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

The inline-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

La tabella Numbers nella funzione sopra è definita come:

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

Nota: la tabella dei numeri non ha indici e nessuna chiave primaria e contiene 1.000.000 di righe.

Una tabella provvisoria del banco di prova:

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;

Sul mio sistema di test, sopra INSERT risulta che sono state inserite 16.900 righe nella tabella #Words .

Per testare le due varianti, I SET STATISTICS IO, TIME ON; e utilizzare quanto segue:

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;

Mi aspettavo che la versione InlineIsPalindrome fosse notevolmente più veloce, tuttavia i seguenti risultati non supportano tale supposizione.

Multi-statement TVF:

Tabella '# A1CE04C3'.Conteggio scansioni 16896, letture logiche 16900, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella "#Words".Conteggio scansioni 1, letture logiche 88, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server:
Tempo CPU = 1700 ms, tempo trascorso = 2022 ms.
Analisi SQL Server e tempo di compilazione:
Tempo CPU = 0 ms, tempo trascorso = 0 ms.

In linea TVF:

Tabella 'Numeri'.Conteggio scansioni 1, letture logiche 1272030, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella "#Words".Conteggio scansioni 1, letture logiche 88, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server:
Tempo CPU = 137874 ms, tempo trascorso = 139415 ms.
Analisi SQL Server e tempo di compilazione:
Tempo CPU = 0 ms, tempo trascorso = 0 ms.

I piani di esecuzione sono simili a:

enter image description here

enter image description here

Perché la variante inline è molto più lenta della variante multi-statement, in questo caso?

In risposta a un commento di @AaronBertrand, ho modificato la funzione dbo.InlineIsPalindrome per limitare le righe restituite dal CTE in modo che corrispondano alla lunghezza della parola di input:

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)
);

Come suggerito da @MartinSmith, ho aggiunto una chiave primaria e un indice cluster alla tabella dbo.Numbers , che certamente aiuta e sarebbe più vicina a ciò che ci si aspetterebbe di vedere in un ambiente di produzione.

Se riesci a eseguire i test sopra ora, ottieni le seguenti statistiche:

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

(17424 righe (s) interessate)
Tabella '# B1104853'.Conteggio scansioni 17420, letture logiche 17424, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella "#Words".Conteggio scansioni 1, letture logiche 90, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server:
Tempo CPU = 1763 ms, tempo trascorso = 2192 ms.

dbo.FunctionIsPalindrome(w.Word) :

(17424 righe (s) interessate)
Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella "#Words".Conteggio scansioni 1, letture logiche 90, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server:
Tempo CPU = 328 ms, tempo trascorso = 424 ms.

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

(17424 righe (s) interessate)
Tabella 'Numeri'.Conteggio scansioni 1, letture logiche 237100, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella 'Worktable'.Conteggio scansioni 0, letture logiche 0, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.
Tabella "#Words".Conteggio scansioni 1, letture logiche 90, letture fisiche 0, letture read-ahead 0, letture logiche lob 0, lob letture fisiche 0, letture lob read-ahead 0.

Tempi di esecuzione di SQL Server:
Tempo CPU = 17737 ms, tempo trascorso = 17946 ms.

Sto testando questo su SQL Server 2012 SP3, v11.0.6020, Developer Edition.

Ecco la definizione della mia tabella dei numeri, con la chiave primaria e l'indice cluster:

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;
  0

I commenti non sono per discussioni estese;questa conversazione è stata [spostata in chat] (http://chat.stackexchange.com/rooms/39415/discussion-on-question-by-max-vernon-multi-statement-tvf-vs-inline-tvf-performan) . 07 mag. 162016-05-07 03:44:13

12

La tabella dei numeri è un heap ed è potenzialmente sottoposta a scansione completa ogni volta.

Aggiungere una chiave primaria in cluster su Number e provare quanto segue con un suggerimento forceseek per ottenere la ricerca desiderata.

Per quanto posso dire, questo suggerimento è necessario poiché SQL Server stima che il 27% della tabella corrisponderà al predicato (30% per il <= e ridotto al 27% per il <>).E quindi che dovrà solo leggere 3-4 righe prima di trovarne una che corrisponda e che possa uscire dal semi join.Quindi l'opzione di scansione è costata molto a buon mercato.Ma in effetti, se esistono palindromi, allora dovrà leggere l'intero tavolo, quindi questo non è un buon piano.

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

Con quei cambiamenti sul posto vola per me (prende 228ms)

enter image description here