Różnica między CTE a SubQuery?

143

Z tego postu Jak używać ROW_NUMBER w następującej procedurze?

Istnieją dwie wersje odpowiedzi, w których jedna używa a, sub-querya druga używa a, CTEaby rozwiązać ten sam problem.

A zatem, jaka jest zaleta używania zapytania CTE (Common Table Expression)nad „pod-zapytaniem” (a zatem bardziej czytelne, co faktycznie robi zapytanie)

Jedyną zaletą korzystania z CTEover sub-selectjest to, że rzeczywiście mogę nazwaćsub-query . Czy istnieją inne różnice między tymi dwoma, gdy CTE jest używane jako proste (nierekursywne) CTE?

— dance2die
źródło

Pytanie pochodne z dobrą dyskusją: stackoverflow.com/q/11169550/781695

— użytkownik

7

IMO, każdy, kto uważa, że CTE jest mniej czytelny, że gigantyczna plama przeplatających się podzapytań nie dostrzegła stosu niewyraźnych zapytań w kształcie zębów piły używanych w większości systemów zarządzania danymi przedsiębiorstwa. Duże, nietrywialne zapytania są zazwyczaj znacznie łatwiejsze do odczytania później lub dla nowych oczu niż podzapytania, a przynajmniej w przypadku Postgres magicznie działają znacznie lepiej w wielu przypadkach. ([Z powodów, których jeszcze nie zrozumiałem [( stackoverflow.com/questions/33731068/… ), ponieważ jest odwrotnie, wydaje się bardziej prawdopodobne.)

— zxq9

102

W wersji z zapytaniem podrzędnym i prostym (nierekurencyjnym) CTE są one prawdopodobnie bardzo podobne. Musiałbyś użyć programu profilującego i aktualnego planu wykonania, aby wykryć wszelkie różnice, a to byłoby specyficzne dla twojej konfiguracji (więc nie możemy podać pełnej odpowiedzi).

W ogóle ; CTE może być używane rekurencyjnie; zapytanie podrzędne nie może. Dzięki temu szczególnie dobrze nadają się do konstrukcji drzewiastych.

— Marc Gravell
źródło

1

Przepraszam, moje pytanie powinno być jaśniejsze. Jaka byłaby różnica między CTE a podzapytaniem w kontekście, w którym CTE jest używane LIKE?

— dance2die

2

@Marc Gravell: Możemy jednak zrobić więcej niż to, ponieważ zachowanie profilera nie jest gwarantowane, w porównaniu z zachowaniem CTE, którym jest (w zakresie oceny).

— casperOne

1

Nie jestem pewien, jak bardzo to stwierdzenie ma sens dla osób, które patrzą na CTS i różnicę w podzapytaniach - A CTE can be used recursively; a sub-query cannot. Przykład byłby świetny.

— Aniket Thakur

88

Główną zaletą wyrażenia Common Table Expression (kiedy nie używasz go do zapytań rekurencyjnych ) jest hermetyzacja, zamiast deklarowania zapytania podrzędnego w każdym miejscu, w którym chcesz go użyć, możesz je zdefiniować raz, ale masz wiele odniesień do tego.

Jednak ten sposób nie oznacza, że jest on wykonywany tylko raz (jak w poprzednich iteracjach tym samym odpowiedź , dziękuję wszystkim osobom, które komentuje). Zapytanie z pewnością ma potencjał do wielokrotnego wykonania, jeśli odwołuje się do niego wiele razy; optymalizator zapytań ostatecznie podejmuje decyzję, jak należy interpretować CTE.

— casperOne
źródło

„Pomyśl o CTE jako zmiennej tabeli tymczasowej” czy to oznacza, że CTE jest przechowywane na dysku lub w pamięci?

— dance2die

Z definicji nie można używać CTE ani podzapytania w wielu zapytaniach. Jestem prawie pewien, że optymalizator obsługuje podzapytanie w taki sam sposób, jak obsługiwałby CTE (ocenianie zestawu wyników tylko raz, niezależnie od tego, ile razy jest on używany w zapytaniu 1)

— AlexCuse

@AlexCuse: Myślę, że wystarczająco wyjaśniłem kontekst CTE, ale dodałem więcej, aby spróbować wyjaśnić więcej.

— casperOne

@AlexCuse: Nie ma również implikacji, że CTE lub podzapytanie mogą być używane w wielu miejscach. Różnica między CTE a optymalizatorem polega jednak na tym, że zachowanie CTE jest gwarantowane, podczas gdy zachowanie optymalizatora nie.

— casperOne

i przyznam, że mogą istnieć przypadki skrajne, w których optymalizator dławi się, a podzapytanie jest oceniane więcej niż raz, chociaż nie napotkałem żadnego. Z drugiej strony używam CTE gdzie tylko mogę;)

— AlexCuse

15

CTEsą najbardziej przydatne do rekurencji:

WITH hier(cnt) AS (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt < @n
        )
SELECT  cnt
FROM    hier

zwróci @nwiersze (do 101). Przydatne do kalendarzy, fikcyjnych zestawów wierszy itp.

Są też bardziej czytelne (moim zdaniem).

Poza tym są CTEi subqueriessą identyczne.

— Quassnoi
źródło

W MSSQL musisz dodać średnik (;) przed Z, mądry porządek pojawi się błąd. powinno być;WITH blabla AS ...)

