wtorek, 28 kwietnia 2015

Synonimy

Jeżeli zdarza się wam używać połączonych serwerów i to w dodatku w dużej ilości zapytań to zdajecie sobie sprawę z tego, że odwoływanie sie do obiektów, korzystając z pełnej czteroczłonowej nazwy jest mało wygodne. Jeżeli dodać do tego fakt, że te same zapytania, czy to procedury składowane muszą wykonywać się zarówno na środowiskach programistycznych, testowych a później produkcyjnych to jest to po prostu proszenie się o telefony czy maile do osób odpowiedzialnych za wsparcie aplikacji.
 
Tworzenie kilku wersji procedury składowanej, dla poszczególnych środowisk nie jest optymalnym podejściem. Z pomocą przychodzą jednakże synonimy. Jest to jedna z funkcjonalności, które nie są prawdopodobnie bardzo powrzechnie wykorzystywane.

Zasadniczo synonimy upraszczają zapytania odwołujące się do serwerów połaczonych, ale także mogą być pomocne w przypadku gdy nazwa tabeli się zmienia a jest ona referencjonowana przez aplikację. Utworzenie synonimu ze starą nazwą tabeli a odwołującego się do nowej nazwy sprawi, że dla aplikacji korzystającej z tego typu obiektu zmiana będzie niezauważalna.

W przypadku serwerów połączonych używanie synonimów ma tą zaletę, że w przypadku gdy są one wykorzystywane np. w procedurach składowanych, to ta sama wersja może być wykorzystywana na różnych środowiskach. Jedyne co musi być specyficzne dla poszczególnych środowisk to obiekt synonimu.

Tworzenie synonimu jest bardzo proste. Założmy, że mamy serwer połączony o nazwie MYSERVER oraz instancji SQL2012. Na serwerze znajduje się baza danych MyRemoteDatabase i zawiera ona tabelę Products. Synonim dla tego typu obiektu wyglądałby następująco.
CREATE SYNONYM dbo.LinkedServerProducts

FOR [MYSERVER\SQL2012].MyRemoteDatabase.dbo.Products

GO


Aby odwołać się teraz do obiektu Products na bazie danych MyRemoteDatabase wystarczy następujące zapytanie.


SELECT * FROM dbo.LinkedServerProducts



Powyższe zapytanie to w dużej mierze uproszczenie. O tym dlaczego nie warto używać SELECT * FROM pisałem w tym miejscu.

Synonimy, choć niezbyt często używane mogą być bardzo przydatne. Szczególnie, gdy zachodzi konieczność odwoływania się do obiektów na serwerach połączonych.

czwartek, 2 kwietnia 2015

Sortowanie wyników w operacjach złączenia pionowego

Jakiś czas temu pisałem o indeksach (część 1, część 2) klastrowych oraz o ich wpływie na sortowanie wyników. Pomimo tego, że dwa wspomniane wpisy dotyczyły pewnych niuansów, na które warto zwrócić uwagę w czasie pracy indeksami klastrowymi, to pokazałem także, że poleganie na indeksach w celu uzyskania posortowanych wyników, może być bardzo zawodne. Przywołałem ten temat jako wstęp do sortowania wyników w przypadku operacji złączenia pionowego (UNION/UNION ALL). A wspomniałem o tym dlatego, że jest spora grupa osób, które myśli, że mając określony indeks w bazie danych i wykorzystując go w zapytaniu uzyskamy wyniki posortowane wg klucza tego indeksu. To jest oczywiście mitem i jedynym sposobem na uzyskanie wyników posortowanych według jakiejś określonej kolejności jest użycie klauzuli ORDER BY.
W przypadku unii sortowanie wyników wg pożądanego schematu może przysporzyć trochę problemów.

W kolejnych akapitach przedstawię kilka różnych klas problemów z jakimi możecie mieć do czynienia w przypadku sortowania zapytań korzystających z UNION ALL. Wszystkie przykłady będą się opierać na dwóch niewielkich zmiennych tabelarycznych.
DECLARE @TABLE1 TABLE (ID INT, TITLE VARCHAR(100))

