WinterGraveyard,
Более подробный ответ на ваш вопрос, если интересно.
Сперва упростим репро.
Использую тестовую бд
opt.
| + simple repro |
use opt;
go
-- 1. Index Seek
declare @a int = 1;
select t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1 where a = @a
go
-- 2. Index Scan
declare @a int = 1;
select * from
(
select t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1
) t
where t.a = @a
go
-- 3. Index Seek
declare @a int = 1;
select * from
(
select t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1
) t
where t.a = 1
go
-- 4. Index Seek
declare @a int = 1;
select * from
(
select t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1
) t
where t.a = @a
option(recompile)
go
|
Теперь по вопросам.
View vs. QueryКогда в запросе используется представление, сервер, а точнее его компонент алгебраизатор - всегда раскрывает текст представления в запрос. Одно исключение - индексированное представление с подказкой noexpand. Иначе - просто подставляет вместо представления - текст. Т.е. представление это некторый синтаксический сахар. Есть оговорки - например, он кэширует дерево представления (можете наблюдать в кэше такие объекты, как Bound Tree), чтобы не парсить его каждыйраз. И использует метеданные представления (эффект, когда пишут select * в представлении, потом меняют внутри что-то, а столбцы остаются прежние, до момента обновления вью). В остальном - сводится к подставлению текста.
Итого - сняли проблему - вью или нет - ваш второй запро для оптимизатора эквивалентен четвертому.
Option recompile vs noneДо 2008 сервера эта опция говорила оптимизатору буквально следующее: при выполнении, пррослушай заново все параметры и перекомпилируй.
Начиная с 2008, разработчики сиквела осознали, что в момент рекомпиляции уже идет выполнение запроса, все необходимые блокировки и т.д. наложены и можно пойти дальше, можно не просто прослушать параметр, вообще заменить параметр на константу, и оптимизировать более агрессивно.
Например select * from t1 where @a = 1. Если просто прослушаем, то построим план либо для всех строк, либо для 0. Но план в кэш не записывается. Зачем тогда строить универсальный план, который учитывает разные значения переменной. Если в момент выполнения в переменной @a значение 2, план в кэш мы не помещаем, то запро превращается в select * from t1 where 2 = 1. такой запро никогда ничего не вернет - и сервер упрощает план просто до сканирвоания константы - Constant Scan.
Итого - опция рекомпиляции - фактически равна тому, как если бы вы прописали константу, т.е. запросу 2 из вашего поста.
Прослушивание параметровЗдесь не играет роли
Проталкивание предикатовФактически, мы свели вопрос к тому, что:
Если используется фильтр по неизвестной переменной в самом запросе - это поиск. Если используется во внешнем запросе это просмотр. Если посмотреть упрощенное репро, видно, что в случае 1 - поиск, в случае 2 - просмотр. Почему?
Дело в проталкивании предикатов и в оконной функции.
Оптимизатор не должен нарушать логику/семантику/смысл запроса.
Если есть оконная функция во вложенном запросе у него есть два выбора:
1) протолкнуть условие поиска во внутренний запрос, отфильтровать и потом пронумеровать
2) пронумеровать во внутреннем запросе, а потом отфильтровать во внешнем.
В случае 1) - можно выполнить поиск, в случае 2) - нужно выполнить сначала нумерацию и сканирование, а потом отфильтровать.
Ясно, что результаты будут разные.
например данные:
Арбуз
Барабан
Велосипед
Есть нумерация по алфавиту и фильтрация по слову Велосипед (where name = 'Велосипед').
1) первый подход - нумеруем потом фильтруем:
Нумеруем:
1 Арбуз
2 Барабан
3 Велосипед
Фильтруем:
3 Велосипед
Результат:
3 Велосипед
2) второй подход - фильтруем потом нумеруем:
Фильтруем:
Велосипед
Нумеруем
1 Велосипед
Результат:
1 Велосипед
Очевидно разные результаты - нельзя проталкивать предикат, т.е. фильтровать до нумерации.
Но! Есть же указание partition by. Т.е. разделить по группам, где критерий группы - условие фильтра.
Уберите в репро partition by t1.a и увидите, что никакого проталкивания предикатов, кроме первого случая нет. Все потому, чтобы не столкнуться ситуацией описанной выше.
Теперь допустим, мы говорим, что нумеровать надо в пределах группы, а группа определяется условием фильтра.
В таком случае, если значение фильтра известно (константа или option recompile - что по сути одно и то же) - оптимизатор проталкивает фильтрацию вниз и может использовать поиск (примеры 3 и 4 из репро).
Но если значение фильтра неизвестно - то оптимизатор, будучи консервативным и чтобы не нарушать логику запоса - не будет проталкивать фильтрацию вниз, а значит у него не будет условия поиска. Так что форсируй поиск по индексу или нет - искать-то не по чему. По этому, ошибка построения плана с форсированными индексами.
Ситуация старая, вот мы обсуждали ее с Полом Уайтом в комментах к статье три года назад:
The Curious Case of the Optimizer that doesn’tА вот обсуждали на форуме:
Не могу понять физику процесса в параметризированном запросе Для решения вашей проблемы, есть два варианта:
1) отказаться от представления
2) использовать option(recompile)
Баланс между удобством поддержки кода и производительностью (затраты на рекомпиляцию)
Выбирайте меньшее из зол для вашей системы и вперед =)
Удачи!