.:: strefa ::.

Od dawna deweloperzy z największymi głowami zastanawiają się nad najlepszymi sposobami komunikacji różnych aplikacji i baz danych. Problematyka ta wbrew pozorom do banalnych nie należy i najczęściej nie ma jednoznacznej odpowiedzi na pytanie o najlepsze rozwiązanie. Oceniać oczywiście można w różnych aspektach: wydajności (szybkości działania), przenoszalności, bezpieczeństwa, ale i to czasem nie wystarcza do dokonania definitywnego wyboru.

Chciałbym tutaj zaprezentować porównanie dwóch technologii transportu danych między SQL Serverem 2005 a testową aplikacją-skryptem napisanym w języku Perl. Ocenie podlegał będzie jedynie aspekt wydajności, gdyż tylko na tym polu można sprawiedliwie porównać technologię o ogólnym przeznaczeniu - ODBC oraz technologię dedykowaną pod środowisko SQL Servera - OLE DB.

Skrypt Perl, działający w środowisku Microsoft Windows 2003 Server SE (32bit, interpreter ze stajni ActiveState w wersji 5.8.8.819) z bazą danych SQL Server 2005 SP1, miał do wykonania dwa zadania:

1. ciągły odczyt 100 tyś. rekordów zawierających dane teleadresowe

2. 1000x odczyt grup rekordów zawężanych wspólnym wyróżnikiem (wspólny NIP/kod pocztowy), wybór jednego z rekordów - o największej liczbie liter 'a' w nazwie i update jednego z pól rekordu

Zadanie 1 - używając analogii rajdowej - można określi jako "długą prostą", natomiast zadanie 2 - "jazda w zakrętach".

Skyrpt wykonany został w dwóch wersjach warstwy dostępu do BD:

1. DBI (v1.52) z DBD::ODBC (v1.13)
2. Win32::SqlServer (v2.002)

A teraz wyniki:

ODBC
zadanie 1: 12 sek.
zadanie 2: 19 sek.

OLE DB
zadanie 1: 23 sek.
zadanie 2: 12 sek.

Wyniki wskazują, że do nieprzerwanych odczytów dużych ilości rekordów wydajniejsze wydaje się ODBC. Jednak muszę podkreślić, że im większa ilość rekordów do pobrania, tym różnice w wydajności dwóch testowanych providerów są mniejsze - przy milionie rekordów stają się już praktycznie niezauważalne. OLE DB jest natomiast lepsze do odczytów sekwencyjnych i modyfikacji danych.

To, co powyżej napisałem, jest jedynie pewną generalną zasadą wskazującą - z określonym prawdopodobieństwem - na najwydajniejsze rozwiązanie. Znam jednak przypadki, które stanowią wyjątek od tej reguły. Niektóre zaawansowane algorytmy (Perl) przypominające charakterystykę operacji z zadania 2, teoretycznie powinny wydajniej działać na providerze OLE DB (baza danych to SQL Server 2k5), jednak działają o 20-25% szybciej na ODBC. Powodem tego może być sam provider (Win32::SqlServer) - o ile dałoby się go napisać lepiej. Może w kolejnych wersjach modułu sytuacja ulegnie zmianie.

Zadanie: znalezienie klucza symetrycznego, którym zaszyfrowano określone dane (SQL Server 2005).

Załóżmy, że w tabeli t:

CREATE TABLE t
(
data varbinary(500)
)

znajduje się 1 rekord z zaszyfrowaną informacją:

-- SELECT * FROM t
0x00F4938C160E8543BBE859267A7748CE01000000B748(..)

Aby wskazać klucz symetryczny, którym została zaszyfrowana informacja, trzeba posłużyć się pewną sztuczką - należy zrzutować zaszyfrowaną informację (typ varbinary) na typ uniqueidentifier:

DECLARE @guid uniqueidentifier
SELECT @guid = CAST(data as uniqueidentifier) FROM t

Po wykonaniu powyższego kodu, zmienna @guid będzie przechowywała tzw. key guid, czyli guid klucza symetrycznego. Teraz wystarczy w widoku sys.symmetric_keys (lub widokach - jeżeli w grę wchodzi kilka baz/serwerów) odnaleźć rekord ze znalezionym guidem:

SELECT name FROM sys.symmetric_keys WHERE key_guid = @guid

Zadanie rozwiązane.

Podczas migracji do wersji SQL Server 2005 trzeba zwrócić uwagę na całą masę zagadnień i często drobne, acz istotne szczegóły umykają uwadze deweloperów. Różnic, które mają wpływ na skuteczność działania kodu T-SQL, jest całkiem sporo - część podana jest w oficjalnych źródłach (m.in. tutaj). Są jednak pewne kwestie, które nie zostały przez Microsoft wyartykułowane, a mogą sprawiać problemy. Chciałbym tutaj przedstawić kilka z nich.

1. sortowanie w widokach

Mimo, że w wersji SQL Server 2000 w widokach klauzula ORDER BY także była teoretycznie niedozwolona (Msg 1033, Level 15, State 1: The ORDER BY clause is invalid in views (..)), co sprytniejsi radzili sobie z sortowaniem w taki sposób:

CREATE VIEW widok AS
SELECT TOP 100 PERCENT *
FROM tabela
ORDER BY kolumna

Taki widok enkapsulował określoną kolejność wierszy. W wersji SQL Server 2005 powyższa konstrukcja nie przyniesie pożądanego efektu w postaci posortowania recordseta (są natomiast inne sposoby - może o nich później napiszę). Jedynym prawidłowym miejscem na definiowanie klauzuli ORDER BY jest w nowej wersji zewnętrzny SELECT z widoku. Może to i uciążliwe, zwłaszcza jeżeli ktoś widokami budował logikę kolejek, ale przynajmniej spójne z dokumentacją.

2. brak możliwości sortowania po aliasie kolumny, jeżeli zastosowano także alias tabeli

Taki oto kod:

SELECT kolumna as moj_alias
FROM tabela t
ORDER BY t.moj_alias

nie zadziała w wersji 2005, a zadziała w wersji 2000. Zadziała także w wersji 2005, jeżeli pozbędziemy się aliasu t tabeli:

SELECT kolumna as moj_alias
FROM tabela
ORDER BY moj_alias

To zagadnienie może wydawać się banalne i bezproblemowe. Tak jednak nie jest - wystarczy sobie wyobrazić sytuację skomplikowanego zapytania, gdzie aliasy były używane w sposób z pierwszego przykładu. Po migracji do SQL Servera 2005 nagłe podczas wywołania zapytania pojawia się błąd (Msg 207, Level 16, State 1, Line 2 Invalid column name '[alias]'.). Ciekawe po jakim czasie przeciętny deweloper domyśli się, że chodzi o aliasy w klauzuli sortującej.

3. funkcja ISNUMERIC()

Zwróćmy uwagę na poniższe wywołanie funkcji ISNUMERIC(), która zwraca 1 dla ciągów numerycznych, a 0 - dla nienumerycznych:

DECLARE @string nvarchar(100)
SET @string = '\1982'
PRINT ISNUMERIC(@string)

W SQL Serverze 2000 wynikiem jest:

0

a w Yukonie:

1

I problem gotowy, jeżeli ktoś uznał, że string - dla którego funkcja zwróci 1 - składa się tylko z cyfr i znaków +/- lub kropki/przecinka. Dopiero w aktualizacji BOL do wersji 2005 Microsoft uzupełnił, że funkcja ISNUMERIC() w SQL Serverze 2005 zwraca 1 także dla ciągów znakowych zawierających "$", "\", "+", "-", ".", "," czy znak tabulacji. O ile uzasadnione jest, że string '22,4' czy nawet '$3' (typ money) uznany jest za numeryczny, o tyle przypadek np.'.,' lub taki, jak w powyższym przykładzie - jest już mocno dyskusyjny. Niestety, trzeba na to uważać.

Na koniec ciekawostka. Poniższy kod:

SELECT col, col
FROM ( SELECT RAND() col ) t

na SQL Serverze 2000 zwróci przykładowo:

0.45805486236289 0.65146429330668276

a na SQL Serverze 2005:

0,970028871101527 0,970028871101527

Jak widać rezultaty powstały w oparciu o zupełnie różne algorytmy procesora kwerend. I rzeczywiście tak jest - ciekawskim polecam sprawdzenie planów zapytań (SET SHOWPLAN_ALL ON) i dostrzeżenie ile razy i w jaki sposób wywoływana jest funkcja RAND().

W poprzednim wpisie napisałem o przeglądarce umożliwiającej anonimowe przeglądanie stron dzięki sieci Tor. Możliwości Tor`a się na tym jednak nie kończą i myślę, że warto przybliżyć samą ideę.

Tor (the onion routing) - to działająca w dużym rozproszeniu sieć hostów węzłowych, które w losowej kombinacji uczestniczą w połączeniu komputera źródłowego i docelowego - zastępując tym samym bezpośrednie połączenie tych komputerów. Tzw. trasowanie cebulowe (ang. onion routing), czyli przesyłanie informacji w sieci Tor, zapewnia anonimowość inicjatorowi połączenia TCP. Kluczem do tego jest taka konstrukcja systemu, że żaden z węzłów sieci Tor nie może znać całej ścieżki utworzonego ad hoc obwodu - widzi tylko noda, od którego otrzymał pakiet. Pierwszy z nodów obwodu komunikuje się z komputerem źródłowym, a ostatni - z docelowym, czyli np. serwerem www czy serwerem pocztowym. Dodatkowo transmisja w ramach sieci Tor jest szyfrowana, a trasa przejścia (obwód) zmieniana jest co 1 minutę.

Należy pamiętać, że Tor nie jest środkiem zapewniającą pełną anonimowość - co prawda sprytnie zabezpiecza komunikację na warstwie transportu, ale do anonimowości pozostaje jeszcze warstwa aplikacyjna i warstwa zdrowo-rozsądkowa umysłu użytkownika...

Więcej informacji i pobieranie: kliknij tutaj

Są dwa sensowne sposoby na pobranie resultseta zwracanego przez procedurę składowaną:

1. przez tabelę (zwykłą lub tymczasową - jak niżej w przykładzie)

CREATE TABLE #temp (kolumna typ_kolumny...)
INSERT INTO #temp EXEC procedura @parametr
SELECT kolumna FROM #temp
-- (!) w SQL Server 2005 zamiast tabeli tymczasowej można też użyć zmiennej tabelarycznej

2. przez OPENQUERY (data access musi być dozwolony)

EXEC sp_serveroption 'server', 'data access', true
SELECT kolumna FROM OPENQUERY(server, 'EXEC procedura @parametr')

Pierwszy sposób jest zdecydowanie bardziej rozpowszechniony. Ma on jednak swoje wady - podczas tworzenia tabeli tymczasowej (deklarowania zmiennej tabelarycznej) trzeba enumeratywnie zdefiniować wszystkie kolumny zwracane przez procedurę.

Pojawiła się nowa wersja przeglądarki Torpark (v1.5.0.7). Przeglądarka ta podczas łączenia z serwerami www automatycznie korzysta z sieci serwerów proxy (tzw. Tor circuit/network), zapewniając tym samym stosunkowo anonimowe przeglądanie stron www. Anonimowy browsing można oczywiście wyłączyć, wtedy przeglądarka nie różni się zasadniczo od Firefoxa, którego framework z resztą wykorzystuje.

Pobierz program: Torpark download