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.