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

Autocommit to jeden z dwóch podstawowych trybów obsługi transakcji w przypadku silnika SQL Server i większości innych liczących się na rynku RDBMS. Mamy z nim do czynienia w sytuacji, kiedy flaga IMPLICIT_TRANSACTIONS jest dla danego połączenia wyłączona (SET IMPLICIT_TRANSACTIONS OFF).

Szereg wyrażeń T-SQL wykonywanych jest transakcyjnie. W takim przypadku wszelkie zmiany najpierw trafiają do loga transakcyjnego, a dopiero później - w drodze zatwierdzenia transakcji - przenoszone są do pliku bazy danych. Komendy transakcyjne to: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, TRUNCATE TABLE, UPDATE. Co ciekawe, komenda SELECT bywa w pewnym sensie transakcyjna w trybie implicit transactions - jej wywołanie skutkuje otwarciem transakcji, o ile transakcja nie została wcześniej założona. Podstawowa różnica między trybem autocommit (AC) a implicit transactions (IT) dotyczy oczywiście sposobu zatwierdzenia transakcji. W trybie AC dzieje się to automatycznie (o ile wyrażenie wykona się bez błędu), w trybie IT natomiast wymagane jest jawne zatwierdzenie lub wycofanie transakcji. Jeżeli to nie nastąpi, SQL Server w momencie zamykania połączenia wycofa wszystkie aktywne transakcje.

Tryb AC jest trybem domyślnym zarówno dla providera Native Client (OLE DB), jak i ODBC (nie jest to zgodne ze standardem ANSI). Naturalnie jawne otwarcie transakcji przy pomocy BEGIN TRAN zawiesza stosowania AC i od tego momentu transakcja musi być zatwierdzona lub wycofana przez użytkownika (aplikację).

Wybór trybu obsługi transakcji w sposób bezpośredni wpływa na ilości operacji wykonywanych na logu transakcyjnym (transaction log, TL), skalę tego wpływu postaram się poniżej zademonstrować. SQL Server nie dostarcza udokumentowanych poleceń do podglądana zawartości TL, co nie znaczy, że jest to niemożliwe. Wykorzystamy do tego celu funkcję fn_dblog (SQL Server 2000, 2005). Funkcja wywołana w kontekście określonej bazy danych zwróci nam zawartość loga transakcyjnego tej bazy:

SELECT * FROM ::fn_dblog(NULL,NULL);
-- parametrami dla funkcji są startowy i końcowy LSN; wartości NULL
-- powodują, że zwrócony będzie cały log

Możemy więc łatwo obserwować, jakie wpisy i w którym momencie trafiły do TL. Komenda CHECKPOINT posłuży nam do oczyszczania loga między poszczególnymi próbami (polecenie to powoduje zapis brudnych stron na na dysk lub inne stable media i co za tym idzie - zwolnienie zapisów transakcji z loga).

Przygotowania środowiska do testów obejmuje stworzenie bazy testowej i prostej tabeli, włączenie trybu AC i wykonanie checkpointa:

CREATE DATABASE testowa05;
GO
USE testowa05;
GO
CREATE TABLE tabA (colA int);
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO
CHECKPOINT;
GO

W tym momencie w logu transakcyjnym bazy testowa05 będą znajdować się dwa wpisy dotyczące operacji CHECKPOINT (inne wpisy także są możliwe, choć mało prawdopodobne). Następnie dodajmy do tabeli 10 rekordów:

INSERT INTO tabA(colA) VALUES(1);
INSERT INTO tabA(colA) VALUES(2);
INSERT INTO tabA(colA) VALUES(3);
INSERT INTO tabA(colA) VALUES(4);
INSERT INTO tabA(colA) VALUES(5);
INSERT INTO tabA(colA) VALUES(6);
INSERT INTO tabA(colA) VALUES(7);
INSERT INTO tabA(colA) VALUES(8);
INSERT INTO tabA(colA) VALUES(9);
INSERT INTO tabA(colA) VALUES(10);

W efekcie powyższych operacji ilość rekordów w logu transakcyjnym wzrosła do:

-- SELECT count(*) FROM ::fn_dblog(NULL,NULL);
51

W zależności od stanu serwera uzyskana ilość wpisów w logu może się różnić o kilka wierszy - wszystko zależy od sposobu wykonania insertów przez procesor kwerend. Po oczyszczeniu tabeli i wywołaniu checkpointa, wykonamy analogiczną operację, z jedną różnicą - 10 operacji INSERT obejmie jedna transakcja:

-- czyszczenie tabeli i loga
TRUNCATE TABLE tabA;
GO
CHECKPOINT;
GO

BEGIN TRAN;
INSERT INTO tabA(colA) VALUES(1);
INSERT INTO tabA(colA) VALUES(2);
INSERT INTO tabA(colA) VALUES(3);
INSERT INTO tabA(colA) VALUES(4);
INSERT INTO tabA(colA) VALUES(5);
INSERT INTO tabA(colA) VALUES(6);
INSERT INTO tabA(colA) VALUES(7);
INSERT INTO tabA(colA) VALUES(8);
INSERT INTO tabA(colA) VALUES(9);
INSERT INTO tabA(colA) VALUES(10);
COMMIT TRAN;

Aktualna ilość wpisów w TL to:

-- SELECT count(*) FROM ::fn_dblog(NULL,NULL);
31

czyli o 40% mniej w stosunku do poprzedniego sposobu. Różnica jest jak widać zasadnicza. Trzeba pamiętać, że zgodnie z implementowanym przez SQL Server protokołem WAL (Write-Ahead Logging Protocol), wszystkie wpisy w logu transakcyjnym związane z określoną stroną danych (8-kB data page) muszą zostać zapisane na stabilnym medium (np. dysku w pliku ldf) zanim zapisana zostanie ta strona (w pliku mdb). Ilość transakcji przekłada się więc wprost na ilość operacji IO wykonywanych przez log managera podczas tzw. flush request.

Z jednej strony mamy więc wydajność, która nakazywałaby zakładanie stosunkowo rozległych transakcji, z drugiej - konkurencyjność, która dużych transakcji nie lubi, gdyż stoją za nimi dłuższe i bardziej zachłanne blokady zasobów. Jak to zwykle bywa w bazach danych - wyjściem jest odpowiedni kompromis. Jeżeli jakiś proces wrzuca do danej tabeli dziesiątki tysięcy rekordów i w tym momencie dostęp do niej nie będzie nikomu potrzebny - jedna transakcja będzie bardzo dobrym rozwiązaniem. Nie jest prawdą, że log transakcyjny strasznie "spuchnie" w takiej sytuacji, co czasem mam nieszczęście słyszeć od SQL-twardzieli (pokazałem powyżej, że log bardziej puchnie właśnie w sytuacji odwrotnej - wielu transakcji).

Chciałbym na koniec zaznaczyć, że duża ilość małych transakcji nie jest w każdym przypadku remedium na problem blokad. Trzeba pamiętać, że każda taka transakcja to oddzielne żądanie najczęściej blokady wyłącznej na określonym zasobie (exclusive lock, X) - czasem dużo bardziej opłaca się jednokrotnie założyć blokadę na większy zakres ilościowy (nawet całą tabelę) - wtedy operacja potrwa dużo szybciej, a więc i docelowy zasób szybciej przestanie być potencjalnym przedmiotem rywalizacji procesów.