Поясните преимущества With

Добрый день!В 2000 SQL не пользовал CTE, т.к. его там нет =). Почитал про CTE и вот не пойму его преимущества.Если рекурсия нужна, то да. А для чего еще? Вот простой пример приведу и объясните, плз, как лучше?
With CTE (id) As
( 
 Select <b>1</b> Union All Select <b>2</b> Union All Select <b>3</b>
)

Select *
From CTE

--или так

Select * Into #tmp
From	(
	 Select <b>1</b> as id Union All Select <b>2</b> as id Union All Select <b>3</b> as id
	) as t

Select *
From #tmp

Drop Table #tmp
13 ответов

Планы сравните.


tpg,Ну понятное дело, что идут некие ресурсы на вставку в темповую таблицу... и это все преимущество?


Anddros,Спасибо за ссылочку! Рекурсия может, конечно, пригодиться... Но повторюсь! про рекурсию прочитал, понял и осознал... у меня вопрос про временный таблицы и CTE. Правильно понимаю, что в селктах разницы не будет? и CTE выполняется один раз и в дальнейшем будет тоже, что и временка?


tpg,Ну понятное дело, что идут некие ресурсы на вставку в темповую таблицу... и это все преимущество?
Вы не гадайте, а посмотрите.


Правильно понимаю, что в селктах разницы не будет? и CTE выполняется один раз и в дальнейшем будет тоже, что и временка?
CTE в вашем случае - не аналог временной таблицы, а аналог вьюхи, генерящийся 'на лету' или подзапроса, который можно многократно использовать в одном запросе. Сравнение с временной таблицей некорректно, ибо сервер 'материализует' выборку, полученную в CTE далеко не всегда.


CTE без рекурсии - это аналог derived table (производной таблицы).Но, в отличие от последней, на имя один раз определённого CTE можно ссылаться много разпод разными алиасами в SELECTе, UPDATEе, DELETEе, INSERTе или MERGEе.Попробуйте проделать это с derived table, и всё сами поймёте. В остальном никаких преимуществ нет.Разве что в случае определения нескольких CTE в одной инструкции каждое CTE можеточень просто ссылаться на любое ранее определённое.А в случае derived table, видимо, придётся использовать APPLY


У меня почти всегда получалось, что код написанный с помощью CTE проигрывал по производительности иным вариантам. Но во многих случаях CTE удобно использовать, особенно когда надо просто быстро набросать запросик.Есть интересная статья по рекурсивным CTE и их производительности. Почитать можно здесь:http://www.sqlservercentral.com/articles/T-SQL/2926/


Вот еще парочка интересных статей производительности CTE от одного известного MVP1. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx2. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-2-row-number-is-executed-number-of-cte-references-x-number-of-rows-from-the-anchor.aspx


Павел-П,спасибо! буду вспоминать буквы!)


1. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx2. http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-2-row-number-is-executed-number-of-cte-references-x-number-of-rows-from-the-anchor.aspx
А вы их хотябы проверяли, чтоб постить такое старьё?И вообще "производительность" тут не причём, "построение неадекватного плана" может ещё пошло бы. CTE это всего лишь принцип записи (синтаксис), а не исполняемый код.


Mnior,А вы сами их хотя бы проверяли? Если хотите информацию опровергнуть или поставить под сомнения - проверьте и напишите, что имеет место быть такая вот вещь. Помимо слов предоставите интернет сообществу еще какую-нибудь полезную информацию."И вообще "производительность" тут не причём, "построение неадекватного плана" может ещё пошло бы. CTE это всего лишь принцип записи (синтаксис), а не исполняемый код."Ну с этим утверждением я согласен. Это тоже самое как сказать, если работает быстро значит быстро.


А вы сами их хотя бы проверяли?
Согласен, недоглядел.По ссылке рассказывается банальщина: CTE это не промежуточные таблы (CTE vs #Tbl). Т.е. яблоки это не груши. Суть проста без всяких ссылок - кэширование помогает при многократном использовании, но тут уже CTE совершенно не причём.Повторяю, CTE это форма записи - синтаксис. И говорить что CTE быстрее/медленнее #@Tbl, это говорить, что красное быстрее кислого. Т.е. совершенно неверно и бредово.Думать что в CTE сиквел будет кэшировать, это совершенно не понимать суть CTE. "Кэшировать сам" (Spool) сиквел будет по своим внутренним принципам, что он делает не только в CTE.