MS SQL Server

CLR integration (lub SQL-CLR) to według mnie jedna z ważniejszych grup funkcjonalności dostarczanych przez SQL Server 2005. Jest to najprościej mówiąc rozszerzenie modelu programistycznego bazy danych o obsługiwany natywnie Microsoft .NET Framework 2.0 Common Language Runtime (CLR). W wersji SQL Server 2000 (i wcześniejszych) CLR nie był w ogóle obsługiwany (ani przez extended stored procedures, ani przez OLE Automation). Open Data Services (ODS) umożliwiało co prawda wykorzystanie C/C++, ale była z tym cała masa problemów.

SQL-CLR pozwala na implementowanie procedur, funkcji, triggerów, agregatów i definiowanie nowych typów danych w językach .NETowskich. Występują oczywiście pewne naturalne ograniczenia w integracji SQL Servera ze środowiskiem zarządzalnym .NET, jednak możliwości nadal są bardzo duże.

SQL-CLR to także nowy arsenał środków w obszarze rozwiązań bazodanowych zorientowanych na wysoką wydajność. Developer jest w stanie wybrać, czy daną funkcjonalność zrealizować w T-SQL, czy może większą efektywność wykaże w danym przypadku implementacja CLR. Umiejętność dokonania trafnego wyboru jest więc całkiem istotna.

Ogólne zalecenia stanowią, że w porównaniu do CLR - T-SQL oferuje szybszy dostęp do danych i wydajniej wykonuje operacje na zbiorach rekordów. CLR natomiast to zasadniczo lepsze rozwiązanie do obliczeń numerycznych. Jednak jak duże mogą być różnice wydajnościowe? Ile można zyskać lub stracić stosując CLR zamiast czystego T-SQL? Na tego typu pytania dokumentacja i metodyki nie odpowiadają. Trzeba więc to zrobić we własnym zakresie - postaram się poniżej zademonstrować różnice wydajnościowe między implementacjami T-SQL i CLR na dwóch przykładach.

Środowisko testowe zakłada istnienie tabeli Dane_adresowe:

CREATE TABLE Dane_adresowe
(
wiersz INT IDENTITY(1,1) PRIMARY KEY,
nazwa_glowna NVARCHAR(100),
nazwa_skr NVARCHAR(100),
miejscowosc NVARCHAR(100),
adres NVARCHAR(100),
nr_lokalu NVARCHAR(100),
nip NVARCHAR(100),
kod_poczt NVARCHAR(100)
);

Dodatkowo występuje tabela tymczasowa #random z losowymi kombinacjami dwóch identyfikatorów wiersz z tabeli Dane_adresowe:

CREATE TABLE #random
(
wiersz1 INT,
wiersz2 INT
);

Tabela ta jest wypełniana odpowiednią ilością rekordów przez zapytanie generujące przypadkowe złączenia dwóch wartości kolumny wiersz (jego logika nie jest teraz istotna). Czasy poszczególnych testów są liczone jako średnia za 3 próby. Aby każdy pomiar odbył się w takich samych warunkach serwera, między poszczególnymi próbami uruchamiane są komendy:

DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS

W pierwszym przykładzie sprawdzimy wydajność sekwencyjnego dostępu do danych z T-SQL i CLR. Przygotowałem dwie funkcje skalarne (ich implementacje zostały tak wykonane i uproszczone, aby maksymalnie sobie odpowiadały):

- FnDataAccess_TSQL (implementacja T-SQL, kod do pobrania: tutaj)
- FnDataAccess_CLR (implementacja CLR-C#, kod do pobrania: tutaj)

Obie funkcje działają oczywiście w identyczny sposób - odczytują dane z dwóch rekordów z tabeli Dane_adresowe (dwoma parametrami wejściowymi są identyfikatory wiersza pobrane z tabeli #random), następnie zwracają wynik w oparciu o obliczenie długości poszczególnych pól i kilka warunków logicznych. Tabela #random liczyła dla tej próby 3000 kombinacji. Wyniki są następujące:

-- SELECT FnDataAccess_TSQL(wiersz1,wiersz2) FROM #random
26 s

-- SELECT FnDataAccess_CLR(wiersz1,wiersz2) FROM #random
36 s

Widać więc, że do takich zastosowań najlepiej wykorzystywać T-SQL, czyli podejść do zadania w sposób klasyczny. Funkcje nie wykonują żadnych zaawansowanych obliczeń, najważniejszym czynnikiem decydującym o ich wydajności jest szybkość dostępu do danych.

W drugim przykładzie zmodyfikujemy nieco zadanie. Tym razem funkcje będą musiały obliczyć tzn. Levenshtein-Distance (google zaprasza zainteresowanych) dla dwóch losowych rekordów z tabeli Dane_adresowe. Mamy więc kolejne implementacje:

- FnCalculateLD_TSQL (implementacja T-SQL, kod do pobrania: tutaj)
- FnCalculateLD_CLR (implementacja CLR-C#, kod do pobrania: tutaj)

Pobranie danych z tabeli Dane_adresowe następuje w taki sam sposób, jak w poprzednim przykładzie. Różnica w kodzie jest sumarycznie niewielka, zasadniczo zmienia się jednak ciężar gatunkowy wykonywanych operacji. Dochodzi konieczność obliczenia sumy z wartości LD dla siedmiu par pól (miejscowości, adresów, nazw itd.) - będą to obliczenie stricte numeryczne (w pliku z kodem funkcji FnCalculateLD_CLR znajduje się przykładowa implementacja LD w C#). Obie implementacje zostały uruchomione na 3000 rekordów tabeli #random - wyniki są następujące:

-- SELECT FnCalculateLD_TSQL(wiersz1,wiersz2) FROM #random
1200 s

-- SELECT FnCalculateLD_CLR(wiersz1,wiersz2) FROM #random
40 s

Jak widać implementacja CLR wykona zadanie 30 razy szybciej niż funkcja zaimplementowana w T-SQL. Różnica jest więc kolosalna.

SQL-CLR to potężne narządzie, mogące wydajnie realizować całkiem skomplikowane zadania, z którymi nigdy nie poradziłby sobie czysty T-SQL. Takie rozszerzenia możliwości klasycznej bazy danych jak CLR integration to według mnie przyszłość rozwiązań bazodanowych, od których wymaga się coraz bardziej skomplikowanych funkcjonalności.

Więcej informacji:
MSDN: Using CLR Integration in SQL Server 2005
MSDN Blogs: SQL Server 2005: CLR Integration