<p dir="ltr">Interesting stuff thanks for the explanation Matt!</p>
<p dir="ltr">Tom</p>
<div class="gmail_quote">On 2 Oct 2015 18:34, &quot;Matt Campbell&quot; &lt;<a href="mailto:mcampbell@pentaho.com">mcampbell@pentaho.com</a>&gt; wrote:<br type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">





<div lang="EN-US" link="blue" vlink="purple">
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d">Hi Tom,<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d">Your second query involves CrossJoins with sets that include multiple levels of the same hierarchy, e.g. [Time].[Year] and [Time].[Quarter].  Native CrossJoin
 depends on members of each set being of the same level in order to form proper push-down tuple queries.  So native eval is disabled for this query and Mondrian’s stuck loading the full Cartesian product of all CJ’d sets into process.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d">We could potentially enhance native eval to cover cases like this, although the native eval logic is complex and it could be a big effort.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d">-matt<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:&quot;Calibri&quot;,sans-serif"> <a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a> [mailto:<a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a>]
<b>On Behalf Of </b>Tom Barber<br>
<b>Sent:</b> Friday, October 02, 2015 5:49 AM<br>
<b>To:</b> Mondrian developer mailing list &lt;<a href="mailto:mondrian@pentaho.org" target="_blank">mondrian@pentaho.org</a>&gt;<br>
<b>Subject:</b> Re: [Mondrian] Query performance issues<u></u><u></u></span></p>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">Sorry should probably also point out both tests were using the same mondrian build 3.6.5<u></u><u></u></p>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Tom<u></u><u></u></p>
</div>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">On 2 October 2015 at 10:43, Tom Barber &lt;<a href="mailto:tom@analytical-labs.com" target="_blank">tom@analytical-labs.com</a>&gt; wrote:<u></u><u></u></p>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<p class="MsoNormal">Hello folks,<u></u><u></u></p>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">I had a query about MDX query performance between Saiku 2 and 3 and so we did some testing and I&#39;d like some expert analysis if anyone has a spare 10 minutes:<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">SELECT<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">NON EMPTY {Hierarchize({[Measures].[Unit Sales]})} ON COLUMNS,<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">NON EMPTY Hierarchize(Union(CrossJoin([Time].[Year].Members, CrossJoin({[Store].[USA].[OR]}, CrossJoin([Customers].[Name].Members, [Product].[Product Name].Members))), Union(CrossJoin([Time].[Year].Members,
 CrossJoin(Filter({[Store].[USA].[OR].[Portland]}, (Exists(Ancestor([Store].CurrentMember, [Store].[Store State]), {[Store].[USA].[OR]}).Count  &gt; 0)), CrossJoin([Customers].[Name].Members, [Product].[Product Name].Members))), Union(CrossJoin({[Time].[1997].[Q3]},
 CrossJoin({[Store].[USA].[OR]}, CrossJoin([Customers].[Name].Members, [Product].[Product Name].Members))), CrossJoin({[Time].[1997].[Q3]}, CrossJoin(Filter({[Store].[USA].[OR].[Portland]}, (Exists(Ancestor([Store].CurrentMember, [Store].[Store State]), {[Store].[USA].[OR]}).Count
  &gt; 0)), CrossJoin([Customers].[Name].Members, [Product].[Product Name].Members))))))) ON ROWS<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">FROM [Sales]<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt"><u></u> <u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">This is the MDX created by an old Saiku 2.6 server and the query executes in about 20 seconds.<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt"><u></u> <u></u></span></p>
</div>
<div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">with set [~Time_Time_Year] as &#39;Exists({[Time].[Time].[Year].Members}, [~Time_Time_Quarter])&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  set [~Time_Time_Quarter] as &#39;{[Time].[Time].[1997].[Q3]}&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  set [~ROWS_Time_Time] as &#39;Hierarchize({[~Time_Time_Year], [~Time_Time_Quarter]})&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  set [~Store_Stores_Store State] as &#39;{[Store].[Stores].[USA].[OR]}&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  set [~Store_Stores_Store City] as &#39;Exists({[Store].[Stores].[USA].[OR].[Portland]}, [~Store_Stores_Store State])&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  set [~ROWS_Store_Stores] as &#39;Hierarchize({[~Store_Stores_Store State], [~Store_Stores_Store City]})&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  set [~ROWS_Customer_Customers] as &#39;{[Customer].[Customers].[Name].Members}&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  set [~ROWS_Product_Products] as &#39;{[Product].[Products].[Product Name].Members}&#39;</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">select NON EMPTY {[Measures].[Unit Sales]} ON COLUMNS,</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">  NON EMPTY (Crossjoin(Crossjoin(Crossjoin([~ROWS_Time_Time], [~ROWS_Store_Stores]), [~ROWS_Customer_Customers]), [~ROWS_Product_Products])) ON ROWS</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">from [Sales]</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">This is the much cleaner MDX from the 3.x server, but this query timesout every time. Can someone shed any light on why the performance is so much worse? I&#39;ve tried cellbatch size and stuff but none of it makes
 a shred of difference.</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">Thanks</span><u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#888888"><u></u> <u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt;color:#888888">Tom</span><span style="color:#888888"><u></u><u></u></span></p>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
</div>

<br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div>