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