INSERT INTO @TABLE1 VALUES

(1, 'Item 1'),

(2, 'Item 2'),

(3, 'Item 3')

 

DECLARE @TABLE2 TABLE (ID INT, TITLE VARCHAR(100))

INSERT INTO @TABLE2 VALUES

(1, 'Item 4'),

(2, 'Item 5'),

(3, 'Item 6'),

(4, 'Item 7')

Sortowanie całego zbioru wyników

Posortowanie całego zbioru wyników jest operacją najprostszą do wykonania. Po prostu wystarczy dodać klauzulę ORDER BY na końcu zapytania.

Poniższe zapytanie posortuje rekordy z obu tabel w kolejności ID

SELECT ID, TITLE FROM @TABLE1 

UNION ALL

SELECT ID, TITLE FROM @TABLE2

ORDER BY ID

Inna kolejność sortowania pierwszego i drugiego zapytania

Posortowanie wyników poszczególnych zapytań w innej kolejności niestety ale nie może być rozwiązane poprzez dodanie ORDER BY do pierwszego zapytania (tuż przed UNION ALL). Operacja sortowania jest ostatnią operacją, jaka jest wykonywane przez SQL Server w momencie wykonywania zapytań.

W przypadku gdyby wymaganiem było wyciągnięcie tylko części wyników z pierwszej i drugiej tabeli z pomocą przychodzą podzapytania z klauzulą TOP.

Poniższe zapytanie wyciąga po dwa rekordy z każdej tabel, przy czym wyniki z pierwszej tabeli są sortowanie rosnąco a z drugiej malejąco.

SELECT ID, TITLE FROM (SELECT TOP 2 ID, TITLE FROM @TABLE1 ORDER BY ID ASC) AS Q1

UNION ALL

SELECT ID, TITLE FROM (SELECT TOP 2 ID, TITLE FROM @TABLE2 ORDER BY ID DESC) AS Q2

Czasami taki rezultat może okazać się pożądany, ale zapewne częściej bardziej oczekiwanym wynikiem będą wszystkie rekordy pochodzące z tabeli pierwszej i drugiej.

Skoro w powyższym przykładzie zadziałał TOP to wystarczy go trochę zmodyfikować.

DECLARE @TOP1 INT

SELECT @TOP1 = COUNT(*) FROM @TABLE1 

 

DECLARE @TOP2 INT

SELECT @TOP2 = COUNT(*) FROM @TABLE2

 

SELECT ID, TITLE FROM (SELECT TOP (@TOP1) ID, TITLE FROM @TABLE1 ORDER BY ID ASC) AS Q1

UNION ALL

SELECT ID, TITLE FROM (SELECT TOP (@TOP2) ID, TITLE FROM @TABLE2 ORDER BY ID DESC) AS Q

Tym razem zamiast stałej, użyta została zmienna, zawierająca ilość rekordów z poszczególnych tabel. Rozwiązanie to ma jeden mały minus. Jest bardzo nieefektywne. Po prostu aby osiągnąć zamierzony efekt potrzeba przeskanować dodatkowo każdą z tabel aby uzyskać ilość rekordów. Czyli w sumie wychodzą 4 operacje skanowania. Nie jest to nic dobrego w przypadku dużych tabel.

Kolejną opcją jest skorzystanie z TOP x PERCENT.

SELECT ID, TITLE FROM (SELECT TOP 66.7 PERCENT ID, TITLE FROM @TABLE1 ORDER BY ID DESC) DATA

UNION ALL

SELECT ID, TITLE FROM (SELECT TOP 100 PERCENT ID, TITLE FROM @TABLE2 ORDER BY ID DESC) DATA

Co ciekawe w powyższym przykładzie pierwsze zapytanie zwraca wyniki poprawnie posortowane, natomiast w przypadku drugiego operacja sortowanie nie miała miejsca. TOP 100 PERCENT był swego czasu defektem w SQL Serverze, jednakże problem ten został naprawiony w wersji SQL 2005. Tak naprawdę gdy SQL widzi TOP 100 PERCENT to ignoruje to zupełnie podczas tworzenia planu wykonania. Podejrzenie właściwego planu wykonania tylko to potwierdza.

unionTop100Percent

Jeżeli chodzi o pierwszą część zapytania, która zwróciła swoje wyniki w odpowiedniej kolejności użyłem 66.7%. Dlatego akurat takiej wartości? Ano dlatego, że 0.667 * 3 (ilość rekordów w tabeli) = 2.001. Po zaokrągleniu w górę daje to 3, czyli sumę wszystkich rekordów. Takie rozwiązanie może być skuteczne tylko w przypadku dobrania odpowiedniej liczby. Czyli znowu jest to nieefektywne a w dodatku także może być nieefektowne rozwiązanie.

Czy istnieje więc sposób by pierwsze i drugie zapytanie mogło być sortowane wg innej kolejności bez korzystania uprzednio np. z tabel tymczasowych?

Tak się składa, że coś takiego, można w dość prosty sposób osiągnąć za pomocą starego dobrego przyjaciela czyli ROW_NUMBER.

SELECT ID, TITLE FROM (

SELECT 1 AS TABLESEQUENCE, ROW_NUMBER() OVER(ORDER BY ID ASC) AS RN, ID, TITLE FROM @TABLE1

UNION ALL

SELECT 2 AS TABLESEQUENCE, ROW_NUMBER() OVER(ORDER BY ID DESC) AS RN, ID, TITLE FROM @TABLE2 

) AS ORDEREDUNION

ORDER BY TABLESEQUENCE, RN

W powyższym przykładzie złączenie jest realizowane w ramach podzapytania. Każde z zapytań poza właściwymi danymi zwraca także odpowiednią kolejność i później w zapytaniu zewnętrznym wyniki są odpowiednio sortowane. Jest to jednakże dość kosztowne zapytanie i w przypadku dużych tabel może zająć relatywnie dużo czasu. Wynika to z faktu, że występują tutaj 3 operacje sortowania, które są dość kosztowne.

Korzystając z powyższej techniki można także rozwiązać problem generowania odpowiedzi w formacie CSV, gdzie pierwszy wiersz ma być nagłówkiem zwierającym listę kolumn.


SELECT DATA FROM (

SELECT 'ID|TITLE' AS HEADER, 'A' AS RN

UNION ALL

SELECT CAST(ID AS VARCHAR) + '|' + TITLE AS DATA, CAST(ROW_NUMBER() OVER(ORDER BY ID) AS VARCHAR) AS RN FROM @TABLE1 

) AS RESULTS(DATA, RN)

ORDER BY RN DESC


W powyższym przykładzie właściwe dane będą posortowane malejąco. Natomiast gdyby sortowanie miało być rosnąco to wystarczy zmienić RN w nagłówku na 0.


SELECT DATA FROM (

SELECT 'ID|TITLE' AS HEADER, '0' AS RN

UNION ALL

SELECT CAST(ID AS VARCHAR) + '|' + TITLE AS DATA, CAST(ROW_NUMBER() OVER(ORDER BY ID) AS VARCHAR) AS RN FROM @TABLE1 

) AS RESULTS(DATA, RN)

ORDER BY RN ASC

Powyższe przykłady pokazały w jaki sposób, można posortować wyniki biorące udział w złączeniu pionowym według indywidualnej kolejności sortowania dla każdego z zapytań.

Zapraszam do eksperymentowania. Polecam testowanie z wykorzystaniem sys.dm_db_index_physical_stats oraz sys.partitions. Zarówno DMF jak i widok katalogowy zawierają informacje na temat ilości rekordów. Jednakże nie zawsze wyniki te będą zgodne z tym co zwróci funkcja COUNT.