Нужно поместить сложный запрос во view. Это ограничение сторонней системы.
1. Можно сделать табличную функцию, где заполняется нескольких табличных переменных и происходит join. Такая функция сначала построит весь набор данных и view будет фильтроваться по нему. Так работает быстро на небольшом числе записей, но их количество скоро вырастет.

2. Можно сделать inline функцию или cte из нескольких частей внутри view. Но select из такого view уже выполняется долго даже на малом числе записей.

Как хинтами или ещё как-то указать оптимизатору, что нужно нечто вроде spool на каждую часть cte для эмуляции варианта №1 внутри варианта №2? Чтобы эту конструкцию поместить во view и фильтры во where могли бы ограничивать хотя бы частично число исходных данных для join, а не готовый результат.

RankatanРекурсией из одного шагаНе могу догадаться как :-(

aleks222Правильно написанный вариант 1 будет работать всегда и быстро.Если функция собирает миллион записей, хотя нужно всего 10 по условию, то как она будет быстрой? Или оптимизатор умеет пробрасывать внешнее условие в неинлайновую функцию?

TaPaKумеетГде почитать? У меня не выходит написать неинлайновую функцию, чтобы внешний параметр учитывался внутри неё.



Все показатели в профилере различаются не более, чем на 0.03% включая reads.

Rankatan
Спасибо, помогло. По крайней мере запрос не обёрнутый во view ускорился существенно. Хотя в документации spool в CTE упоминается только относительно remote server.

tunknownПо крайней мере запрос не обёрнутый во view ускорился существенно. Хотя в документации spool в CTE упоминается только относительно remote server.Обёрнутый во view запрос с CTE тормозит. Все Eager Spool вернулись в Lazy Spool. Однако, обёрнутый в инлайн-функцию сохранил Eager Spool в плане, фильтрует по внешнему условию в самом начале и работает быстро.

Не может ли такое (недокументированное?)поведение измениться в очередном SP/CU? У меня 12.0.5557.0 (X64)

invm
Если вам непременно нужна фильтрация до соединения, то это делается гораздо проще и без хинтов и манипуляций с рекурсиейМне нужна не только фильтрация, но и Eager Spool. Запрос достаточной развестый, работал быстро только в процедуре или в неинлайновой функции с табличными переменными.

top (9223372036854775807) попробую на всякий случай.

invmНе нужен вам Eager Spool. С точки зрения дальнейшего потребления данных другими итераторами, он ничем не отличается от Lazy.
План нужно анализировать и искать причину тормозов.В запросе несколько вложенных CTE, join на самих себя, по некоторым делается сложение текста через xml и т.д. Естественно, он тормозит без "материализации".

RankatanТам можно добиться, чтобы формировался spool через рекурсию и потом в остальных запросах обращение происходило именно к этому сформированному spool, но сейчас сходу не смог сделать пример, надо экспериментировать.При внешней фильтрации по такой "материализуемой" функции Eager Spool выживает не всегда. Иногда сваливается в Lazy, видимо, в зависимости от совпадения фильтра с индексами. При этом сваливании производительность хотя и падает, но остаётся приличной.

Кстати, откуда такой метод? Личные эксперименты, документация или чей-то опыт?

Владислав Колосовtunknown,

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

invmПродолжаете упорствовать?

Читайте до полного пониманияПрочитал. Lazy спулит только необходимые записи, Eager весь указанный набор данных, в моём случае- каждую вложенную CTE. Такой костыль в моём случае превращает синтаксический сахар в физическую "материализацию". План+профилер подтверждают многократное ускорение.

Работа с основной таблицей не предусматривает update/delete, только нечастые insert. Предполагаю, что упомянутые по ссылкам проблемы в моём случае имеют низкий приоритет.

invmПлохо прочитали. Спулят они одно и то же, только eager все сразу , а lazy постепенно.Это и нужно.

invmЕсли план отличается только типом конкретного спула , ни о каком многократном ускорении речи быть не может.План развесистый, он отличается кроме Lazy<->Eager некоторыми перестановками операторов.