[Mondrian] Woefully slow query

Luc Boudreau lucboudreau at gmail.com
Sun Feb 2 21:26:56 EST 2014


I think Andy is right.

Luc
On Feb 2, 2014 5:51 PM, "Andy Grohe" <agrohe21 at gmail.com> wrote:

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


More information about the Mondrian mailing list