SQL Server – REPLACE (T-SQL vs. CLR)

Geneza

Jakiś czas temu czytałem przemyślenia Adama Machanica na temat możliwości zastąpienia niektórych funkcji systemowych SQL Servera ich odpowiednikami napisanymi .NET (CLR). Testy były ciekawe, a wyniki obiecujące. Ja, że jestem niedowiarkiem, wichrzycielem i w ogóle, zżerająca mnie rdza nie daje spokoju, postanowiłem zrobić mały test na własną rękę.

Aktorzy przedstawienia

Jako jeden z przykładów została podana funkcja REPLACE, która w T-SQL służy do zastępowania we wskazanym tekście wskazanego tekstu wskazanym tekstem :-) Znaczy się, sygnatura jest jak niżej:

REPLACE (tekst_wejściowy, tekst_do_zastąpienia, tekst_zastępujący)

Napisałem sobie taki kawałek kodu C#, który jest niejako odpowiednikiem funkcjonalnym funkcji REPLACE dostępnej w SQL Server:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
  [Microsoft.SqlServer.Server.SqlFunction]
  [return: SqlFacet(MaxSize = -1)]
  public static SqlString ufn_clr_Replace(
      [SqlFacet(MaxSize = -1)] 
        SqlString Input,
      [SqlFacet(MaxSize = -1)] 
        SqlString ToReplace,
      [SqlFacet(MaxSize = -1)] 
        SqlString Replacement)
  {
    return new SqlString(Input.Value.Replace(ToReplace.Value, Replacement.Value));
  }
};

Kodu chyba nie ma co tłumaczyć (funkcja skalarna, pobiera trzy parametry typu nvarchar(max) i zwraca napis typu nvarchar(max) – tu użyłem atrybutu SqlFacet, by wymusić typ max po stronie SQL Servera). Kod skompilowałem, dodałem assembly (PERMISSION_SET oczywiście SAFE) do bazy o znajomo brzmiącej nazwie test (moja baza-poligon, w simple recovery). Przykładowe użycie tej funkcji:

SELECT test.dbo.ufn_clr_Replace('Ala ma kota', 'Ala', 'Ola') -- wynik: Ola ma kota

Benchmark

Pomyślałem, że test powinien zbadać, jak wypada ta funkcja CLR-owa ufn_clr_Replace w porównaniu z T-SQL-ową funkcją REPLACE, gdzie kryterium oczywiście będzie czas wykonywania operacji przy zmiennych długościach napisów i zmiennej ilości znaków do zastąpienia. Aby taki test przeprowadzić, napisałem kawałek kodu T-SQL:

USE test;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
  DROP TABLE dbo.TestTable;
GO
PRINT '-- Creating dbo.TestTable';
CREATE TABLE dbo.TestTable (
  SampleText nvarchar(max) NOT NULL
);
GO
IF OBJECT_ID('dbo.ReplaceTestResults', 'U') IS NOT NULL
  DROP TABLE dbo.ReplaceTestResults;
GO
PRINT '-- Creating dbo.ReplaceTestResults';
CREATE TABLE dbo.ReplaceTestResults (
  FunctionName sysname NOT NULL,
  StringLength int NOT NULL,
  CharsToReplace int NOT NULL,
  Duration int NOT NULL
);
GO
DECLARE
  @i int,
  @j int = 10,
  @t datetime,
  @fake nvarchar(max),
  @ToReplace nvarchar(max) = N'a',
  @ReplaceWith nvarchar(max) = N'x';
