Comments 6
Обратите внимание, что в плане запроса результат CTE вычисляется дважды (по одному разу для каждой ссылки). Там есть два просмотра таблицы SalesOrderHeader и два хэш-агрегата. Даже при использовании CTE (или представления) SQL Server будет вычислять результат дважды. Не дайте ввести вас в заблуждение этому Table Spool. Эта буферизация просто кэширует результат CTE, чтобы избежать вычисления всего агрегата один раз для каждого сотрудника.
Вот вообще непонятная фраза. Так два или один раз?
Я бы ещё понял, будь так: один раз начинаем, а от какой-то кэшированной середины два независимых вычисления... но написано-то наоборот - начинаем независимо два раза, а потом... а зачем тогда вообще кэширование и что оно делает? а если имеется в виду, что, начав второй раз, сервер на середине вдруг соображает, что это у него уже есть, бросает всё и лезет в кэш - так и вовсе бред.
Или имеется в виду вообще нечто типа "в плане мы нарисуем два раза, а на самом деле посчитаем один раз"?
Эта буферизация просто кэширует результат CTE, чтобы избежать вычисления всего агрегата один раз для каждого сотрудника.
По-русски это означает что-то типа "один раз - плохо, мы постараемся этого избежать и посчитаем дважды".
Иногда СТЕ проигрывает в производительности подзапросам. Нужно проверять что выгоднее использовать. В одном случае у меня получился запрос с подзапросам вдвое быстрее чем с СТЕ.+ join В СТЕ нужно стремится уменьшить объем данных через фильтр WHERE и только потом использовать в основном запросе. Тем более это должно быть обосновано, зачем нужен здесь СТЕ.
Обычно вообще невозможно предсказать, как сервер выполнит CTE - закэшит или заинлайнит. Случаи, когда достаточно быстрый запрос вдруг неожиданно начинает выполняться в разы дольше, и разбирательство указывает именно на изменение решения построителя-оптимизатора, которое фиксится обновлением сведений о статистике данных - бывали.
Правда, не знаю, как с подобным обстоят дела именно у SQL Server.
Речь идёт об абсолютно одинаковых запросах с CTE и с подзапросом в FROM/JOIN или о разных запросах? В первом случае это было бы очень странное и неожиданное поведение оптимизктора, т.к. семантически CTE и inline ничем не различаются: один и тот же текст SQL, где какой-то части дано имя (хотя и inline подзапросу дается алиас). Если это разные запросы, особенно в случае переписывания на подзапрос с корреляцией, то неудивительно. Все же CTE стоит рассматривать в первую очередь как инструмент улучшения читаемости и следование принципу DRY, а потенциальные свызанные с ним оптимизации - как побочный эффект. Хотя в случае с SQL Server, возможно, есть какие-то подводные камни с этой конструкцией.
семантически CTE и inline ничем не различаются
Представьте себе CTE, который вызывает недетерминированную функцию, и внешний запрос, использующий 2 копии CTE. Например, запрос типа:
WITH cte (val) AS (
SELECT RAND()
)
SELECT *
FROM cte t1
JOIN cte t2 ON t1.val = t2.val;Кэширование даст 1 итоговую строку. Инлайн даст пустой набор (точнее, вероятность того, что вернётся 1 запись, весьма мала).
Опробованы все версии популярных DBMS, доступные на dbfiddle, с соответствующей корректировкой кода под конкретную DBMS. SQL Server, MariaDB на именно этот запрос дают у меня пустой набор. MySQL, PostgreSQL, SQLite, Oracle - возвращают 1 запись. Так что даже невозможно сказать, какая реализация популярнее - нет явного лидера.
И текста последнего стандарта у меня нет... но что-то я сильно сомневаюсь, что этот момент в нём описан. Хотя вопрос-то принципиальный...
Да, если что, я за кэширование.
Работал с CTE на который было более 30 ссылок в одном запросе через UNION ALL
Несмотря на длинный план запроса, я котором CTE должен как бы каждый раз отрабатывать, за счёт кэширования результата - скорость работы была в 7 раз быстрее, чем без CTE
Возможно оптимизатор ORACLE ещё грамотно работает, но CTE действительно очень полезная вещь для подобного.
Common Table Expressions