<p dir="ltr">If I recall correctly, this had/has to do with the cell batch size property.</p>
<div class="gmail_quote">On Feb 2, 2014 4:48 PM, &quot;Julian Hyde&quot; &lt;<a href="mailto:jhyde@pentaho.com">jhyde@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">
Well, I guess I&#39;m pleased that things have gotten a lot better in mondrian-4! I think there were a couple of bugs about generating lots of SQL statements. I don&#39;t recall which they were, or when they got fixed.<br>

<br>
Julian<br>
<br>
<br>
On Jan 31, 2014, at 11:27 AM, Tom Barber(Alabs) &lt;<a href="mailto:tom@analytical-labs.com">tom@analytical-labs.com</a>&gt; wrote:<br>
<br>
&gt; Ok here is the issue:<br>
&gt;<br>
&gt; SELECT NON EMPTY {Hierarchize({{[Invoice Date].[Invoice<br>
&gt; Date].[Year].Members}, {[Invoice Date].[Invoice<br>
&gt; Date].[Month].Members}})} ON COLUMNS,<br>
&gt; NON EMPTY {Hierarchize({[Customer].[Customer].[Customer].Members})} ON ROWS<br>
&gt; FROM [Aging]<br>
&gt;<br>
&gt; Using the following schema and ddl and ~ 35k rows on Postgres.<br>
&gt;<br>
&gt; On  mondrian Trunk it does this for literally hours:<br>
&gt;<br>
&gt; 2014-01-31 16:54:16,950 DEBUG [mondrian.sql] 228: Segment.load:<br>
&gt; executing sql [select &quot;aging_link&quot;.&quot;Country Code&quot; as &quot;c0&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot; as &quot;c1&quot;, &quot;datelookup_1&quot;.&quot;calendaryear&quot; as<br>
&gt; &quot;c2&quot;, sum(&quot;aging_link&quot;.&quot;Invoice Amount&quot;) as &quot;m0&quot; from<br>
&gt; &quot;public&quot;.&quot;aging_link&quot; as &quot;aging_link&quot;, &quot;public&quot;.&quot;datelookup&quot; as<br>
&gt; &quot;datelookup_1&quot; where &quot;aging_link&quot;.&quot;Country Code&quot; = &#39;Somewhere&#39; and<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot; in (&#39;272483545&#39;, &#39;272483941&#39;,<br>
&gt; &#39;272484027&#39;, &#39;272484605&#39;, &#39;272485439&#39;, &#39;272485822&#39;, &#39;272485950&#39;,<br>
&gt; &#39;272486276&#39;, &#39;272486317&#39;, &#39;272486544&#39;, &#39;272486867&#39;, &#39;272487956&#39;,<br>
&gt; &#39;272488313&#39;, &#39;272488876&#39;, &#39;272488959&#39;, &#39;272489022&#39;, &#39;272490318&#39;,<br>
&gt; &#39;272490404&#39;, &#39;272490420&#39;, &#39;272491126&#39;, &#39;272491270&#39;, &#39;272491449&#39;,<br>
&gt; &#39;272491452&#39;, &#39;272491861&#39;, &#39;272492033&#39;, &#39;272492541&#39;, &#39;272493292&#39;,<br>
&gt; &#39;272493672&#39;, &#39;272493870&#39;, &#39;272494071&#39;, &#39;272494899&#39;, &#39;272496598&#39;,<br>
&gt; &#39;272497054&#39;, &#39;272497249&#39;, &#39;272497492&#39;, &#39;272497588&#39;, &#39;272497997&#39;,<br>
&gt; &#39;272498169&#39;, &#39;272498718&#39;, &#39;272499261&#39;, &#39;272499571&#39;, &#39;272500138&#39;,<br>
&gt; &#39;272501706&#39;, &#39;272501889&#39;, &#39;272502530&#39;, &#39;272503207&#39;, &#39;272503278&#39;,<br>
&gt; &#39;272504776&#39;, &#39;272504789&#39;, &#39;272505571&#39;, &#39;272506037&#39;, &#39;272506107&#39;,<br>
&gt; &#39;272507126&#39;, &#39;272507407&#39;, &#39;272509487&#39;, &#39;272510830&#39;, &#39;272512951&#39;,<br>
&gt; &#39;272513194&#39;, &#39;272513219&#39;, &#39;272513222&#39;, &#39;272513251&#39;, &#39;272513404&#39;,<br>
&gt; &#39;272513590&#39;, &#39;272514423&#39;, &#39;272514915&#39;, &#39;272515554&#39;, &#39;272516825&#39;,<br>
&gt; &#39;272516979&#39;, &#39;272517000&#39;, &#39;272517422&#39;, &#39;272519192&#39;, &#39;272519332&#39;,<br>
&gt; &#39;272520123&#39;, &#39;272521296&#39;, &#39;272521308&#39;, &#39;272521692&#39;, &#39;272522187&#39;) and<br>
&gt; &quot;aging_link&quot;.&quot;Due Date&quot; = &quot;datelookup_1&quot;.&quot;daydate&quot; group by<br>
&gt; &quot;aging_link&quot;.&quot;Country Code&quot;, &quot;aging_link&quot;.&quot;Customer Number&quot;,<br>
&gt; &quot;datelookup_1&quot;.&quot;calendaryear&quot;]<br>
&gt;<br>
&gt; Running 100&#39;s of queries just iterating through the whole fact table.<br>
&gt;<br>
&gt; If I run the identical schema on Mondrian 4 it runs a grand total of 10<br>
&gt; queries and the whole thing finishes in 12 seconds (including rendering<br>
&gt; 12500 rows in Saiku)<br>
&gt;<br>
&gt; 2014-01-31 19:26:24,327 DEBUG [mondrian.sql] 125:<br>
&gt; SqlTupleReader.readTuples [[Invoice Date].[Invoice Date].[Year]]:<br>
&gt; executing sql [select &quot;datelookup&quot;.&quot;calendaryear&quot; as &quot;c0&quot; from<br>
&gt; &quot;aging_link&quot; as &quot;aging_link&quot;, &quot;datelookup&quot; as &quot;datelookup&quot; where<br>
&gt; &quot;aging_link&quot;.&quot;Invoice Date&quot; = &quot;datelookup&quot;.&quot;daydate&quot; group by<br>
&gt; &quot;datelookup&quot;.&quot;calendaryear&quot; order by &quot;datelookup&quot;.&quot;calendaryear&quot; ASC<br>
&gt; NULLS LAST]<br>
&gt; 2014-01-31 19:26:24,434 DEBUG [mondrian.sql] 125: , exec 106 ms<br>
&gt; 2014-01-31 19:26:24,435 DEBUG [mondrian.sql] 125: , exec+fetch 108 ms, 3<br>
&gt; rows<br>
&gt; 2014-01-31 19:26:24,435 DEBUG [mondrian.sql] 126:<br>
&gt; SqlTupleReader.readTuples [[Invoice Date].[Invoice Date].[Month]]:<br>
&gt; executing sql [select &quot;datelookup&quot;.&quot;calendaryear&quot; as &quot;c0&quot;,<br>
&gt; &quot;datelookup&quot;.&quot;calendarmonth&quot; as &quot;c1&quot;, &quot;datelookup&quot;.&quot;monthname&quot; as &quot;c2&quot;<br>
&gt; from &quot;aging_link&quot; as &quot;aging_link&quot;, &quot;datelookup&quot; as &quot;datelookup&quot; where<br>
&gt; &quot;aging_link&quot;.&quot;Invoice Date&quot; = &quot;datelookup&quot;.&quot;daydate&quot; group by<br>
&gt; &quot;datelookup&quot;.&quot;calendaryear&quot;, &quot;datelookup&quot;.&quot;calendarmonth&quot;,<br>
&gt; &quot;datelookup&quot;.&quot;monthname&quot; order by &quot;datelookup&quot;.&quot;calendaryear&quot; ASC NULLS<br>
&gt; LAST, &quot;datelookup&quot;.&quot;calendarmonth&quot; ASC NULLS LAST]<br>
&gt; 2014-01-31 19:26:24,520 DEBUG [mondrian.sql] 126: , exec 85 ms<br>
&gt; 2014-01-31 19:26:24,521 DEBUG [mondrian.sql] 126: , exec+fetch 86 ms, 31<br>
&gt; rows<br>
&gt; 2014-01-31 19:26:24,522 DEBUG [mondrian.sql] 127:<br>
&gt; SqlTupleReader.readTuples [[Customer].[Customer].[Customer]]: executing<br>
&gt; sql [select &quot;aging_link&quot;.&quot;Country Code&quot; as &quot;c0&quot;, &quot;aging_link&quot;.&quot;Customer<br>
&gt; Number&quot; as &quot;c1&quot;, &quot;aging_link&quot;.&quot;Customer Name&quot; as &quot;c2&quot; from &quot;aging_link&quot;<br>
&gt; as &quot;aging_link&quot; group by &quot;aging_link&quot;.&quot;Country Code&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot;, &quot;aging_link&quot;.&quot;Customer Name&quot; order by<br>
&gt; &quot;aging_link&quot;.&quot;Country Code&quot; ASC NULLS LAST, &quot;aging_link&quot;.&quot;Customer<br>
&gt; Number&quot; ASC NULLS LAST]<br>
&gt; 2014-01-31 19:26:25,254 DEBUG [mondrian.sql] 127: , exec 732 ms<br>
&gt; 2014-01-31 19:26:25,374 DEBUG [mondrian.sql] 127: , exec+fetch 852 ms,<br>
&gt; 12382 rows<br>
&gt; 2014-01-31 19:26:25,669 DEBUG [mondrian.sql] 128:<br>
&gt; SqlStatisticsProvider.getColumnCardinality: executing sql [select<br>
&gt; count(distinct &quot;Country Code&quot;) from &quot;aging_link&quot;]<br>
&gt; 2014-01-31 19:26:25,944 DEBUG [mondrian.sql] 128: , exec 274 ms<br>
&gt; 2014-01-31 19:26:25,944 DEBUG [mondrian.sql] 128: , exec+fetch 275 ms, 1<br>
&gt; rows<br>
&gt; 2014-01-31 19:26:25,944 DEBUG [mondrian.sql] 129:<br>
&gt; SqlStatisticsProvider.getColumnCardinality: executing sql [select<br>
&gt; count(distinct &quot;calendaryear&quot;) from &quot;datelookup&quot;]<br>
&gt; 2014-01-31 19:26:25,966 DEBUG [mondrian.sql] 129: , exec 21 ms<br>
&gt; 2014-01-31 19:26:25,966 DEBUG [mondrian.sql] 129: , exec+fetch 22 ms, 1 rows<br>
&gt; 2014-01-31 19:26:25,966 DEBUG [mondrian.sql] 130:<br>
&gt; SqlStatisticsProvider.getColumnCardinality: executing sql [select<br>
&gt; count(distinct &quot;Customer Number&quot;) from &quot;aging_link&quot;]<br>
&gt; 2014-01-31 19:26:26,177 DEBUG [mondrian.sql] 130: , exec 210 ms<br>
&gt; 2014-01-31 19:26:26,177 DEBUG [mondrian.sql] 130: , exec+fetch 211 ms, 1<br>
&gt; rows<br>
&gt; 2014-01-31 19:26:26,178 DEBUG [mondrian.sql] 131: Segment.load:<br>
&gt; executing sql [select &quot;aging_link&quot;.&quot;Country Code&quot; as &quot;c0&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot; as &quot;c1&quot;, &quot;datelookup&quot;.&quot;calendaryear&quot; as<br>
&gt; &quot;c2&quot;, sum(&quot;aging_link&quot;.&quot;Invoice Amount&quot;) as &quot;m0&quot; from &quot;aging_link&quot; as<br>
&gt; &quot;aging_link&quot;, &quot;datelookup&quot; as &quot;datelookup&quot; where &quot;aging_link&quot;.&quot;Country<br>
&gt; Code&quot; in (&#39;BELGIUM&#39;, &#39;Belgium&#39;) and &quot;datelookup&quot;.&quot;calendaryear&quot; in<br>
&gt; (2011, 2012, 2013) and &quot;aging_link&quot;.&quot;Invoice Date&quot; =<br>
&gt; &quot;datelookup&quot;.&quot;daydate&quot; group by &quot;aging_link&quot;.&quot;Country Code&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot;, &quot;datelookup&quot;.&quot;calendaryear&quot;]<br>
&gt; 2014-01-31 19:26:26,186 DEBUG [mondrian.sql] 132:<br>
&gt; SqlStatisticsProvider.getColumnCardinality: executing sql [select<br>
&gt; count(distinct &quot;calendarmonth&quot;) from &quot;datelookup&quot;]<br>
&gt; 2014-01-31 19:26:26,221 DEBUG [mondrian.sql] 132: , exec 34 ms<br>
&gt; 2014-01-31 19:26:26,221 DEBUG [mondrian.sql] 132: , exec+fetch 35 ms, 1 rows<br>
&gt; 2014-01-31 19:26:26,222 DEBUG [mondrian.sql] 133: Segment.load:<br>
&gt; executing sql [select &quot;aging_link&quot;.&quot;Country Code&quot; as &quot;c0&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot; as &quot;c1&quot;, &quot;datelookup&quot;.&quot;calendaryear&quot; as<br>
&gt; &quot;c2&quot;, &quot;datelookup&quot;.&quot;calendarmonth&quot; as &quot;c3&quot;, sum(&quot;aging_link&quot;.&quot;Invoice<br>
&gt; Amount&quot;) as &quot;m0&quot; from &quot;aging_link&quot; as &quot;aging_link&quot;, &quot;datelookup&quot; as<br>
&gt; &quot;datelookup&quot; where &quot;aging_link&quot;.&quot;Country Code&quot; in (&#39;BELGIUM&#39;, &#39;Belgium&#39;)<br>
&gt; and &quot;datelookup&quot;.&quot;calendaryear&quot; in (2011, 2012, 2013) and<br>
&gt; &quot;aging_link&quot;.&quot;Invoice Date&quot; = &quot;datelookup&quot;.&quot;daydate&quot; group by<br>
&gt; &quot;aging_link&quot;.&quot;Country Code&quot;, &quot;aging_link&quot;.&quot;Customer Number&quot;,<br>
&gt; &quot;datelookup&quot;.&quot;calendaryear&quot;, &quot;datelookup&quot;.&quot;calendarmonth&quot;]<br>
&gt; 2014-01-31 19:26:26,389 DEBUG [mondrian.sql] 131: , exec 210 ms<br>
&gt; 2014-01-31 19:26:26,434 DEBUG [mondrian.sql] 131: , exec+fetch 256 ms,<br>
&gt; 12447 rows<br>
&gt; 2014-01-31 19:26:26,485 DEBUG [mondrian.sql] 133: , exec 261 ms<br>
&gt; 2014-01-31 19:26:26,542 DEBUG [mondrian.sql] 133: , exec+fetch 319 ms,<br>
&gt; 18685 rows<br>
&gt; 2014-01-31 19:26:27,261 DEBUG [mondrian.sql] 134: Segment.load:<br>
&gt; executing sql [select &quot;aging_link&quot;.&quot;Country Code&quot; as &quot;c0&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot; as &quot;c1&quot;, &quot;datelookup&quot;.&quot;calendaryear&quot; as<br>
&gt; &quot;c2&quot;, sum(&quot;aging_link&quot;.&quot;Invoice Amount&quot;) as &quot;m0&quot; from &quot;aging_link&quot; as<br>
&gt; &quot;aging_link&quot;, &quot;datelookup&quot; as &quot;datelookup&quot; where &quot;aging_link&quot;.&quot;Country<br>
&gt; Code&quot; in (&#39;Ireland&#39;, &#39;Sweden&#39;) and &quot;aging_link&quot;.&quot;Customer Number&quot; in<br>
&gt; (&#39;953724073&#39;, &#39;953724440&#39;, &#39;960814035&#39;) and &quot;datelookup&quot;.&quot;calendaryear&quot;<br>
&gt; in (2011, 2012, 2013) and &quot;aging_link&quot;.&quot;Invoice Date&quot; =<br>
&gt; &quot;datelookup&quot;.&quot;daydate&quot; group by &quot;aging_link&quot;.&quot;Country Code&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot;, &quot;datelookup&quot;.&quot;calendaryear&quot;]<br>
&gt; 2014-01-31 19:26:27,261 DEBUG [mondrian.sql] 135: Segment.load:<br>
&gt; executing sql [select &quot;aging_link&quot;.&quot;Country Code&quot; as &quot;c0&quot;,<br>
&gt; &quot;aging_link&quot;.&quot;Customer Number&quot; as &quot;c1&quot;, &quot;datelookup&quot;.&quot;calendaryear&quot; as<br>
&gt; &quot;c2&quot;, &quot;datelookup&quot;.&quot;calendarmonth&quot; as &quot;c3&quot;, sum(&quot;aging_link&quot;.&quot;Invoice<br>
&gt; Amount&quot;) as &quot;m0&quot; from &quot;aging_link&quot; as &quot;aging_link&quot;, &quot;datelookup&quot; as<br>
&gt; &quot;datelookup&quot; where &quot;aging_link&quot;.&quot;Country Code&quot; in (&#39;Ireland&#39;, &#39;Sweden&#39;)<br>
&gt; and &quot;aging_link&quot;.&quot;Customer Number&quot; in (&#39;953724073&#39;, &#39;953724440&#39;,<br>
&gt; &#39;960814035&#39;) and &quot;datelookup&quot;.&quot;calendaryear&quot; in (2011, 2012, 2013) and<br>
&gt; &quot;aging_link&quot;.&quot;Invoice Date&quot; = &quot;datelookup&quot;.&quot;daydate&quot; group by<br>
&gt; &quot;aging_link&quot;.&quot;Country Code&quot;, &quot;aging_link&quot;.&quot;Customer Number&quot;,<br>
&gt; &quot;datelookup&quot;.&quot;calendaryear&quot;, &quot;datelookup&quot;.&quot;calendarmonth&quot;]<br>
&gt; 2014-01-31 19:26:27,265 DEBUG [mondrian.sql] 135: , exec 3 ms<br>
&gt; 2014-01-31 19:26:27,266 DEBUG [mondrian.sql] 135: , exec+fetch 5 ms, 5 rows<br>
&gt; 2014-01-31 19:26:27,266 DEBUG [mondrian.sql] 134: , exec 4 ms<br>
&gt; 2014-01-31 19:26:27,267 DEBUG [mondrian.sql] 134: , exec+fetch 6 ms, 3 rows<br>
&gt;<br>
&gt;<br>
&gt; The above is the entire query that is pushed to the database, as you can<br>
&gt; see, much quicker and cleaner.<br>
&gt;<br>
&gt; Anyway, there you go.<br>
&gt;<br>
&gt; Tom<br>
&gt; &lt;schema.xml&gt;&lt;ddl.txt&gt;_______________________________________________<br>
&gt; Mondrian mailing list<br>
&gt; <a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
&gt; <a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<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" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
</blockquote></div>