Разница между CTE и SubQuery?

Из этого поста Как использовать ROW_NUMBER в следующей процедуре?

Есть две версии ответов, где один использует SubQuery а другой использует CTE решить ту же проблему.

Теперь, в чем преимущество использования CTE (Common Table Expression) сверх какub-query(таким образом, более читабельно, что на самом деле делает запрос)

Единственное преимущество использования CTE Субселект состоит в том, что я могу назвать субзапрос. Есть ли какие-либо другие различия между этими двумя, когда CTE используется в качестве простого (не рекурсивного) CTE?

Источник

10 ответов

Решение

В подзапросе против простых (нерекурсивных) версий CTE они, вероятно, очень похожи. Вы должны будете использовать профилировщик и фактический план выполнения, чтобы определить любые различия, которые будут характерны для вашей настройки (поэтому мы не можем сказать вам ответ полностью).

В общем; CTE может использоваться рекурсивно; подзапрос не может. Это делает их особенно хорошо подходящими для древовидных структур.

Источник

Основным преимуществом Common Table Expression (когда он не используется для рекурсивных запросов) является инкапсуляция, вместо того, чтобы объявлять подзапрос в каждом месте, где вы хотите его использовать, вы можете определить его один раз, но иметь несколько ссылок к этому.

Однако это не означает, что он выполняется только один раз (согласно предыдущим итерациям этого ответа, спасибо всем, кто прокомментировал). Запрос определенно может быть выполнен несколько раз, если на него ссылаются несколько раз; Оптимизатор запросов в конечном итоге принимает решение о том, как следует интерпретировать CTE.

Источник

CTEНаиболее полезны для рекурсии:

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

вернусь @n строки (до 101). Полезно для календарей, пустых наборов строк и т. Д.

Они также более читабельны (по моему мнению).

Помимо этого, CTEи subqueries идентичны

Источник

Одно различие, которое не было упомянуто, состоит в том, что на один CTE можно ссылаться в нескольких частях объединения.

Источник

Один важный факт, который никто не упомянул, заключается в том, что (по крайней мере, в postgres) CTE являются заборами оптимизации:

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

То есть они будут обрабатываться как их собственный атомарный запрос, а не как весь план запроса. Мне не хватает опыта, чтобы дать лучшее объяснение, но вы должны проверить семантику для версии SQL, которую вы используете; для опытных пользователей возможность создать забор для оптимизации может повысить производительность, если вы являетесь экспертом в управлении планировщиком запросов; однако в 99% случаев вам следует избегать попытки указывать планировщику запросов, что делать, потому что то, что вы думаете, будет быстрее, вероятно, хуже, чем то, что, по его мнению, будет быстрее.:-)

Источник

Если я что-то упустил, вы можете так же легко назвать CTE и подзапросы.

Я предполагаю, что основное различие заключается в удобочитаемости (я нахожу CTE более читабельным, потому что он определяет ваш подзапрос заранее, а не в середине).

И если вам нужно что-то сделать с рекурсией, у вас возникнут небольшие проблемы с выполнением этого подзапроса;)

Источник

Помимо ответов других, если один и тот же подзапрос используется несколько раз, вы можете заменить все эти подзапросы одним CTE. Это позволяет вам повторно использовать ваш код лучше.

Источник

Вам также нужно понять одну вещь: в более старых версиях SQL Server (да, многим по-прежнему необходимо поддерживать базы данных SQL Server 2000), CTE не допускаются, и тогда производная таблица является вашим лучшим решением.

Источник

  1. С CTE вы можете использовать рекурсию.

  2. С CTE вам нужно написать его только один раз, но вы можете ссылаться на него в нескольких местах с запросом. Таким образом, это может позволить вам избежать повторения, а также может облегчить чтение и интерпретацию запроса.

  3. Кажется, что CTE предоставляет метаданные о себе оптимизатору запросов, так что если CTE упоминается более одного раза в одном и том же запросе (например, если он присоединяется к самому себе), оптимизатор запросов потенциально может использовать эти метаданные для улучшения общего план выполнения запроса (с подзапросами этого не происходит).

Источник

СОВЕТ: (МАКСРЕКУРСИЯ n)

Вы можете ограничить количество уровней рекурсии, разрешенных для конкретного оператора, используя MAXRECURSION подсказка и значение от 0 до 32 767 в OPTION пункт

Например, вы можете попробовать:

OPTION 
      (MAXRECURSION 150)

GO
Источник
Другие вопросы по тегам