Сообщение [SQL SERVER] Представление, оконные функции и внешний поиско от 11.12.2014 7:52
Изменено 11.12.2014 8:02 Olaf
Добрый день, коллеги!
Решил зафиксировать проблему, которая у меня возникла в виде отдельной темы. Возможно, кому-то пригодится и позволит сэкономить время на поиск решения, да и у меня твердо отложится в голове и в следующий раз я не наступлю на те же грабли.
Краткая предыстория
В версии SQL сервер 2005 существовало странное поведение, а скорее всего баг, связанный с поисковыми предикатами, которые использовались за пределами представления, содержащего оконную функцию, например row_number
В частности оптимизатор принимал решение сначала просканировать индекс/таблицу, а только потом наложить внешний поисковый предикат на запрос. Хотя если выполнить эквивалент этого запроса без представления, то оптимизатор правильно разбирал ситуацию и использовал поиск по индексу. Картинка ниже демонстрирует данное поведение.
Проблема была невесть какая и легко решалась через использование inline функции с параметром, нужно было лишь помнить о ней. Кроме того, начиная с версии 2008 ошибка была исправлена и больше не привлекала к себе внимание. План аналогичной пары запросов уже выглядел вот так
Наши дни
Успешно забыв о поведении, описанном выше, решил написать запрос по обработке данных для версии 2014. Сам запрос очень большой и сложный, поэтому я приведу только важную часть, которая продемонстрирует проблему. В отличие от предыстории, конструкция запроса содержит оконную функцию в предложении order by и top with ties в инструкции select.
А вот план запроса с представлением и без
Как мы видим, другая вариация запроса с представлением, оконной функцией и внешним предикатом, а проблема все та же, т.е. сначала оптимизатор выполняет запрос из представления, а потом накладывает фильтр из предиката. Разница в производительности на больших объемах очень ощутима.
Обойти данное поведение можно через inline функцию. Надеюсь, в следующих версиях и эта ошибка будет исправлена.
Тестовые данные
Решил зафиксировать проблему, которая у меня возникла в виде отдельной темы. Возможно, кому-то пригодится и позволит сэкономить время на поиск решения, да и у меня твердо отложится в голове и в следующий раз я не наступлю на те же грабли.
Краткая предыстория
В версии 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 функцию. Надеюсь, в следующих версиях и эта ошибка будет исправлена.
Тестовые данные
Скрытый текст | |
| |
[SQL SERVER] Представление, оконные функции и внешний поиско
Добрый день, коллеги!
Решил зафиксировать проблему, которая у меня возникла в виде отдельной темы. Возможно, кому-то пригодится и позволит сэкономить время на поиск решения, да и у меня твердо отложится в голове и в следующий раз я не наступлю на те же грабли.
Краткая предыстория
В версии SQL сервер 2005 существовало странное поведение, а скорее всего баг, связанный с поисковыми предикатами, которые использовались за пределами представления, содержащего оконную функцию, например row_number
В частности оптимизатор принимал решение сначала просканировать индекс/таблицу, а только потом наложить внешний поисковый предикат на запрос. Хотя если выполнить эквивалент этого запроса без представления, то оптимизатор правильно разбирал ситуацию и использовал поиск по индексу. Картинка ниже демонстрирует данное поведение.
Проблема была невесть какая и легко решалась через использование inline функции с параметром, нужно было лишь помнить о ней. Кроме того, начиная с версии 2008 ошибка была исправлена и больше не привлекала к себе внимание. План аналогичной пары запросов уже выглядел вот так
Наши дни
Успешно забыв о поведении, описанном выше, решил написать запрос по обработке данных для версии 2014. Сам запрос очень большой и сложный, поэтому я приведу только важную часть, которая продемонстрирует проблему. В отличие от предыстории, конструкция запроса содержит оконную функцию в предложении order by и top with ties в инструкции select.
А вот план запроса с представлением и без
Как мы видим, другая вариация запроса с представлением, оконной функцией и внешним предикатом, а проблема все та же, т.е. сначала оптимизатор выполняет запрос из представления, а потом накладывает фильтр из предиката. Разница в производительности на больших объемах очень ощутима.
Обойти данное поведение можно через inline функцию. Надеюсь, в следующих версиях и эта ошибка будет исправлена.
Тестовые данные
Решил зафиксировать проблему, которая у меня возникла в виде отдельной темы. Возможно, кому-то пригодится и позволит сэкономить время на поиск решения, да и у меня твердо отложится в голове и в следующий раз я не наступлю на те же грабли.
Краткая предыстория
В версии 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 функцию. Надеюсь, в следующих версиях и эта ошибка будет исправлена.
Тестовые данные
Скрытый текст | |
| |