Można sobie wyobrazić wiele scenariuszy, w których aplikacje, skrypty, czy usługi dokonują operacji dodawania nowych danych do bazy SQL Server. O ile w przypadku małych ilości rekordów wydajność może być praktycznie pominięta w rozważaniach, o tyle w przypadku aplikacji (platform) integracyjnych, ilości rekordów wrzucanych do bazy mogą dochodzić do setek tysięcy/milionów dziennie. Wydajność zaczyna mieć wtedy znaczenie co najmniej istotne, a często - priorytetowe.
Załóżmy sytuację początkową - aplikacja napisana w Perlu (lub Javie), która co kilkanaście sekund łączy się do określonego serwera FTP i sprawdza istnienie pliku o danej nazwie w odpowiednim katalogu. Jeżeli plik zostanie odnaleziony, aplikacja odczytuje go i odpowiednio przetwarza jego zawartość. Efektem tego przetwarzania jest około 50000 elementów w tablicy asocjacyjnej (lub zmiennej innego typu) zawierających dane teleadresowe, które mają być wrzucone do tabeli jako 50000 rekordów. Aplikacja łączy się do bazy SQL Server przez ODBC (lub JDBC) i - co wydaje się naturalne - per każdy element tablicy wykonuje operację typu:
INSERT INTO client_data (colA, colB, colC, ...) VALUES ('aaaa', 'bbbb', 'cccc', ...)
Proces dodawania 50000 rekordów do tabeli trwa w produkcyjnych warunkach około 1-2 min.
Opisany scenariusz można pewnie spotkać w nie jednej firmie. Co w nim można zoptymalizować? Jak się okazuje - przynajmniej dwie kwestie.
Przede wszystkim chciałbym uściślić/sprecyzować jedno pojęcie, którym będę się posługiwał - jest nim batch. Batch to zbiór instrukcji T-SQL przesłanych jednocześnie z aplikacji do SQL Server. Batchem bywa więc zarówno jedna instrukcja (SELECT, INSERT, UPDATE), jak i skomplikowany skrypt. Batch jest traktowany i przetwarzanie przez SQL Server jako łączny zestaw operacji, tzn. jest całościowo parsowany, bindowany, algebryzowany i optymalizowany (przy czym algebryzacja i optymalizacja logicznie dotyczą poszczególnych wyrażeń).
Jakie to ma znaczenie w odniesieniu do opisanego powyżej scenariusza? Otóż zasadnicze. Inserty wysyłane są do serwera pojedynczo, tzn. jeden per batch, to po prostu marnotrawstwo zasobów (istnieje tu pewna analogia do charakterystyki kursorów). Dla każdego batcha SQL Server musi wykonać te same stosunkowo złożone czynności kompilacyjne. Dodatkowo dochodzi jeszcze bardzo ważna kwestia warstwy transportu - w pakietach z jedną operacją INSERT stosunek treści (czyli kodu T-SQL) do całościowej ilości przesyłanych informacji jest bardzo niewielki. Skutkuje to tym, że sumarycznie więcej w pakietach informacji sterujących (nagłówkowych) niż meritum - transmisja ma więc minimalną efektywność, a koszty związane z jej obsługą są relatywnie bardzo wysokie. To tak jakby komunikować się za pomocą e-maili czy SMSów, wysyłając w poszczególnych wiadomościach wyłącznie jedną literę.
Należy więc doprowadzić do sytuacji, w której aplikacja będzie wysyłała do SQL Server batche zawierające np. kilkaset operacji INSERT. Wtedy wydajniejsza będzie zarówno komunikacja z serwerem, jak też i procedowanie tych operacji w bazie danych. W środowisku testowym udawało mi się uzyskiwać nawet kilkunastokrotne zwiększenie wydajności opisanego na wstępie scenariusza. Trzeba drogą empiryczną ustalić stosunek ilości insertów w batchu, dla którego wydajność importu całej grupy rekordów jest największa. W moich testach były to okolice dziesiątej części liczby rekordów (dla 50000 było to ok. 6500 insertów w batchu).
Nie należy się raczej obawiać przekroczenia ograniczeń co do ilości znaków w batchu. Maksimum wynosi: 64 KB * rozmiar pakietu (zwykle 4 KB), co daje 256 MB.
Implementacja powyższego zalecenia w warstwie DAL aplikacji jest bardzo prosta, wystarczy w pętli agregować treść instrukcji INSERT (koniecznie oddzielanych nową linią) do zmiennej typu string, a po przekroczeniu ustalonej ich ilości, wysłać tak stworzone zapytanie do bazy danych.
Dodatkowym zabiegiem optymalizacyjnym będzie wyłączenie domyślnej opcji AutoCommit (większość providerów ustawia ją podczas zestawiania połączenia do bazy, domyślnie jest włączona) i objęcie wszystkich batchy jedną transakcją. Spowoduje to dodatkowo zwykle kilkunastoprocentowy wzrost wydajności, gdyż SQL Server nie będzie musiał zatwierdzać transakcji po każdym dodaniu rekordu, które nie zakończy się błędem (AutoCommit) - zrobi to jednorazowo dla wszystkich insertów.
W jednym z poprzednich postów napisałem, że SQL Server 2005 teoretycznie ignoruje klauzulę sortującą zawartą w definicji widoku, a jedynym prawidłowym i zalecanym miejscem na jej definiowanie jest zewnętrzny SELECT z widoku. Jak można się domyślić, istnieją sposoby na enkapsulowanie kolejności wierszy w samym obiekcie view - są to jednak tylko efekty uboczne innych operacji. Opiszę dwa z nich:
1. TOP X PERCENT ... ORDER BY kolumna
W wersji SQL Server 2000 efekt sortowania dawała taka konstrukcja:
CREATE VIEW widok AS
SELECT TOP 100 PERCENT *
FROM tabela
ORDER BY kolumna;
W wersji SQL Server 2005 powyższa definicja sortowania nie wywoła. Wydaje się, że programiści Yukonu celowo zablokowali efekt sortujący tego dość popularnego sposobu. Wystarczy jednak zamienić TOP 100 na np. TOP 99.999(..), aby wynikowy recordset znowu był posortowany. Rozwiązanie takie ma pewną wadę: może nie zwrócić wszystkich wierszy. Ze względu na ograniczenie precyzji typu numeric do 38 pozycji, największą liczbą w klauzuli TOP jest:
SELECT TOP (99.999999999999999999999999999999999999) ...
co jednocześnie determinuje maksymalną ilość wierszy wyświetlanych w widoku teoretycznie do liczby 10^38 rekordów. Praktycznie jednak liczba ta jest znacznie mniejsza, więc przy dużych tabelach raczej nie można polegać na takim rozwiązaniu.
2. ROW_NUMBER() OVER (ORDER BY col)
Poniższy kod:
CREATE VIEW widok AS
SELECT kol1, kol2, ..., ROW_NUMBER() OVER (ORDER BY kolX) as kol_sort
FROM tabela;
również sprawi, że utworzony widok będzie posortowany. Powodem tego efektu (ubocznego) jest oczywiście zastosowanie funkcji partycjonującej do stworzenia jednej z kolumn. Funkcja partycjonująca określa porządek rekordów, którym funkcja rankingowa ROW_NUMBER() przyporządkowuje odpowiednie wartości - w sposób naturalny następuje więc sortowanie. Co oczywiste, SQL Server po wykonaniu powyższej operacji nie aplikuje już randomizacji kolejności rekordów, zwracane są więc one w porządku narzuconym podczas generowania rezultatu. Rozwiązanie takie ma pewną wadę: kolumna kol_sort musi występować wśród kolumn odczytywanych z widoku (oczywiście może to odbywać się w sposób niejawny - przez "gwiazdkę") - inaczej SQL Server zaniecha sortowania.
Każde z rozwiązań "sortowania na siłę" ma dość poważne wady, należy więc stosować je w ostateczności, a we wszystkich możliwych sytuacjach sortowanie narzucać w zewnętrznym zapytaniu z widoku. Należy też pamiętać, że opisane wyżej zachowania mogą być zmienione bez ostrzeżenia np. przez service pack.
Interpretacja składni T-SQL przez SQL Server ściśle zależy od systemu powiązań bazy danych (database collation), a czasem - całego serwera (server collation). W dokumentacji nie jest to mocno artykułowane i często kwestia ta bywa niesłusznie pomijana. Poniższy przykład zobrazuje potencjalny problem. Stwórzmy testową bazę danych:
CREATE DATABASE collation_test;
Następnie ustawmy collation na Polish_CI_AS, czyli system powiązań, który nie uwzględnia wielkości liter:
ALTER DATABASE collation_test COLLATE Polish_CI_AS;
Stwórzmy tabelę o nazwie tEstoWa:
CREATE TABLE tEstoWa ( colA int );
Teraz odwołamy się do tabeli, deklarując jej nazwę wyłącznie małymi literami:
-- USE testowa;
SELECT colA FROM testowa;
Zapytanie wykonało się bez żadnego błędu. Zmieńmy następnie collation na Polish_CS_AS (case sensitive):
ALTER DATABASE collation_test COLLATE Polish_CS_AS;
A następnie wykonajmy ponownie zapytanie. Uzyskamy taki oto błąd:
-- USE testowa;
-- SELECT colA FROM testowa;
Msg 208, Level 16, State 1, Line 1
Invalid object name 'testowa'.
SQL Server zwróci analogiczny błąd, jeśli określimy nazwę kolumny nie uwzględniając wielkości liter. Należy pamiętać, że reguły wielkości liter odnoszą się do nazw wszystkich obiektów w bazie danych - m.in. tabel, procedur, funkcji, także zmiennych i parametrów. Niezależne od collation są tylko słowa kluczowe języka T-SQL.
Kilka dni temu w pracy miałem sytuację, że mimo case insensitive collation bazy danych przeniesionej na nowy serwer, procedury, w których do składni podchodzono dość swobodnie (do zmiennej zadeklarowanej jako @zmienna odwoływano się jako @ZMIENNA), zaczęły rzucać błędami. Pomogła dopiero zmiana collation dla całego serwera na case insensitive. Niestety zmiana server collation jest bardzo kłopotliwa, gdyż odbywa się przez rekonfigurację serwera w trybie setup i wymaga przebudowy bazy master (wszystkie inne bazy są dropowane). Warto więc pomyśleć o opisywanych kwestiach już na etapie instalacji SQL Servera.
Chciałbym zwrócić uwagę na potencjalne problemy związane z budowaniem zapytań zawierających podzapytania nieskorelowane (uncorrelated subqueries). W pewnych sytuacjach występują zasadnicze rozbieżności między tym, jak zapytanie pojmuje deweloper, a jak - procesor kwerend SQL Servera. Załóżmy istnienie dwóch tabel:
CREATE TABLE tab1
(
colA int,
colB nvarchar(10)
)
CREATE TABLE tab2
(
colX int
)
-- wypełnianie tabel przykładowymi danymi
INSERT INTO tab1 (colA, colB) VALUES (1,'A')
INSERT INTO tab1 (colA, colB) VALUES (2,'B')
INSERT INTO tab1 (colA, colB) VALUES (3,'C')
INSERT INTO tab1 (colA, colB) VALUES (4,'D')
INSERT INTO tab2 (colX) VALUES (44)
INSERT INTO tab2 (colX) VALUES (55)
Następujące zapytanie:
SELECT colA
FROM tab2
oczywiście zwróci błąd 207 ( Msg 207, Level 16, State 1, Line 1: Invalid column name 'colA'.), gdyż kolumna colA nie istnieje w tabeli tab2. Jeżeli jednak powyższe zapytanie zostanie użyte jako podzapytanie w następującej kwerendzie:
SELECT colB
FROM tab1
WHERE colA IN
(SELECT colA FROM tab2)
SQL Server nie zwróci błędu i co więcej - wykona zapytanie praktycznie w sposób równoważny sytuacji braku warunku WHERE. Chyba nie trzeba nikogo przekonywać, jakie niebezpieczeństwa może to powodować przy okazji operacji modyfikacji danych.
Nasuwa się proste pytanie: o co chodzi i czy jest to błąd? Semantyka podpowiadałaby, że zapytanie wykonywane jest błędnie, w rzeczywistości jednak SQL Server przetwarza je zgodnie ze standardami języków blokowo-strukturalnych, do których zaliczany jest także SQL.
Powyższa kwerenda logicznie realizowane jest w następujący sposób:
1. sprawdzenie istnienia kolumny colA w tabeli tab2
2. kolumna nie istnieje, procesor szuka najbliższej logicznie klauzuli FROM
3. sprawdzenie, czy tabela określona w znalezionej klauzuli FROM zawiera colA
4. kolumna istnieje w tabeli tab1, zostaje więc uwzględniona w podzapytaniu
W efekcie powyższego zapytanie przetwarzane jest jako:
SELECT tab1.colB
FROM tab1
WHERE tab1.colA IN
(SELECT tab1.colA FROM tab2)
SQL Server będzie realizował takie zapytanie przez LEFT SEMI JOINa z warunkiem złączenia:
[tab1].[colA]=[tab1].[colA]
Przy takim warunku złączenia wynikowy zbiór rekordów nie jest w żaden sposób zawężany - to dlatego problematyczna kwerenda zwraca wszystkie rekordy z tabeli tab1. Analogicznie zachowują się także inne silniki BD (w tym Oracle, PostgreSQL, MySQL).
Aby uniknąć problemów, które mogą wynikać z opisanych uwarunkowań, należy deklarować nazwy kolumn w sposób jednoznaczny, tj. albo przez pełną kwalifikację, albo przez aliasy.
Dziękuję znajomemu z pracy - Rafałowi Zwolakowi za wskazanie problematycznego zapytania.
Poniższe rozważania dotyczą platformy SQL Server 2005, jednak spora ich część pośrednio lub bezpośrednio odnosi się także do wersji 2000.
Operacje typu join to nieodzowny element większości zapytań, nie bez znaczenia jest więc umiejętność spojrzenia na joiny w aspekcie wydajności. O ile wydajnością zapytania ad hoc najczęściej możemy się nie przejmować, o tyle zapytanie zaszyte w ciele produkcyjnej procedury składowanej, wykonywane dziennie setki razy - jest już chyba godnym obiektem obserwacji.
Z logicznego punktu widzenia poszczególne joiny budowane są z 1-3 następujących po sobie faz:
CROSS JOIN
1. iloczyn kartezjański
INNER JOIN
1. iloczyn kartezjański
2. warunek filtrujący ON
OUTER JOIN
1. iloczyn kartezjański
2. warunek filtrujący ON
3. dodanie wierszy z oznaczonej tabeli
Powyższe rozróżnienie ma kilka ważnych konsekwencji. Jedną z nich jest fakt, że w INNER JOINach nie jest istotne, czy określony warunek zawężający umieścimy w INNER JOIN ON ... - czy w WHERE ..., gdyż ten typ joina składa się z dokładnie dwóch operacji logicznych i między zastosowaniem warunku ON i WHERE żadne wiersze nie są już do wynikowego zbioru dodawane. Efektem powyższego jest ponadto fakt, że umiejscowienie warunku nie wpływa na wydajność zapytania (w 99% przypadków uzyskamy te same plany wykonań). Oczywiście zupełnie inaczej zachowują się OUTER JOINy, gdzie ze względu na ostatnią fazę (dodanie wierszy z oznaczonej tabeli), warunek WHERE stosowany jest w stosunku do innego zbioru rekordów niż warunek ON.
Jeżeli chodzi o aspekt fizyczny budowania złączeń, to odbywa się to poprzez 3 algorytmy:
- loop join
- merge join
- hash join
Strategie joinowania można wymuszać, np.:
SELECT kolumna
FROM tabela_a a
INNER LOOP JOIN tabela_b b
ON (..warunek..)
Nie jest to jednak zalecana metoda walki z niewydajnymi planami zapytań - trzeba raczej doprowadzić do sytuacji, w której sam optimizer dobiera odpowiedni sposób realizacji kwerendy (indeksy, aktualne statystyki, query tuning).
Spośród wymienionych strategii złączeń najefektywniejsze są merge joiny. Loop join może w danej sytuacji również być najlepszym algorytmem, natomiast występowanie w planach wykonań operacji typu hash join prawie zawsze oznacza brak jakiegoś indeksu lub nieaktualną statystykę.
W fazie tworzenia zapytania deweloperzy często ograniczają się do stosowania joinów wszędzie tam, gdzie jest to możliwe. Tymczasem join nie zawsze jest najwydajniejszym rozwiązaniem, czasem zastosowanie klauzuli EXISTS daje lepsze rezultaty, załóżmy istnienie 2ch tabel:
CREATE TABLE zamowienia
(
nr_zamowienia int,
nazwa_zamowienia nvarchar(50)
)
CREATE TABLE miejsca_zamowien
(
nr_zamowienia int,
miejscowosc nvarchar(50)
)
Szukane zapytanie ma wyświetlić miejscowości, w których nie było zamówień. Rozwiązanie z joinem wygląda tak:
SELECT mz.miejscowosc
FROM miejsca_zamowien mz
LEFT OUTER JOIN zamowienia z
ON (mz.nr_zamowienia = z.nr_zamowienia)
WHERE z.nr_zamowienia IS NULL
Zakładając przeciętne uwarunkowania środowiska zadania, zapytanie można często zakodować w efektywniejszy sposób, tj.:
SELECT miejscowosc
FROM miejsca_zamowien mz
WHERE NOT EXISTS
(SELECT * FROM zamowienia WHERE nr_zamowienia = mz.nr_zamowienia)
Jeżeli chodzi o kolejność występowania złączeń w zapytaniu, to o ile nie następuje zmiana logiki zapytania - z punktu widzenia wydajności kolejność jest nieistotna. Optimizer sam potrafi dobrać najoptymalniejszy układ joinowania tabel.
Więcej informacji: Itzik Ben-Gan, Lubor Kollar, Dejan Sarka, Inside Microsoft® SQL Server™ 2005 T-SQL Querying (Chapter 5), MS Press 2006
|
