Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Товарищи, а вот объясните мне физику процесса.
Когда я объявляю CTE , это всегда просто "snippet" который компилятор подставит в код SELECTа, или бывают случаи, когда в памяти создается виртуальная таблица, соответствующая данным, которые "начитаны" в CTE ?
25 июн 15, 18:15    [17816912]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
_djХомяГ
Guest
Виртуальной таблицы не создается , при обращении к cte "разворачиваются" инструкции которыми описано cte
25 июн 15, 18:18    [17816924]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8813
Выполняется так же, как и любой другой запрос.
25 июн 15, 18:19    [17816925]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31960
Cammomile
Когда я объявляю CTE , это всегда просто "snippet" который компилятор подставит в код SELECTа
Да
25 июн 15, 22:39    [17817680]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
gang
Member

Откуда:
Сообщений: 1394
Cammomile,
В принципе коллеги правы, CTЕ для движка такой же кусок кода как подзапрос или обращение к вью. Однако, если говорить о физике процесса, то сценарии в которых наиболее активно используют СТЕ (в частности рекурсии) часто приводят к выполнению движком Spool-операций. Для которых, в свою очередь, промежуточные результаты материализуются в виде объектов WorkTable размещаемых в tempdb. Еще раз: использование WorkTable в tempdb не особенность СТЕ, а особенность ряда операций, в том числе, Spool. Просто в наиболее распространенных сценариях использования СТЕ вероятность того, что оптимизатору понадобится Spool-оператор выше.
26 июн 15, 10:07    [17818612]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Форсировать этот процесс можно?
26 июн 15, 12:18    [17819609]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8813
Cammomile,

зачем? Вам "кажется" или Вы "знаете"?
26 июн 15, 12:20    [17819620]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Cammomile,

Документированными средствами нет. Это довольно популярный запрос на фичу (129 голосов), голосуйте, если хотите, чтобы добавили такой хинт, предложение пока открыто: Provide a hint to force intermediate materialization of CTEs or derived tables - by Adam Machanic. Но то, что тикет был открыт в "Opened 05.10.2006 12:33:16" и до сих пор висит в активных - не оставляет надежд на скорое изменение ситуации.
26 июн 15, 12:31    [17819666]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Владислав Колосов,что зачем? Что я знаю? Вы с кем сейчас разговариваете?
26 июн 15, 12:33    [17819683]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Владислав Колосов
зачем? Вам "кажется" или Вы "знаете"?


Вопрос не праздный. Действительно, в MSSQL такая проблема есть - отсутствие принудительной материализации CTE. В Оракле это решается спец. хинтом, а в MSSQL почему-то поленились сделать. А оно нужно, если CTE используется в сложном запросе несколько раз. Во всяком, случае, материализация оного в Оракле часто сильно выручает в плане производительности.

Вероятно, в MSSQL материализации CTE не сделали просто потому, что есть альтернативы SELECT INTO и инсерты в таблицы-переменные, а Оракле таких альтернатив нет, потому и пришлось материализацию CTE делать.
26 июн 15, 12:35    [17819695]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
К сожалению селект инто и прочее подобное совершенно не работает, когда пишешь inline функцию, или view
26 июн 15, 12:37    [17819708]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Cammomile
К сожалению селект инто и прочее подобное совершенно не работает, когда пишешь inline функцию, или view

Неистово плюсую. Материализации CTE в MSSQL категорически не хватает.
26 июн 15, 12:39    [17819717]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Меж тем, тут вот пишут, что добавление TOP 1 с соответсвующим ORDER BY, при запросе к ЦТЕ, вроде как форсирует "материализацию".

Я проверил, и оказалось, что Steam Cggregate меняется на Compute Scalar

В моем случае, кажется, это решение вопроса.

Псевдокод для примера:

 
 
 ;WITH CTE AS ( SELECT ID FROM Types WHERE TypeName = 'SomeType') 


 SELECT OA.XXX, ST.* 
 FROM dbo.SomeTable ST 
 OUTER APPLY (SELECT XXX= CASE WHEN ST.ID = (SELECT ID FROM CTE) THEN 'IsType' ELSE '___' END) OA 

 GO
 

 ;WITH CTE AS ( SELECT  ID FROM Object WHERE TypeName = 'SomeType') 


 SELECT OA.XXX, ST.* 
 FROM dbo.SomeTable ST 
 OUTER APPLY (SELECT XXX= CASE WHEN ST.ID = (SELECT TOP 1 ID FROM CTE ORDER BY ID ) THEN 'IsType' ELSE '___' END) OA 
26 июн 15, 13:24    [17819949]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8813
Cammomile, это шутка, что ли? Зачем здесь CTE?
26 июн 15, 13:42    [17820094]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Владислав, вы просто не можете схватывать на лету, и, очевидно, не понимаете о чем речь. Тратить время, на разжевывание проблемы персонально для вас, мне совершенно неинтересно. По вашим вопросам, я уже понял, что на мой вопрос вы ответить просто не в состоянии.
26 июн 15, 13:50    [17820154]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Cammomile
Меж тем, тут вот пишут, что добавление TOP 1 с соответсвующим ORDER BY, при запросе к ЦТЕ, вроде как форсирует "материализацию".
С точки зрения повторного использования результата, CTE не материализуется, ни с TOP 1, ни без. Точка.
А псевдокод ваш нерабочий. Если хотите проиллюстрировать свою проблему, подберите более удачный пример.
26 июн 15, 13:57    [17820198]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Cammomile,

Не верьте =)

Максимум - эта конструкция просто не позволит серверу раскрыть подзапрос. При этом, снизив число вариантов плана, что обычно работает в худшую сторону, хотя, иногда, действительно, может помочь. Но даже если помогло это: 1) не значит, что это нормально 2) не значит что со временем (когда данные/статистика изменятся) ситуация останется прежней.

