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


Odpowiedzi:


62

Powiedziałbym, że są to różne koncepcje, ale nie różnią się zbytnio od określenia „kreda i ser”.

  • Tabela tymczasowa nadaje się do ponownego wykorzystania lub wykonywania wielu przebiegów przetwarzania na zestawie danych.

  • CTE może służyć do powtarzania się lub po prostu do poprawy czytelności.
    Podobnie jak widok lub wbudowana funkcja wartościująca w tabeli może być również traktowana jak makro, które należy rozwinąć w głównym zapytaniu

  • Tabela tymczasowa to kolejna tabela z pewnymi regułami dotyczącymi zakresu

Mam zapisane procesy, w których używam zarówno (i zmiennych tabeli)


12
Tabele tymczasowe pozwalają również na indeksy, a nawet statystyki, które są czasami konieczne, podczas gdy CTE nie.
CodeCowboyOrg

9
Myślę, że ta odpowiedź nie podkreśla wystarczająco faktu, że CTE mogą prowadzić do strasznych wyników. Zwykle odwołuję się do tej odpowiedzi na dba.stackexchange. Twoje pytanie pojawia się na drugim miejscu w mojej wyszukiwarce, jeśli szukam, cte vs temporary tableswięc IMHO ta odpowiedź musi lepiej podkreślić wady CTE. TL; DR połączonej odpowiedzi: CTE nigdy nie powinno być używane do wykonania. . Zgadzam się z tym cytatem, ponieważ doświadczyłem wad CTE.
TT.

2
@TT. Ciekawy. Uważam, że CTE działają znacznie lepiej
Squ1rr3lz

198

To zależy.

Po pierwsze

Co to jest wspólne wyrażenie tabeli?

(Nierekurencyjne) CTE jest traktowane bardzo podobnie do innych konstrukcji, które mogą być również używane jako wyrażenia tabeli wbudowanej w SQL Server. Pochodne tabele, widoki i funkcje wartościowane w tabeli wbudowanej. Zauważ, że chociaż BOL mówi, że CTE „można traktować jako tymczasowy zestaw wyników”, jest to opis czysto logiczny. Najczęściej nie jest on sam w sobie zmaterializowany.

Co to jest tabela tymczasowa?

To jest zbiór wierszy przechowywanych na stronach danych w tempdb. Strony danych mogą znajdować się częściowo lub całkowicie w pamięci. Dodatkowo tabela tymczasowa 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

Plan 1

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

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

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

Materializacja definicji CTE

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

Wymagałoby to skopiowania około 8 GB danych do tabeli tymczasowej, a następnie nadal istnieje narzut związany z wyborem z niej.

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 na moim komputerze około 4 minut.

Tylko 15 wierszy z 1 000 000 losowo generowanych wartości pasuje do predykatu, ale kosztowne skanowanie tabeli odbywa się 16 razy w celu ich zlokalizowania.

wprowadź opis obrazu tutaj

Byłby to dobry kandydat do materializacji wyniku pośredniego. Równoważne przepisywanie tabeli tymczasowej zajęło 25 sekund.

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 

Z planem

Pośrednia materializacja części zapytania do tabeli tymczasowej może czasami być przydatna, nawet jeśli jest oceniana tylko raz - kiedy pozwala na rekompilację reszty zapytania z wykorzystaniem statystyk dotyczących zmaterializowanego wyniku. Przykład tego podejścia znajduje się w artykule SQL Cat When To Break Down Complex Queries .

W pewnych okolicznościach SQL Server użyje bufora do buforowania pośrednich wyników, np. CTE, i uniknie konieczności ponownej oceny tego poddrzewa. Jest to omówione w (zmigrowanym) elemencie Connect. Podaj wskazówkę, aby wymusić pośrednią materializację CTE lub tabel pochodnych . Jednak nie są tworzone żadne statystyki i nawet jeśli liczba zbuforowanych wierszy miała się znacznie różnić od szacowanej, nie jest możliwe, aby plan wykonania w toku dynamicznie dostosowywał się w odpowiedzi (przynajmniej w bieżących wersjach. Adaptacyjne plany zapytań mogą stać się możliwe w przyszłość).


33
Jest to jedyna odpowiedź, która daje odpowiedź na rzeczywiste pytanie (czyli pytanie, która ma lepszą wydajność, a nie jaka jest różnica lub która jest twoją ulubioną) i poprawnie odpowiada na to pytanie: „To zależy” jest właściwą odpowiedzią. Jest to również jedyna odpowiedź z danymi pomocniczymi do wyjaśnienia, kilka innych (z dużą liczbą głosów) stwierdziło, że jedna jest lepsza od drugiej bez odniesień i dowodów ... Żeby było jasne, wszystkie te odpowiedzi są również błędne . Ponieważ „To zależy”
Arkaine55

2
Jest to również dobrze napisana, dobrze przytoczona odpowiedź. Poważnie na najwyższym poziomie.
Dan Williams

50

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


9
Widziałem to z własnego doświadczenia. CTE działają znacznie wolniej.
goku_da_master

7
CTE również działają wolniej, ponieważ wyniki nie są buforowane. Więc za każdym razem, gdy używasz CTE, ponownie uruchamia zapytanie, plan i wszystko.
goku_da_master

1
A silnik db może zdecydować się na ponowne uruchomienie zapytania nie tylko dla każdego odwołania, ale dla każdego wiersza zapytania konsumenta, jako skorelowane podzapytanie ... musisz zawsze na to uważać, jeśli nie jest to pożądane.
Mike M,

Tabela tymczasowa jest przechowywana w tempdb na serwerze SQL Server, który jest dyskiem, ale ma tę zaletę, że jest indeksowany, a optymalizator SQL działa dobrze w przypadku wybranych zapytań. Nie jestem pewien, w której bazie danych lub obszarze dysku jest przechowywany CTE (gdy przekracza rozmiar pamięci i jest umieszczony w kolejce do stronicowania we / wy), ale nigdy nie jest optymalizowany z dużą ilością danych. Czasami korzystałem z opcji kompilatora (z rekompilacją), aby przyspieszyć
rmehra76

33

Tabele tymczasowe są zawsze na dysku - tak długo, jak długo CTE może być przechowywane w pamięci, najprawdopodobniej będzie szybsze (podobnie jak zmienna tabeli).

Ale z drugiej strony, jeśli ładowanie danych twojego CTE (lub zmiennej tabeli temp) stanie się zbyt duże, zostanie ono również zapisane na dysku, więc nie ma dużej korzyści.

Ogólnie wolę CTE od tabeli tymczasowej, ponieważ zniknął po tym, jak go użyłem. Nie muszę myśleć o rzuceniu tego wyraźnie ani o czymkolwiek.

Tak więc nie ma jasnej odpowiedzi w końcu, ale osobiście wolałbym CTE zamiast tabel temp.


2
W przypadku SQLite i PostgreSQL tabele tymczasowe automatycznie usuwane (zwykle na koniec sesji). Nie wiem jednak o innych DBMS.
Serrano

1
CTE jest jak tymczasowy widok. Dane AFAIK nie są przechowywane, więc nic nie może być przechowywane w pamięci ani na dysku. Ważna uwaga, za każdym razem, gdy używasz CTE, zapytanie jest uruchamiane ponownie.
Rob

1
Osobiście nigdy nie widziałem, aby CTE działało lepiej niż tabela Temp dla szybkości. A dobrze debugowanie jest znacznie łatwiejsze dzięki tabeli temp
Mark Monforti

7

Więc zapytanie, które zostałem przydzielony do optymalizacji, zostało napisane z dwoma CTE na serwerze SQL. Trwało to 28 sekund.

Spędziłem dwie minuty na konwersji ich na tabele tymczasowe, a zapytanie zajęło 3 sekundy

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

Trzy minuty pracy, a teraz działa 12x szybciej, a wszystko to dzięki usunięciu CTE. Osobiście nie będę używał CTE, które są trudniejsze do debugowania.

Szalone jest to, że CTE były używane tylko raz, a indeksowanie ich okazało się o 50% szybsze.


6

CTE nie zajmie żadnej fizycznej przestrzeni. To po prostu zbiór wyników, którego możemy użyć.

Tabele tymczasowe są tymczasowe. Możemy tworzyć indeksy, ograniczenia jak zwykłe tabele, do których musimy zdefiniować wszystkie zmienne.

Zakres tabeli tymczasowej tylko w ramach sesji. EX: Otwórz dwa okna zapytań SQL

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

select * from #temp

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

select * from #temp

4
>> „to tylko zbiór wyników, którego możemy użyć”. -> To nie jest dokładne. CTE nie jest „zestawem wyników”, ale kodem wbudowanym. Silnik zapytań SQL Server analizuje kod CTE jako część tekstu zapytania i tworzy zgodnie z nim plan wykonania. Pomysł, że CTE jest wbudowany, jest dużą zaletą używania CTE, ponieważ pozwala serwerowi na stworzenie „planu wykonania połączenia”
Ronen Ariely,

4

Używałem obu, ale w ogromnych, złożonych procedurach zawsze znajdowałem tabele tymczasowe jako lepsze i bardziej metodyczne. CTE mają swoje zastosowania, ale generalnie mają małe dane.

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


3

Spóźniony na przyjęcie, ale ...

Środowisko, w którym pracuję, jest bardzo ograniczone, ponieważ obsługuje produkty niektórych dostawców i zapewnia usługi o wartości dodanej, takie jak raportowanie. Ze względu na ograniczenia wynikające z polityki i umowy, zwykle nie pozwala mi się na luksus oddzielnej tabeli / przestrzeni danych i / lub możliwość tworzenia stałego kodu [robi się to trochę lepiej, w zależności od aplikacji].

IOW, zwykle nie mogę opracować procedury składowanej, UDF lub tabel tymczasowych itp. Prawie wszystko muszę robić za pośrednictwem interfejsu aplikacji (Crystal Reports - dodaj / połącz tabele, ustaw gdzie klauzule z w / w CR itp. ). Jedną MAŁĄ zaletą 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 przez to CTE i uzyskałem bardzo dobre wyniki „zdalnie”. CTE pomagają również w utrzymywaniu raportów, nie wymagając opracowywania kodu, przekazywania administratorowi bazy danych w celu kompilacji, szyfrowania, przesyłania, instalacji, a następnie wymagają wielopoziomowego testowania. Mogę wykonać CTE przez interfejs lokalny.

Wadą używania CTE z CR jest to, że każdy raport jest oddzielny. Dla każdego raportu należy zachować każdy CTE. Tam, gdzie mogę robić SP i UDF, mogę opracować coś, co może być używane przez wiele raportów, wymagając tylko łączenia z SP i przekazywania parametrów, tak jakbyś pracował na zwykłej tabeli. CR nie radzi sobie zbyt dobrze z parametrami w poleceniach SQL, więc może brakować tego aspektu CR / CTE. W takich przypadkach zwykle próbuję zdefiniować CTE, aby zwrócić wystarczającą ilość danych (ale nie WSZYSTKICH danych), a następnie używam możliwości wyboru rekordów w CR, aby pokroić to w kostkę.

Więc ... mój głos jest na CTE (dopóki nie otrzymam przestrzeni danych).


3

Jednym z zastosowań, w których znalazłem doskonałą wydajność CTE, było połączenie stosunkowo złożonego zapytania z kilkoma tabelami, z których każda miała kilka milionów wierszy.

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

Chociaż wyniki dla CTE nie są zapisywane w pamięci podręcznej, a zmienne tabeli mogły być lepszym wyborem, tak naprawdę chciałem je po prostu wypróbować i znalazłem pasujące do powyższego scenariusza.


Myślę też, że ponieważ używam CTE tylko w połączeniu, tak naprawdę wykonuję CTE tylko raz w moim zapytaniu, więc buforowanie wyników nie było tak dużym problemem pod tym względem
zakupy

1

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

Tradycyjna tabela tymczasowa przechowuje dane w tymczasowej bazie danych, co spowalnia tabele tymczasowe; jednak zmienne tabeli nie.


1

Właśnie to przetestowałem - zarówno CTE, jak i nie-CTE (gdzie zapytanie zostało wpisane dla każdej instancji unii) zajęło ~ 31 sekund. CTE uczyniło kod bardziej czytelnym - zmniejszyłem go z 241 do 130 linii, co jest bardzo miłe. Z drugiej strony tabela Temp zmniejszyła ją do 132 linii i zajęła pięć sekund. Bez żartów. wszystkie te testy zostały zapisane w pamięci podręcznej - wcześniej wszystkie zapytania były uruchamiane wiele razy.


1

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

Musiałem użyć zestawu danych (~ 100000) ze złożonego zapytania tylko RAZ w mojej procedurze składowanej.

  • Tabela tymczasowa powodowała obciążenie w języku SQL, w którym moja procedura działała powoli (ponieważ tabele tymczasowe to rzeczywiste zmaterializowane tabele, które istnieją w tempdb i utrzymują się przez cały okres mojej bieżącej procedury)

  • Z drugiej strony, w przypadku CTE, CTE utrzymuje się tylko do momentu uruchomienia następującego zapytania. Tak więc CTE jest poręczną strukturą w pamięci z ograniczonym zakresem. CTE nie używają domyślnie tempdb.

Jest to jeden ze scenariuszy, w którym CTE mogą naprawdę pomóc uprościć kod i przewyższyć tabelę temp. Miał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

1
Twoja odpowiedź wydaje się być bardzo ogólna ... Jak mierzysz, że „CTE przewyższył tabelę temp.”? Masz jakieś pomiary czasu? Moim zdaniem powinieneś zmienić swoją odpowiedź i dodać więcej szczegółów.
Il Vic

Tak, mam pomiary czasu i plan wykonania na poparcie mojego oświadczenia.
Amardeep Kohli

Nie można dodać img do planu wykonania z powodu ograniczonych uprawnień. Zaktualizuj szczegóły po rozwiązaniu problemu
Amardeep Kohli
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.