Proces wykonywania zapytania jest bardzo złożony, ale najogólniej mówiąc zapytanie w pierwszej kolejności jest parsowane, a potem następuje utworzenie planu wykonania. Określa on, jak zapytanie zostanie wykonane przez bazę. Najczęściej (i na szczęście !!) to, jak baza wykonuje nasze zapytanie diametralnie różni się od logicznego porządku, zgodnie z którym je tworzymy. Na to, jak baza skompiluje zapytanie, ma wpływ wiele czynników, między innymi:

Potem plan jest buforowany, zatem jeżeli ponownie będziemy chcieli wykonać to zapytanie, to już ponowna kompilacja nie będzie konieczna. Kiedy baza już „wie” jak zapytanie będzie wykonane, dane które nas interesują, są ładowane do pamięci z dysku (jeśli ponownie wykonamy to zapytanie, nie trzeba będzie ich ponownie pobierać - będą zbuforowane). Ostatecznie zapytanie jest wykonywane, a wynik jest zwracany do klienta.

Indeks to struktura pomocnicza, która ma przyspieszać dostęp do poszczególnych wierszy w tabeli. Działanie indeksu w bazie jest analogiczne do indeksu umieszczonego na końcu książki – w książce pozwala on przenieść się bezpośrednio do interesującego nas hasła, natomiast w bazie zamiast słów mamy wiersze.

Kiedy zapytanie jest wykonywane, najkorzystniejszą operacją jakiej poddawany jest indeks jest wyszukiwanie zgodnie z nim (Seek). Druga, mniej korzystna to pełen odczyt (Scan).

W czasie pracy mamy możliwość sprawdzenia, jaki plan zostanie przez bazę wygenerowany. Jeżeli zaznaczymy interesujące nas zapytanie i naciśniemy wyróżniony przycisk:

2.4    Polecenia 

Oprócz samego planu bardzo często korzysta się także z 2 poleceń, pozwalających uzyskać parametry danego zapytania są to:

• SET STATISTICS TIME ON

Ta opcja pozwala uzyskać informacje o tym ile czasu zajęło wykonanie zapytania oraz o czasie, przez jaki zajęty był procesor.

• SET STATISTICS IO ON 

Ta opcja umożliwia uzyskać informacje przy wykonaniu zapytania o liczbie dostępów do danych umieszczonych w buforze (dostęp logiczny) i dostępów do danych na dysku (odczyt fizyczny).

3    Zapytania

Mając już pewną poglądową wiedzę związaną z wewnętrznym wykonaniem zapytania oraz wybranymi narzędziami można przystąpić do omówienia kilku problemów.

3.1    UNION/UNION ALL

Wiele osób zdaje się nie znać dokładnie semantyki polecenia UNION a także UNION ALL, w związku z tym zawsze korzystają z UNION, najwyraźniej nie mając świadomości, jaki wpływ ma to na wydajność. Otóż UNION zawsze usuwa wszystkie duplikaty ze zbioru wynikowego (taki „ukryty” DISTINCT), a UNION ALL nie wykonuje tej filtracji (dopuszcza duplikaty w zbiorze wynikowym). Także, jeżeli tylko mamy zagwarantowane, że duplikaty nie występują lub nie stanowią one problemu należy stosować UNION ALL.

Aby nie być gołosłownym i uzmysłowić powagę problemu przeprowadziłem przykładowy test (w tym teście i następnych czasy są zawsze przedstawiane w milisekundach). Wykorzystałem do tego 2 tabele:

 

Zapytania były bardzo proste:

SELECT first_name,last_name,email
FROM dbo.Students
UNION
SELECT first_name,last_name,email
FROM dbo.Teachers

oraz

SELECT first_name,last_name,email
FROM dbo.Students
UNION ALL
SELECT first_name,last_name,email
FROM dbo.Teachers

Wyniki uzyskane:

 

 

 

 

Porównując plany, także bez większego wysiłku można dostrzec różnicę:

UNION:

UNION ALL:

 

Plan dla UNION jest bardziej kosztowny.

Nie trzeba chyba teraz nikogo przekonywać, jakie znaczenie mogą mieć 3 litery i dlaczego warto o nich pamiętać.

3.1    FULLTEXTSEARCH VS. LIKE ‘%wyraz%’

    Często spotykamy się z koniecznością wyszukiwania jakiegoś wyrazu lub jego fragmentu w kolumnie znakowej. Niemalże natychmiast przychodzi na myśl konstrukcja typu LIKE '%wyraz%'. Niestety, użycie '% przed wyszukiwanym wyrazem uniemożliwia efektywne wykorzystanie potencjalnego indeksu, który może zostać założony na tej kolumnie. Z pomocą w tej trudnej sytuacji przychodzi nam wyszukiwanie pełnotekstowe. Jakie są korzyści płynące z tego rozwiązania?

    Wykorzystane zostały 2 tabele, w każdym kroku testu zawierały takie same dane. Na jedną tabelę został założony zwykły indeks na przeszukiwanej kolumnie, a druga tabela dysponowała indeksem pełnotekstowym, wobec czego można było korzystać z konstrukcji T-SQL charakterystycznych dla FT (Full-Text).

Zapytania wyglądały następująco:

Zwykłe zapytanie:

SELECT article_abstractID
FROM dbo.ArticleAbstracts_IX
WHERE abstract LIKE '%MSI%';

 Zapytanie pełnotekstowe:

SELECT article_abstractID
FROM dbo.ArticleAbstracts_FT
WHERE CONTAINS(abstract,'"*MSI*"')

Wyniki:

 

 

 
Rząd różnicy jest naprawdę duży, ale oczywiście, nic nie ma za darmo: baza musi FT utrzymać, aktualizować, katalog zajmuje także miejsce na dysku. Kiedy jest to uzasadnione (duży rozmiar przeszukiwanej kolumny, rzadko aktualizowana zwartość) należy pamiętać o ‘pełnym tekście’ i w miarę możliwości stosować. Potencjalne korzyści są naprawdę duże. Fulltext-search to nie tylko klauzula CONTAINS. Ta usługa oferuje naprawę spore możliwości. Zainteresowani więcej informacji znajdą w Books Online.

3.3    Funkcje ‘inline’ i ‘multistatment’

    Funkcje to według mnie dość kontrowersyjny temat. Zwłaszcza, że przy pomocy nich niektórzy „na siłę” starają się przenieść zasadę code reuse do bazy danych. Kończy się to m.in. złączeniami na funkcjach zamiast na tabelach, z którymi nie radzą sobie nawet najmocniejsze serwery. Zatem do stosowania funkcji należy podchodzić z rozwagą.
Pozostaje jeszcze inny problem, gdyż SQL Server ma 3 rodzaje funkcji:
 
•    skalarne (którymi się teraz nie będę zajmował)
•    2 rodzaje funkcji zwracających tabele „jednopoleceniowe” (inline) i „wielopoleceniowe” (multistatement).

Funkcje zwracające tabele oferują inne możliwości: pierwsza z nich może zawierać tylko 1 polecenie (SELECT), druga może składać się z wielu poleceń. Warto sprawdzić, która z nich powinna być preferowana przed drugą.

Na potrzeby tego testu powstał następujący schemat: 

 

 

 

Tabelą, której zawartość zmieniano w czasie testu była tabela dbo.Posts.

Funkcja multistatement:

CREATE FUNCTION dbo.mulfn_GetUsersInRole (@roleID INT)
RETURNS @UsersTab TABLE
(
      userID INT NOT NULL
)
AS
BEGIN

     INSERT INTO @UsersTab (userID)
      SELECT U.userID
      FROM USERS U
            INNER JOIN dbo.Authorities A ON U.userID = A.userID
            INNER JOIN dbo.Roles R ON A.roleID = R.roleID
            AND A.roleID = @roleID; 
      RETURN;
END
GO

 Funkcja inline:

 

CREATE FUNCTION dbo.inlfn_GetUsersInRole(@roleID INT)
RETURNS TABLE
AS
      RETURN (SELECT U.userID
            FROM USERS U
            INNER JOIN dbo.Authorities A ON U.userID = A.userID
            INNER JOIN dbo.Roles R ON A.roleID = R.roleID
            AND R.roleID = @roleID);
GO

 

Zapytania:

SELECT T.thread_name
FROM dbo.Threads T
WHERE EXISTS (SELECT *
                  FROM dbo.Posts P
                  WHERE P.threadID = t.threadID
                  AND P.authorID IN (SELECT *
                                   FROM dbo.mulfn_GetUsersInRole(4)))

SELECT T.thread_name
FROM dbo.Threads T
WHERE EXISTS (SELECT *
                  FROM dbo.Posts P
                  WHERE P.threadID = t.threadID
                        AND P.authorID IN (SELECT *
                                         FROM dbo.inlfn_GetUsersInRole(4)))

 
Wyniki:

 

 

 

 

 W tej chwili, chyba nie ulega wątpliwości, że jeżeli tylko mamy taką możliwość, należy stosować jednopoleceniowe funkcje. Skąd jednak bierze się aż tak drastyczna różnica w wydajności? Odpowiedź można odnaleźć, jeśli zastosujemy instrukcję SET STATISTICS IO ON. (Liczba wierszy dla przykładu wynosiła 500 000). Dla zapytania z funkcją inline wynik będzie następujący:

 

Table 'Threads'. Scan count 3, logical reads 58, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Roles'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Authorities'. Scan count 3, logical reads 253, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 3, logical reads 124, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 3, logical reads 3719, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Natomiast zapytanie z funkcją multistatement da następujący rezultat:

Table '#0E391C95'. Scan count 1, logical reads 968983, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 3391, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Threads'. Scan count 1, logical reads 36, physical reads 0, read-ahead reads 27, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Kluczowa jest tutaj liczba odczytów logicznych. Tymczasowa tabela #0E391C95 utworzona przy wywołaniu funkcji multistatement została odczytana aż 968983 razy, a korzystając z wersji inline mamy ok. 4000 odczytów łącznie. Jeśli będziemy sprawdzać plany dla tych zapytań zauważymy jeszcze jedną ciekawą rzecz. Otóż zapytanie z funkcją inline generuje 1 plan. Natomiast zapytanie z funkcja multistatement generuje 2 plany: osoby dla zapytania i osobny dla funkcji. Zatem baza w trakcie kompilowania nie może uprościć operacji pomiędzy zapytaniem zewnętrznym a funkcją, to także przyczynia się do o wiele niższej wydajności.  

3.4    Daty

SQL Server nie jest reklamowany jako baza danych, ale jako „kompletna platforma dla rozwiązań biznesowych”. W oparciu o SQL Server można tworzyć rozwiązania OLAP, drążyć dane. Mamy dostępną architekturę dla budowy aplikacji rozproszonych (Service Broker), mamy wsparcie dla XML, zaszyto integracje z CLR, listę nowych funkcjonalności można by jeszcze długo wymieniać. Niestety, SQL Server 2005 jest kolejną edycją, w której nie zdołano stworzyć osobnego typu danych dla daty i czasu. Data i czas są przechowywane łącznie jako DATETIME lub SMALLDATETIME. Stwarza to rzecz jasna pole do licznych błędów i powoduje spore komplikacje.

Dość częstym problemem jest konieczność porównywania dat. Np. jeśli przechowując dane uwzględniamy minuty i sekundy, a chcemy wybrać daty, które należą do określonego dnia. W takim przypadku mogą pojawić się trudności, przyjrzyjmy się temu bliżej.

Dysponujemy funkcją CONVERT za pomocą, której można usunąć zbędne informacje, wyświetlić daty w określonym formacie i dzięki temu je porównać. Podejście to wykorzystuje poniższe zapytanie, którego zadaniem jest wybrać z tabeli wypełnionej losowymi datami, te które przypadają na dzisiejszy dzień (trywialne, ale na nasze potrzeby wystarczy).

SELECT date
FROM dbo.Dates
WHERE CONVERT(varchar(32),date,121) = CONVERT(varchar(32),GETDATE(),121)

Można także spróbować inaczej i wykorzystać rzutowanie dla uzyskania tego samego efektu:

SELECT date
FROM dbo.Dates
WHERE CAST(FLOOR(CAST(date AS FLOAT))AS DATETIME) = CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)

W tym miejscu należy się kilka słów komentarza, gdyż to rzutowanie sprawia wrażenie skomplikowanego, jednak spełnia ono swoją rolę. Czasami na forach pojawia się następujące rozwiązanie: CAST(GETDATE() AS INT), ale według mnie kryje się tutaj pewna pułapka. Porównajmy następujące wywołania i wyniki:

SELECT CAST(CAST(CAST ('2007-02-25 11:59:00' AS DATETIME)AS INT)AS DATETIME)

Wynikiem jego będzie: 2007-02-25 00:00:00.000
Natomiast minutę później:

SELECT CAST(CAST(CAST ('2007-02-25 12:00:00' AS DATETIME)AS INT)AS DATETIME)

Dostaniemy: 2007-02-26 00:00:00.000

Takie rzutowanie może prowadzić do błędów, dlatego lepiej zwracać uwagę nawet na takie szczegóły.
Zobaczmy, jak te 2 rozwiązania radzą sobie w działaniu, w sytuacji, kiedy na kolumnę date założono indeks.

 

 

Nie da się ukryć, że zastosowanie CAST daje lepsze rezultaty, ale czy to jest najlepsze zapytanie, na jakie nas stać ? Rzecz jasna, że nie. Przyjrzyjmy się planowi zapytania dla CAST:

 

 

Ponieważ wykonujemy operacje WHERE CAST(FLOOR(CAST(date AS FLOAT))AS DATETIME) = CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME) na kolumnie, po której wyszukujemy, musimy wykonywać kosztowny Index Scan. Przebudujmy nasze zapytanie:

SELECT date
FROM dbo.Dates
WHERE date >= CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)
AND date < CAST(FLOOR(CAST(DATEADD(DAY,1,GETDATE()) AS FLOAT))AS DATETIME)

Wybieramy te daty, które zawierają się pomiędzy północą dnia dzisiejszego włącznie, a północą dnia następnego wyłącznie. Semantyka pozostaje taka sama, ale plan zmienił się, mamy teraz upragniony Index Seek:

 

 

Jak ta zmiana przekłada się na czas wykonania? Czy wysiłek włożony w przebudowę zapytania opłacił się? Przekonajmy się zestawiając zwycięzcę (CAST) z nową wersją zapytania.

 

 

 

 

Wydaje mi się, że już nikogo nie trzeba przekonywać do tego, że nie należy wykonywać zbędnych operacji na kolumnach, po których wyszukujemy i w takich sytuacjach, jeśli jest to tylko to możliwe, należy przebudowywać zapytania.

4    Badania

Wszystkie testy były przeprowadzone na komputerze Athlon 64 X2 3800+ z 2 GB RAM, system operacyjny: MS Windows Server 2003 EE, baza danych: MS SQL Server 2005 DE. Testy składały się z 4 krotnego uruchomienia badanego zapytania, przy czym pod uwagę brane były tylko 3 ostatnie wyniki, a następnie były one uśredniane. Miało to na celu testowanie przy zbuforowanym planie wykonania, jak i stronach danych, gdyż dawało to szansę przynajmniej na częściowe przybliżenie środowiska produkcyjnego. Skrypty wykorzystane przy badaniu znajdują się w załączniku.

5    Zakończenie

Treść artykułu nie obejmowała tak istotnych tematów jak kursory (tak negatywnie oceniane) czy widoki zmaterializowane. Nie wnikałem także we wpływ poszczególnych indeksów na wykonanie zapytania, gdyż może to stanowić tematykę oddzielnego artykułu.

6    Bibliografia

1.    Ben-Gan I., Kollar L., Sarka D., Inside Microsoft SQL Server 2005: T-SQL Querying, MS Press 2006
2.    Ben-Gan I., Sarka D., Wolter R., Inside Microsoft SQL Server 2005: T-SQL Programming, MS Press 2006
3.    Agarwal S., Baryshnikov B., Davidson T., Elmore K., Ribeiro D., Thomas J.,Troubleshooting Performance Problems in SQL Server 2005, Technet 2005
4.    Marathe A., Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005,
Technet 2004
5.    Books Online