Czy istnieje różnica w wydajności między CTE, zapytaniem podrzędnym, tabelą tymczasową lub zmienną tabeli?

Nie ma żadnej reguły. Uważam, że CTE jest bardziej czytelne i używam ich , chyba że wykazują jakiś problem wydajności, w którym to przypadku badam rzeczywisty problem, a nie domyślam się, że CTE jest problemem i staram się go ponownie napisać przy użyciu innego podejścia. Zwykle chodzi o coś więcej niż sposób, w jaki wybrałem deklaratywne określenie moich intencji za pomocą zapytania.

Są z pewnością przypadki, kiedy można rozwikłać CTE lub usunąć subqueries i zastąpić je tabelą # temp i skróć czas trwania. Może to być spowodowane różnymi rzeczami, takimi jak stare statystyki, niemożność uzyskania dokładnych statystyk (np. dołączenie do funkcji o wartości tabelarycznej), równoległość, a nawet niemożność wygenerowania optymalnego planu ze względu na złożoność zapytania (w takim przypadku rozbijanie go może dać optymalizatorowi szansę walki). Ale są również przypadki, w których We/Wy związane z tworzeniem tabeli # temp może przeważyć inne aspekty wydajności, które mogą nadać konkretny kształt planowi za pomocą CTE mniej atrakcyjne.

Szczerze mówiąc, jest zbyt wiele zmiennych, aby zapewnić "poprawną" odpowiedź na twoje pytanie. Nie ma przewidywalnego sposobu, aby wiedzieć, kiedy zapytanie może wskazywać na korzyść jednego lub drugiego podejścia - po prostu wiedz, że teoretycznie ta sama semantyka dla CTE lub pojedynczego zapytania podrzędnego powinna wykonać dokładnie to samo. Myślę, że twoje pytanie byłoby bardziej wartościowe, jeśli przedstawisz niektóre przypadki, w których nie jest to prawdą - być może odkryłeś ograniczenie w optimizer (lub odkrył znany), lub może się zdarzyć, że Twoje zapytania nie są semantycznie równoważne lub że zawierają element, który utrudnia optymalizację.

Więc sugerowałbym napisanie zapytania w sposób, który wydaje ci się najbardziej naturalny i odbiegać tylko wtedy, gdy odkryjesz rzeczywisty problem wydajności, który ma optymalizator. Osobiście oceniam ich CTE, a następnie subquery, z tabelą # temp jako ostatecznością.

# temp jest zmaterializowana, a CTE nie.

CTE to tylko składnia, więc w teorii jest to tylko subquery. Jest wykonywana. # temp jest zmaterializowany. Tak więc kosztowny CTE w połączeniu, który jest wykonywany wiele razy, może być lepszy w # temp. Z drugiej strony, jeśli jest to łatwa ocena, która nie jest wykonywana, ale kilka razy, to nie jest warta narzutu #temp.

Są niektórzy ludzie na tak, że nie lubię zmiennej tabeli, ale lubię je jako zmaterializowane i szybciej tworzyć niż # temp. Czasami optymalizator zapytań radzi sobie lepiej z # temp w porównaniu ze zmienną tabelkową.

Możliwość tworzenia PK na zmiennej # temp lub table daje optymalizator zapytań więcej informacji niż CTE (ponieważ nie Można zadeklarować PK na CTE).

Tylko 2 rzeczy, które myślę, że zawsze lepiej używać tabeli # Temp zamiast CTE są:

1. Nie można umieścić klucza podstawowego na CTE, więc dane, do których dostęp ma CTE, będą musiały przejść każdy z indeksów w tabelach CTE, a następnie po prostu uzyskać dostęp do PK lub indeksu w tabeli tymczasowej.

2. Ponieważ nie można dodać ograniczeń, indeksów i kluczy podstawowych do CTE, są one bardziej podatne na wkradanie się błędów i złe data.

- onedaywhen yesterday

Oto przykład, w którym ograniczenia # table mogą zapobiec złym danym, co nie ma miejsca w CTE

DECLARE @BadData TABLE ( 
                       ThisID int
                     , ThatID int );
INSERT INTO @BadData
       ( ThisID
       , ThatID
       ) 
VALUES
       ( 1, 1 ),
       ( 1, 2 ),
       ( 2, 2 ),
       ( 1, 1 );

IF OBJECT_ID('tempdb..#This') IS NOT NULL
    DROP TABLE #This;
CREATE TABLE #This ( 
             ThisID int NOT NULL
           , ThatID int NOT NULL
                        UNIQUE(ThisID, ThatID) );
INSERT INTO #This
SELECT * FROM @BadData;
WITH This_CTE
     AS (SELECT *
           FROM @BadData)
     SELECT *
       FROM This_CTE;