Count(Filter(...)) の最適化 （Mosha Pasumansky さんのブログから）

Mosha Pasumansky さんのブログ投稿のご紹介です。（翻訳の許可を頂いています。翻訳の正確さは保証できません。）

原文：http://www.sqljunkies.com/WebLog/mosha/archive/2007/11/23/92198.aspx

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

「Optimizing Count(Filter(...)) expressions」

私のブログの読者のみなさんはご存じのように、Katai（※訳者注：SQL Server 2008 の開発コードネーム）で「ブロック計算モード（block computation mode）」と呼ばれている「バルク評価モード（bulk evaluation mode）」は、「セルバイセル評価モード（cell-by-cell evaluation mode）」と比べて、かなり良いパフォーマンスを発揮します。ですから、Analysis Services の MDX における最も重要な最適化テクニックは、「ブロック計算」で実行されるように MDX を書き直すことです。これは言うのが簡単ですが、実施するのは常に簡単という訳ではありません。

Katmai CTP5 の リリースに際して、マイクロソフトは「ブロック計算」が行われる場合とそうでない場合の状況を概説する BOL 記事（※訳者注：Books On Line 記事）を公開しました。Chris Webb はこの記事を取り上げて、彼のブログの投稿「the list of set functions is a bit limited (where is Filter ?)」で言及しています。私は、このブログ投稿のリプライで「MDX 計算の中で Filter 関数が一般的に使われるとは思っていなかった」と述べましたが、Greg Galloway はすぐに Filter 関数が使用される例の一つとして Count(Filter(...)) を挙げました。"特定の条件を満たす対象の数"を調べるこのような計算は、実に一般的な計算です。(Greg の例は、特定の期間内に 10 以上の手術を担当した医師の人数を調べる、という例でした。)

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 で（※訳者注：Mosha Pasumansky さんによる MDX 開発/実行環境。http://www.mosha.com/msolap/mdxstudio.htm からダウンロード可能。）このクエリを実行すると、以下のような統計情報を得るでしょう。

Time : 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 

このクエリが「セルバイセルモード」で実行されたことに関するベストヒントは "Cells calculated" パフォーマンスカウンタの値です。このような MDX の最適化において通常採用されるアプローチは、繰り返し使用されるセルの数を減らすことです。これを行う方法として、マニュアルで 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]

Filter 関数の中の Exists 関数は空白を排除します。これを再び MDX Studio で実行して、新しい統計情報を見てみましょう。

Time : 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 秒まで減少しました。これは良い結果ですが、つじつまが合いません。"Cells calculated" の値は 400 万から 5 万まで減りました。およそ 85 倍です。しかし、実行時間の減少はたったの 2 倍でしかありません。

これに対する説明としては、MDX 計算の中に Exists (または NonEmpty) を記述するのは、通常は悪いアイデアであるということです。これを記述した以降、全てのセルに対して "SE query" が発生しています。（統計情報の "Query Subcube" イベントでも見ることができます。）今回のケースでは、そのようなクエリが 7900 回も発生しています。（※訳者注：統計情報の "SE queries" を参照）。Exists 関数の中のセットは常に同じでありかつコンテキストが固定されているため、1 回のクエリだけは実際にディスクに行かなければなりませんが、他の 7899 回はキャッシュにヒットします。しかし、 "SE query" の発行に起因するオーバーヘッドは、たとえキャッシュから回答が得られる場合であっても無視できないものです。

結論としては、このアプローチによりいくらかのパフォーマンス向上は得られますが、残りの部分では良い振る舞いをしません。そしてこれは、優れた「ブロック計算モード」に切り替える目標から、より遠くに向かわせているだけです。私たちは、Filter 関数を排除する方向でクエリを書き直す必要があります。幸いなことに、それは可能です。Count 関数はセット内のタプルの数を返し、Filter 関数は特定の条件を満たすタプルのセットを返すことを思い出しましょう。要するに、条件を満たしたタプルの数を数えています。Count 関数を Sum 関数に変更して、条件と一致したら 1 をカウントアップし、条件と一致しなかったたら 0 をカウントアップするようにすれば同じ結果を得るでしょう。

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]

このクエリを実行すると、次の統計情報を得ます。

Time : 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 秒まで減りました。6948セル（これは結果となるセルセットの正確なセル数です。）が計算されるだけですので、全てセルの中で Sum 関数がとても効率的に動作していると言えます。

しかし、これはまだ本当の「ブロック計算モード」ではありません。このクエリは、1 回のオペレーションで全体のクエリを計算する代わりに、いまだに全てのセルのために Sum を実行しています。何が原因で、それができないのでしょうか。問題は、Sum 関数の中にある Iif 関数にあります。私は過去に、Iif 関数とその「ブロック計算モード」との相互作用について書きました。その記事を再読すると、Iif 関数が属性座標ではなくセル値を参照する状態のシナリオにいることがわかるでしょう。ここでできる唯一の対応は NULL を返す分岐を持つことです。そして、それは恐らく、条件を満たす製品が全くないときに 0 の代わりに 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 秒まで減りますが、まだ改良の余地があり、まだまだ最良の実行プランに達していません。いまこそ MDX のクエリオプティマイザヒントを使用するべきタイミングです。式 Iif([Measures].[Internet Order Quantity] > 5, 1, NULL) を見てみると、[Measures].[Internet Order Quantity] が NULL である場合に NULL になることが保証されていることが分かります。そこで、NON_EMPTY_BEHAVIOR についての MDX スクリプトを定義します。副次式 (subexpression) で NON_EMPTY_BEHAVIOR を定義できないため、特別な計算メジャーにそれを分離します。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 倍の向上です。）

さらに良いお知らせがあります。それは Katmai の 11 月の CTP5 から開始されますが、（数日前にリリースされたので、私はやっと公式にそれについて話すことができます。）明示的に NON_EMPTY_BEHAVIOR を定義する処置は必要ではありません。エンジンがそれ自体を認識します。ですから、前のクエリ(AS2005 で 4 秒かかったもの)でさえ、AS2008 では 0.2 秒で戻ります。

(この Katmai のクエリオプティマイザの改良は、ブロック計算クエリプランの改良の周りにある小さな氷山の一角に過ぎません。もしそのための十分な時間があれば、私は今度のブログ投稿でより深くこの対象をカバーするつもりです。）

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • -

「Count(Filter(...))の最適化」をまとめますと、以下のようになります。
(1) Count(Filter([Product].[Product].[Product], [Measures].[Internet Order Quantity] > 5))
　 → 30秒。全く工夫のない計算処理であり、すごく遅いです。
(2) Count(Filter(Exists([Product].[Product].[Product], , "Internet Sales"), [Measures].[Internet Order Quantity] > 5))
　 → 12秒。Exists 関数を使用して限定することで "Cells calculated" を減らしましたが、Exists 関数により"SE queries" が増加するため十分な高速化にはなりません。 この方向では改善幅も限定されます。
(3) Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, 0))
　 → 6秒。Count(Filter()) を Sum + Iif にリライトする方法により、計算量（"Cells calculated"）がさらに減り、パフォーマンスが改善されます。ただしこれでも（無駄をかなり省いているとは言え）本当の「ブロック計算モード」とは言えません。
(4) Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL))
　 → 4秒。Iif 関数の最適化として NULL を返すように変更し、さらに改善しています。まだ最良の実行プランに達していません。
(5) NON_EMPTY_BEHAVIOR クエリオプティマイザヒント
　 →0.2秒。NON_EMPTY_BEHAVIOR クエリオプティマイザヒントを指定することで、「ブロック計算モード」相当の速度で実行させることができます。かなり速いです。
(6) AS2008 では NON_EMPTY_BEHAVIOR クエリオプティマイザヒント の指定は必要ありません。自動的に「ブロック計算モード」になります。
　　※ただし、(1)〜(4)のどの書き方で「ブロック計算モード」になるのかは定かではありません。たぶん(4)だけだと思います...

なお、2008 では NON_EMPTY_BEHAVIOR が非推奨機能になっていますのでご注意ください。
http://msdn.microsoft.com/ja-jp/library/ms143346.aspx

次回の投稿では実際に自分で試してみます。特に AS2008 で (1)〜(4) の速度に注目してみます。