Оптимизация view с пользовательскими функциями в MS SQL
Приветствую.
Намедни пришлось разбираться с проблемой оптимизации view — таблиц MS SQL Server 2008, содержащих пользовательские функции. Так как на основных ресурсах, посвящённых этой тематике, готовых рекомендаций найдено не было, ниже делюсь своим опытом.
Исходная таблица хорошо оптимизирована таким образом, что на текущем объёме (10 млн записей-проводок) операции как записи, так и чтения с фильтрацией по аналитикам происходят практически моментально.
Основная фильтрация во время выборок производится по полям UDN_D_Acc и UDN_C_Acc, в которых хранятся, соответственно, ID счетов дебета и кредита.
Имеется также сервер аналитических отчётов (SSAS), который периодически закачивает данные на базе предварительно созданных view-таблиц. Факт по понятным причинам базируется на выборках из таблицы проводок, так что основная задача состоит в том, чтобы оптимизировать view фактов.
Далее для примера используется часть оптимизируемой view (результирующая view будет состоять из нескольких аналогичных запросов). Код view:
Выполнение запроса — минута на 150 000 записей, что примерно на порядок превосходит ожидаемое время. Причина очевидна после запуска профайлера — функция
Первым делом проверяю свойство детерменированности функции:
Получаю на выходе 0, перерабатываю функцию, после чего она становится детерминированной.
Время выполнения — также около минуты. Не помогло.
Вторым шагом переделываю запрос с тем, чтобы заставить оптимизатор вычислять функцию однократно:
20 секунд, что всё равно слишком много. При этом появился забавный эффект: если из двух условий оставить одно (любое), время выполнения сокращается до менее чем одной секунды. Явно, проблема в построении плана запроса — надо использовать прямые инструкции планировщику.
Третий вариант:
Здесь использовалась инструкция HASH — при этом ядро SQL SERVER вначале создаст в памяти слепок каждой выборки, после чего уже будет производить по ним join.
Время выполнения — 1 секунда. То, что надо, но есть одно «но»: выражение
В принципе,
Четвёртый вариант выглядел как
Больше двух минут, и профайлер показывает 150 000 вычислений функций. То есть, в такой конструкции хинт на детерминированной функции просто не работает.
Мысль о том, что это очевидный баг движка СУБД, греет, но задача не решена. Из общих соображений надо два условия заменить одним:
Результат — искомая 1 секунда. При этом, как видно по тексту запроса, даже не используются хинты: здесь оптимизатор сам догадался прохешировать подзапрос.
2. В случае возможности менять окружение и при использовании запроса без других join'ов можно обойтись опцией хэширования, что принудительно вычислит значения функций один раз.
3. По непонятным причинам один подзапрос в where будет хэшироваться оптимизатором, два и более — нет.
4. Похоже на то, что вообще вызов пользовательских функций более чем в одном подзапросе сбивает оптимизатор с толку и заставляет его считать «в лоб».
5. В случае использования пользовательских функций в рамках единственного подзапроса / условия, оптимизатор способен определить детерминированность и отработать правильно.
По пунктам 3-5 официальный хелп молчит — надеюсь, статья будет полезна другим разработчикам.
Намедни пришлось разбираться с проблемой оптимизации 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Оптимизация
Первым делом проверяю свойство детерменированности функции:
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)В принципе,
select * from OLAP_ProfitLoss_view option (hash 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 официальный хелп молчит — надеюсь, статья будет полезна другим разработчикам.
0 комментариев