Кроме того, вы же изменяете смысл запроса - что если мне не нужна одна строка?
Ок, пишем максимальный bigint - что если завтра оптимизатор научится распознавать этот трюк как он научился убирать top(100) percent? Даже если не научится - зачем добавлять дополнительное требование к сортировке при помощи order by?

Если дописать в запросе
+
 ;WITH CTE AS ( SELECT  ID FROM Object WHERE TypeName = 'SomeType') 
 SELECT OA.XXX, ST.* 
 FROM dbo.SomeTable ST 
 OUTER APPLY (SELECT XXX= CASE 
WHEN ST.ID = (SELECT TOP 1 ID FROM CTE ORDER BY ID ) THEN 'IsType' 
WHEN ST.ID+1 /*ну или что-нибудь*/ = (SELECT TOP 1 ID FROM CTE ORDER BY ID ) THEN 'IsType2' 
ELSE '___' END) OA 

Сколько раз будет осуществлен доступ к базовому объекту Object по плану запроса? Если будет две ветки с Top - то какая это материализация, если дважды доступ к базовому объекту?

Единственный вид операторов в SQL Server, который способен сохранять промежуточные резултаты в плане для переиспользования это Spool-ы. Для каждого отдельного запроса, путем тех или иных манипуляций, можно попробовать заставить оптимизатор использовать Spool, но это: 1) не универсально - для каждого запроса свой огород 2) не гарантируется - нет гарантии, что с выходом очередного SP или переходом на новую версию то что работало для старого оптимизатора, будет оптимизировано в новом также 3) не очевидно - для других разработчиков и себя самого в будущем 4) и, самое главное, все равно не спасет во многих случаях, т.к. по сравнению с той же временной таблице - это очень условная материализация. Т.к. помимо отказа от выполнения подзапроса несколько раз, материализация во временную таблицу также дает: упрощение плана - исключается вся ветка заполнения временной таблицы, новая и неискаженная статистика по столбцам, возможность построить какие угодно индексы. И плюс ликвидирует все те недостатки перечисленные выше про spool.

Учитывая вышесказанное, я бы никому не советовал использовать в продуктовом коде какие-либо способы материализации кроме временных таблиц и/или табличных переменных (по ситуации). По крайней мере до тех пор, пока нормальный механизм материализации не будет встроен разработчиками в движок сиквела.
26 июн 15, 14:03    [17820239]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
SomewhereSomehow
Учитывая вышесказанное, я бы никому не советовал использовать в продуктовом коде какие-либо способы материализации кроме временных таблиц и/или табличных переменных (по ситуации).

материализация при помощи multiline table udf достойна отдельного упоминания, как мне кажется
26 июн 15, 14:27    [17820418]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Shakill
SomewhereSomehow
Учитывая вышесказанное, я бы никому не советовал использовать в продуктовом коде какие-либо способы материализации кроме временных таблиц и/или табличных переменных (по ситуации).

материализация при помощи multiline table udf достойна отдельного упоминания, как мне кажется
Разве речь не про ускорение выполнения запроса?
Как может ускорить тормоз под названием multistatement UDF?
26 июн 15, 14:30    [17820444]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Shakill,

Возможно, но в данном случае, я их отнес к категории табличных переменных, поскольку, говоря про метариализацию в операторах плана, я имел ввиду то, что вам, как разработчику, для такой материализации не нужно создавать никаких дополнительных структур и объектов, типа тех же функций/переменных/временных таблиц. Ведь речь, насколько я понял, именно об этом.
26 июн 15, 14:35    [17820478]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
invm, псевдокод нерабочий? Да не может того быть! Только что отлично отработал на моем псевдосервере в псевдосреде разработки!


SomewhereSomehow, спасибо.
26 июн 15, 14:37    [17820490]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
SomewhereSomehow, вообще речь идет вот о чем.


Есть некое представление, с больгим количеством строк. Нужно добавить в это представление несколько аплаев, внутри которых будут функции с многими параметрами. Некоторые из этих параметров, скажем так, "константы". Но начитать заранее в переменные, я не могу, потому что у меня представление.

Я сделал предположение, что эти "константы" можно сохранить, начитав их в СТЕ, но тогда вот этот вот подзапрос из СТЕ будет применен на каждую строку основного набора данных. Что, конечно же плохо. Вот я и решил уточнить, нельзя ли как-нибудь форсировать "сохранение" заранее начитанных данных.

Ну нет, так нет... что тут поделать?
26 июн 15, 14:41    [17820517]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
o-o
Guest
вы чего на людей бросаетесь, как обозвали свое твоерение, так другие его и называют
Cammomile
Псевдокод для примера

а что код нерабочий, так куда более подходящее слово "ниочемный".
это же не репро [репро -- это то, что воспроизводится не только у ватора], что из него можно увидеть-то?
26 июн 15, 14:46    [17820553]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Cammomile
invm, псевдокод нерабочий? Да не может того быть! Только что отлично отработал на моем псевдосервере в псевдосреде разработки!
Вот ерничать не нужно. Ваш псевдокод
CASE WHEN ST.ID = (SELECT ID FROM CTE) ...
даст ошибку, если CTE возвращает более одной записи.
26 июн 15, 14:46    [17820555]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос про поведение СТЕ  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Cammomile,

Не уверен, что правильно понял вашу ситуацию, но если речь о том, что есть какие-то константы, которые нужно использовать несколько раз в качестве аргументов функций и чтобы их каждый раз не писать вы используете cte - то это не должно оказать существенного влияния на производительность. Если я не так понял, то приведите какой-нибудь пример с кодом.
26 июн 15, 14:50    [17820582]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить