Które są bardziej wydajne, CTE lub tymczasowe tabele?

To zależy.

Po pierwsze

Co to jest Common table Expression?

A (non recursive) CTE jest traktowany bardzo podobnie do innych konstrukcji, które mogą być również używane jako wyrażenia tabeli inline w SQL Server. Pochodne tabele, widoki i funkcje wartościowane w tabeli inline. Zauważ, że chociaż BOL mówi, że CTE "może być traktowany jako tymczasowy zestaw wyników" , jest to czysto logiczny opis. Częściej niż nie jest materlializowana we własnym racja.

Czym jest stół tymczasowy?

Jest to zbiór wierszy przechowywanych na stronach danych w programie tempdb. Strony danych mogą znajdować się częściowo lub w całości w pamięci. Dodatkowo tymczasowa tabela może być indeksowana i posiadać statystyki kolumnowe.

Dane Testowe

CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);

INSERT INTO T(B)
SELECT TOP (1000000)  0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
     master..spt_values v2;

Przykład 1

WITH CTE1 AS
(
SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780

Uwaga w powyższym planie nie ma wzmianki o CTE1. Po prostu uzyskuje bezpośredni dostęp do tabel podstawowych i jest traktowany tak samo as

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM   T
WHERE  A = 780 

Przepisanie przez zmaterializowanie CTE do pośredniej tymczasowej tabeli tutaj byłoby znacznie nieproduktywne.

Zmaterializowanie definicji CTE

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T

Wymagałoby skopiowania około 8GB danych do tymczasowej tabeli, wtedy nadal istnieje narzut wyboru z niej zbyt.

Przykład 2

WITH CTE2
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY A) AS RN
         FROM   T
         WHERE  B % 100000 = 0)
SELECT *
FROM   CTE2 T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   CTE2 T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

Powyższy przykład zajmuje około 4 minut na moim komputerze.

Tylko 15 wierszy z 1 000 000 losowo wygenerowanych wartości dopasuj predykat, ale kosztowne skanowanie tabeli zdarza się 16 razy, aby je zlokalizować.

INSERT INTO #T
SELECT *,
       ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM   T
WHERE  B % 100000 = 0

SELECT *
FROM   #T T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   #T T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

Pośrednia materializacja części zapytania do tymczasowej tabeli może być czasami przydatna, nawet jeśli jest oceniana tylko raz - kiedy pozwala na rekompilację reszty zapytania korzystając ze statystyk na zmaterializowany wynik. Przykład tego podejścia znajduje się w artykule SQL Cat kiedy rozbić złożone zapytania.

W pewnych okolicznościach SQL Server użyje bufora do buforowania pośredniego wyniku, np. CTE, i uniknie konieczności ponownej oceny tego drzewa podrzędnego. Jest to omówione w (migrated) Connect item dostarczyć podpowiedź do wymuszenia pośredniej materializacji CTE lub pochodnych tabel. Jednak nie są tworzone statystyki na ten temat i nawet jeśli liczba spooled rzędy miały znacznie różnić się od szacowanych nie jest możliwe, aby plan realizacji w toku dynamicznie dostosowywał się w odpowiedzi (przynajmniej w obecnych wersjach. Adaptacyjne plany zapytań mogą stać się możliwe w przyszłości).

CTE ma swoje zastosowania-gdy dane w CTE są małe i istnieje duża poprawa czytelności, jak w przypadku tabel rekurencyjnych. Jednak jego wydajność nie jest z pewnością lepsza niż zmienne tabel, a gdy mamy do czynienia z bardzo dużymi stołami, tabele tymczasowe znacznie przewyższają CTE. Dzieje się tak, ponieważ nie można zdefiniować indeksów na CTE i gdy masz dużą ilość danych, które wymagają połączenia z inną tabelą (CTE jest po prostu jak makro). Jeśli łączysz się z wieloma stołami z milionami wierszy rekordów w każdym, CTE będzie działać znacznie gorzej niż tabele tymczasowe.

Tabele Temp są zawsze na dysku - tak długo, jak Twój CTE może być przechowywany w pamięci, najprawdopodobniej będzie szybszy(jak zmienna tabelkowa).

Ale z drugiej strony, jeśli obciążenie danych Twojego CTE (lub zmiennej tabeli temp) stanie się zbyt duże, będą one również przechowywane na dysku, więc nie ma większych korzyści.

Ogólnie rzecz biorąc, wolę CTE niż tabelę temp, ponieważ zniknął po tym, jak go użyłem. Nie muszę myśleć o tym wprost.

Więc na końcu nie ma jasnej odpowiedzi, ale osobiście wolałbym CTE niż tabele temp.

Tak więc zapytanie do optymalizacji zostało napisane dwoma CTE w SQL serverze. Zajęło 28 sekund.

Spędziłem dwie minuty konwertując je do tabel temp i zapytanie zajęło 3 sekundy

Dodałem indeks do tabeli temp na polu, na którym był łączony i zmniejszyłem go do 2 sekund

Trzy minuty pracy, a teraz działa 12x szybciej, wszystko przez usunięcie CTE. Osobiście Nie będę używał CTEs, nigdy nie są one trudniejsze do debugowania.

The crazy thing is CTE były używane tylko raz i nadal umieszczanie na nich indeksu okazało się o 50% szybsze.

CTE nie zajmie żadnej fizycznej przestrzeni. Jest to tylko zestaw wyników, którego możemy użyć join.

Tabele Temp są tymczasowe. Możemy tworzyć indeksy, ograniczenia jak normalne tabele do tego musimy zdefiniować wszystkie zmienne.

Zakres tabeli Temp tylko w ramach sesji. EX: Otwórz dwa okno zapytania SQL

create table #temp(empid int,empname varchar)
insert into #temp 
select 101,'xxx'

