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

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

Предположим у нас есть простейший CTE (задача упрощена для вопроса).

cteResult as
(
 select ...
)

select r.*
  from cteResult r


Далее из того же CTE необходимо вывести несколько результатов в другой форме
1) select count(1) from cteResult
2) select fkType, count(1) as cnt from cteResult
3) select ... from cteResult...
union all
select .. from cteResult...

И таких много. Можно сказать "мега-статистика".

Далее,

что бы вывести несколько результатов одним запросом (не сами данные, а уже количественные с запросами) делается простое перобразование в XML, а уже потом его "распарсивание".

В целом одно дело обмануть и сделать "одно SQL выражение".
Другое дело заставить "использовать одно CTE один раз, как если бы у нас была одна временная таблица или табличная переменная". В CTE cteResult подразумевается большое количество строк да и тот запрос что их достаёт не самый шустрый (точнее сказать нужно быстрое получение результата).

Как это сделать?
26 янв 13, 20:39    [13834108]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
 		CTE - это синтаксический сахар, а не реальный объект. Его нельзя использовать многократно в разных запросах.

Чем не устраивает сохранение результата во временной таблице? На нее потом и индексы можно навесить, если потребуется.
26 янв 13, 21:26    [13834302]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

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

Да ладно? :)

А вы думаете почему я тут вопрос задал?

invm
Чем не устраивает сохранение результата во временной таблице? На нее потом и индексы можно навесить, если потребуется.

А тем что она создаётся как физическая таблица в tempdb, при этом результат будет содержать дестки и иногда сотни тысяч строк и тем что такой запрос буду выполнять одновременно куча пользоваталей (что-то вроде "гугла в одной из систем").
Собственно поэтому в названии темы "без временной таблицы".

Обычно, когда надо делать просто подсчёт общее количество строк - проблем особо нет, но тут целая "статистика".
26 янв 13, 21:58    [13834390]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
qwerty112
Guest
NIIIK
invm
Чем не устраивает сохранение результата во временной таблице? На нее потом и индексы можно навесить, если потребуется.

А тем что она создаётся как физическая таблица в tempdb, при этом результат будет содержать дестки и иногда сотни тысяч строк и тем что такой запрос буду выполнять одновременно куча пользоваталей (что-то вроде "гугла в одной из систем").
Собственно поэтому в названии темы "без временной таблицы".

а вы точна знаете/понимаете, что такое временная таблица ?
26 янв 13, 22:06    [13834407]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

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

вы думаете я не знаю различия между # и ##
или не поняли что я сказал про "реальная физическая таблица на диске в базе tempdb, которая будет снижать быстродействие и загружать (или медленно работать при загруженно) жесткий диск? Может попрошу когда-нить SSD поставить, но пока "нет".
26 янв 13, 22:09    [13834418]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
NIIIK
invm
CTE - это синтаксический сахар, а не реальный объект. Его нельзя использовать многократно в разных запросах.

Да ладно? :)

А вы думаете почему я тут вопрос задал?
Приведите работающий пример обратного.
26 янв 13, 22:21    [13834456]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
 		Вообще-то СТЕ - это тот же VIEW, но определенный только для текущего запроса.
Каждый следующий СТЕ в цепочке заставляет сервер ПОВТОРНО проводить поиск и отбор данных в соответствии с ранее определенными СТЕ, которые он использует в выборке.

Поэтому если результаты какого-то СТЕ планируется многократно использовать в последующих запросах, значительно экономнее (с точки зрения ресурсов сервера, времени выполнения запроса и задействованных промежуточных таблиц) один раз зафиксировать этот результат в виде временной таблицы, которую в дальнейшем серверу уже не придется МНОГОКРАТНО повторно пересчитывать.

Хотя чисто внешне - да, все выглядит красиво и привлекательно.
26 янв 13, 22:21    [13834457]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Cygapb-007
Вообще-то СТЕ - это тот же VIEW, но определенный только для текущего запроса.
Каждый следующий СТЕ в цепочке заставляет сервер ПОВТОРНО проводить поиск и отбор данных в соответствии с ранее определенными СТЕ, которые он использует в выборке.


Спасибо, кэп :)
Я же тему "не по этому создал" :)

Cygapb-007
Поэтому если результаты какого-то СТЕ планируется многократно использовать в последующих запросах, значительно экономнее (с точки зрения ресурсов сервера, времени выполнения запроса и задействованных промежуточных таблиц) один раз зафиксировать этот результат в виде временной таблицы, которую в дальнейшем серверу уже не придется МНОГОКРАТНО повторно пересчитывать.

Хотя чисто внешне - да, все выглядит красиво и привлекательно.


Смотря какой результат. Временные таблицы хороши не во всех случаях.
Если запросы "пользовательские", частые и могут быть запущены большим количеством пользователей - они должны быть "не статистические".
Использовать агегрирование, тот же distinct на больших объёмах данных в целом не правильно. Хорошо ещё с самими данными, пейджинг есть.
Другое дело когда надо подсчитать "количество" - простейший статистический запрос. В целом может работать быстро в зависимости от сложности условий (но всё равно, хоть по индексам, но проверяет "все строки"). Ещё сложнее задача когда по факту надо подсчитать "статистику с группировками по различным атрибутам".

Представте, выбрали вы 300 тысяч людей, отобразили первых 20ть, потом
Статистика (количественная) по городам
Статистика по полу
Статистика по возвратным группам
Статистика по .... типу занятости....

Так ещё и задача "что бы работало как у Гугла и т. п." (а критерии поиска достаточно гибкие, по ним не предподсчитаешь).
26 янв 13, 22:41    [13834484]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Гость333
Member

Откуда:
Сообщений: 3683
invm
CTE - это синтаксический сахар, а не реальный объект.

Рекурсивный CTE -- тоже сахар?
26 янв 13, 22:59    [13834523]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
 		За кэпа спасибо, заслужил:))
NIIIK
Далее из того же CTE необходимо вывести несколько результатов в другой форме
1) select count(1) from cteResult
2) select fkType, count(1) as cnt from cteResult
3) select ... from cteResult...
union all
select .. from cteResult...

то есть нужна обвертка результатов СТЕ (на 300 000 строк) в SQL-механизм промежуточного хранения типа XML (но не временную таблицу), поддерживающий быструю обработку статистических запросов... В то время как SQL заточен именно под обработку табличных данных...
Уважаю:) Будет интересно посмотреть результат исследования:)
26 янв 13, 23:08    [13834545]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Гость333
invm
CTE - это синтаксический сахар, а не реальный объект.

Рекурсивный CTE -- тоже сахар?
Рекурсивное CTE -- особый случай. А в контексте вопроса ТСа, любое СТЕ можно считать синтаксическим сахаром.
26 янв 13, 23:31    [13834587]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
 		А вообще - рекурсия в СТЕ оправдана или лучше ее все же избегать?
26 янв 13, 23:33    [13834595]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
 		Cygapb-007,

Рекурсивное СТЕ -- просто инструмент. Нет заведомо хороших или плохих инструментов.
Применимость рекурсии нужно оценивать в каждом случае индивидуально, как обычно, сравнивая планы рекурсивного и нерекурсивного вариантов.
26 янв 13, 23:50    [13834638]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
 		Цель "не использовать здоровые временные таблицы" и заставить СКЛ-сервер быть "умным". Как будто-то СКЛ сервер "сделал несколько ответвлений" выполнив один раз CTE и "пошёл использовать этот результат".

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

Ладно бы там задача была "чисто результат группировать по-разному", можно быть как-то решил оконными/агрегтными функциями/подитогами и т. п. По факту там ещё надо будет "джойнить и т. п.".
26 янв 13, 23:51    [13834641]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

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

Рекурсивное СТЕ -- просто инструмент. Нет заведомо хороших или плохих инструментов.
Применимость рекурсии нужно оценивать в каждом случае индивидуально, как обычно, сравнивая планы рекурсивного и нерекурсивного вариантов.


Если хотите поофтопить по поводу рекурсивного CTЕ и если вы про "иерархию" (чаще всего) то советую подумать про тип hierarchyid или его костылям-заменителям '001.005.003' и запросам where fieldsName like '001.005.%'

По моей теме "есть чё" ?
27 янв 13, 00:05    [13834664]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

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

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

Ладно бы там задача была "чисто результат группировать по-разному", можно быть как-то решил оконными/агрегтными функциями/подитогами и т. п. По факту там ещё надо будет "джойнить и т. п.".
Может быть стоит изменить постановку задачи? Не предоставлять каждому из одновременно работающих пользователей "универсальный запрос" на 300 00 строк, из которого тот сможет получить свою статистику, а сразу сделать много-много запросов под каждую из статистических задач, каждый из которых будет работать на действительно необходимых для построения этой конкретной статистики данных? Может тогда действительно удастся обойтись простыми СТЕ-VIEWшками "без использования" временных таблиц? (на самом деле результаты View тоже хранятся в таблице в процессе обработки запроса и удаляются при его завершении)
27 янв 13, 00:06    [13834666]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
NIIIK
invm
Cygapb-007,

Рекурсивное СТЕ -- просто инструмент. Нет заведомо хороших или плохих инструментов.
Применимость рекурсии нужно оценивать в каждом случае индивидуально, как обычно, сравнивая планы рекурсивного и нерекурсивного вариантов.


Если хотите поофтопить по поводу рекурсивного CTЕ...
не, я полностью согласен со сравнением планов выполнения
27 янв 13, 00:11    [13834674]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
NIIIK
По моей теме "есть чё" ?
Ну вот как приведете пример использования одного СТЕ в разных запросах, так и по вашей теме продолжим.
27 янв 13, 00:19    [13834684]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 611
NIIIK

Представте, выбрали вы 300 тысяч людей, отобразили первых 20ть, потом
Статистика (количественная) по городам
Статистика по полу
Статистика по возвратным группам
Статистика по .... типу занятости....

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

для таких случаев есть сводные таблицы в Excel и\или кубы в SSAS. зачем огород городить?
27 янв 13, 00:21    [13834685]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
 		Все же не удержался от оффтопа
invm
Cygapb-007,

Рекурсивное СТЕ -- просто инструмент. Нет заведомо хороших или плохих инструментов.
Применимость рекурсии нужно оценивать в каждом случае индивидуально, как обычно, сравнивая планы рекурсивного и нерекурсивного вариантов.
Надеюсь, последний вопрос по теме рекурсии СТЕ с моей стороны - а встречались ли Вам случаи, когда рекурсия оказывалась более предпочтительным вариантом:)?
27 янв 13, 00:25    [13834694]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

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


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

invm
NIIIK
По моей теме "есть чё" ?
Ну вот как приведете пример использования одного СТЕ в разных запросах, так и по вашей теме продолжим.

Конкретный пример или общий?
Конкретный - задолбаетесь читать, даже если я сделаю один вариант сформированного динамического запроса.

Диклевич Александр
для таких случаев есть сводные таблицы в Excel и\или кубы в SSAS. зачем огород городить?

Ага, хоршие названия технологий. Хоршее КунгФу.

Я конечно не Гуру Анализис Сёрвиса, но что бы создать такой Куб как нужен там, так ещё с полнотекстовым поиском и т. п. условиями поиска у меня наверное откроется "дверь в астрал".

Тут я собственно не зря вопрос конкретный поставил. Ещё в теме.
Я не хочу даже выходить в этой задаче за пределы SQL.
Тем более там нет "преагрегированных данных" в принципе. И измерения выделить нельзя. Это не та статистика которая собирается в классических отчётах. Это здоровенный динамически созданный запрос поиска в котором некоторые таблицы и куски могут джонится, а могут нет. Только есть запрос "возвращающий страницы как в гугле", а есть запрос считающий "количество", а потом есть ещё куча запросо которые показывают это "количество" по различным категориям.


ЕЩЁ РАЗ.

Мне нужна та же хрень, которая делается через временную таблицу, но БЕЗ временной таблицы (и табличной переменной).

Что-нить в этом духе кому-нить известно?
27 янв 13, 00:39    [13834725]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
NIIIK
Мне нужна та же хрень, которая делается через временную таблицу, но БЕЗ временной таблицы (и табличной переменной).

Что-нить в этом духе кому-нить известно?
Может быть, "та же хрень, которая делается через временную таблицу, но БЕЗ временной таблицы" называется "постоянная таблица" :)? С колонкой типа "Достоверная дата"(или "Дата формирования")(эээ... или не с колонкой в самой таблице, а в отдельной таблице на одну строку и одно поле)?
Тогда если в начале выполнения запроса обнаруживается, что данные не достоверны, выполняется реформирование таблицы с долгими и мучительными вычислениями, а если все ОК - сразу берется аналитика по кем-то заботливо подготовленному результату...
А заполняется как раз той цепочкой СТЕ, с которой начался топик...
27 янв 13, 00:51    [13834743]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Cygapb-007
NIIIK
Мне нужна та же хрень, которая делается через временную таблицу, но БЕЗ временной таблицы (и табличной переменной).

Что-нить в этом духе кому-нить известно?
Может быть, "та же хрень, которая делается через временную таблицу, но БЕЗ временной таблицы" называется "постоянная таблица" :)? С колонкой типа "Достоверная дата"(или "Дата формирования")(эээ... или не с колонкой в самой таблице, а в отдельной таблице на одну строку и одно поле)?
Тогда если в начале выполнения запроса обнаруживается, что данные не достоверны, выполняется реформирование таблицы с долгими и мучительными вычислениями, а если все ОК - сразу берется аналитика по кем-то заботливо подготовленному результату...
А заполняется как раз той цепочкой СТЕ, с которой начался топик...


Вариант с "постоянной таблицей" мог бы быть полезен для предоставления возможности пользователю выбирать страницы своего запроса без перевыполнения запроса.

Тут мы оптимизируем быстродействие единичного запуска.
27 янв 13, 00:56    [13834754]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
NIIIK
Cygapb-007
пропущено...
Может быть, "та же хрень, которая делается через временную таблицу, но БЕЗ временной таблицы" называется "постоянная таблица" :)? С колонкой типа "Достоверная дата"(или "Дата формирования")(эээ... или не с колонкой в самой таблице, а в отдельной таблице на одну строку и одно поле)?
Тогда если в начале выполнения запроса обнаруживается, что данные не достоверны, выполняется реформирование таблицы с долгими и мучительными вычислениями, а если все ОК - сразу берется аналитика по кем-то заботливо подготовленному результату...
А заполняется как раз той цепочкой СТЕ, с которой начался топик...


Вариант с "постоянной таблицей" мог бы быть полезен для предоставления возможности пользователю выбирать страницы своего запроса без перевыполнения запроса.

Тут мы оптимизируем быстродействие единичного запуска.
Ну это только если ужать диапазон достоверности до посекунды, ятд
27 янв 13, 01:00    [13834761]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса с CTE без временно таблички при повторном обращении к тому же CTE  [new]
NIIIK
Member

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

Это если иметь понятие "выполнный поиск на такое-то время" и "просматривание результатов выполненнго поиска", а не "новый поиск".
Данные могут менятся и пока кто-то на страницу пырится в браузере.
27 янв 13, 01:05    [13834771]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить