|
Как уже знают читатели моего блога, режим массового вычисления (в Katmai он называется режим блочных вычислений (block computation mode)) даёт намного большую производительность MDX чем, поячеечный (cell-by-cell). Следовательно, наиболее важной техникой оптимизации MDX в Analysis Services является написание MDX, который задействует механизм блочного режима вычислений. Легко сказать, но не всегда легко сделать. Вместе с релизом Katmai CTP5, Microsoft опубликовал статью BOL, описывающую в общих чертах условия, при которых блочные вычисления возможны и при которых нет. Крис Веб (Chris Webb) прицепился к этой статье и упомянул в своём блоге, что “список функций над множествами немного ограничен (где же Filter?)“. В ответ на это я сказал, что не думаю что Filter – это распространённая функция в MDX вычислениях, но Грег Галовей (Greg Galloway) тут же привёл хороший пример с Count(Filter(…)). И это действительно распространённое вычисление – довольно таки часто необходимо узнать сколько элементов множества удовлетворяют определённому условию. (Пример Грега был найти сколько врачей приняли 10 и более пациентов за определённый период времени)
Давайте решим аналогичную задачу в кубе Adventure Works и посмотрим как можно оптимизировать её решение. В терминах Adventure Works, нашей задачей будет найти количество продуктов, которые были заказыны более 5 раз через интернет. Такое выражение может быть записано так: Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5)) В действительности, ни в AS2005 ни в AS2008 функция Filter не оптимизирована для работы в режиме блочных вычислений, следовательно, запрос с таким выражением будет исполняться в поячеечном режиме: WITH MEMBER [Measures].[High Volume Products Count] AS
Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count] Если это запрос запустить из MDX Studio, мы получим такую статистику: Время : 30 sec 781 ms
Calc covers : 4
Вычислено ячеек (Cells calculated) : 4217436
Sonar subcubes : 2
Запросы к данным (SE queries) : 1
Попаданий в кеше (Cache hits) : 1
Промахов в кеше (Cache misses) : 1
Вставок в кеш (Cache inserts) : 1
Поисков в кеше (Cache lookups) : 2
Использование памяти (Memory Usage),KB : 4160 Помимо большого времени выполнения, на то, что запрос выполнился в поячеечном режиме, наиболее точно указывает счётчик «Вычислено ячеек». Сейчас, большиснтво разработчиков предложит способ оптимизации, заключающийся в уменьшении количества ячеек, которые нужно перебрать. Один из способов сделать это состоит в ручном исключении всех пустых ячеек из параметра функции Filter. Т.е., если значение меры [Internet Order Quantity] является NULL для некоторых продуктов, то это, естественно, меньше 5. Таким образом запрос можно оптимизировать так: WITH MEMBER [Measures].[High Volume Products Count] AS
Count(Filter(
Exists([Product].[Product].[Product],,"Internet Sales")
,[Measures].[Internet Order Quantity] > 5))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count] Здесь функция Exists внутри Filter исключает пустые ячейки из подсчёта. Запустим получившийся запрос в MDX Studio и посмотрим на новую статистику Время : 12 sec 46 ms
Calc covers : 954
Вычислено ячеек (Cells calculated) : 50036
Sonar subcubes : 7900
Запросы к данным (SE queries) : 7899
Попаданий в кеше (Cache hits) : 7899
Промахов в кеше (Cache misses) : 1
Вставок в кеш (Cache inserts) : 1
Поисков в кеше (Cache lookups) : 7900
Использование памяти (Memory Usage),KB : 43936 Видим, что время уменьшилось с 30 секунд до 12. Это хорошо, но что-то не сходится. Количество ячеек уменьшилось с 4 миллионов до 50 тысяч, т.е. в 85 раз, а время выполнения всего в 2 раза. Это объясняет тот факт, что использование Exists (или NonEmpty) в MDX рассчётах обычно плохая идея – т.к. теперь для каждой ячейки, выполняется запрос к движку хранения данных (storage engine) (это также можно увидеть в событии “Query Subcube” при трассировке запроса). В нашем случае было выполнено 7900 таких запросов. Т.к. параметр-множество функции Exists был всегда одним и тем же, и контекст вычислений не менялся, то только один такой запрос вызвал обращение к диску, а остальные 7899 были взяты из кеша, однако издержки выполнения запросов к данным не малы, даже когда результаты берутся из кеша. Этот подход, даже несмотря на выигрыш в производительности, работает не очень хорошо с остальными компонентами системы, и лишь уводит нас от достижения цели использовать превосходный режим блочных вычислений. Нам надо переписать вычисления таким образом, чтобы исключить функцию Filter. К счастью, это возможно. Давайте вспомним, что функция Count возвращает количество кортежей (tuples) в множестве, а функция Filter возвращает множество кортежей, которые удовлетворяют опредлённым условиям. Т.е. мы считаем сколько кортежей удовлетворяют заданному условию. Если мы преобразуем количество в сумму и будем добавлять единичку всякий раз когда условие верно и ноль в противном случае, то мы получим тот же результат. Т.е.: Count(Filter(set, condition)) = Sum(set, Iif(condition, 1, 0)) Используя эту формулу можно переписать наше выражение следующим образом: WITH MEMBER [Measures].[High Volume Products Count] AS
Sum(
[Product].[Product].[Product],
Iif([Measures].[Internet Order Quantity] > 5,1,0))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count] Выполнение этого запроса даёт такую статистику: Время : 6 sec 375 ms
Calc covers : 4
Вычислено ячеек (Cells calculated) : 6948
Sonar subcubes : 1
Запросы к данным (SE queries) : 1
Попаданий в кеше (Cache hits) : 1
Промахов в кеше (Cache misses) : 1
Вставок в кеш (Cache inserts) : 1
Поисков в кеше (Cache lookups) : 2
Использование памяти (Memory Usage),KB : 0 Вот это уже значительно лучше. Время уменьшилось до 6 секунд и можно сказать, что в пределах каждой ячейки функция Sum работает очень эффективно, потому что было вычислено только 6948 ячеек (это в точности количество ячеек в результирующем наборе ячеек (resulting cellset)). Хотя, мы ещё не в настоящем режиме блочных вычислений. Мы всё ещё вычисляем сумму для каждой ячейки вместо того, чтобы вычислить весь запрос за одну операцию. Что не даёт нам это сделать? Сейчас проблема заключается в функции Iif внутри Sum. Раньше я уже писал про функциюю Iif и её взаимодействие с режимом блочных вычислений. Если перечитать эту статью, то можно увидеть, что мы находимся в той ситуации когда условие функции Iif зависит от самого значения ячейки, но не зависит от координат свойств (attribute coordinates), и единственное что мы можем здесь сделать – заменить одну из ветвей функции Iif на NULL. Вероятно, имеет смысл возвращать NULL вместо нуля, когда ни один продукт не удовлетворяет нашему условию. После этого мы получим следующее: WITH MEMBER [Measures].[High Volume Products Count] AS
Sum(
[Product].[Product].[Product],
Iif([Measures].[Internet Order Quantity] > 5,1,NULL))
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count] Это улучшает время до 4 секунд, но это всё ещё не даёт наилучшего плана выполнения (execution plan). Сейчас самое время использовать указания по оптимизации производительности. Глядя на выражение Iif([Measures].[Internet Order Quantity] > 5,1,NULL) можно заметить что оно гарантированно вернёт NULL, когда [Measures].[Internet Order Quantity] будет NULL. Следовательно, для него мы можем определить NON_EMPTY_BEHAVIOR. Т.к. NEB нельзя задать для подвыражений (subexpressions), мы выделим его в вычисляемую меру (calculated measure). Напишем такой фрагмент внутри MDX кода CREATE HIDDEN Summator;
[Measures].[Summator] = Iif([Measures].[Internet Order Quantity] > 5,1,NULL);
NON_EMPTY_BEHAVIOR([Measures].[Summator]) = [Measures].[Internet Order Quantity]; Затем используем его внутри вычислений: WITH
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], [Measures].[Summator])
SELECT [Customer].[Customer Geography].[Country] ON 0
, [Date ].[Calendar].[Date ].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count] Наконец-то, когда мы запустим этот запрос, он выполнится за 0.2 секунды (218 милисекунды). Вот это та производительность, которую мы ожидаем получить от режима блочных вычислений! От 32 секунд до 0.2 секунды – улучшение более чем в 160 раз! (на самом деле менее чем в 160, но простим Моше эту незначительную погрешность – прим. перев.) И теперь ещё хорошие новости: начиная с ноябрьского Katmai CTP5 (который был выпущен пару дней назад, и теперь, наконец-то, я могу открыто говорить о нём), трюк с точным заданием NON_EMPTY_BEHAVIOR больше не нужен, т.к. движок (engine) сам распознаёт это. Следовательно, даже наш предпоследний запрос (выполняющийся 4 секунды на AS2005) занимает всего 0.2 секунды на AS2008. (Данное улучшение в оптимизаторе Katmai – это только крошечная верхушка айсберга всех улучшений в планах запросов блочных вычислений, и я собираюсь осветить эту тему более подробно в грядущих блогах, если у меня будет на это время). Комментарии: Fabio сказал: Привет Mosha, Как всегда фантастическая статья, но мне кажется, что про использование функции Filter ты прав только если мы говорим об MDX запросах, т.к. используя вычисляемые меры как ты сможешь задать срез (slice) без использования функции Filter? Декабрь 3, 2007 11:34 AM mosha сказал: Fabio,
На самом деле эта техника непосредственно применима к вычисляемым мерам. В примере блога, техника была использована для вычисляемой меры [High Volume Products Count] и может быть использована в любом MDX запросе Декабрь 3, 2007 12:39 PM Deepak Puri сказал: Привет Mosha, Будет ли задание NON_EMPTY_BEHAVIOR некорректным если определить [Summator] непосредственно в запросе, например так: WITH
Member [Measures].Summator as
Iif([Measures].[Internet Order Quantity] > 5,1,NULL),
Non_Empty_Behavior = [Measures].[Internet Order Quantity]
MEMBER [Measures].[High Volume Products Count] AS
Sum([Product].[Product].[Product], [Measures].[Summator])
SELECT Non Empty [Customer].[Customer Geography].[Country] ON 0
, Non Empty [Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count] Декабрь 3, 2007 2:12 PM mosha сказал: Deepak, ты прав, задание NON_EMPTY_BEHAVIOR непосредственно на вычисляемой мере будет некорректным. В данном случае только установка этого свойства в MDX коде будет правильной. Декабрь 3, 2007 2:22 PM Teo Lachev сказал: Mosha, Оптимизирована ли функция Filter в Katmai? Трюк с суммой исполняется в действительности несколько медленнее на Katmai на кубе с 100,000 клиентов. А ещё, не планируешь ли ты усовершенствование MDX Studio для использования библиотеки ADOMD Katmai? with member ProfitableCustomers as 'Sum
( [Customer].[Customer Name].[Customer Name],
Iif ([Measures].[Profit] > 0, 1, NULL)
)'
select ProfitableCustomers on 0
from Декабрь 18, 2007 12:11 PM mosha сказал: Тео, трюк с суммой всё ещё должен работать быстрее Count(Filter()) в Katmai. Наверно, твой запрос не был достаточно большим чтобы ты почувствовал разницу.
Я вношу изменения в MDX Studio чтобы она лучше работала с Katmai, но я не планирую использовать ADOMD Katmai т.к. версия ADOMD для AS2005 работает без проблем с Katmai |