select * from #temp

Uruchom to zapytanie w pierwszym oknie następnie uruchom poniższe zapytanie w drugim oknie można znaleźć różnicę.

select * from #temp

Używałem obu, ale w ogromnych skomplikowanych procedurach zawsze uważałem tabele temp za lepsze do pracy i bardziej metodyczne. CTE mają swoje zastosowania, ale na ogół z niewielkimi danymi.

Na przykład stworzyłem Zębatki, które wracają z wynikami dużych obliczeń w 15 sekund, ale konwertują ten kod do uruchomienia w CTE i widziałem, że działa ponad 8 minut, aby osiągnąć te same wyniki.

Spóźnienie na imprezę, ale...

Środowisko, w którym pracuję jest bardzo ograniczone, wspierając niektóre produkty dostawców i dostarczając usługi o wartości dodanej, takie jak raportowanie. Ze względu na ograniczenia polityki i umowy, zwykle nie wolno mi korzystać z luksusu oddzielnej tabeli / przestrzeni danych i / lub możliwości tworzenia stałego kodu [robi się trochę lepiej, w zależności od aplikacji].

IOW, i nie mogą zazwyczaj tworzyć procedury składowane lub tabele UDFs lub temp itp. I pretty wiele trzeba zrobić wszystko za pośrednictwem mojego interfejsu aplikacji (Crystal Reports-Dodaj / link tabele, ustawić gdzie klauzule z W / W CR, itp.). Małą ratunkiem jest to, że Crystal pozwala mi używać poleceń (a także wyrażeń SQL). Niektóre rzeczy, które nie są wydajne dzięki zwykłej możliwości dodawania / łączenia tabel, można zrobić, definiując polecenie SQL. Używam CTEs przez to i uzyskałem bardzo dobre wyniki "zdalnie". CTE pomaga również w konserwacji / raportowaniu, nie wymagając, aby Kod był opracowany, przekazany dba do kompilacji, szyfrowania, transferu, instalacji, a następnie wymaga testowania wielopoziomowego. Mogę zrobić CTE przez lokalny interfejs.

Down page of using CTEs w / CR is, every report is separate. Każdy CTE musi być utrzymany dla każdego sprawozdania. Gdzie mogę zrobić SPs i UDFs, mogę stworzyć coś, co może być używane przez wiele raportów, wymagających tylko linkowania do SP i przekazywania parametrów, jak gdybyś pracował na zwykłej tabeli. CR nie jest zbyt dobry w prowadzeniu parametry do poleceń SQL, tak że aspekt CR/CTE może brakować. W takich przypadkach zwykle staram się zdefiniować CTE, aby zwrócić wystarczającą ilość danych (ale nie wszystkie dane), a następnie użyć możliwości wyboru rekordów w CR, aby pokroić i pokroić w kostkę.

Więc... mój głos jest na CTEs (dopóki nie dostanę mojej przestrzeni danych).

Jednym z zastosowań, w którym znalazłem doskonałą wydajność CTE, było miejsce, w którym musiałem połączyć stosunkowo złożone zapytanie do kilku tabel, które miały kilka milionów wierszy każda.

Użyłem CTE, aby najpierw wybrać podzbiór oparty na indeksowanych kolumnach, aby najpierw wyciąć te tabele do kilku tysięcy odpowiednich wierszy, a następnie dołączył CTE do mojego głównego zapytania. To wykładniczo zmniejszyło czas wykonywania moich zapytań.

Podczas gdy wyniki dla CTE nie są buforowane i zmienne tabeli mogą mieć był lepszym wyborem, naprawdę chciałem je wypróbować i znaleźć odpowiedni powyższy scenariusz.

Jest to naprawdę otwarte pytanie, a wszystko zależy od tego, jak jest używane i typu tabeli temp (tabela zmienna lub tradycyjna tabela).

Tradycyjna tabela temp przechowuje dane w temp DB, co spowalnia tabele temp; jednak zmienne tabel nie.

Właśnie to przetestowałem - zarówno CTE, jak i non-CTE (gdzie zapytanie było wypisywane dla każdej instancji Unii) obie zajmowały ~31 sekund. CTE uczyniło kod znacznie bardziej czytelnym - zmniejsz go z 241 do 130 linii, co jest bardzo ładne. Z kolei tabela Temp zmniejszyła go do 132 linii, a bieg trwał pięć sekund. Nie żartuj. wszystkie te testy były buforowane - wszystkie zapytania były uruchamiane wiele razy wcześniej.

Z mojego doświadczenia w SQL Server, znalazłem jeden ze scenariuszy, w których CTE przewyższa tabelę Temp

Potrzebowałem użyć zbioru danych(~100000) ze złożonego zapytania tylko raz w mojej procedurze składowanej.

• Tabela Temp powodowała narzut na SQL gdzie moja procedura była wykonujących się powoli (ponieważ tabele Temp są rzeczywistymi tabelami zmaterializowanymi, które exist in tempdb and persistent for the life of my current procedure)

• Z drugiej strony, z CTE, CTE utrzymują się tylko do następujące zapytanie jest uruchamiane. Tak więc CTE jest poręczną strukturą w pamięci o ograniczonej Zakres. CTE nie używa tempdb domyślnie.

Jest to jeden ze scenariuszy, w którym CTE może naprawdę pomóc uprościć kod i przewyższyć tabelę Temp. Użyłem 2 CTE, coś w stylu

WITH CTE1(ID, Name, Display) 
AS (SELECT ID,Name,Display from Table1 where <Some Condition>),
CTE2(ID,Name,<col3>) AS (SELECT ID, Name,<> FROM CTE1 INNER JOIN Table2 <Some Condition>)
SELECT CTE2.ID,CTE2.<col3>
FROM CTE2
GO