Encyklopedia SQL - T-SQL Optymalizacja Zapytań  

Autor: Paweł Wilkosz

Opublikowano: 2012-02-10

Redagując ostatnią już część z cyklu Encyklopedia SQL, swoje rozważania chciałbym skoncentrować na najlepszych praktykach pisania zapytań w języku T-SQL, aby osiągnąć najwyższą możliwą jakość wykonania, względem szybkości działania, wykorzystania zasobów sprzętowych oraz wewnętrznych mechanizmów serwera SQL, do wydajnego przetwarzania zbiorów danych.

Przed wykonaniem zadań zapoznaj się z następującym materiałem:

• Typy danych w T-SQL
• Wyświetlanie danych, podstawowy SELECT i wybór kolumn
• Łączenie warunków ograniczających – AND, OR, WHERE
• Łączenie warunków ograniczających – IN, BETWEEN
• Indeksowanie tabel, indeks klastrowy i nieklastrowy

Po wykonaniu zadań będziesz wiedział:

• w jaki sposób konstruować wydajne zapytania T-SQL.

Cykl życia zapytania T-SQL

Zanim zapytanie zostanie wykonane na wskazanej bazie danych, przechodzi ono przez szereg faz, związanych z analizą składniową, standaryzacją wyboru właściwego indeksu czy odpowiedniej metody złączania tabel. Większość z tych mechanizmów jest przetwarzana przez sam SQL Server, jednakże programista również ma istotny wpływ na podejmowane przez serwer bazodanowy decyzje. Ogólniej rzecz ujmując, od momentu wciśnięcia przycisku Execute w SQL Server Management Studio, bądź wykonania innego polecenia, związanego z rozkazem wykonania zapytania, kod T-SQL przechodzi przez następujące fazy:

1. Sprawdzenie poprawności syntaktycznej przez parser T-SQL.
2. Standaryzacja (ujednolicenie zapisu kodu).
3. Optymalizacja.
4. Opracowanie planu wykonania zapytania.
5. Skompilowanie zapytania wg przygotowanego planu.
6. Wybór przez Query Optimizer najlepszego sposobu dostępu do danych.

W dalszej części publikacji dokonamy analizy poszczególnych faz.

Poprawność syntaktyczna

Język T-SQL, podobnie jak inne języki deklaratywne czy strukturalne, posiada swoją własną składnię, dzięki której maszyna (a w tym wypadku serwer bazodanowy) jest w stanie zrozumieć intencje programisty. Faktem oczywistym jest, iż zapytanie, zanim zostanie wykonane, musi posiadać poprawną względem syntaktyki składnię. Oprócz instrukcji SQL, programista może również poniekąd podpowiedzieć serwerowi, w jaki sposób powinien pracować na danym zapytaniu, aby osiągnąć jeszcze większą wydajność (np. instrukcja UPDATE STATISTIC – aktualizuje statystyki, na podstawie których przygotowywany był plan wykonania zapytania). Przykład aktualizacji danych statystycznych dla tabeli Sales.SalesOrderDetail został przedstawiony na poniższym listingu:

UPDATE STATISTICS Sales.SalesOrderDetails;

Standaryzacja

Proces standaryzacji należy rozumieć w kategoriach zapisu znaczników języka T-SQL w jednolitej postaci. Najlepszym przykładem, jaki można przytoczyć w tej kwestii, jest używanie aliasów do nazw tabel, szczególnie popularnych w zapytaniach ze złączeniami JOIN.

Optymalizacja

W kolejnym etapie SQL Server należy dokonać analizy zapytania pod względem przygotowania planu wykonania zapytania (ta kwestia zostanie omówiona w następnym podpunkcie), analizy wyszukiwania danych, wyboru indeksów oraz techniki złączania tabel. SQL Server określa, która tabela będzie traktowana jako nadrzędna, a która jako podrzędna oraz w jaki sposób osiągnie dostęp do danych itp. Wszystkie obserwacje z analizy są skrupulatnie zapisywane w planie wykonania zapytania.

Opracowanie planu wykonania zapytania

Plan wykonania zapytania stanowi swoistego rodzaju schemat postępowania określający „krok po kroku”, w jaki sposób kod T-SQL będzie przetwarzany, skąd, kiedy i w jaki sposób będą pobierane dane, jakie mechanizmy zostaną użyte do przetwarzania danych itp. Plan wykonania jest dostępny dla programisty i powinien stanowić pierwsze miejsce poszukiwania przyczyn nieoptymalnych procedur. Na Rys. 1. został przedstawiony plan wykonania zapytania kodu SQL do przeliczania bonusów (publikacja: Tworzenie funkcji bazodanowych – CREATE FUNCTION).

Rys. 1. Plan wykonania zapytania procedury przeliczającej premie.

Informacje, zawarte w planie, to m.in. odczytanie indeksu, pętla wyszukiwania, sposób porządkowania danych, przeszukiwanie indeksu, tabeli, wskaźników, wybieranie danych, zgodność funkcji skrótu, złączenia. Po wskazaniu planu wykonania na dany element, pojawi się dodatkowe okno, w który programista może uzyskać jeszcze więcej informacji np. na temat ilości odczytu danych z dysku fizycznego, zużycia CPU, estymowanego kosztu wykonania danej operacji itp. (Rys. 2.).

Rys. 2. Szczegóły statystyczne pętli wyszukiwania dla wskazanego zapytania.

Skompilowanie zapytania wg przygotowanego planu

Kiedy plan jest już utworzony przez SQL Server, zostaje on zapisany w buforze procedury po to, by przy następnym wykonaniu tego samego kodu nie tracić czasu na ponowne jego przygotowanie. Kolejnym etapem jest kompilacja zapytania wraz ze wskazówkami wykonania, jakie dostarcza ów plan.

Wybór przez Query Optimizer najlepszego sposobu dostępu do danych

Zadaniem Query Optimizera jest m.in. wskazanie, czy lepszym sposobem wyszukiwania danych jest wykorzystanie indeksów, czy może pobranie zawartości całej tabeli. W przypadku, gdy tabela zawiera relatywnie mało rekordów (np. 50) nie ma sensu angażować indeksów, do których dostęp w przypadku tak małej ilości danych jest po prostu mniej wydajny. Po tych wszystkich zabiegach, zapytanie jest wykonywane, a wynik zwracany programiście.

Dobre praktyki tworzenia zapytań

Na zakończenie chciałbym podzielić się z Tobą najlepszymi praktykami konstruowania zapytań w języku T-SQL, bazując na własnym doświadczeniu oraz wiedzy uzyskanej chociażby z Books Online.

• Lepiej poświęcić trochę więcej czasu na wylistowanie kolumn w tabeli select (SELECT kolumna1, kolumna2, …, kolumnaN), zamiast stosować zapis SELECT *. Pozwoli to serwerowi SQL na pobranie tylko tych danych, które są kluczowe względem wykonania zapytania, a nie wszystkich. Dodatkowo pomoże to zidentyfikować problemy związane z potencjalnymi nieprawidłowościami w zmianach schematu bazy danych.
• Jeżeli jest to możliwe, nie należy nadużywać klauzuli IS NULL/IS NOT NULL. Wartości NULL wymagają uruchomienia dodatkowych funkcji do jej obsługi, co może wpływać na czas wykonania zapytania.
• Nie należy nadużywać funkcji LIKE %wzorzec%. Dopasowanie idealnego rozwiązania do wzorca wyrażenia regularnego może spowalniać czas uzyskania wyników.
• Należy używać klauzuli DISTINCT tylko wtedy, gdy jest to konieczne. Wykorzystanie DISTINCT w SELECT powoduje usunięcie duplikatów, wymaga także uruchomienia dodatkowych mechanizmów, związanych ze wznowieniem operacji sortowania, aby można byłoby te duplikaty zlokalizować i usunąć. Jeżeli programista jest pewny, że w danej kolumnie nie pojawią się powtórzenia, użycie DISTINCT nie jest zalecane.
• Powinno się zawsze określać nazwy kolumn podczas wstawiania wierszy poleceniem INSERT (np. INSERT INTO Tabela(kolumna1, kolumna2) VALUES (wartość1, wartość2), zamiast INSERT INTO Tabela VALUES(wartość1, wartość2). Zabezpieczy to programistę przed ewentualnymi problemami, gdy do tabeli Tabela zostanie wstawiona dodatkowa kolumna.

Informacja

Zapamiętaj, że: Podczas tworzenia zapytań na bazie danych zawsze należy przeglądać plan wykonania zapytania, aby zidentyfikować potencjalne problemy z wydajnością i sterowaniem za pomocą kodu T-SQL. Należy stosować się do najlepszych praktyk tworzenia kodu T-SQL, chociażby wymagało to pozornie tylko większego nakładu pracy. Gdy wszystkie metody optymalizacji zawodzą, warto wykorzystać liczniki systemowe w narzędziu Performance Monitor, aby ustalić, co jest przyczyną wąskich gardeł w pracy serwera.

Podsumowanie

W ostatniej części cyklu Encyklopedia SQL chciałem uwrażliwić Cię na kwestie związane z wydajnym i bezpiecznym tworzeniem zapytań w T-SQL. Wraz ze wzrostem informacji w bazie danych problem skuteczności wykonania danej procedury, czy funkcji, będzie odnosił kluczowe znaczenie, dlatego nie warto odkładać tego problemu na później. Zostawiony w ten sposób tzw. „dług technologiczny” będzie trudniejszy i bardziej będzie stanowił ryzyko w czasie naprawy, gdy problemy z wydajnością zaczną coraz bardziej dawać o sobie znać.

 

---