Wydajność CTE

Wydajność CTE zależy od tego, czy optymalizator potraktuje je jako materializowane, czy rozwinięte inline.

CTE jako element planu zapytania

CTE, czyli Common Table Expression, jest nazwanym podzapytaniem definiowanym zwykle za pomocą klauzuli WITH. Pozwala podzielić złożone zapytanie na logiczne etapy, ale sama nazwa CTE nie oznacza automatycznie utworzenia tymczasowej tabeli ani zapisania wyniku na dysku. O tym, jak dane będą przetwarzane, decyduje optymalizator konkretnego silnika bazodanowego. Może on włączyć treść CTE bezpośrednio do głównego zapytania, czyli potraktować ją jako konstrukcję inline, albo wykonać ją wcześniej i używać przygotowanego wyniku jako osobnego etapu planu.

Różnica ma znaczenie, gdy CTE zawiera filtrowanie, sortowanie, agregację, złączenia lub funkcje wymagające dużej liczby operacji. Przy rozwinięciu inline optymalizator może przesunąć warunki WHERE bliżej tabel źródłowych, ograniczyć liczbę odczytywanych wierszy i dobrać indeksy do końcowego zapytania. Przy materializacji wynik CTE jest wyznaczany jako oddzielny zbiór, co może ograniczyć wielokrotne wykonywanie tego samego obliczenia, ale jednocześnie wymaga pamięci, a czasem także tymczasowego zapisu na dysku.

Materializacja a wielokrotne użycie CTE

Największe różnice wydajności pojawiają się wtedy, gdy to samo CTE jest używane więcej niż raz albo gdy jego obliczenie jest kosztowne. Jeżeli silnik rozwija CTE inline, każda referencja może zostać niezależnie włączona do planu. W efekcie ta sama tabela może być odczytywana kilka razy, a agregacja lub złączenie może zostać wykonane wielokrotnie. Materializacja może w takim przypadku ograniczyć powtarzanie obliczeń, ponieważ wynik pośredni powstaje raz i jest później wykorzystywany przez kolejne fragmenty zapytania.

Materializacja nie jest jednak z definicji korzystna. Jeśli główne zapytanie wykorzystuje tylko niewielką część wierszy zwróconych przez CTE, wcześniejsze wyliczenie całego wyniku może spowodować niepotrzebny odczyt danych, sortowanie i zajęcie pamięci roboczej. Plan z materializowanym wynikiem może również utrudnić optymalizatorowi wykorzystanie indeksu z tabeli źródłowej, ponieważ filtr z zapytania zewnętrznego nie zawsze zostanie zastosowany przed utworzeniem wyniku pośredniego. Ocena zależy więc od liczności danych, selektywności filtrów, kosztu złączeń i liczby użyć CTE.

Pomiar kosztu wykonania i interpretacja planu

Wpływ CTE należy oceniać na podstawie planu wykonania, a nie samego wyglądu zapytania. W silniku obsługującym instrukcję EXPLAIN można sprawdzić przewidywany sposób dostępu do tabel, kolejność złączeń, rodzaj sortowania, szacowaną liczbę wierszy oraz koszt poszczególnych operatorów. W wielu systemach dostępny jest także wariant pokazujący rzeczywiste czasy i liczby wierszy po wykonaniu zapytania, na przykład EXPLAIN ANALYZE. Dokładna składnia i znaczenie opcji zależą od używanego produktu, dlatego nie należy przenosić ustawień jednego silnika do innego bez sprawdzenia jego dokumentacji.

Porównanie powinno obejmować co najmniej dwa warianty: zapytanie z CTE oraz równoważne zapytanie zapisane jako podzapytanie lub bezpośrednie złączenie. Test trzeba przeprowadzić na danych o reprezentatywnej wielkości, z aktualnymi statystykami i przy podobnym obciążeniu systemu. Ważne są nie tylko milisekundy czasu całkowitego, lecz także liczba odczytanych stron, liczba przetworzonych wierszy, zużycie pamięci, operacje na plikach tymczasowych i różnica między estymowaną a rzeczywistą licznością. Duża rozbieżność tych wartości może wskazywać na nieaktualne statystyki lub nietrafne założenia optymalizatora, a nie na samą obecność CTE.

Praktyczne ograniczanie kosztu CTE

Wydajność można poprawić przez ograniczenie danych jeszcze przed kosztownymi operacjami. Filtry dotyczące kolumn tabel źródłowych powinny być formułowane tak, aby silnik mógł wykorzystać istniejące indeksy; stosowanie funkcji na indeksowanej kolumnie może utrudnić wyszukanie zakresu, zależnie od produktu i rodzaju indeksu. Warto także unikać niepotrzebnego SELECT *, ponieważ szerszy wiersz zwiększa koszt odczytu i przetwarzania. Przy wielokrotnym użyciu CTE trzeba sprawdzić, czy korzystniejszy jest jeden wspólny wynik pośredni, czy kilka niezależnych planów, które mogą zastosować różne filtry dla każdej referencji.

Nie należy wymuszać materializacji ani rozwinięcia inline bez pomiaru i znajomości możliwości konkretnego silnika. Niektóre systemy udostępniają wskazówki optymalizatora lub składnię określającą sposób traktowania CTE, lecz ich działanie, dostępność i ograniczenia są zależne od wersji produktu. Jeśli zapytanie działa wolno, diagnostyka powinna obejmować plan wykonania, indeksy, statystyki, blokady, czas oczekiwania oraz rozmiar danych. Sama zamiana CTE na zagnieżdżone podzapytanie często nie daje poprawy, ponieważ optymalizator może wygenerować identyczny plan dla obu zapisów. CTE warto więc traktować przede wszystkim jako narzędzie organizacji zapytania, a decyzję o jego wpływie na wydajność opierać na rzeczywistym planie i pomiarach.