Multi-Statement TVF vs Inline TVF Performance

Porównując niektóre odpowiedzi na pytanie Palindrome (tylko 10 000 użytkowników, ponieważ usunąłem odpowiedź), otrzymuję mylące wyniki.

Zaproponowałem wielowątkową, związaną ze schematem TVF, która, jak sądzę, byłaby szybsza niż uruchamianie standardowej funkcji, którą jest. Miałem również wrażenie, że TVF z wieloma stwierdzeniami będzie „wbudowany”, chociaż w tej kwestii się mylę, jak zobaczycie poniżej. To pytanie dotyczy różnicy w wydajności tych dwóch stylów TVF. Najpierw musisz zobaczyć kod.

Oto wielowątkowy 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

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

NumbersTabela w powyższej funkcji określa się jako:

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

Uwaga: Tabela liczb nie ma żadnych indeksów ani klucza podstawowego i zawiera 1 000 000 wierszy.

Stół tymczasowy do łóżek testowych:

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;

W moim systemie testowym powyższe INSERTpowoduje wstawienie 16 900 wierszy do #Wordstabeli.

Aby przetestować dwie odmiany, SET STATISTICS IO, TIME ON;korzystam z następujących opcji:

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;

Spodziewałem się, że InlineIsPalindromewersja będzie znacznie szybsza, jednak poniższe wyniki nie potwierdzają tego przypuszczenia.

Multi-Statement TVF:

Tabela „# A1CE04C3”. Liczba skanów 16896, logiczne odczyty 16900, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „Tabela robocza”. Liczba skanów 0, logiczne odczyty 0, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „#Words” Liczba skanów 1, logiczne odczyty 88, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Czasy wykonania programu SQL Server:
czas procesora = 1700 ms, czas, który upłynął = 2022 ms.
Czas analizy i kompilacji programu SQL Server: czas
procesora = 0 ms, czas, który upłynął = 0 ms.

Inline TVF:

Tabela „Liczby”. Liczba skanów 1, logiczne odczyty 1272030, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „Tabela robocza”. Liczba skanów 0, logiczne odczyty 0, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „#Words” Liczba skanów 1, logiczne odczyty 88, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Czasy wykonania programu SQL Server:
czas procesora = 137874 ms, czas, który upłynął = 139415 ms.
Czas analizy i kompilacji programu SQL Server: czas
procesora = 0 ms, czas, który upłynął = 0 ms.

Plany wykonania wyglądają następująco:

Dlaczego w tym przypadku wariant wbudowany jest o wiele wolniejszy niż wariant wielowyrazowy?

W odpowiedzi na komentarz @AaronBertrand zmodyfikowałem tę dbo.InlineIsPalindromefunkcję, aby ograniczyć liczbę wierszy zwracanych przez CTE do długości słowa wejściowego:

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

Jak sugerował @MartinSmith, dodałem do dbo.Numberstabeli klucz podstawowy i indeks klastrowany , co z pewnością pomaga i byłoby bliższe temu, czego można oczekiwać w środowisku produkcyjnym.

Ponowne uruchomienie powyższych testów daje teraz następujące statystyki:

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

(Dotyczy 17424 wierszy)
Tabela „# B1104853”. Liczba skanów 17420, odczyt logiczny 17424, odczyt fizyczny 0, odczyt z wyprzedzeniem 0, log logiczny z odczytem 0, zapis fizyczny lob 0, odczyt z logem z wyprzedzeniem 0.
Tabela „Tabela robocza”. Liczba skanów 0, logiczne odczyty 0, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „#Words” Liczba skanów 1, logiczne odczyty 90, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Czasy wykonania programu SQL Server:
czas procesora = 1763 ms, czas, który upłynął = 2192 ms.

dbo.FunctionIsPalindrome(w.Word):

(Wpływają na to 17424 wiersze)
Tabela „Tabela robocza”. Liczba skanów 0, logiczne odczyty 0, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „#Words” Liczba skanów 1, logiczne odczyty 90, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Czasy wykonania programu SQL Server:
czas procesora = 328 ms, czas, który upłynął = 424 ms.

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

(17424 wiersz (ów) dotyczy)
Tabela „Liczby”. Liczba skanów 1, logiczne odczyty 237100, fizyczne odczyty 0, odczytywanie z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „Tabela robocza”. Liczba skanów 0, logiczne odczyty 0, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.
Tabela „#Words” Liczba skanów 1, logiczne odczyty 90, fizyczne odczyty 0, odczyt z wyprzedzeniem 0, lob logiczne odczyty 0, lob fizyczne odczyty 0, lob odczyty z wyprzedzeniem 0.

Czasy wykonania programu SQL Server:
czas procesora = 17737 ms, czas, który upłynął = 17946 ms.

Testuję to na SQL Server 2012 SP3, v11.0.6020, Developer Edition.

Oto definicja mojej tabeli liczb z kluczem podstawowym i indeksem klastrowym:

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;

Tabela liczb jest stertą i jest potencjalnie w pełni skanowana za każdym razem.

Dodaj klastrowany klucz podstawowy Numberi spróbuj wykonać następujące czynności ze forceseekwskazówką, aby uzyskać pożądane wyszukiwanie.

O ile wiem, ta wskazówka jest potrzebna, ponieważ SQL Server tylko szacuje, że 27% tabeli będzie pasować do predykatu (30% dla <=i zmniejszone do 27% o <>). Dlatego też musi tylko odczytać 3-4 wiersze, zanim znajdzie taki, który pasuje i może wyjść z pół-łączenia. Opcja skanowania jest więc bardzo tania. Ale tak naprawdę, jeśli istnieją jakieś palindromy, będzie musiał przeczytać całą tabelę, więc nie jest to dobry plan.

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

Po wprowadzeniu tych zmian leci dla mnie (zajmuje 228 ms)