Jasne i ciemne strony baz danych

… w szczególności MS SQL Server ;-)

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

Posted by C3PO w dniu 9 lipca 2010


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.

Wykres 1

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.

Wykres 2

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.
Reklamy

Komentarzy 14 to “SQL Server – REPLACE (T-SQL vs. CLR)”

  1. Marek Adamczuk said

    Sprawdź jeszcze, czy na pewno ten odpowiednik jest identyczny funkcjonalnie. Z testów, które kiedyś robiliśmy z Pawłem Potasińskim wyszło nam, że jest case sensitive, a T-SQL REPLACE uwzględnia collation. Test może dać zatem różne wyniki zależnie od collation. Pewnie collation z serii BIN czy BIN2 dadzą bardziej zbliżone rezultaty do CLR, bo nie muszą korzystać z tabel porównań.

  2. C3PO said

    Marek, dzięki za komentarz. Sprawdzę to, ale już nawet na tym etapie wychodzi na to, że do codziennych zastosowań systemowa funkcja zupełnie wystarczy i CLR nam fajerwerków nie oferuje.

  3. C3PO said

    Marek, sprawdziłem i funkcjonalnie nie jest to taki sam REPLACE. Będę musiał powtórzyć ten test, tym razem CLR będzie używał metody Regex.Replace, która oferuje opcję IgnoreCase.

    • Marek Adamczuk said

      Super. Jestem ciekawy wyników. Oczywiście funkcjonalnie i tak nie powtórzysz REPLACE’a, bo oprócz wielkości liter collation steruje jeszcze całym mnóstwem innych śmieci takich jak akcenty, długość, kana i stroną kodową dla nie unicode. O to nawet RegExpa nie podejrzewam :). Oczywiście, dla konkretnego zastosowania brak uwzględnienia collation może być wygodny. Chociażby przy tłumaczeniu stron kodowych na piechotę, co już ćwiczyłeś.

  4. […] początku bieżącego miesiąca podjąłem próbę przetestowania pod kątem wydajności napisanego w CLR zamiennika dla systemowej funkcji REPLACE w […]

  5. […] początku bieżącego miesiąca podjąłem próbę przetestowania pod kątem wydajności napisanego w CLR zamiennika dla systemowej funkcji REPLACE w […]

  6. test said

    This article will help the internet visitors for creating new web site or even a blog from start to
    end.

  7. Thanks , I have recently been looking for info approximately this topic for a long time and
    yours is the greatest I’ve came upon till now. However, what in regards to the bottom line? Are you positive about the supply?

  8. Hello! I understand this is kind of off-topic however I
    had to ask. Does building a well-established blog such as yours require a large amount of
    work? I’m brand new to blogging however I do write
    in my journal everyday. I’d like to start a blog so I can share my personal experience and
    feelings online. Please let me know if you have any ideas or tips for
    new aspiring bloggers. Appreciate it!

  9. Oh my goodness! Awesome article dude! Thank you, However
    I am experiencing troubles with your RSS. I don’t know the reason why I can’t join it.
    Is there anybody else having identical RSS issues? Anybody who
    knows the answer can you kindly respond? Thanx!!

  10. What’s up to all, how is all, I think every one is getting more from this site,
    and your views are good for new users.

  11. Greetings! I know this is kinda off topic nevertheless I’d figured
    I’d ask. Would you be interested in exchanging links or maybe guest writing a
    blog post or vice-versa? My website addresses a lot of the same topics as yours and I believe we could greatly benefit from
    each other. If you happen to be interested feel free to send me an e-mail.
    I look forward to hearing from you! Wonderful blog by the
    way!

  12. curatare canapele

    SQL Server – REPLACE (T-SQL vs. CLR) « Jasne i ciemne strony baz danych

  13. Henrietta said

    I love it when people get together and share views.
    SQL Server – REPLACE (T-SQL vs. CLR) Jasne i ciemne strony baz danych – curatenie
    apartamente timisoara (Henrietta)

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

 
%d blogerów lubi to: