Добрый день, коллеги!
Решил зафиксировать проблему, которая у меня возникла в виде отдельной темы. Возможно, кому-то пригодится и позволит сэкономить время на поиск решения, да и у меня твердо отложится в голове и в следующий раз я не наступлю на те же грабли.
Краткая предыстория
В версии SQL сервер 2005 существовало странное поведение, а скорее всего баг, связанный с поисковыми предикатами, которые использовались за пределами представления, содержащего оконную функцию, например row_number
-- Представление
create view dbo.vwMonths as
select Id, Name, row_number() over(partition by name order by Id) as N
from dbo.Months
go
-- Запросы
/* 1 */ select *, row_number() over(partition by Name order by Id) from dbo.Months where Name = 'февраль'
/* 2 */ select * from dbo.vwMonths where Name = 'февраль'
В частности оптимизатор принимал решение сначала просканировать индекс/таблицу, а только потом наложить внешний поисковый предикат на запрос. Хотя если выполнить эквивалент этого запроса без представления, то оптимизатор правильно разбирал ситуацию и использовал поиск по индексу. Картинка ниже демонстрирует данное поведение.
Проблема была невесть какая и легко решалась через использование inline функции с параметром, нужно было лишь помнить о ней. Кроме того, начиная с версии 2008 ошибка была исправлена и больше не привлекала к себе внимание. План аналогичной пары запросов уже выглядел вот так
Наши дни
Успешно забыв о поведении, описанном выше, решил написать запрос по обработке данных для версии 2014. Сам запрос очень большой и сложный, поэтому я приведу только важную часть, которая продемонстрирует проблему. В отличие от предыстории, конструкция запроса содержит оконную функцию в предложении order by и top with ties в инструкции select.
-- Представление
create view dbo.vwMonths2 as
select top 1 with ties Id, Name
from dbo.Months
order by row_number() over(partition by name order by Id)
go
-- Запросы
/* 1 */
select top 1 with ties *
from dbo.Months
where Name = 'февраль'
order by row_number() over(partition by Name order by Id)
/* 2 */
select *
from dbo.vwMonths2
where Name = 'февраль'
А вот план запроса с представлением и без
Как мы видим, другая вариация запроса с представлением, оконной функцией и внешним предикатом, а проблема все та же, т.е. сначала оптимизатор выполняет запрос из представления, а потом накладывает фильтр из предиката. Разница в производительности на больших объемах очень ощутима.
Обойти данное поведение можно через inline функцию. Надеюсь, в следующих версиях и эта ошибка будет исправлена.
Тестовые данные
| | Скрытый текст |
| |
create table dbo.Months
(
Id int PRIMARY KEY CLUSTERED,
Name varchar(30)
)
go
create index IX_Months_Name on dbo.Months(Name)
go
delete from dbo.Months
insert into dbo.Months
select 1, 'январь' union all
select 2, 'февраль' union all
select 3, 'март' union all
select 4, 'апрель' union all
select 5, 'май' union all
select 6, 'июнь' union all
select 7, 'июль' union all
select 8, 'август' union all
select 9, 'сентярь' union all
select 10, 'октябрь' union all
select 11, 'ноябрь' union all
select 12, 'декабрь'
go
create view dbo.vwMonths as
select Id, Name, row_number() over(partition by name order by Id) as N
from dbo.Months
go
create view dbo.vwMonths2 as
select top 1 with ties Id, Name
from dbo.Months
order by row_number() over(partition by name order by Id)
go
|
| | |