— Obinna Nnenanya

2

@ObinnaNnenanya: tylko jeśli nie jest to pierwsza instrukcja w pakiecie. Kończące swoje wypowiedzi średnikami jest dobrym pomysłem, choć SQL Server nie egzekwować je w obecnych wersjach innych niż wcześniej WITH, MERGEi podobnie

— Quassnoi

10

Jedyną różnicą, o której nie wspomniano, jest odniesienie do pojedynczego CTE w kilku częściach związku

— user340140
źródło

8

O ile czegoś nie brakuje, równie łatwo możesz nazwać CTE i podzapytania.

Myślę, że główną różnicą jest czytelność (uważam, że CTE jest bardziej czytelne, ponieważ definiuje podzapytanie z góry, a nie pośrodku).

A jeśli chcesz coś zrobić z rekurencją, będziesz miał trochę problemów z zrobieniem tego z podzapytaniem;)

— AlexCuse
źródło

1

Nie jestem pewien, czy jest jakakolwiek nieestetyczna różnica (choć spodziewam się, że w pewnych sytuacjach mogą wystąpić drobne różnice w planie wykonania). Chcesz mnie oświecić?

— AlexCuse,

2

Możesz nazywać CTE, ale aliasów możesz używać tylko dla podzapytań. Różnica polega na tym, że możesz ponownie używać CTE z wieloma aliasami (por. Przykład @Michael Petito w jego komentarzu do casperOne). Nie znam żadnego sposobu na zrobienie tego z podzapytaniami.

— kmote

7

Ważnym faktem, o którym nikt nie wspomniał, jest to, że (przynajmniej w postgres) CTE to ogrodzenia optymalizacyjne:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

Oznacza to, że będą traktowane jako własne zapytania niepodzielne, a nie dołączone do całego planu zapytania. Brakuje mi wiedzy pozwalającej na lepsze wyjaśnienie, ale powinieneś sprawdzić semantykę używanej wersji sql; dla zaawansowanych użytkowników możliwość tworzenia ogrodzenia optymalizacyjnego może zwiększyć wydajność, jeśli jesteś ekspertem w kontrolowaniu planowania zapytań; Jednak w 99% przypadków należy unikać prób mówienia planistowi zapytań, co ma robić, ponieważ to, co myślisz, że będzie szybsze, jest prawdopodobnie gorsze niż to, co według niego będzie szybsze. :-)

— Ajax
źródło

6

Dodając do odpowiedzi innych osób, jeśli jedno i to samo podzapytanie zostało użyte kilka razy, możesz zastąpić wszystkie te podzapytania jednym CTE. Pozwala to na lepsze ponowne wykorzystanie kodu.

— AK
źródło

4

Jedną rzeczą, którą musisz zrozumieć, jest to, że w starszych wersjach SQL Server (tak, wiele osób nadal musi obsługiwać bazy danych SQL Server 2000), CTE nie są dozwolone, a wtedy tabela pochodna jest najlepszym rozwiązaniem.

— HLGEM
źródło

2

WSKAZÓWKA: (MAXRECURSION n)

możesz ograniczyć liczbę poziomów rekursji dozwolonych dla określonej instrukcji, używając MAXRECURSIONwskazówki i wartości między 0 a 32767 w OPTIONklauzuli

Na przykład możesz spróbować:

OPTION 
      (MAXRECURSION 150)

GO

— Podstawowy_
źródło