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

Откуда:
Сообщений: 1677
И между прочим, вторым полем дополнительной таблицы может быть бит "Начато переформирование таблицы" со всеми вытекающими следствиями
27 янв 13, 01:07    [13834773]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
В общем, свои варианты "покрасить в клеточку" и "перекрасить в горошек" я высказал, при случае передавайте привет и моё почтение Кристобалю Хозевичу :)
27 янв 13, 01:10    [13834777]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
NIIIK
invm
Ну вот как приведете пример использования одного СТЕ в разных запросах, так и по вашей теме продолжим.
Конкретный пример или общий?
Конкретный - задолбаетесь читать, даже если я сделаю один вариант сформированного динамического запроса.
Вполне ожидаемое "бла-бла-бла". В документации все изложено более чем однозначно:
http://msdn.microsoft.com/en-us/library/ms175972%28v=sql.105%29.aspx
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement

NIIIK
Мне нужна та же хрень, которая делается через временную таблицу, но БЕЗ временной таблицы (и табличной переменной).
Если вам требуется результат какого-либо запроса многократно использовать, то таки придется этот самый результата сохранить в какую-либо таблицу (или, если вы любитель нестандартного, в XML и т.п.).
Никаких волшебных хреней, ускоряющих выполнение суперпупермегазапросов, в сервере не предусмотрено.
27 янв 13, 18:31    [13835920]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
invm,

1) выйдите из темы
2) по-моему из темы и постановки задачи должно быть понятно что я "и так не плохо осведомлён" о назначении CTE и временных таблицы
3) Я ищу "умный метод". Но бесполезных сообщений уже на две страницы. А сюда ещё "даже Глори не зашёл" (выходные наверное... ). Он конечно "адепт Мс СКЛ Сервера" и даёт как правило справочные ответы, но хотя бы не тупит.
4) Перечитайте пункт первый и не набалтывайте счётчик сообщений.
27 янв 13, 18:46    [13835943]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
NIIIK,

Если вам не нравится, что вас тыкают носом в ваши же собственные глупости, то не пишите их.
А ищите вы не "умный метод", а "философский камень".
27 янв 13, 19:06    [13835981]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
NIIIK
Цель "не использовать здоровые временные таблицы" и заставить СКЛ-сервер быть "умным". Как будто-то СКЛ сервер "сделал несколько ответвлений" выполнив один раз CTE и "пошёл использовать этот результат".

Вот от CTE отойти можно. Нельзя прийти к временной таблице с большим количеством данных.
Я всё таки не понимаю сути первоначального вопроса.

Вы хотите заставить сервер сохранить результат первого запроса и даьнейшие выборки делать из этого результата?
Или вы хотите заставить сервер сохранить текст первого запроса, что бы не писать его в следующих выборках с дополнительными условиями?

Других вариантов-то нету, только эти два.

Оба решаются - первый временными таблицами, второй например, inline функциями.
27 янв 13, 19:40    [13836068]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
я хочу заставить сервер "начиная с определённого момент" разветвлить обработку данных.

Я даже возвращаю "несколько датасетов" одним запросом при помощи "for xml"

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

Но при этом я понимаю что если я два раза использовал один ЦТЕ - запрос выполнится два раза. Если бы этого не понимал - то увидел бы в плане выполнения запроса.

with cteSearchResult as
(
 select ....
),

cteResult1 as
(
select
(
 select
  from cteResult ...
 group 
     by ....
for xml
) as xResult1

,
cteResult2 as
(
select
(
 select
  from cteResult ...
 group 
     by ....
for xml
) as xResult2


select (select xResult1 from cteResult1) as xResult1,
         (select xResult2 from cteResult2) as xResult2 --Можно в переменные запихнуть отдельно, можно в общи ХМЛ сложить.
   
В итогде эти ХМЛники всё равно должны быть распарсены, но количество данных сравнительно небольшое.
В моём случае я же их и сохраняю в переменные и парсю.
Это "попытка" ускорить. Такое я ещё НЕ применил. Но идея в том что бы и не выполнять cteSearchResult  многократно и НЕ сохранять во временную таблицы (или постоянную или табличную переменную и т. д.).
27 янв 13, 20:04    [13836124]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
NIIIK
я хочу заставить сервер "начиная с определённого момент" разветвлить обработку данных.
А, что бы запрос с некоторого момента развлетвлвлся, и далее из одного результата получался бы несколько результатов?

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

Или приведите пример, где это не так, и потребует дополнительных ресурсов на сервере.

Касательно вашего примера использования вложенных CTE - не понял, что тут "эффективного"
ИМХО там просто делается несколько запросов, это можно делать и без использования XML.
27 янв 13, 23:05    [13836617]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
alexeyvg,

Да тут похоже никто "не внял" в чём вопрос.
Причём озвучено 10ть раз начиная с заголовка "без временной таблицы" а так же и без физической таблицы и т. п.

Сама суть моего вопроса "есть ли какой-то оптимальный метод".

Я не говорил что "мой методо" оптимален. Я говорил что он возвращает несколько различных наборов данных одним запросом. Он БЫЛ БЫ оптимальным, если бы каждый раз небыло бы выполнения того самого CTE, а-ля "именнованного подзапроса", а-ля "вьюхи".

>Так ведь для этого временная таблица идеально подходит.
Нет, она "неидеально подхододит".
1) она создаётся физически в базе tempdb
2) она будет содержать очень большое количество НеАгрегированных данных
3) она будет создаваться каждым пользователем из большого числа который выполняет поиск
4) она будет "загружать жесткий диск" дополнительными запросами (а он тут даже не SSD)
5) Я БЛИН В САМОЙ ЗАДАЧЕ ПОСТАВИЛ условие что её не должно быть.

предположим мне нужно знать "только количество строк которое есть в cteResult" - вы предлагаете мне создавать временную таблицу, записывать тут весь результат со всеми колонками, потом делать к этой таблице count ?
Потом задача усложняется "найти общее количество и количество по каждому из 10ти типов (не важно каких, будет 10ть строчеу/груп значений)"

Временная таблица сожержит 300 000+ тысяч строк, а потом два запроса возвращающие в сумме 11 строк?
Офигенная "оптимизация" ! А может быть мне это ещё в курсоре подсчитать? Или выбрать на клиента и там цикл сделать?

Пока быстрее работает даже пару раз выполнить cteResult и ещё несколько раз можно.

ещё раз,

КОМУ НЕ ЯСНА СУТЬ ВОПРОСА?

я ищу более "умный и бысрый способ" как в случае если бы эта таблица была бы создана, но при этом "без неё".

Если кто-то считает что "его нет" - можно даже это не писать.

Мне нужно что-то "сопостовимое по качеству" вроде
1) "получение последовательности числен с рекурсивным CTE" (кстати до которого я когда-то сам дошёл зная об Оракловском connect by prior)
2) вроде оконных функций partition by ....
3) вроде агрегатных CLR функций
4) вроде XMLBulkLoad, "Bulk insert"
5) всякие примочки вроде GROUPING SETS, Cube, rollup
6) как аналитические функции в Оракл, позволяющие обращаться к любой строчке внутри группы, делать умные "нарастующие итоги".
27 янв 13, 23:40    [13836726]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
NIIIK
>Так ведь для этого временная таблица идеально подходит.
Нет, она "неидеально подхододит".
1) она создаётся физически в базе tempdb
2) она будет содержать очень большое количество НеАгрегированных данных
3) она будет создаваться каждым пользователем из большого числа который выполняет поиск
4) она будет "загружать жесткий диск" дополнительными запросами (а он тут даже не SSD)
5) Я БЛИН В САМОЙ ЗАДАЧЕ ПОСТАВИЛ условие что её не должно быть.
Кроме пункта 5 остальные требования - бред (в такой постановке). Сервер так и делает при выполнении даже обычных запросов - хранит промежуточные результаты в памяти, в том числе используя tempdb, если не хватает памяти: по другому не получится - данные то нужны, что бы их использовать, какие тут варианты...
NIIIK
предположим мне нужно знать "только количество строк которое есть в cteResult" - вы предлагаете мне создавать временную таблицу, записывать тут весь результат со всеми колонками, потом делать к этой таблице count ?
Потом задача усложняется "найти общее количество и количество по каждому из 10ти типов (не важно каких, будет 10ть строчеу/груп значений)"

Временная таблица сожержит 300 000+ тысяч строк, а потом два запроса возвращающие в сумме 11 строк?
Офигенная "оптимизация" ! А может быть мне это ещё в курсоре подсчитать? Или выбрать на клиента и там цикл сделать?
Нет, нужно всего лишь записать во временную таблицу 10 строк ("найти количество по каждому из 10ти типов"), а потом из неё расчитать общее количество.

Подумайте, обычно такие решения и требуются.

В курсоре или на клиенте с циклом не советую, будет медленнее.

Повторю:
alexeyvg
Или приведите пример, где это не так, и потребует дополнительных ресурсов на сервере.
28 янв 13, 00:55    [13836902]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
alexeyvg
Кроме пункта 5 остальные требования - бред (в такой постановке). Сервер так и делает при выполнении даже обычных запросов - хранит промежуточные результаты в памяти, в том числе используя tempdb, если не хватает памяти: по другому не получится - данные то нужны, что бы их использовать, какие тут варианты...

Ох Боже мой!
Вам не лениво?
Я реально не плохо понимаю как сервер обрабатывает запросы. На счёт того что он "сам себе создаёт временных таблицы" можно поспорить. Но не уводите в оффтоп.
Пока все сообщения тут от людей даже непонимающих вопроса. И это даже после примера "в каком дуже решение" мне нужно. Вы ведь понимаете что мы не два раза выполняем запрос с различными группировками когда используем операторы Cube, Rollup что бы посмотреть "подитоги" и т. п. ?

И задача мной для "разума форума" поставлена специально так. Я знаю чего я хочу. Другие методы, со временной таблице тем более, я и так знаю и это ЯСНО С ПЕРВЫХ СТРОК.

alexeyvg
Нет, нужно всего лишь записать во временную таблицу 10 строк ("найти количество по каждому из 10ти типов"), а потом из неё расчитать общее количество.

Подумайте, обычно такие решения и требуются.

Нет, там не такой случай. Там "более сложные с дополнительными условиями расчёты и при этом несколько результатов по различному принципу (и с дополнительными джойнами/проверками и т. п.".

alexeyvg

В курсоре или на клиенте с циклом не советую, будет медленнее.

Да ладно? Что правда что ли? Я то всё время так делал... и тут написал. В целом для расчёта "просто количества" я и во временную таблицу вставлять не буду. Или для того что можно сделать "полным оператором group by с его подолнительными ..."

Повторю:
alexeyvg
Или приведите пример, где это не так, и потребует дополнительных ресурсов на сервере.

Что не так? Создавать таблицу не с одним полем и большим количеством строк, к которой потом ещё надо будет обращаться (ктороая на жестком диске) ?

Вот, предствте, если бы не было оператора having и нельзя было бы написать "подзапросом а на следующем уров в where проверить". И вам бы надо было вставлять данные во временную таблицу, что бы потом их "отфильтровать по-разному". Понравился бы подход?

Вот и я хочу написать что-то в духе что я написал "для примера", только в плане видить что "один раз сервер лезит в этот ЦТЕ".
Более того, это даже вопрос не "разных датасетов на выходе". Часто бывает что к одному СТЕ (с разными алиасами или в разных подзапросах и т. п.) приходится обращаться несколько раз.

Можите называть мой вопрос как "метод хитрого кэширования" или т. п.

Что вы тему загаживаете второй страницей ненужных сообщений?
28 янв 13, 01:17    [13836946]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
NIIIK
alexeyvg
Или приведите пример, где это не так, и потребует дополнительных ресурсов на сервере.

Что не так? Создавать таблицу не с одним полем и большим количеством строк, к которой потом ещё надо будет обращаться (ктороая на жестком диске) ?
Ну это же общие слова.
NIIIK
Вот, предствте, если бы не было оператора having и нельзя было бы написать "подзапросом а на следующем уров в where проверить". И вам бы надо было вставлять данные во временную таблицу, что бы потом их "отфильтровать по-разному". Понравился бы подход?
Нееет, ситуайия совсем другая.

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

Где тут лишние ресурсы-то? Вам же нужны эти данные, вы их всё равно хотите получить и отдать клиенту - ну так и положите их во временную таблицу, что бы сделать дополнительные выборки.

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

Теоретически возможно разве что получение некоторых агрегатов, нереализованных микрософтом в оконных функциях и т.п., но вас то вопрос про другое?
NIIIK
Можите называть мой вопрос как "метод хитрого кэширования" или т. п.
Конкретнее, чем оно должно отличаться от нехитрого, крестьянского кеширования в памяти или на диске?
NIIIK
Что вы тему загаживаете второй страницей ненужных сообщений?
Оооо, 100 балллв! В теме есть один умный д'Артаньян и все прочие участники форума, которые только загаживают гениальную тему!

Вы сами то предложите решение, или предложите какие то усовершенствования в MSSQL, что вы хотите, а то так и останитесь непонятым при жизни, как большинство гениев :-)
28 янв 13, 01:45    [13836979]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
qwerty112
Guest
NIIIK
alexeyvg

В курсоре или на клиенте с циклом не советую, будет медленнее.

Да ладно? Что правда что ли? Я то всё время так делал... и тут написал.



... и автор надеялся, что его тут кто-то будет воспринимать всерьёз ?

зы
а вообще, на фоне таких заяв
автор
"получение последовательности числен с рекурсивным CTE" (кстати до которого я когда-то сам дошёл зная об Оракловском connect by prior)

т.е. - на "книшку" - "не разорялся" ? :)

не удивлюсь, вот, например, в виде этого
автор
Потом задача усложняется "найти общее количество и количество по каждому из 10ти типов (не важно каких, будет 10ть строчеу/груп значений)"

автор "ищщщет" простое
select
   count(*) as xz1,
   sum(case when КакойтоНисебехераТип=1 then 1 end) as xz2,
   sum(case when КакойтоНисебехераТип=1 and КакайитоНисебехераГруппа=128 then 1 end) as xz3,
   ....
28 янв 13, 02:15    [13837036]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
qwerty112
Guest
fix
qwerty112
не удивлюсь, если вот, например, в виде этого
28 янв 13, 02:17    [13837039]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Можно я еще вопрос задам? Спасибо:)
Сервер выполнил цепочку СТЕ, завершившуюся запросом на 300 000 строк. С этого момента в цепочке СТЕ должно начаться ветвление, то есть, не высчитывая повторно эти 300 000 строк, к ним надо обращаться ОДНОВРЕМЕННО из следующего далее в цепочке СТЕ запроса, в котором много-много аналитических подзапросов, каждый из которых крутит-вертит эти 300 000 строк "по-своему" и выдает, грубо, одну цифру. После параллельной обработки всех подзапросов основной запрос предоставляет пользователю свод по итогам подзапросов размером в несколько строк.
Я правильно понял постановку вопроса?

Если правильно - дальше:)
При этом очень нежелательно разрывать СТЕ и формировать временную таблицу, потому что ранее в цепочке были определены весьма полезные СТЕ, которые тоже задействованы в аналитических подзапросах.
Если это не так - каковы мотивы, вынуждающие искать "слитное" написание запроса?
28 янв 13, 06:51    [13837138]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Гузы
Guest
NIIIK
...
Представте, выбрали вы 300 тысяч людей, отобразили первых 20ть, потом
Статистика (количественная) по городам
Статистика по полу
Статистика по возвратным группам
Статистика по .... типу занятости....

Так ещё и задача "что бы работало как у Гугла и т. п." (а критерии поиска достаточно гибкие, по ним не предподсчитаешь).


Не совсем по теме, но AS в руки и будет Вам счастье.
Можно и бритвой деревья валить, а топором бриться :))
28 янв 13, 09:32    [13837333]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8731
Cygapb-007
Можно я еще вопрос задам? Спасибо:)
Сервер выполнил цепочку СТЕ, завершившуюся запросом на 300 000 строк. С этого момента в цепочке СТЕ должно начаться ветвление, то есть, не высчитывая повторно эти 300 000 строк, к ним надо обращаться ОДНОВРЕМЕННО из следующего далее в цепочке СТЕ запроса, в котором много-много аналитических подзапросов, каждый из которых крутит-вертит эти 300 000 строк "по-своему" и выдает, грубо, одну цифру. После параллельной обработки всех подзапросов основной запрос предоставляет пользователю свод по итогам подзапросов размером в несколько строк.
Я правильно понял постановку вопроса?

Если правильно - дальше:)
При этом очень нежелательно разрывать СТЕ и формировать временную таблицу, потому что ранее в цепочке были определены весьма полезные СТЕ, которые тоже задействованы в аналитических подзапросах.
Если это не так - каковы мотивы, вынуждающие искать "слитное" написание запроса?


Отношение к таблицам базы как к некоему "незыблемому"... Тогда проще базу в ридонли,- и не мучаться вопросами скорости...
КТЭ, как механизм, сами по себе,- это сахар относительно подзапросов. Возможно, когда-то, когда труд рядового программиста будет унизительно туп и WOW!!!-оплачиваем, тогда появится сахар над сахаром (кэшируемые/публичные результаты исполнения CTE). А пока это не укладывается в концепцию MS SQL Server. Посмотрите в сторону продуктов по подготовке отчётности,- вероятно в них (при допущении статичности исходных данных) существуют подобные механизмы...

ИМБО, есличо...

PS Мда.... Суровые таганрогские программисты... У них даже CTE становятся публичными
28 янв 13, 09:43    [13837382]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
SIMPLicity_, вопрос был к ТС, ваш ответ считаю оффтопом
28 янв 13, 09:48    [13837398]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8731
Как говновариант - в своё время, было используемо странное решение - из-за большой длинны названия временных таблиц, большего 128 символов, возникали траблы с пересечением наименований таблиц в TEMPdb. Не знаю, удалось ли этим кому-то реально вопользоваться (там, кажется, это невозможно из-за распределения прав доступа).

ЕслиЧо,- я блондинко и подобной херью не занимался, просто наткнулся где-то мимопроходя ёпта..
28 янв 13, 09:50    [13837409]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Cygapb-007
каковы мотивы, вынуждающие искать "слитное" написание запроса?
Ему нужно другое.

У ТСа есть тяжелый запрос, возвращающий 300К строк. И ему нужно потом по этому результирующему набору считать разную аналитику. В процессе размышлений по оптимизации этого хозяйства родилась "гениальная" мысль -- все оформить единым СТЕ, возвращающим XML, а потом этот XML парсить и возвращать клиенту все полученные наборы. Реализовав сие, автор понял, что СТЕ всего-лишь синтаксический сахар и, грубо, будет развернуто в derived table во всех местах запроса, где используется. Т.е. никакого промежуточного хранения нет и его тяжелый запрос все равно выполнялся многократно.

Теперь автор ищет волшебное средство использовать многократно результат запроса, нигде явно его не сохраняя. Попытки объяснить автору, что даже если такое секретное средство есть, то серверу все равно придется где-то эти 300К строк хранить, ни к чему не привели.
28 янв 13, 09:50    [13837411]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8731
Cygapb-007
SIMPLicity_, вопрос был к ТС, ваш ответ считаю оффтопом


Да ответ тоже какбэ ТС, просто в контексте Вашего коментария-уточнения.
Есличо,- обидеть/задеть не хотел.... Извините, нафсякей...
28 янв 13, 09:52    [13837418]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK
Я реально не плохо понимаю как сервер обрабатывает запросы.
На счёт того что он "сам себе создаёт временных таблицы" можно поспорить.

Два взаимоисключающих утверждения.
Посмотрите (хотя бы в BOL, там всего две строчки текста), как работает физический оператор Table Spool.
28 янв 13, 10:36    [13837642]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
Cygapb-007
Можно я еще вопрос задам? Спасибо:)
Сервер выполнил цепочку СТЕ, завершившуюся запросом на 300 000 строк. С этого момента в цепочке СТЕ должно начаться ветвление, то есть, не высчитывая повторно эти 300 000 строк, к ним надо обращаться ОДНОВРЕМЕННО из следующего далее в цепочке СТЕ запроса, в котором много-много аналитических подзапросов, каждый из которых крутит-вертит эти 300 000 строк "по-своему" и выдает, грубо, одну цифру. После параллельной обработки всех подзапросов основной запрос предоставляет пользователю свод по итогам подзапросов размером в несколько строк.
Я правильно понял постановку вопроса?
Так не совсем правильно это интерпретировать.

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

Хотя как некое общее направление мысли это верно.
Cygapb-007
Если правильно - дальше:)
При этом очень нежелательно разрывать СТЕ и формировать временную таблицу, потому что ранее в цепочке были определены весьма полезные СТЕ, которые тоже задействованы в аналитических подзапросах.
Так вот и нужно пример!

Допустим, нам нужно получить (тут некий гипотетический синтаксис):
with cteSearchResult as ( select .... ),
cteResult1 as ( select ... from cteSearchResult ...),
cteResult2 as ( select ... from cteSearchResult ...)
select * from cteSearchResult;
select * from cteResult1;
select * from cteResult1;

Если мы получаем первым запросом данные из cteSearchResult, то мы всё равно выделяем для этого ресурсы, соответственно можео положить данные во временную таблицу, и сделать потом два ледующих запроса из неё.

А если нам не нужен первый запрос, то тут всё зависит от содержимого других запросов - вполне возможно, что планы буду кардинально отличаться, и не нужно выделять ресурсы на хранение промежуточного результата.

Если же нужно выделять эти ресурсы (то есть план показывает, что сервер делает выборку как в определении cteSearchResult, а потом из этих данных что то вычисляет и агрегирует), то тогда можно выделить эти ресурсы в tempdb самому - всё равно они выделяются!

Конечно, было бы замечательно, если бы сервер делал это сам.

Я бы хотел, что бы можно было использовать CTE несколько раз в двух вариантах - с материализацией промежуточных результатов (это для многократных вычислений по слепку данных без ручного создания временных таблиц) и без неё (это просто синтаксическое удобство, для уменьшения объёма кода), это былли бы хорошие фичи.

Но тем не менее проблема не так остра, как писал ТС, решение есть, приведено выше.
28 янв 13, 11:30    [13837984]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
invm
Cygapb-007
каковы мотивы, вынуждающие искать "слитное" написание запроса?
Ему нужно другое.

У ТСа есть тяжелый запрос, возвращающий 300К строк. И ему нужно потом по этому результирующему набору считать разную аналитику. В процессе размышлений по оптимизации этого хозяйства родилась "гениальная" мысль -- все оформить единым СТЕ, возвращающим XML, а потом этот XML парсить и возвращать клиенту все полученные наборы. Реализовав сие, автор понял, что СТЕ всего-лишь синтаксический сахар и, грубо, будет развернуто в derived table во всех местах запроса, где используется. Т.е. никакого промежуточного хранения нет и его тяжелый запрос все равно выполнялся многократно.

Теперь автор ищет волшебное средство использовать многократно результат запроса, нигде явно его не сохраняя. Попытки объяснить автору, что даже если такое секретное средство есть, то серверу все равно придется где-то эти 300К строк хранить, ни к чему не привели.
Мне кажется, тут другое.
Отчетливо понимая, что 300К строк УЖЕ хранятся "где-то" в сервере, автор хочет, минуя оформление этого "где-то" в виде временной таблицы, в аналитических запросах обрабатывать имено это "где-то".
ЯТД, что именно этот вопрос и был вынесен на обсуждение.

PS
alexeyvg
Я бы хотел, что бы можно было использовать CTE несколько раз в двух вариантах - с материализацией промежуточных результатов (это для многократных вычислений по слепку данных без ручного создания временных таблиц) и без неё (это просто синтаксическое удобство, для уменьшения объёма кода), это былли бы хорошие фичи.
Спасибо, я думаю, что автор топика имел в виду именно это
28 янв 13, 12:19    [13838329]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexeyvg
Я бы хотел, что бы можно было использовать CTE несколько раз в двух вариантах - с материализацией промежуточных результатов (это для многократных вычислений по слепку данных без ручного создания временных таблиц) и без неё (это просто синтаксическое удобство, для уменьшения объёма кода), это былли бы хорошие фичи.

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

Пример для MSSQL:
with cte (id, guid) as
(   select 1, newid()  union all
    select 2, newid()  union all
    select 3, newid()
)
select *
from cte c1 full join cte c2 on c1.guid = c2.guid;
— результат содержит 6 строк, т.к. формирование GUID'ов происходит два раза.

То же самое для Оракла:
with cte (id, guid) as
(   select 1, sys_guid() from dual  union all
    select 2, sys_guid() from dual  union all
    select 3, sys_guid() from dual
)
select *
from cte c1 full join cte c2 on c1.guid = c2.guid;
— здесь результат уже содержит 3 строки, т.к. происходит промежуточная материализация в таблицу с именем вида SYS_TEMP_0FD9D665C_113CDB.

Причём я почему-то думаю, что материализацию в Оракле можно отключить неким хинтом (не настолько хорошо знаю хинты, чтобы утверждать наверняка). Материализацию же в MSSQL включить никак нельзя.
28 янв 13, 12:33    [13838425]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить