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

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Всем доброе время суток!
Интересует "общий вопрос" как правильно избавлятся от Table spool в планах запроса, который по сути "временная таблица в tempdb".
Я собственно и сам эти времянки могу делать.
Появляются они в основном когда я использую группы в grouping set (чем больше наборов, тем больше table spool-ов).
Пытался "шамать с индексами". Создавал индексы и пополям которые в группе/джойнах/выборке полей, даже "тупо со всеми".
Пока НЕ помогло. Я вот только надеюсь что эта "временная таблица" создаётся не для тех данных которые я выбираю "в CTE раньше", а для тех что я агрегирую.

Картинку в этом куске плана запроса я вижу "как прикрепил".

По факту "немалое замедление" даёт выборка полей с использованием
case по grouping_id
потом агрегатная фунция count() внутри котороЙ другой case c дополнительной проверкой.

С этими "полями/фукциями" работает
1246

Без этих полей
773

(это "лучшие результаты")

Не устраивают оба.

Как избавлятся от table spool-ов и правильно писать "дополнительные условния" при группировках?
Нужны общие концепции, какие индексы должны быть "и т. п."

Заранее спасибо.

К сообщению приложен файл. Размер - 22Kb
14 фев 13, 13:29    [13925870]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Kanadec
Member

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

А вы в запросе случаем не исползуете, option(force order).
я замечал, такое бывает при использовании этой опции.

Возможно, вам и не удасться избавиться от спулов, так как сикуль считает что это наиболее оптимальный план с использованием спулинга.
14 фев 13, 13:52    [13926108]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

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

Нет, не использую.
Изначально там "здоровая CTE с кривым union и некоторыми другими моментами из-за криовсти базы".
На основе эти данных надо считать статистику "количество", "количество по такому-то атрибуту" и т. д.
Раньше это вставлялось во временную таблицу (много данных) и потом к ней делалось несколько запросов и возвращалось несколько датасетов, а я как раз старался свести к одному запросу.

Тема начиналась тут Оптимизация CTE, только сейчас вернулся. Но похоже "правы были те кто говорил о том что СКЛ Сервер их сам создаёт".

Запрос конечно работает быстрее, да ещё в профайлере показывает меньше чтений/записей/CPU, но всё равно "не айс".
А мне ещё этот "один большой денормализованный датасет" надо раскидать по "тем маленьким, которые были раньше".

Я на интуитивном уровне не понимаю зачем если есть один grouping sets оператор и "один поток данных" SQL Server сохраняет данные во временной таблицу.
14 фев 13, 15:55    [13927080]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Кто-нить может объяснить основную причину появления table spool и "как с ними бороться" (В целом) ?
15 фев 13, 13:33    [13931588]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Кто-нить может объяснить основную причину появления table spoo

Спроектированная вами схема данных
Написанный вами запрос

NIIIK
и "как с ними бороться" (В целом) ?

Правильно проектировать схему
Переписывать запорос
15 фев 13, 13:37    [13931627]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Glory
NIIIK
Кто-нить может объяснить основную причину появления table spoo

Спроектированная вами схема данных
Написанный вами запрос

NIIIK
и "как с ними бороться" (В целом) ?

Правильно проектировать схему
Переписывать запорос


Сам люблю так делать :)
15 фев 13, 13:41    [13931651]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

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

Для вас поставлю вопрос так "какие ошибки приводят к таким результатам" ?
15 фев 13, 13:42    [13931656]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Для вас поставлю вопрос так "какие ошибки приводят к таким результатам" ?

Вы серьезно считаете, что в синтаксисе TSQL есть какие то "особые" команды, которые приводят к низкой производительности запроса ?
15 фев 13, 13:45    [13931669]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Glory
NIIIK
Для вас поставлю вопрос так "какие ошибки приводят к таким результатам" ?

Вы серьезно считаете, что в синтаксисе TSQL есть какие то "особые" команды, которые приводят к низкой производительности запроса ?

Я считаю что существуют "стандартные причины для каких=то проблем".
Начиная от where t.filedName like '%string'
Заканчивая кривыми подзапросами (в списке полей, в where), использованием функций в where/join, которые как минимум "забивают" индексы или ещё хуже являются подзапросами, написанием distinct/group by выражений в пользовательских запросах, потому что какая-то сущность не выделена.

В моём случае у меня "здоровенный запрос поиска" и надо выводить статистику по различным категориям.
Надо сказать "производительность его я уже повысил", далее могу сказать что с этими table spool работает не на много долше теперь.
Но хочется "понимания" когда и почему SQL Server решает что "нужна временная таблица".
15 фев 13, 14:33    [13932056]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
hpv
Member

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

Table Spool

MSDN
The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input.


Не то?

Eager Spool
И в догонку Avoiding Eager Spool ,хоть это и не ваш случай

Как вариант попробовать преписать с помощью ROLLUP/CUBE
15 фев 13, 16:08    [13932796]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

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

Спасибо, жалко что читаю в попыхах, но интересно.
Примеры в основном для ДМЛ операций и там это логично.
(то что в МСДН я уже видел).

Повтыкаю на счёт rollup, cube, но я ими до сих пор не так легко оперериую как group by и т.п.
Пока не понимаю в чём разница может быть для планов (там всё равно надо делать те же вычисления).
15 фев 13, 16:45    [13933115]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
hpv
Как вариант попробовать преписать с помощью ROLLUP/CUBE


RollUp конечно работает быстрее и без Table Spool, но его специфика такова что "все группы не выдаёт".

Cube конечно можно искользовать, но потом проверка в HAVING

grouping_id(
         
         7 полей
         ) in (127, 112, 111, 95,63)


Работает с тем же результатом, но дольше и Table Spool-ов больше.
Так ещё и группировка нужна "фиговая".
Что-то в духе "группировать не по идешшнику человека/предприятия/...), а по "фамилия, имя, отчество, др и названеие, описание предприятия).
Групп вообще пока получается 4ре + "итого"
17 фев 13, 14:57    [13938580]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
aleks2
Guest
NIIIK
Но хочется "понимания" когда и почему SQL Server решает что "нужна временная таблица".


Наверное, просто и незатейливо, не хватает памяти под промежуточные действия запроса, не?
17 фев 13, 15:23    [13938636]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

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

"Где-то там в зелёной части" находятся 10 000+ строк
На синенькую сейчас внимания обращать не надо (но она важна), там union и запрос который ничего не возвращает.

Красная часть - "по теме".

Каждый из Table Spool берёт 5,2% стоимости.
Как я понимаю всего 9*5,2 = 46,8% от стоимости "всего запроса".
Каждая из 10+ строк может попасть от 0 до каждой группы.
В зелёненьком квадрате видно как я выбрал эти "атрибуты" при помощи (select 1 as isColumn) t as .... (условия попадания в колонку)

Далее "за зелёной частью" есть операции с большим Cost, но там только Index Seek, где я похоже сделать уже ничего не могу. Часть из них джой с ФТСом

У кого-нить есть идеи?

К сообщению приложен файл. Размер - 121Kb
21 фев 13, 17:41    [13962674]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
идей нет?!
22 фев 13, 12:27    [13966110]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Glory
Member

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

Про что ?
22 фев 13, 12:39    [13966202]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

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

Про что ?


Как заставить "красненькую часть" работать быстрее.
Что бы как-то "по умному" написать запрос или "есть какой-то метод о котором я даже не слышал".

Кстати, Table Spool по сути "временная таблица".
Когда "теститил" залил данные в обычную физическую таблицу (уже результат этого запроса).

Обратил внимание что если работать потом с физической таблицей "отдельно от основного запроса", то работает быстрее, а если работать со временной таблицей или пересоздавать эту же физическую внутри запроса - работает в 2-3 раза дольше.

Может ли "быть бонусом для производительности" какой-нить SSD жёсткий диск для базы tempdb и т. п. ?
22 фев 13, 12:46    [13966260]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Как заставить "красненькую часть" работать быстрее.

Избавиться от нее.
Если вы знаете самый лучший план выполнения своего запроса, то укажите его серверу.

NIIIK
Что бы как-то "по умному" написать запрос или "есть какой-то метод о котором я даже не слышал".

Для проектирования сферического коня в вакууме нужно использовать специальное программное обеспечение.

NIIIK
Может ли "быть бонусом для производительности" какой-нить SSD жёсткий диск для базы tempdb и т. п. ?

Оптимизатор не проверяет, на каком физическом оборудовании работает сервер.
22 фев 13, 12:59    [13966353]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

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

Задача по сути типовая. "есть общее количество", а потом из этого общего кол-ва более 10000 частично считается "из ни то...", "из ни это ..."

Как бы вы хотели что бы я поставил вопрос при этом незабив кучей лишней информации?

Я понимаю что "железо не проверяет", но "на быстром винте возможно это будет быстрее работать" раз уж ту "такие ограничения"
22 фев 13, 13:17    [13966531]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Как бы вы хотели что бы я поставил вопрос при этом незабив кучей лишней информации?

Чем Table Spool хуже Index Scan-а ?
Почему оптимизатор не в праве использовать Table Spool ?
Только потому, что вам просто _кажется_, что это не оптимально ?
А оптимизатору "кажется" по-другому.
При этом у оптимизатора есть математический алгоритм построения плана и критерии определения оптимальности.
А у вас только ваше "Задача по сути типовая."

NIIIK
Я понимаю что "железо не проверяет", но "на быстром винте возможно это будет быстрее работать" раз уж ту "такие ограничения"

А если ему к спине швабру привязать, то он еще и пол помоет. (с) анекдот
22 фев 13, 13:26    [13966606]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

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

Тут вопрос "как хитро замутить", что бы было быстро.

Я и так там "наприменял" нетипичных решений, пока помогло, причём хорошо.

Обратите внимание, что на каждом spool вфход 10000+ строк, на выходе меньше.

Большенство из полей в grouping set null-овые

получаются
1) Сейчас left join (select 1 as isValue) t on <condition>
2) До этого case-оператором.

Но те где "нулл" даже считать не нужно, а grouping setы всё равно создаёт результат для нуловых значений.
если считать "каждое значение по отдельности", то можно поставить where ... is null и т. п., table Spool нет, но тут нельзя "взять и исключить. Должено быть и "общее количество" и его "составляющие" (причём один и тот же найденый экземпляр может попадать в несколько колонок).
22 фев 13, 13:45    [13966750]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Да тут даже не вопрос "не в праве", особено если это действительно необходимо.

Ну вот на основании чего вы лично считаете, что Table Spool здесь не нужен ?
Что вы лично видите на месте Table Spool в этом запросе и почему ?
22 фев 13, 13:55    [13966826]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK
но "на быстром винте возможно это будет быстрее работать" раз уж ту "такие ограничения"

Не факт, что за время выполнения запроса временные таблицы для spool'ов будут сброшены на диск. Они вроде бы небольшие и вполне могут сидеть в кэше. Хотя специально этот вопрос не изучал, могу ошибаться.
22 фев 13, 14:03    [13966893]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Glory
NIIIK
Да тут даже не вопрос "не в праве", особено если это действительно необходимо.

Ну вот на основании чего вы лично считаете, что Table Spool здесь не нужен ?
Что вы лично видите на месте Table Spool в этом запросе и почему ?


Правильнее сказать что "может есть какой-то метод, который его уберёт".
Или сделает эту часть быстрее.

Представте, вы нашли 10000 записей
потом вам их надо разбить на N групп по различным атрибутом (где-то это просто проверка на значение, где-то несколько полей).
Некоторые колонки были изначально, которые я "создал" (либо операторы Case, либо джойн фейкового select 1 c тем же условием)
Потом по этим атрибутам применил groupping sets (есть группы и с несколькими полями, но их меньше, небыло там "идешника сущности").

В итоге
1) обрабатываются строки (И выдаётся результат) где значения этих атрибутов null (но мне не нужны эти результаты и если бы был способ от этого уйти "до подсчёта" было бы хорошо)
2) строки обрабатыаются по несколько раз, а хотелось бы как бы что бы "одна строка подситалась бы несколько раз за один проход". В общем хотелось бы "умный метод", подсчёта такой статистики.
22 фев 13, 14:16    [13966959]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно избавлятся от Table Spool в планах запроса при использовании grouping sets?  [new]
Glory
Member

Откуда:
Сообщений: 104751
NIIIK
Представте, вы нашли 10000 записей
потом вам их надо разбить на N групп по различным атрибутом (где-то это просто проверка на значение, где-то несколько полей).
Некоторые колонки были изначально, которые я "создал" (либо операторы Case, либо джойн фейкового select 1 c тем же условием)
Потом по этим атрибутам применил groupping sets (есть группы и с несколькими полями, но их меньше, небыло там "идешника сущности").

Ну так укажите это оптимизатору, если вы считаете, что ваш план лучше
22 фев 13, 14:40    [13967155]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить