W moim pierwszym sesnownym wpisie na tym blogu (http://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.
