Count(Filter(...)) の最適化 - その2：AS2008 での実験結果

前回の投稿「Count(Filter(...)) の最適化（Mosha Pasumansky さんのブログから）」http://blogs.sqlpassj.org/nagasaki/archive/2008/08/31/25540.aspx の続きです。

AS2008 で、サンプルデータベース「Adventure Works DW 2008」のサンプルキューブ「Adventure Works.cube」を使用して試してみました。パフォーマンス測定のため「MDX Studio」を使用させてもらっています。

(1)

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]

Time : 40 sec 203 ms
Calc covers : 0
Cells calculated : 2836944
Sonar subcubes : 2
SE queries : 1
Cache hits : 3
Cache misses : 0
Cache inserts : 0
Cache lookups : 3
Memory Usage KB : 52

AS2005（前回投稿で、同じクエリで約30秒）より遅くなったということではありません（実行環境が違います）。これが基本速度です。

(2)

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]

Time : 12 sec 312 ms
Calc covers : 0
Cells calculated : 50217
Sonar subcubes : 7277
SE queries : 7276
Cache hits : 7278
Cache misses : 0
Cache inserts : 0
Cache lookups : 7278
Memory Usage KB : 1212

前回投稿の AS2005 と同じような振る舞いです。

(3)

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 : 8 sec 156 ms
Calc covers : 0
Cells calculated : 7128
Sonar subcubes : 1
SE queries : 1
Cache hits : 3
Cache misses : 2
Cache inserts : 1
Cache lookups : 5
Memory Usage KB : 0

前回投稿の AS2005 と同じような振る舞いです。

(4)

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]

Time : 578 ms
Calc covers : 0
Cells calculated : 7128
Sonar subcubes : 1
SE queries : 1
Cache hits : 4
Cache misses : 0
Cache inserts : 0
Cache lookups : 4
Memory Usage KB : 0

前回投稿の AS2005 より高速です。Mosha さんが述べているように、2008 では NON_EMPTY_BEHAVIOR の明示が無くても高速化されます。

(5)

 --MDX Script
CREATE HIDDEN Summator;
[Measures].[Summator] = Iif([Measures].[Internet Order Quantity] > 5, 1, NULL);
NON_EMPTY_BEHAVIOR([Measures].[Summator]) = [Measures].[Internet Order Quantity];

 --Query
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]

非推奨になっていますが一応 NON_EMPTY_BEHAVIOR を試してみました。1 分以内に結果が戻らず、計測不能という結果です。 AS2008 での NON_EMPTY_BEHAVIOR の使用は避けたほうがよさそうです。

(6)
ここまでの最速は(4)ですが、(4)の結果セットには 0 ではなく NULL が表示されており、厳密に言えば(1)〜(3)までの結果セットとは異なっています。そこで、結果セットに NULL ではなく 0 を表示するように以下のような記述に変更してみました。

WITH
MEMBER [Measures].[High Volume Products Count] AS 
Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL, 0, 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]

Time : 1 sec 968 ms
Calc covers : 0
Cells calculated : 78588
Sonar subcubes : 1
SE queries : 3
Cache hits : 8
Cache misses : 0
Cache inserts : 0
Cache lookups : 8
Memory Usage KB : 0

この書き方ですと、実行時間は 2 秒程度で済んでいますが計算量（"Cells calculated"）がかなり増えてしまっています。

(7)
そこで、別の計算メンバを使用して、結果セットに NULL ではなく 0 を表示するような別の方法に変更してみました。

WITH
MEMBER [Measures].[High Volume Products Count] AS 
Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)), SOLVE_ORDER = 1
MEMBER [Measures].[High Volume Products Count 2] AS 
Iif([Measures].[High Volume Products Count] = NULL, 0, [Measures].[High Volume Products Count]), SOLVE_ORDER = 2
SELECT [Customer].[Customer Geography].[Country] ON 0
,[Date].[Calendar].[Date].MEMBERS ON 1
FROM [Adventure Works]
WHERE [Measures].[High Volume Products Count 2]

Time : 812 ms
Calc covers : 0
Cells calculated : 7128
Sonar subcubes : 1
SE queries : 1
Cache hits : 6
Cache misses : 0
Cache inserts : 0
Cache lookups : 6
Memory Usage KB : 0

この書き方であれば、約 0.6 秒の(4) と比較しても遜色ありません。"Cells calculated" の値も (4) と同じです。

まとめ
・AS2008 でも Count(Filter(...)) の書き方は自動的には（インスタンスのクエリオプティマイザでは）最適化されない。 Sum + Iif( , , NULL) に変換する必要がある。
・AS2008 では NON_EMPTY_BEHAVIOR を明示的に指定する必要はない。（逆に、指定したら非常に遅くなる振る舞いを見せた。）
・表示上、NULL から 0 に変更しておきたい場合は Sum + Iif 部分が 1 回の計算で済むような工夫が望ましい。具体例は (7) を参照のこと。