Jaka jest różnica między CTE a tabelą Temp?


150

Jaka jest różnica między Common Table Expression (CTE) a tabelą tymczasową?A kiedy powinienem używać jednego nad drugim?

CTE

WITH cte (Column1, Column2, Column3)
AS
(
     SELECT Column1, Column2, Column3
     FROM SomeTable
)

SELECT * FROM cte

Tabela temp

SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable
+3

Zobacz także [Jaka jest różnica między tabelą tymczasową a zmienną tabeli w SQL Server?] (Http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table -variable-in-sql-server) 15 lut. 122012-02-15 17:02:17

+1

Powiązane z SO: [Które są bardziej wydajne, CTE lub tabele tymczasowe?] (Http://stackoverflow.com/a/698634/27535) 16 lut. 122012-02-16 07:52:38

178

Jest to dość szerokie, ale dam ci tak ogólną odpowiedź, jak tylko mogę.

CTE ...

  • Są niezindeksowalne (ale mogą używać istniejących indeksów na obiektach, do których istnieją odwołania)
  • Nie można mieć ograniczeń
  • Są zasadniczo jednorazowe VIEW s
  • Trwać tylko do momentu uruchomienia następnego zapytania
  • Może być rekurencyjny
  • Nie masz dedykowanych statystyk (polegaj na statystykach dotyczących podstawowych obiektów)

#Temp Tables ...

  • Są prawdziwe zmaterializowane tabele, które istnieją w tempdb
  • Może być indeksowany
  • Może mieć ograniczenia
  • Trwaj w życiu obecnego POŁĄCZENIA
  • Może odwoływać się do innych zapytań lub podprocedur
  • Masz dedykowane statystyki generowane przez silnik

Jeśli chodzi o użycie każdego z nich, mają bardzo różne przypadki użycia.Jeśli masz bardzo duży zestaw wyników lub musisz odwołać się do niego więcej niż raz, umieść go w tabeli #temp .Jeśli ma być rekurencyjny, jednorazowy lub po prostu dla uproszczenia logicznego, preferowany jest CTE .

Ponadto należy CTEnigdy nie używaj do wykonania.Prawie nigdy nie przyspieszysz przy użyciu CTE, ponieważ znowu jest to widok jednorazowy.Możesz zrobić z nimi kilka fajnych rzeczy, ale przyspieszenie zapytania nie jest jednym z nich.


23

EDYTOWAĆ:

Zobacz komentarze Martina poniżej:

CTE nie jest zmaterializowany jako tabela w pamięci.Jest to tylko sposób na enkapsulację definicji zapytania.W przypadku OP będzie on wstawiony i taki sam jak po prostu SELECT Column1, Column2, Column3 FROM SomeTable .W większości przypadków nie zostają zmaterializowane z góry, dlatego nie zwraca wierszy WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X , a także sprawdza plany wykonania.Chociaż czasami możliwe jest włamanie się do planu, aby uzyskać szpulę.Istnieje element łączący, który żąda podpowiedzi.- Martin Smith 15 lutego 12 o 17:08


Oryginalna odpowiedź

CTE

Read more on MSDN

CTE tworzy tabelę używaną w pamięci, ale jest ważna tylko dla określonego zapytania następującego po niej.Przy użyciu rekursji może to być efektywna struktura.

Możesz także rozważyć użycie zmiennej tabeli.Jest on używany jako tabela tymczasowa i może być użyty wiele razy bez konieczności ponownego materializowania dla każdego połączenia.Ponadto, jeśli chcesz utrwalić kilka rekordów teraz, dodaj kilka kolejnych rekordów po następnej selekcji, dodaj kilka kolejnych rekordów po innym op, a następnie zwróć tylko te kilka rekordów, to może być poręczna struktura, jak to robi nie trzeba go upuszczać po wykonaniu.Głównie tylko cukier syntaktyczny.Jeśli jednak utrzymasz niską liczbę wierszy, nigdy nie zmaterializuje się na dysku.Zobacz What's the difference between a temp table and table variable in SQL Server? aby uzyskać więcej informacji.

Tabela temp

Read more on MSDN - Scroll down about 40% of the way

Tabela tymczasowa jest dosłownie tabelą utworzoną na dysku, tylko w konkretnej bazie danych, którą każdy może usunąć.Obowiązkiem dobrego demona jest zniszczenie tych tabel, gdy nie są już potrzebne, ale administrator bazy danych może również je wymazać.

Stoły tymczasowe występują w dwóch odmianach: lokalna i globalna.Pod względem serwera MS Sql używasz oznaczenia #tableName dla lokalnego i ##tableName oznaczenia globalnego (zwróć uwagę na użycie pojedynczego lub podwójnego # jako cechy identyfikującej).

Zauważ, że w tabelach tymczasowych, w przeciwieństwie do zmiennych tabel lub CTE, możesz stosować indeksy i tym podobne, ponieważ są to legalnie zapisane tabele w normalnym znaczeniu tego słowa.


Zwykle używałbym tabel tymczasowych dla dłuższych lub większych zapytań, a także CTE lub zmiennych tabeli, jeśli miałem już mały zestaw danych i chciałem po prostu szybko napisać trochę kodu dla czegoś małego.Doświadczenie i porady innych wskazują, że powinieneś używać CTE, w których masz małą liczbę wierszy zwracanych z niego.Jeśli masz dużą liczbę, prawdopodobnie skorzystasz z możliwości indeksowania tabeli tymczasowej.

+11

CTE nie jest zmaterializowany jako tabela w pamięci.Jest to tylko sposób na enkapsulację definicji zapytania.W przypadku OP zostanie on wstawiony i tak samo jak po prostu 'SELECT Column1, Column2, Column3 FROM SomeTable' 15 lut. 122012-02-15 16:55:26

+4

W większości przypadków nie są zmaterializowane z przodu, dlatego nie zwraca wierszy 'Z T (X) AS (SELECT NEWID()) WYBIERZ * OD T T1 DOŁĄCZ T T2 WŁĄCZ T1.X = T2.X', sprawdź także plany wykonania.Chociaż czasami możliwe jest [włamanie się do planu] (http://explainextended.com/2009/05/28/generating-xml-in-subqueries/), aby uzyskać bufor.Istnieje [podłącz element] (https://connect.microsoft.com/SQLServer/feedback/details/218968/provide-a-hint-to-force-intermediate-materialization-of-ctes-or-derived-tables) prosząc o podpowiedź. 15 lut. 122012-02-15 17:08:33


12

CTE może być wywoływany wielokrotnie w zapytaniu i jest oceniany za każdym razem, gdy jest przywoływany - proces ten może być rekurencyjny.Jeśli jest tylko raz skierowany, zachowuje się podobnie jak pod-zapytanie, chociaż CTE można sparametryzować.

Tabela tymczasowa jest fizycznie utrwalona i może być indeksowana.W praktyce optymalizator zapytań może również utrwalać łączenie pośrednie lub wyniki pod-zapytań za kulisami, takie jak operacje buforowania, więc nie jest prawdą, że wyniki CTE nigdy nie są utrwalane na dysku.

Zmienne tabeli IIRC (z drugiej strony) są zawsze strukturami w pamięci.

+4

CTE można sparametryzować?W jaki sposób?Ponadto zmienne tabelowe nie są * zawsze * strukturami w pamięci.Zobacz [doskonałą odpowiedź] Martina (http://dba.stackexchange.com/a/16386/1192) na powiązane pytanie. 30 wrz. 162016-09-30 06:14:36


10

Tabela temp jest prawdziwym obiektem w tempdb, ale cte jest tylko rodzajem opakowania wokół złożonego zapytania, aby uprościć składnię organizowania rekursji w jednym kroku.


12

accepted answer tutaj mówi: „CTE nigdy nie powinien być używany do osiągów” - ale może to wprowadzić w błąd.W kontekście CTE w porównaniu z tabelami tymczasowymi, właśnie skończyłem usuwać próbkę śmieci ze zbioru przechowywanych proc, ponieważ niektóre doofusy musiały myśleć, że używanie tabel tymczasowych jest niewielkie lub żadne.Wrzuciłem los do CTE, z wyjątkiem tych, które zgodnie z prawem miały być ponownie użyte w całym procesie.Uzyskałem około 20% wydajności według wszystkich wskaźników.Następnie ustawiłem usuwanie wszystkich kursorów, które próbowały zaimplementować przetwarzanie rekurencyjne.To tam widziałem największy zysk.Skończyło się na dziesięciokrotnym skróceniu czasu reakcji.

CTE i tabele tymczasowe mają bardzo różne przypadki użycia.Chcę tylko podkreślić, że chociaż nie jest to panaceum, zrozumienie i prawidłowe użycie CTE może prowadzić do naprawdę doskonałych ulepszeń zarówno jakości kodu, jak i łatwości konserwacji i szybkości.Odkąd się nimi zajmuję, widzę tabele i kursory jako wielkie zło przetwarzania SQL.Mogę teraz dobrze radzić sobie ze zmiennymi tabeli i CTE dla prawie wszystkiego teraz.Mój kod jest czystszy i szybszy.

  0

Bądźmy uczciwi - kursorzy są * wielkimi * złymi;tabele tymczasowe są w najgorszym wypadku * mniejszym * złem.:-) To naprawdę * niesprawiedliwe umieszczać je na tym samym poziomie, co widziałeś sam. 22 sty. 182018-01-22 20:50:17

  0

@ RDFozz ma rację, [piekło ma 9 kręgów, jak wszyscy wiemy] (https://en.wikipedia.org/wiki/Inferno_ (Dante) #Nine_circles_of_Hell).Pozwala umieścić tabele tymczasowe na 2. i kursory na ... 7.?;) 22 sty. 182018-01-22 21:52:08


6

Głównym powodem używania CTE jest dostęp do funkcji okna, takich jak row_number() i wiele innych.

Oznacza to, że możesz szybko i sprawnie more efficiently than other means in most practical cases pierwszy lub ostatni wiersz na grupę BARDZO BARDZO - more efficiently than other means in most practical cases .

with reallyfastcte as (
select *, 
row_number() over (partition by groupingcolumn order by sortingcolumn) as rownum
from sometable
)
select *
from reallyfastcte
where rownum = 1;

Możesz uruchomić podobne zapytanie do powyższego, używając skorelowanego podzapytania lub używając zapytania podrzędnego, ale CTE będzie szybszy w prawie wszystkich scenariuszach.

Ponadto CTE mogą naprawdę uprościć kod.Może to prowadzić do wzrostu wydajności, ponieważ lepiej rozumiesz zapytanie i możesz wprowadzić więcej logiki biznesowej, aby optymalizator był bardziej selektywny.

Ponadto CTE mogą zwiększyć wydajność, jeśli zrozumiesz swoją logikę biznesową i wiesz, które części zapytania powinny być uruchamiane jako pierwsze - zazwyczaj umieść najpierw najbardziej selektywne zapytania, które prowadzą do zestawów wyników, które mogą używać indeksu w następnym połączeniu i dodaj option(force order) podpowiedź zapytania

Wreszcie, CTE nie używają domyślnie tempdb, więc ograniczasz rywalizację na tym wąskim gardle poprzez ich użycie.

Tabele tymczasowe powinny być używane, jeśli konieczne jest wielokrotne wysyłanie zapytań do danych, lub alternatywnie, jeśli mierzysz swoje zapytania i odkrywasz, że wstawiasz je do tabeli tymczasowej, a następnie dodajesz indeks, który poprawia wydajność.

  0

wszystkie dobre punkty ... +1 30 sty. 182018-01-30 13:05:25


5

Wydaje się, że w stosunku do CTE jest trochę negatywności.

Moje rozumienie CTE polega na tym, że jest to w zasadzie rodzaj adhoc.SQL jest językiem deklaratywnym i opartym na zestawach.CTE to świetny sposób na zadeklarowanie zestawu!Brak możliwości indeksowania CTE jest właściwie dobrą rzeczą, ponieważ nie musisz!To naprawdę rodzaj cukru syntaktycznego, dzięki któremu zapytanie jest łatwiejsze do odczytu/zapisu.Każdy przyzwoity optymalizator opracuje najlepszy plan dostępu przy użyciu indeksów w tabelach podstawowych.Oznacza to, że można skutecznie przyspieszyć zapytanie CTE, postępując zgodnie z zaleceniami dotyczącymi indeksów w tabelach podstawowych.

Ponadto, ponieważ zdefiniowałeś zestaw jako CTE, nie oznacza to, że wszystkie wiersze w zestawie muszą zostać przetworzone.W zależności od zapytania optymalizator może przetworzyć „tylko tyle” wierszy, aby spełnić zapytanie.Może potrzebujesz tylko pierwszych 20 lub więcej na ekranie.Jeśli zbudowałeś tabelę tymczasową, naprawdę musisz czytać/zapisywać wszystkie te wiersze!

Na tej podstawie powiedziałbym, że CTE są świetną cechą SQL i mogą być używane wszędzie, gdzie ułatwiają czytanie.Zastanowiłbym się tylko nad tabelą tymczasową dla procesu wsadowego, który naprawdę musiałby przetworzyć każdy pojedynczy rekord.Nawet wtedy afaik nie jest tak naprawdę zalecany, ponieważ w tabeli tymczasowej znacznie trudniej jest bazie danych pomóc w buforowaniu i indeksowaniu.Lepiej byłoby mieć stały stół z polem PK unikalnym dla danej transakcji.

Muszę przyznać, że moje doświadczenie dotyczy głównie DB2, więc zakładam, że CTE działają w podobny sposób w obu produktach.Będę szczęśliwie poprawiony, jeśli CTE są w jakiś sposób gorsze w serwerze SQL.;)