[Mondrian] Woefully slow query

Andy Grohe agrohe21 at gmail.com
Sun Feb 2 17:51:00 EST 2014


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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20140202/bb014ef2/attachment-0001.html 


More information about the Mondrian mailing list