Posts tagged zmienne tabelaryczne

Przyjęło się i przekazywane jest w środowisku stwierdzenie, że w przypadku, gdy w zapytaniu użyta jest zmienna tabelaryczna (zwana przez niektórych “tabelą małpkową”), optymalizator zawsze zakłada/widzi w zmiennej tabelarycznej dokładnie jeden wiersz. Z tego powodu plany wykonania są dalekie od oczekiwanych, przez co musimy sterować planami (np. jawnie określając kolejność tabel w złączeniach).

Postanowiłem troche poszperać w temacie.

Spróbujmy uruchomić taki kawałek kodu T-SQL:

DECLARE @t TABLE (c1 int PRIMARY KEY);
DECLARE @x TABLE (c2 int PRIMARY KEY);
INSERT INTO @t SELECT DISTINCT number FROM master.dbo.spt_values;
INSERT INTO @x SELECT TOP 1 number FROM master.dbo.spt_values;
SELECT * FROM @t t INNER JOIN @x x ON t.c1 = x.c2 OPTION (RECOMPILE); --(1)
SELECT * FROM @t t INNER JOIN @x x ON t.c1 = x.c2;                    --(2)

Zapytania (1) i (2) są złączeniami dwóch zmiennych tabelarycznych – @x – zawierającej 1 wiersz oraz @t – zawierającej ponad 2000 wierszy.

Dla zapytań (1) i (2) oglądamy dwa różne plany wykonania (ale nie plany estymowane, a plany rzeczywiste) o tym samym koszcie:

Pierwsza obserwacja - w obu przypadkach kolejność zmiennych tabelarycznych w złączeniu była różna (w przypadku zapytania (1) z opcją RECOMPILE kolejność była “prawidłowa” – skanowanie zmiennej zawierającej mniejszą ilość wierszy i wyszukanie rekordów w zmiennej “większej”).

Zobaczmy w planach wykonania szczegóły operatorów użytych w stosunku do zmiennej @t.

W zapytaniu (2) w operatorze Clustered Index Scan liczba przy pozycji Actual Number of Rows (rzeczywista liczba wierszy)  zgadza się z rzeczywistością (2164), zaś w przypadku zapytania (1) używającego opcji RECOMPILE (“rekompiluj zapytanie i nie używaj planu z cache’u”) w operatorze Clustered Index Seek liczba obok Acutal Number of Rows wynosi 1. Skąd w drugim planie wzięła się poprawna informacja o rzeczywistej liczbie wierszy? Odpowiedź przychodzi, gdy spojrzymy na całość planu wykonania. W takich sytuacjach liczba wierszy z tabeli “zewnętrznej” (Outer) jest równa ilości iteracji pętli Nested Loops – 2164 to właśnie ilość “obrotów pętli”.  Nadal jednak estymowana ilość wierszy dla tych zmiennych wynosi 1 (słownie: jeden). A z tego wynika, że – z klauzulą OPTION (RECOMPILE) czy bez niej – optymalizator zakłada, że w zmiennej tabelarycznej jest zawsze 1 rekord. Swego rodzaju ciekawostką są różne plany wykonania obu przedstawionych zapytań, ale równie dobrze może to być przypadek lub bug (dość powtarzalny).

W tym miejscu przyznaję, że gdyby nie Maciek Pilecki, chodząca księga wiedzy o zagadnieniach optymalizacji SQL Servera, nie wpadłbym na oczywistą pomyłkę, którą popełniłem pisząc ten wpis w jego pierwotnej postaci. Co się mianowicie stało? Ano, jakimś cudem pomyliłem plany wykonania obu zapytań i tak się tym zasugerowałem, że pomyślałem, iż klauzula OPTION (RECOMPILE) umożliwia dokładne policzenie rekordów w zmiennej tabelarycznej SQL Serverowi. Oczywiście mój błąd. Dobrze, że Maciek był na posterunku i mogłem dzięki niemu poprawić ten wpis oraz swój krzywy światopogląd, a przy okazji jeszcze czegoś się nauczyć (dzięki, Maćku).