Jasne i ciemne strony baz danych

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

Posts Tagged ‘konwersja’

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.

Posted in MS SQL Server | Otagowane: , , , | 4 komentarze »

SQL Server – Jak wylistować dostępne kodowania funkcją tabelaryczną CLR?

Posted by C3PO w dniu 16 czerwca 2010


Ostatnimi czasy przesiaduję trochę na portalu WSS.pl. Na forum tegoż portalu wywiązała się całkiem interesująca dyskusja nad konwersją pomiędzy stroną kodową Mazovia (CP620) a stroną Windows-1250. Cały wątek jest tutaj. Postanowiłem drążyć temat dalej. Na razie stanęliśmy na tym, że mamy funkcję T-SQL:

IF OBJECT_ID('dbo.ufn_MazoviaTo1250', 'FN') IS NOT NULL
  DROP FUNCTION dbo.ufn_MazoviaTo1250;
GO
CREATE FUNCTION dbo.ufn_MazoviaTo1250 (@String varchar(maX))
RETURNS varchar(max)
WITH SCHEMABINDING
AS
BEGIN
  SET @String = @String COLLATE Polish_BIN;
  RETURN
    REPLACE (
      REPLACE (
        REPLACE (
          REPLACE (
            REPLACE (
              REPLACE (
                REPLACE (
                  REPLACE (
                    REPLACE (
                      REPLACE (
                        REPLACE (
                          REPLACE (
                            REPLACE (
                              REPLACE (
                                REPLACE (
                                  REPLACE (
                                    REPLACE (
                                      REPLACE (
                                        @String, CHAR(165), CHAR(209) -- Ń
                                      ), CHAR(143), CHAR(165)         -- Ą
                                    ), CHAR(163), CHAR(211)           -- Ó
                                  ), CHAR(156), CHAR(163)             -- Ł
                                ), CHAR(149), CHAR(198)               -- Ć
                              ), CHAR(144), CHAR(202)                 -- Ę
                            ), CHAR(152), CHAR(140)                   -- Ś
                          ), CHAR(160), CHAR(143)                     -- Ź
                        ), CHAR(161), CHAR(175)                       -- Ż
                      ), CHAR(134), CHAR(185)                         -- ą
                    ), CHAR(141), CHAR(230)                           -- ć
                  ), CHAR(145), CHAR(234)                             -- ę
                ), CHAR(146), CHAR(179)                               -- ł
              ), CHAR(164), CHAR(241)                                 -- ń
            ), CHAR(162), CHAR(243)                                   -- ó
          ), CHAR(158), CHAR(156)                                     -- ś
        ), CHAR(166), CHAR(159)                                       -- ź
      ), CHAR(167), CHAR(191)                                         -- ż
    ) COLLATE database_default;
END;
GO
-- Test
SELECT dbo.ufn_MazoviaTo1250 ('Ź•śĄŁ˜ ˇ†Ť‘’¤˘ž¦§'); 

Jeden z uczestników dyskusji zasugerował, by problem rozwiązać za pomocą funkcji napisanej w .NET (CLR). Wydaje się to być rozsądnym posunięciem. W końcu CLR może się spisywać lepiej w przypadku operacji na tekście (zwłaszcza długim). Na pierwszy ogień jednak postanowiłem rzucić sobie zadanie – napisanie funkcji do listowania dostępnych kodowań z poziomu .NET. Dzięki temu dowiem się, jakie kodowania mogę wykorzystać w konwersjach z użyciem metod klas z przestrzeni System.Text (np. klasy Encoding). Czyli rozchodzi się o to, by wiedzieć, jakie strony kodowe mogę konwertować niekoniecznie znak po znaku.

Po chwili skrobania wyszło mi coś takiego:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName="FillEncodings",
        Name="ufn_CLR_GetEncodings",
        TableDefinition="CodePage int,
          EncodingName nvarchar(255),
          DisplayName nvarchar(255)"
        )]
    public static IEnumerable ufn_CLR_GetEncodings()
    {
        EncodingInfo[] Encodings = Encoding.GetEncodings();
        return Encodings;
    }
    public static void FillEncodings(
      object Obj,
      out int CodePage,
      out string EncodingName,
      out string DisplayName)
    {
        EncodingInfo e = (EncodingInfo) Obj;
        EncodingName = e.Name;
        DisplayName = e.DisplayName;
        CodePage = e.CodePage;
    }
};

W razie wątpliwości, co należy z tym kodem zrobić, zajrzyj na przykład tutaj: http://geekswithblogs.net/frankw/archive/2008/05/03/a-quick-walk-through-of-clr-integration-with-sql-server.aspx.

Test wdrożonej funkcji wygląda tak:

SELECT * FROM dbo.ufn_CLR_GetEncodings() ORDER BY CodePage;

I okazuje się, że Mazovia oczywiście nie znalazła się na liście. No cóż, pozostaje zatem żmudna podmiana znak po znaku. Ale o tym już napiszę następnym razem, jak tylko napiszę właściwy kod i przetestuję szybkość rozwiązania CLR vs. szybkość rozwiązania T-SQL. Ciąg dalszy nastapi ;-)

 

Posted in MS SQL Server | Otagowane: , , , , | 7 komentarzy »