WHILE @j <= 1000
BEGIN 
  SET @i = 1;
  WHILE @i <= @j
  BEGIN
    PRINT '-- Testing ' + CONVERT(varchar(4), @j) + ' chars, '
      + CONVERT(varchar(4), @i) + ' to replace';
    WITH CTE AS (
      SELECT 1 AS n
      UNION ALL
      SELECT n + 1
      FROM CTE
      WHERE n < 1000
    )
    INSERT INTO dbo.TestTable (SampleText)
    SELECT REPLICATE('a', @i) + REPLICATE('b', @j - @i)
    FROM CTE OPTION (MAXRECURSION 0);
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    DBCC FREEPROCCACHE WITH NO_INFOMSGS;
    SET @t = GETDATE();
    SELECT @fake = dbo.ufn_clr_Replace(
      SampleText, @ToReplace, @ReplaceWith
    )
    FROM dbo.TestTable;
    INSERT INTO dbo.ReplaceTestResults (
      FunctionName, StringLength, CharsToReplace, Duration
    )
    SELECT 'ufn_clr_Replace', @j, @i, DATEDIFF(ms, @t, GETDATE());
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    DBCC FREEPROCCACHE WITH NO_INFOMSGS;
    SET @t = GETDATE();
    SELECT @fake = REPLACE(SampleText, @ToReplace, @ReplaceWith)
    FROM dbo.TestTable;
    INSERT INTO dbo.ReplaceTestResults (
      FunctionName, StringLength, CharsToReplace, Duration
    )
    SELECT 'REPLACE', @j, @i, DATEDIFF(ms, @t, GETDATE());
    TRUNCATE TABLE dbo.TestTable;
    SET @i = @i + CASE WHEN @i = 1 THEN 9 ELSE 10 END;
  END;
  SET @j = @j + 10;
END;

Słowo wyjaśnienia:

• tworzę tabelę TestTable, do której wrzucam na początku każdej iteracji na nowo 1000 rekordów, ale za każdym razem wartości w kolumnie SampleText są inne (zmienia się długość – @j oraz ilość znaków do zastąpienia – @i),
• w każdej iteracji zbieram czas wykonania zapytania zwracającego wszystkie rekordy z tabeli TestTable potraktowane porównywanymi funkcjami T-SQL i CLR,
• aby wyniki były niezależne od tego, co siedzi w cache’ach, opróżniam przed każdym testem buffer cache i plan cache (polecenia DBCC),
• po każdej iteracji czyszczę tabelę TestTable (TRUNCATE TABLE).

Po wszystkim mam wyniki zapisane w tabeli ReplaceTestResults.

Na mojej maszynie (laptop Core 2 Duo, 4GB RAM, 64-bit Windows 7 i SQL Server 2008 R2 wersja RTM) test trwał około godziny czasu. Powtórzyłem go kilka razy, żeby sprawdzić, czy wyniki są powtarzalne (i są).

The winner is…

Wyniki można by było pewnie zaprezentować na sto różnych sposobów. Ja postanowiłem użyć dwóch wykresów.

Powyższy wykres pokazuje, w ilu procentach przypadków funkcja CLR działała szybciej, niż funkcja T-SQL w zależności od długości napisów. Widać, że równowaga zaczyna się gdzieś w okolicach 500-550 znaków.

Powyższy wykres pokazuje, w ilu procentach przypadków  funkcja CLR działała szybciej, niż funkcja T-SQL w zależności od ilości zastępowanych znaków. Od 400 znaków w zasadzie nie ma już wątpliwości, które rozwiązanie działało szybciej.

Czy można zatem powiedzieć wprost, które rozwiązanie (T-SQL czy CLR) jest lepsze? Oczywiście, NIE. Gdy tekst wejściowy jest krótki lub do zastąpienia jest niewiele znaków, stara, dobra funkcja REPLACE działa szybciej, niż jej CLR-owy odpowiednik. Ale jeżeli mamy długi tekst (setki lub tysiące znaków), a dodatkowo ilość znaków do zastąpienia jest także pokaźna (setki), opłaca sie mieć w arsenale funkcję CLR.

What’s next?

Co można zrobić dalej?

• porównać wydajność REPLACE i funkcji CLR napisanej z wykorzystaniem klasy RegEx (wyrażenia regularne)
• porównać wydajność innych przypadków funkcji systemowych (SUBSTRING, STUFF, PATINDEX,…) z ich odpowiednikami CLR
• porównać, ile zasobów zużywają zapytania wykorzystujące odpowiedniki T-SQL i CLR
• i oczywiście – można przeprowadzić powyższy test lepiej, dokładniej, dogłębniej i dać mi znać, gdzie popełniłem błędy w sztuce.