Jasne i ciemne strony baz danych

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

SQL Server – Konwersja kodowania CP620 (Mazovia) na CP1250 w CLR

Posted by C3PO w dniu 22 czerwca 2010


W moim pierwszym sesnownym wpisie na tym blogu (https://sqlwars.wordpress.com/2010/06/16/sql-server-jak-wylistowac-dostepne-kodowania-funkcja-tabelaryczna-clr/) podałem rozwiązanie problemu konwersji ze standardu Mazovia do Windows-1250 zaimplementowane w T-SQL. Obiecałem sobie, że przetestuję też rozwiązanie alternatywne stworzone w CLR. Oto ono:

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(
        DataAccess=DataAccessKind.None,
        SystemDataAccess=SystemDataAccessKind.None,
        IsDeterministic=true,
        Name="ufn_clr_MazoviaTo1250")]
    [return:SqlFacet(MaxSize=-1)]
    public static SqlString ufn_clr_MazoviaTo1250(
      [SqlFacet(MaxSize=-1)] SqlString Input
    )
    {
      return Input.Value
        .Replace((char)260,  (char)323) //Ń
        .Replace((char)377,  (char)260) //Ą
        .Replace((char)321,  (char)211) //Ó
        .Replace((char)347,  (char)321) //Ł
        .Replace((char)8226, (char)262) //Ć
        .Replace((char)144,  (char)280) //Ę
        .Replace((char)152,  (char)346) //Ś
        .Replace((char)160,  (char)377) //Ź
        .Replace((char)711,  (char)379) //Ż
        .Replace((char)8224, (char)261) //ą
        .Replace((char)356,  (char)263) //ć
        .Replace((char)8216, (char)281) //ę
        .Replace((char)8217, (char)322) //ł
        .Replace((char)164,  (char)324) //ń
        .Replace((char)728,  (char)243) //ó
        .Replace((char)382,  (char)347) //ś
        .Replace((char)166,  (char)378) //ź
        .Replace((char)167,  (char)380); //ż
    }
};

Czemu tak na piechotę? Bo niestety, na liście obsługiwanych przez klasy z przestrzeni System.Text stron kodowych nie ma CP620 (Mazovia).

Trudność? Liczby oznaczające kody poszczególnych literek się zmieniły w porównaniu z T-SQL. Ale to dlatego, że napisy wchodzące do metod w .NET są kodowane w Unicode (UTF-16). Stąd potrzeba odrobiny cierpliwości i użycia w SQL Server funkcji UNICODE, by dowiedzieć się, jakie są kody liter i odpowiadających im znaków z kodowania Mazovia w kodowaniu UTF-16. Czyli, jeśli chciałem sprawdzić, jaki kod ma literka w UTF-16, robiłem coś takiego:

SELECT UNICODE(N'Ą');

W CLR miało być szybciej (tak twierdził jeden z uczestników dyskusji na forum portalu WSS.pl). I jest szybciej (ale musiałem sprawdzić ;-)). Wykonałem banalny test porównujący wydajność konwersji na 100 tysiącach wierszy przy użyciu obu funkcji (T-SQL i CLR):

IF OBJECT_ID('dbo.TestTableMazovia', 'U') IS NOT NULL
  DROP TABLE dbo.TestTableMazovia;
GO
CREATE TABLE dbo.TestTableMazovia (
  SampleText varchar(max) NOT NULL
);
GO
WITH CTE AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM CTE
  WHERE n < 100000
)
INSERT INTO dbo.TestTableMazovia (SampleText)
SELECT 'Ź•śĄŁ˜ ˇ†Ť‘’¤˘ž¦§' FROM CTE OPTION (MAXRECURSION 0);
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DECLARE @t datetime = GETDATE();
SELECT dbo.ufn_MazoviaTo1250(SampleText) FROM dbo.TestTableMazovia;
SELECT DATEDIFF(ms, @t, GETDATE());
GO
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DECLARE @t datetime = GETDATE();
SELECT dbo.ufn_clr_MazoviaTo1250(SampleText) FROM dbo.TestTableMazovia;
SELECT DATEDIFF(ms, @t, GETDATE());
GO

Wynik:
T-SQL – 14.5 s
CLR – 2.5 s

Okazało się, że rozwiązanie T-SQL jest w tym przypadku około 6-7 razy wolniejsze (na wynik testu nie wpłynęła także zmiana typu danych kolumny na nvarchar(max) czy na varchar(100) – ani rozmiar, ani Unicode’owość nie zmieniły wyników pomiaru czasu trwania zapytań). Sprawdziłem obie funkcje także pod kątem działania na jednym, ale dość długim napisie (ponad 8000 znaków). Wynik niemal taki sam (XXX ms do XX ms).

Jedyne ale jest takie, że łatwiej jest wygenerować funkcję w T-SQL, jeśli mam daną tabelę z przejściami między poszczególnymi stronami kodowymi. Ale jeśli priorytetem jest wydajność, wydaje się, że CLR działa lepiej.

Advertisements

komentarze 4 to “SQL Server – Konwersja kodowania CP620 (Mazovia) na CP1250 w CLR”

  1. What’s up everyone, it’s my first go to see at this web site, and article is truly fruitful in favor of
    me, keep up posting such articles or reviews.

  2. This article is genuinely a good one it helps new web people, who are wishing for blogging.

  3. Have you ever considered about adding a little bit more than just your articles?

    I mean, what you say is fundamental and all. But imagine if you added some great graphics or video
    clips to give your posts more, „pop”! Your content is excellent but with
    images and video clips, this website could certainly be
    one of the best in its niche. Superb blog!

  4. n/a/ said

    This web site truly has all of the info I wanted
    about this subject and didn’t know who to ask.

Skomentuj

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

Logo WordPress.com

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

Zdjęcie z Twittera

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d blogerów lubi to: