IIF の評価（→振る舞い）の仕組みとクエリヒント（SQL Server CAT ブログから）

SQL Server CAT ブログで、MDX の IIF 関数の評価（→振る舞い）の仕組みと、クエリヒントについて投稿がありました。IIF 関数のクエリヒントは AS2008 新機能のようですね。

■SQL Server CAT チームブログ「IIF Function Query Hints in SQL Server Analysis Services 2008」
http://blogs.msdn.com/sqlcat/archive/2008/09/04/iif-function-query-hints-in-sql-server-analysis-services-2008.aspx

そこで、ここ http://blogs.sqlpassj.org/nagasaki/archive/2008/08/31/25540.aspx で使ったクエリで試してみました（AS2008 です）。

(1)ヒントなし

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

2 秒くらいです。

(2) 固定値 0「hint Eager」、計算式「hint Strict」

Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL,
 0 hint Eager,
 Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) hint Strict)

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

同じく 2 秒くらいです。固定値 0 は(おそらく Inexpensive な default branch として) Eager、計算式の方は Strict で評価されているようです。

(3) 固定値 0「hint Eager」、計算式「hint Eager」

Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL,
 0 hint Eager,
 Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) hint Eager)

Time             : 46 sec 987 ms
Calc covers      : 0
Cells calculated : 2908404
Sonar subcubes   : 1
SE queries       : 2
Cache hits       : 4
Cache misses     : 0
Cache inserts    : 0
Cache lookups    : 4
Memory Usage KB  : 0

46 秒もかかりました。

(4) 固定値 0「hint Strict」、計算式「hint Strict」

Iif(Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) = NULL,
 0 hint Strict,
 Sum([Product].[Product].[Product], Iif([Measures].[Internet Order Quantity] > 5, 1, NULL)) hint Strict)

Time             : 1 min 39 sec 65 ms
Calc covers      : 0
Cells calculated : 5738220
Sonar subcubes   : 1
SE queries       : 2
Cache hits       : 6
Cache misses     : 0
Cache inserts    : 0
Cache lookups    : 6
Memory Usage KB  : -4

99 秒もかかりました。
(3)、(4)はずいぶん遅くなりました。逆に言えば、クエリオプティマイザが「Strict」と「Eager」を間違えるとかなり遅くなる可能性がある、ということですね。あまり積極的に使うものではないのでしょうけど、最終手段として覚えておいても良さそうです。