Czy istnieje różnica w wydajności między CTE, podzapytaniem, tabelą tymczasową lub zmienną tabelową?


222

W tym doskonałym pytaniu SO omówiono różnice między CTEi sub-queries.

Chciałbym konkretnie zapytać:

W jakich okolicznościach każde z poniższych jest bardziej wydajne / szybsze?

  • CTE
  • Pod-zapytanie
  • Stół tymczasowy
  • Zmienna tabeli

Tradycyjnie używałem dużo do temp tablesprogramowania stored procedures- ponieważ wydają się one bardziej czytelne niż wiele powiązanych ze sobą zapytań cząstkowych.

Non-recursive CTEs bardzo dobrze enkapsulują zestawy danych i są bardzo czytelne, ale czy istnieją szczególne okoliczności, w których można powiedzieć, że zawsze będą działały lepiej? czy może zawsze trzeba bawić się z różnymi opcjami, aby znaleźć najbardziej wydajne rozwiązanie?


EDYTOWAĆ

Ostatnio powiedziano mi, że pod względem wydajności tabele tymczasowe są dobrym wyborem, ponieważ mają powiązany histogram, tj. Statystyki.


4
Ogólna odpowiedź: to zależy. I zależy to od wielu czynników, każde ogólne stwierdzenie jest prawdopodobnie fałszywe - w niektórych sytuacjach. Zasadniczo: musisz przetestować i zmierzyć - sprawdź, który z nich jest dla Ciebie najlepszy!
marc_s

@marc_s - ok; może to pytanie należy zamknąć, ponieważ jest subiektywne? Pamiętaj, że wiele pytań SQL dotyczących SO można uznać za subiektywne.
whytheq

1
Może zostać zamknięty, ponieważ jest zbyt szeroki - i zgadzam się z tobą - wiele rzeczy i tematów w SQL naprawdę dostanie odpowiedź , zależy od tego . Czasami można wymienić dwa lub trzy kryteria, aby podjąć decyzję, ale tutaj twoje pytanie jest prawie niemożliwe, aby udzielić solidnej porady - zależy to od bardzo - twoich struktur tabel, danych w tych tabelach, zapytań, których używasz, twoja strategia indeksowania i wiele więcej ....
marc_s,

@marc_s dobrze byłoby spróbować i zachować - jakieś porady na temat możliwych modyfikacji OP, aby spróbować uczynić go bardziej szczegółowym i wąskim?
whytheq

Uwaga: to pytanie dotyczy SQL Server. W przypadku innych baz danych, takich jak postgres, CTE jest często znacznie wolniejszy niż równoważne podzapytania (patrz http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ )
Jay,

Odpowiedzi:


243

SQL jest językiem deklaratywnym, a nie językiem proceduralnym. Oznacza to, że konstruujesz instrukcję SQL, aby opisać pożądane wyniki. Nie mówisz silnikowi SQL, jak wykonać tę pracę.

Zasadniczo dobrym pomysłem jest pozwolić silnikowi SQL i optymalizatorowi SQL na znalezienie najlepszego planu zapytań. Opracowanie silnika SQL wymaga wielu osobo-letnich starań, więc niech inżynierowie zrobią to, co umieją.

Oczywiście zdarzają się sytuacje, w których plan zapytań nie jest optymalny. Następnie chcesz użyć wskazówek dotyczących zapytania, zrestrukturyzować zapytanie, zaktualizować statystyki, użyć tabel tymczasowych, dodać indeksy itp., Aby uzyskać lepszą wydajność.

Co do twojego pytania. Wydajność CTE i podzapytań powinna teoretycznie być taka sama, ponieważ oba zapewniają te same informacje optymalizatorowi zapytań. Jedną różnicą jest to, że CTE użyte więcej niż jeden raz można łatwo zidentyfikować i obliczyć raz. Wyniki można następnie zapisać i odczytać wiele razy. Niestety, SQL Server nie wydaje się korzystać z tej podstawowej metody optymalizacji (można to nazwać wspólną eliminacją podzapytań).

Tabele tymczasowe to inna sprawa, ponieważ zapewniasz więcej wskazówek na temat uruchamiania zapytania. Jedną z głównych różnic jest to, że optymalizator może wykorzystać statystyki z tabeli tymczasowej do ustalenia swojego planu zapytań. Może to spowodować wzrost wydajności. Ponadto, jeśli masz skomplikowane CTE (podzapytanie), które jest używane więcej niż jeden raz, wówczas przechowywanie go w tabeli tymczasowej często zapewnia zwiększenie wydajności. Zapytanie jest wykonywane tylko raz.

Odpowiedź na twoje pytanie brzmi: musisz się pobawić, aby uzyskać oczekiwaną wydajność, szczególnie w przypadku złożonych zapytań, które są uruchamiane regularnie. W idealnym świecie optymalizator zapytań znalazłby idealną ścieżkę wykonania. Chociaż często tak się dzieje, możesz znaleźć sposób na uzyskanie lepszej wydajności.


11
Niektóre badania firmy Microsoft dotyczące możliwych przyszłych ulepszeń w tym obszarze znajdują się w publikacji „Skuteczne wykorzystanie podobnych podwyrażeń do przetwarzania zapytań”, dostępnej tutaj
Martin Smith,

3
Biorąc pod uwagę, że ten artykuł został zaprezentowany w 2007 roku, masz pojęcie, czy włączono go do SQL Server 2012?
Gordon Linoff,

3
Świetna odpowiedź! Dla podkreślenia: SQL jest językiem deklaratywnym i nie kontrolujemy, JAK dane są pobierane. Dlatego wydajność / szybkość różni się w zależności od zapytania.
Simcha Khabinsky

2
@RGS. . . Indeksy w tabelach tymczasowych zdecydowanie poprawiają zapytania, które mogą skorzystać z tych indeksów - podobnie jak w przypadku indeksów w tabeli stałej. Ale jeśli zmaterializujesz podzapytanie jako tabelę tymczasową, możesz stracić przewagę indeksów w oryginalnych tabelach.
Gordon Linoff,

2
@RGS. . .Gdy silnik bazy danych zmaterializuje podzapytanie / CTE w trakcie wykonywania złożonego zapytania, nie dodaje indeksów do materializacji. Możesz to zrobić ręcznie, korzystając z tabel tymczasowych.
Gordon Linoff,

77

Nie ma reguły. Uważam, że CTE są bardziej czytelne i używam ich, chyba że wykazują pewne problemy z wydajnością, w takim przypadku badam rzeczywisty problem, a nie domyślam się, że CTE jest problemem i próbuję go napisać inaczej, stosując inne podejście. Zazwyczaj chodzi o coś więcej niż sposób, w jaki zdecydowałem się zadeklarować moje zamiary za pomocą zapytania.

Z pewnością istnieją przypadki, w których można rozwikłać CTE lub usunąć podzapytania i zastąpić je tabelą #temp i skrócić czas trwania. Może to wynikać z różnych rzeczy, takich jak nieaktualne statystyki, niemożność uzyskania nawet dokładnych statystyk (np. Połączenie z funkcją o wartościach w tabeli), równoległość lub nawet niemożność wygenerowania optymalnego planu z powodu złożoności zapytania ( w takim przypadku jego rozbicie może dać optymalizatorowi szansę na walkę). Ale są też przypadki, w których operacje we / wy związane z tworzeniem tabeli temperatur mogą przewyższać inne aspekty wydajności, które mogą sprawić, że dany kształt planu będzie mniej atrakcyjny przy użyciu CTE.

Szczerze mówiąc, istnieje zbyt wiele zmiennych, aby zapewnić „poprawną” odpowiedź na twoje pytanie. Nie ma przewidywalnego sposobu dowiedzenia się, kiedy zapytanie może przechylić się na korzyść takiego czy innego podejścia - po prostu wiedz, że teoretycznie ta sama semantyka dla CTE lub pojedynczego podzapytania 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ą - może być tak, że odkryłeś ograniczenie w optymalizatorze (lub odkryłeś znane), lub może być tak, że twoje zapytania nie są semantycznie równoważne lub ten zawiera element, który udaremnia optymalizację.

Sugeruję więc napisanie zapytania w sposób, który wydaje ci się najbardziej naturalny, i odbiegać tylko wtedy, gdy odkryjesz rzeczywisty problem z wydajnością, jaki ma optymalizator. Osobiście oceniam je według CTE, a następnie podzapytania, przy czym tablica #temp jest ostatecznością.


4
+1 okazuje się być subiektywnym pytaniem; Mam nadzieję, że nie zostanie zamknięty, ponieważ jest zbyt niejasny, ponieważ dotychczasowe odpowiedzi są pouczające. Rozumiem :-) nie podoba ci się, gdy zmieniają się pytania, ale czy masz jakieś sugestie dotyczące zawężenia pytania w PO?
whytheq

2
Myślę, że to pytanie jest w porządku, zauważysz, że nie ma jeszcze jednego głosu do zamknięcia, ale jeśli odpowiedzi zaczną się wymachiwać, to prawdopodobnie zostanie zamknięte. Jak zasugerowałem w mojej odpowiedzi, jeśli masz konkretny przypadek, w którym widzisz dużą różnicę między CTE a podzapytaniem, zacznij nowe pytanie od rzeczywistych zapytań i planów wykonania (i może być lepiej pasować do dba.se ) . Po prostu zdaj sobie sprawę, że odpowiedź na to pytanie może nie być taka sama dla innego zapytania z tym samym scenariuszem.
Aaron Bertrand

Tuż pod twoim pytaniem znajdują się linki link / edit / close / flag- jeśli były głosy, by zamknąć pytanie, zobaczysz close (n)gdzie nreprezentuje liczbę użytkowników, którzy głosowali za zamknięciem twojego pytania. Po kliknięciu linku zobaczysz powody, dla których użytkownicy zostali wybrani.
Aaron Bertrand

@whytheq zobacz także ten najnowszy post na blogu autorstwa Boba Beauchemina . Nie traktuje konkretnie CTE vs. podzapytania, ale stosuje się ten sam rodzaj koncepcji: jeśli wybierzesz nieintuicyjny wzorzec ze względu na wydajność, udokumentuj to bzdury i ponownie je odwiedzaj, aby upewnić się, że dziwactwo, które odkryłeś, jest nadal prawdziwe. Mogę nawet zasugerować pozostawienie komentarza do bardziej naturalnej wersji zapytania, chyba że masz niezawodny system kontroli źródła, który utrzymuje poprzednią wersję.
Aaron Bertrand

1
Naprawiono link powyżej: sqlskills.com/blogs/bobb/…
ADJenks

19

#temp jest zmaterializowany, a CTE nie.

CTE to tylko składnia, więc teoretycznie jest to tylko podzapytanie. Jest wykonywany. #temp jest zmaterializowany. Dlatego kosztowne CTE w łączeniu, które jest wykonywane wielokrotnie, może być lepsze 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ą ludzie na SO, którzy nie lubią zmiennych tabeli, ale lubię je, ponieważ są zmaterializowane i szybsze w tworzeniu niż #temp. Są chwile, kiedy optymalizator zapytań radzi sobie lepiej z wartością #temp w porównaniu ze zmienną tabelową.

Możliwość utworzenia PK na zmiennej #temp lub tabeli daje optymalizatorowi zapytania więcej informacji niż CTE (ponieważ nie można zadeklarować PK na CTE).


co to akronim „TVP” ... coś podobnego do #temp?
whytheq

TVP staje się popularnym terminem, ponieważ brzmi imponująco (dla niektórych). W skrócie, TVP to tabela przekazywana jako parametr. Każdy, kto użył zmiennych tabeli, będzie z nimi w domu.
WonderWorker

1
OSTRZEŻENIE - TVP nie mają planów wykonania! Nie używaj TVP do niczego innego, najprostszego z krótkich list odnośników. Jeśli wykonasz na nich złożone połączenia, wstawki lub aktualizacje, możesz napotkać ogromne problemy z optymalizacją. Zaufaj mi, zostałem przez to spalony.
Heliac

12

Myślę, że tylko 2 rzeczy sprawiają, że ZAWSZE lepiej jest używać tabeli # Temp niż CTE:

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

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


-ponad wczoraj

Oto przykład, w którym ograniczenia #table mogą zapobiegać 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;

3
ALWAYSjest trochę za daleko, ale dziękuję za odpowiedź. Jeśli chodzi o czytelność, użycie CTE może być dobrą rzeczą.
whytheq

3
W ogóle nie rozumiem twojego drugiego punktu. Z mojego punktu widzenia zapytanie definiujące CTE jest analogiczne do ograniczeń, które nałożyłbyś na tabelę tymczasową, zauważając, że to pierwsze może zawierać dowolnie złożone predykaty, podczas gdy drugie jest znacznie bardziej ograniczone (np. CHECKOgraniczenie odnoszące się do wielu wierszy / tabel jest nie dozwolony). Czy możesz opublikować przykład, w którym CTE wykazuje błąd, którego nie ma odpowiednik tabeli tymczasowej?
dniu
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.