Автор: Mosha Pasumansky
Перевод: Александр Орловский
Дата публикации оригинала: 2007-01-28
Источник: Блог Mosha Pasumansky

Функция IIF одна из наиболее популярных функций MDX (что было подтверждено этими исследованиями). Еще она может значительно снизить производительность, в чём часто обвиняют другие части системы. Во многих случаях можно переписать выражение MDX так, чтобы избавиться от IIF. В других случаях возможно немного изменить IFF для увеличения производительности. Пока я и другие указывают на этот факт в блогах, сообщениях на форумах, люди продолжают использовать и использовать эту функцию (и делают это неправильно). Что еще хуже, после того, как они видят оператор CASE в примерах и в BOL, люди начинают использовать CASE вместо IIF. С точки зрения производительности CASE всегда хуже, чем IIF. Наконец, IIF можно в некоторых случаях оптимизировать, но CASE – никогда. Таким образом, я решил выделить специальный блог, посвященный использованию и демонстрации IIF с примерами как это может быть ужасно и как это можно исправить.

Для того чтобы проще было продемонстрировать на стандартном кубе Adventure Works некоторые примеры сделаны в чем-то искусственными, но не сложно представить, что примеры из реальной жизни могут уложиться в похожие шаблоны.

Предположим, что мы хотим посчитать меру Normalized Cost (Нормализованную стоимость), которая совпадает с мерой Product Cost, исключая случай, когда отсутствует промо-акция, то есть, когда Promotion Type равно No Discount, она должна равняться Freight Cost плюс Standard Product Cost. С использованием IIF-функции решение выглядело бы следующим образом:

CREATE [Normalized Cost] =
Iif( [Promotion].[Promotion Type].CurrentMember IS [Promotion].[Promotion Type].&[No Discount]
,[Measures].[Internet Standard Product Cost]+[Measures].[Internet Freight Cost]
,[Measures].[Internet Standard Product Cost]
);

Проверим производительность такого подхода. Чтобы бы сделать измерение производительности нетривиальным мы также введем в запрос YTD-вычисление. Запрос, который мы измеряем, будет выглядеть так:

WITH
MEMBER Measures.[Normalized Cost] AS Iif(
[Promotion].[Promotion Type].CurrentMember IS [Promotion].[Promotion Type].&[No Discount]
,[Measures].[Internet Standard Product Cost]+[Measures].[Internet Freight Cost]
,[Measures].[Internet Standard Product Cost])
MEMBER [Ship Date].[Date].RSum AS Sum([Ship Date].[Date].[Date].MEMBERS), SOLVE_ORDER=10
SELECT
[Promotion].[Promotion Type].[Promotion Type].MEMBERS on 0
,[Product].[Subcategory].[Subcategory].MEMBERS*[Customer].[State-Province].[State-Province].MEMBERS ON 1
FROM [Adventure Works]
WHERE ([Ship Date].[Date].RSum, Measures.[Normalized Cost])

На моем лэптопе этот запрос выполнился за 36 секунд (заметьте, что мы также включили Promotion Type атрибут на оси для того, чтобы обе формулы приняли участие в запросе, и чтобы сделать более сложным для оптимизатора, хотя вообще-то здесь это не имеет значения. SOLVE_ORDER=10 необходимо для того, что предотвратить другую оптимизацию, относящуюся к нарастающим итогам (running sum), которая скроет неэффективность IIF. В конце концов, мы хотим увидеть случай, когда хитрости оптимизатора не работают, и IIF работает плохо).

Сейчас мы используем тот факт, что если условие IIF смотрит только на координаты члена, а не на значения ячеек, то почти всегда можно переписать запрос, используя одно или несколько выражений SCOPE. В этом простом, но общем случае, достаточно одного присваивания:

CREATE [Normalized Cost] = [Measures].[Internet Standard Product Cost];
([Promotion].[Promotion Type].&[No Discount], Measures.[Normalized Cost] =
[Measures].[Internet Standard Product Cost]+[Measures].[Internet Freight Cost];

Это правильный подход, поместить вычисление в MDX Script, но поскольку предыдущий запрос был написан с использованием вычисляемых элементов, где нельзя использовать синтаксис SCOPE или выражений присваивания, мы можем в целях тестирование переписать SCOPE в выражение WITH CELL CALCULATION. Наш запрос будет выглядеть так:

WITH
MEMBER Measures.[Normalized Cost] AS [Measures].[Internet Standard Product Cost]
CELL CALCULATION ScopeEmulator
FOR '([Promotion].[Promotion Type].&[No Discount],measures.[Normalized Cost])'
AS [Measures].[Internet Freight Cost]+[Measures].[Internet Standard Product Cost]
MEMBER [Ship Date].[Date].RSum AS Sum([Ship Date].[Date].[Date].MEMBERS), SOLVE_ORDER=10
SELECT
[Promotion].[Promotion Type].[Promotion Type].MEMBERS on 0
,[Product].[Subcategory].[Subcategory].MEMBERS*[Customer].[State-Province].[State-Province].MEMBERS ON 1
FROM [Adventure Works]
WHERE ([Ship Date].[Date].RSum, Measures.[Normalized Cost])

Этот запрос эквивалентен предыдущему, но выполняется 0.36 секунды, точно в 100 раз быстрее. Секрет того, почему второй запрос намного быстрее, прост. Так как выражение SCOPE определяет статические подкубы, на которых применяются формулы, оптимизатор может разделить пространство, описанное запросом на две части – первая, где вычисляется одна формула, и вторая, разрезанная по значению No Discount, где вычисляется вторая формула. Оптимизатор запросов может провести bulk-вычисление на обоих подпространствах и результаты будут посчитаны с быстротой молнии (более подробно о bulk-вычислениях читайте в этой статье.

С функцией IIF такое деление невозможно, а оптимизатор не настолько умен, чтобы понять, что условие внутри IIF статично. Поэтому оптимизатор опасается выбрать bulk-вычисление в плане запроса, т.к. опасается вычислить слишком много ненужных ячеек. Как мы знаем, вычисление результатов ячейка за ячейкой намного медленнее, чем bulk-вычисление. Сейчас много сообщений в форумах и группах новостей о магическом параметре «Cache Policy=9» в строке подключения. Несомненно, если он используется, то первый запрос выполняется за секунду. Но это очень опасный путь. «Cache Policy=9» дает указывание оптимизатору выбрать план bulk-вычислений в любых ситуациях. Хотя во многих случаях (включая этот) это лучший план, существует много случаев, когда bulk-вычисление медленнее и хуже масштабируется. Лучше всего оставить такие решения оптимизатору, который из десятка встроенных планов должен быть применен на конкретном подпространстве и подвыражении. Также «Cach Policy=9» документирован в BOL как зарезервированный для будущего использования, что означает, что официально не поддерживается, хотя информация и просочилась в форумы. Он поддерживается, только если рекомендован официальной службой технической поддержки. Мы надеемся, что в следующей версии оптимизатор станет более интеллектуальным, и будем в состоянии выбирать более хороший план автоматически.

Сейчас, как мы обозначили выше, IIF можно заменить на SCOPE, только если условие содержит координаты членов, но не значения ячеек. Итак, что если условие требует просмотра значений ячеек? Здесь нет общих техник, но есть несколько простых подсказок, которые можно применить. Давайте рассмотрим классический случай проверки деления на ноль. Я уже писал на эту тему раньше, но фокусировался больше на аспектах корректности, чем на производительности.

Предположим мы хотим посчитать коэффициент продаж к стоимости.

CREATE Ratio = [Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount];

Но мы озабочены, что когда Internet Sales Amount будет равно нулю, мы можем получить деление на ноль. В действительности, в Adventure Works, Internet Sales Amount никогда не принимает значение ноль в таблице фактов. Оно может быть NULL, но т.к. Internet Standard Product Cost находится в той же группе мер, и каждое из них может быть NULL, то, как мы знаем, когда Internet Sales Amount равно NULL, тогда и Internet Standard Product Cost равно NULL, и NULL/NULL в MDX определяется как NULL. Таким образом, в нашем случае вообще не требуется проверять что либо, т.к. здесь никогда не будет деления на ноль. Для проверки простого деления давайте запустим следующий MDX-запрос. Он делает не так много для суммирования коэффициента, но это простой путь указать расчет деления на огромном наборе.

WITH
MEMBER Measures.Ratio AS [Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount]
MEMBER Measures.TestPerf AS SUM(
([Product].[Subcategory].[Subcategory].MEMBERS
,[Customer].[State-Province].[State-Province].MEMBERS
,[Customer].[Customer].[Customer].MEMBERS
,[Promotion].[Promotion Type].[Promotion Type].MEMBERS
,[Ship Date].[Date].[Date].MEMBERS), Measures.Ratio)
SELECT TestPerf ON 0
FROM [Adventure Works]
 
Этот запрос выполнился всего за 0.1 секунды, понятно, что это из-за того, что использовался bulk-план. Теперь, предположим, что для Internet Sales Amount возможно значение ноль по любой причине (например, ноль может прийти из таблицы фактов). Значит, нам от этого надо защититься. Но что нам надо вернуть в случае деления на ноль? Это сильно зависит от бизнес-требований. Иногда требуется вернуть Н/Д (нет данных). Выражение записывается так:

CREATE Ratio =
IIF([Measures].[Internet Sales Amount]=0
,"NA"
,[Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount]);

К сожалению, это плохой выбор и с точки зрения производительности, и с точки зрения функциональности. Если любое MDX-выражение будет использовать коэффициент, будет существовать риск вычисления арифметической операции между числами и строкой Н/Д, что в лучшем случае вызовет ошибку «неправильные типы данных, использованных в операции», а худшем может привести к ошибочным результатам. Производительность такого выражения может значительно упасть. Я не хочу запускать здесь исходный запрос, а только запрос на уменьшенном множестве

WITH
MEMBER Measures.Ratio AS
IIF([Measures].[Internet Sales Amount]=0
,"NA"
,[Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount])
MEMBER Measures.TestPerf AS SUM(
([Product].[Subcategory].[Subcategory].MEMBERS
,[Customer].[State-Province].[State-Province].MEMBERS
,[Promotion].[Promotion Type].[Promotion Type].MEMBERS
,[Ship Date].[Date].[Date].MEMBERS), Measures.Ratio)
SELECT TestPerf ON 0
FROM [Adventure Works]

Запрос выполняется за 64 секунды (и результаты плохи – они пустые из-за смешанных операций между строками и числами). Некоторые вопросы производительности могут быть объяснены операциями над строками, а как насчет использования 0 вместо «Н/Д?»

WITH
MEMBER Measures.Ratio AS
IIF([Measures].[Internet Sales Amount]=0
,0
,[Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount])
MEMBER Measures.TestPerf AS SUM(
([Product].[Subcategory].[Subcategory].MEMBERS
,[Customer].[State-Province].[State-Province].MEMBERS
,[Promotion].[Promotion Type].[Promotion Type].MEMBERS
,[Ship Date].[Date].[Date].MEMBERS), Measures.Ratio)
SELECT TestPerf ON 0
FROM [Adventure Works]

Он завершается за 35 секунд, что уже немного лучше, но все еще не очень хорошо. И если мы будем использовать исходный запрос

WITH
MEMBER Measures.Ratio AS
IIF([Measures].[Internet Sales Amount]=0
,0
,[Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount])
MEMBER Measures.TestPerf AS SUM(
([Product].[Subcategory].[Subcategory].MEMBERS
,[Customer].[State-Province].[State-Province].MEMBERS
,[Customer].[Customer].[Customer].MEMBERS
,[Promotion].[Promotion Type].[Promotion Type].MEMBERS
,[Ship Date].[Date].[Date].MEMBERS), Measures.Ratio)
SELECT TestPerf ON 0
FROM [Adventure Works]

Мы получим известную ошибку #Ошибка Выражение содержит функцию которая не может работать на множестве из более чем 4,294,967,296 кортежей. (#Error The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples).

Эта ошибка случается, когда некоторая операция в MDX требует итерации по множеству, содержащему более 4 Гб кортежей. Обычно это происходит, когда нужно выполнить «плохое» вычисление на гигантском множестве. Мы заметили, что результат декартова произведения внутри SUM является, действительно, гигантским – он содержит более 4 Гб кортежей. А поскольку наш вычислимый член использует IIF, режим bulk-вычисления на применяется, и вместо этого обработчик запросов должен выполнять итерации ячейка за ячейкой по всему множеству. Итерации приходится делать по количеству кортежей, превышающему 4 ГБ, поэтому итерации не удаются. К счастью, в этой ситуации существует простое решение. Вспомните дискуссию о разреженных и плотных пространствах, которую мы вели в этой статье. Постоянное выражение 0 является плотным в любом пространстве, но постоянное выражение NULL является разреженным в любом пространстве. Поэтому простая замена 0 на NULL позволяет оптимизатору запросов использовать режим bulk-вычислений, поскольку он знает, что ветка “then” функции IIF всегда будет тривиально разреженной (всегда не будет содержать данных)

WITH
MEMBER Measures.Ratio AS
IIF([Measures].[Internet Sales Amount]=0
,NULL
,[Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount])
MEMBER Measures.TestPerf AS SUM(
([Product].[Subcategory].[Subcategory].MEMBERS
,[Customer].[State-Province].[State-Province].MEMBERS
,[Customer].[Customer].[Customer].MEMBERS
,[Promotion].[Promotion Type].[Promotion Type].MEMBERS
,[Ship Date].[Date].[Date].MEMBERS), Measures.Ratio)
SELECT TestPerf ON 0
FROM [Adventure Works]

Это запрос теперь выполняется за 0.1 секунды, что, несомненно, говорит о том, что выполняется bulk-вычисление. Но, давайте вернёмся к бизнес-требованиям. Что, если бизнес-пользователи не хотят видеть NULL, и что, если они не хотят вместо этого видеть «Н/Д»? Что ж, этот тип форматирования лучше всего сделать, используя логику FORMAT_STRING, которая существует как раз для этих целей – для форматирования сырых значений ячеек, и приведения их к виду, который будет приятен для просмотра конечным пользователем. Мы можем использовать FORMAT_STRING чтобы преобразовать значения NULL в «Н/Д» как показано в примере ниже:

WITH
MEMBER Measures.Ratio AS
IIF([Measures].[Internet Sales Amount]=0,NULL,[Measures].[Internet Standard Product Cost]/[Measures].[Internet Sales Amount])
,FORMAT_STRING='#.#;;;NA'
SELECT
Measures.Ratio ON 0
,[Promotion].[Promotion Type].[Promotion Type].MEMBERS ON 1
FROM [Adventure Works]

В заключение мой совет – старайтесь избегать использования IIF где это возможно, а если этого не удаётся избежать, используйте её так, чтобы стало возможным bulk-вычисление. Я также рекомендую никогда не использовать оператор CASE для вычислений, производительность которых критична.


Для удобства отслеживания новых публикаций рекомендуем подписаться на рассылку или на канал RSS.

Читайте также: