Оптимизация view с пользовательскими функциями в MS SQL

Приветствую.

Намедни пришлось разбираться с проблемой оптимизации view — таблиц MS SQL Server 2008, содержащих пользовательские функции. Так как на основных ресурсах, посвящённых этой тематике, готовых рекомендаций найдено не было, ниже делюсь своим опытом.

Описание системы и постановка задачи

Имеется некая ERP-система, интегрированная с бухгалтерией. Ядро системы содержит таблицу Passes с данными по проводкам и аналитическим разрезам (одну на все счета). По некоторым причинам обращения к таблице на чтение производятся через view-таблицу Passes_view.
Исходная таблица хорошо оптимизирована таким образом, что на текущем объёме (10 млн записей-проводок) операции как записи, так и чтения с фильтрацией по аналитикам происходят практически моментально.

Основная фильтрация во время выборок производится по полям UDN_D_Acc и UDN_C_Acc, в которых хранятся, соответственно, ID счетов дебета и кредита.

Имеется также сервер аналитических отчётов (SSAS), который периодически закачивает данные на базе предварительно созданных view-таблиц. Факт по понятным причинам базируется на выборках из таблицы проводок, так что основная задача состоит в том, чтобы оптимизировать view фактов.

Далее для примера используется часть оптимизируемой view (результирующая view будет состоять из нескольких аналогичных запросов). Код view:
create view OLAP_ProfitLoss_view
as

select S.UDN_Doc Doc_UDN, S.d1 CFU_UDN, S.d2 Descr_UDN, S.d3 ReqTrip_UDN, S.d4 AccUser_UDN, S.d5 Val_UDN, S.c2 Customer_UDN,
- S.s Sum_RUR, - S.v Sum_Val,
convert(datetime, convert(varchar(25), S.Date_Pass, 4), 4) Date_Pass,
S.od2 CustomerCustomer_UDN
from dbo.Passes_view S
where S.UDN_D_Acc = dbo.AccPlan_getAccUDN('80') and S.UDN_C_Acc in (dbo.AccPlan_getAccUDN('60'), dbo.AccPlan_getAccUDN('61'))


Выполнение запроса — минута на 150 000 записей, что примерно на порядок превосходит ожидаемое время. Причина очевидна после запуска профайлера — функция dbo.AccPlan_getAccUDN, возвращающая ID счёта по номеру, выполняется для каждой строки запроса.

Оптимизация

Первым делом проверяю свойство детерменированности функции:
select OBJECTPROPERTY (object_id ('[dbo].[AccPlan_getAccUDN]'), 'isDeterministic')

Получаю на выходе 0, перерабатываю функцию, после чего она становится детерминированной.
Время выполнения — также около минуты. Не помогло.

Вторым шагом переделываю запрос с тем, чтобы заставить оптимизатор вычислять функцию однократно:
select S.UDN_Doc Doc_UDN, S.d1 CFU_UDN, S.d2 Descr_UDN, S.d3 ReqTrip_UDN, S.d4 AccUser_UDN, S.d5 Val_UDN, S.c2 Customer_UDN,
- S.s Sum_RUR, - S.v Sum_Val,
convert(datetime, convert(varchar(25), S.Date_Pass, 4), 4) Date_Pass,
S.od2 CustomerCustomer_UDN
from dbo.Passes_view S
where S.UDN_D_Acc in (select dbo.AccPlan_getAccUDN('80'))
and S.UDN_C_Acc in (select dbo.AccPlan_getAccUDN('60') union all select dbo.AccPlan_getAccUDN('61'))


20 секунд, что всё равно слишком много. При этом появился забавный эффект: если из двух условий оставить одно (любое), время выполнения сокращается до менее чем одной секунды. Явно, проблема в построении плана запроса — надо использовать прямые инструкции планировщику.

Третий вариант:
select S.UDN_Doc Doc_UDN, S.d1 CFU_UDN, S.d2 Descr_UDN, S.d3 ReqTrip_UDN, S.d4 AccUser_UDN, S.d5 Val_UDN, S.c2 Customer_UDN,
- S.s Sum_RUR, - S.v Sum_Val,
convert(datetime, convert(varchar(25), S.Date_Pass, 4), 4) Date_Pass,
S.od2 CustomerCustomer_UDN
from dbo.Passes_view S
where S.UDN_D_Acc in (select dbo.AccPlan_getAccUDN('80'))
and S.UDN_C_Acc in (select dbo.AccPlan_getAccUDN('60') union select dbo.AccPlan_getAccUDN('61'))
option (hash join)

Здесь использовалась инструкция HASH — при этом ядро SQL SERVER вначале создаст в памяти слепок каждой выборки, после чего уже будет производить по ним join.
Время выполнения — 1 секунда. То, что надо, но есть одно «но»: выражение option (hash join) непонятно из каких соображений нельзя применить внутри view.
В принципе, select * from OLAP_ProfitLoss_view option (hash join) даёт также 1 секунду (что, кстати, непонятно), но OLAP-сервер самостоятельно строит обращения к БД и не позволяет вставлять в них предопределённые опции. То есть, вся оптимизация должна быть зашита внутри view. Кроме того, метод «некрасив»: у меня во view могут появиться и другие join'ы, которые могут дать провал по производительности при использовании хэширования. Надо научиться применять хинт прицельно на оптимизируемые условия.

Четвёртый вариант выглядел как
select S.UDN_Doc Doc_UDN, S.d1 CFU_UDN, S.d2 Descr_UDN, S.d3 ReqTrip_UDN, S.d4 AccUser_UDN, S.d5 Val_UDN, S.c2 Customer_UDN,
- S.s Sum_RUR, - S.v Sum_Val,
convert(datetime, convert(varchar(25), S.Date_Pass, 4), 4) Date_Pass,
S.od2 CustomerCustomer_UDN
from dbo.Passes_view S
inner hash join
(select dbo.AccPlan_getAccUDN('80') Acc_UDN) Acc80
on Acc80.Acc_UDN = S.UDN_D_Acc
inner hash join
(select dbo.AccPlan_getAccUDN('60') Acc_UDN union select dbo.AccPlan_getAccUDN('61') Acc_UDN) Acc60_80
on Acc60_80.Acc_UDN = S.UDN_C_Acc

Больше двух минут, и профайлер показывает 150 000 вычислений функций. То есть, в такой конструкции хинт на детерминированной функции просто не работает.
Мысль о том, что это очевидный баг движка СУБД, греет, но задача не решена. Из общих соображений надо два условия заменить одним:
select S.UDN_Doc Doc_UDN, S.d1 CFU_UDN, S.d2 Descr_UDN, S.d3 ReqTrip_UDN, S.d4 AccUser_UDN, S.d5 Val_UDN, S.c2 Customer_UDN,
- S.s Sum_RUR, - S.v Sum_Val,
convert(datetime, convert(varchar(25), S.Date_Pass, 4), 4) Date_Pass,
S.od2 CustomerCustomer_UDN
from dbo.Passes_view S
inner join
(select dbo.AccPlan_getAccUDN('80') Acc_D_UDN, dbo.AccPlan_getAccUDN('60') Acc_C_UDN
union
select dbo.AccPlan_getAccUDN('80') Acc_D_UDN, dbo.AccPlan_getAccUDN('61') Acc_C_UDN) Acc
on Acc.Acc_D_UDN = S.UDN_D_Acc and Acc.Acc_C_UDN = S.UDN_C_Acc


Результат — искомая 1 секунда. При этом, как видно по тексту запроса, даже не используются хинты: здесь оптимизатор сам догадался прохешировать подзапрос.

Выводы

1. Даже детерминированные функции от констант в условии where будут вызываться столько раз, сколько строк фильтруется в датасете.
2. В случае возможности менять окружение и при использовании запроса без других join'ов можно обойтись опцией хэширования, что принудительно вычислит значения функций один раз.
3. По непонятным причинам один подзапрос в where будет хэшироваться оптимизатором, два и более — нет.
4. Похоже на то, что вообще вызов пользовательских функций более чем в одном подзапросе сбивает оптимизатор с толку и заставляет его считать «в лоб».
5. В случае использования пользовательских функций в рамках единственного подзапроса / условия, оптимизатор способен определить детерминированность и отработать правильно.

По пунктам 3-5 официальный хелп молчит — надеюсь, статья будет полезна другим разработчикам.