[Mondrian] Mondrian generating SQL that cannot be indexed
Luc Boudreau
lucboudreau at gmail.com
Mon Jul 21 16:38:55 EDT 2014
I've actually been working on a branch on my weekends to address ranges.
It can be found here: https://github.com/pentaho/mondrian/tree/smr
These changes would help quite a bit. Not sure about your particular query,
since I believe this is a segment query, and I have not found a way to
nativize ranges for these yet. You can grab it, build it and let us know if
it helps.
Luc
On Mon, Jul 21, 2014 at 3:51 PM, Julian Hyde <julianhyde at gmail.com> wrote:
> That’s pretty yucky SQL. I sympathize.
>
> Can you try refactoring the SQL to other queries that gives the same
> answer?
>
> 1. Move `dim_rul_band`.`band` = 'RUL 39-30’, up as an AND clause, because
> it is common in both branches of the OR.
>
> 2. Try removing the OR, and dealing with Jun and Jul in one clause:
>
> (
> `dim_date`.`DAY_DATE`, `dim_rul_band`.`band`
> )
> in
> (
> (DATE '2014-06-01', 'RUL 39-30’),
>
> ...
>
> (DATE '2014-07-08', 'RUL 39-30')
> )
>
>
>
> 3. Try ‘dim_date.DAY_DATE BETWEEN DATE ‘2014-06-01’ AND DATE ‘2014-07-08’.
> (Mondrian is not currently capable of making that optimization — see
> http://jira.pentaho.com/browse/MONDRIAN-1494 — but it would be
> interesting to see the performance difference.)
>
> Post the performance numbers for the various queries and we will see which
> one we’d like to generate. (In an ideal world… making Mondrian generate
> those queries is another matter… I’m not promising anything…)
>
> Julian
>
>
> On Jul 21, 2014, at 6:50 AM, Ricardo Fradinho <
> ricardo.fradinho at webdetails.pt> wrote:
>
> Hi,
>
> I have a performance problem caused by the queries that Mondrian generates.
> These queries always force full table scans because there's no indexing
> that can be done do help these queries.
>
> The problem happens when I select my time range as (2014-06-01 :
> 2014-07-08) and Mondrian rewrites this as (2014-06) + (2014-07-01 :
> 2014-07-08) which is translated into this nasty SQL:
>
> select
> `dim_site`.`SITE` as `c0`,
> count(distinct `fact_tetrapak_500k`.`dim_function_key`) as `m0`
> from
> `dim_site` as `dim_site`,
> `fact_tetrapak_500k` as `fact_tetrapak_500k`,
> `dim_date` as `dim_date`,
> `dim_rul_band` as `dim_rul_band`
> where `fact_tetrapak_500k`.`dim_site_key` = `dim_site`.`dim_site_key`
> and `fact_tetrapak_500k`.`dateToProcess` = `dim_date`.`DATE_SK`
> and `fact_tetrapak_500k`.`dim_rul_band_key` =
> `dim_rul_band`.`DIM_RUL_BAND_KEY`
> and
> (
> (
> `dim_date`.`MONTH_NAME` = 'June'
> and `dim_date`.`YEAR_NUMBER` = 2014
> and `dim_rul_band`.`band` = 'RUL 39-30'
> )
> or
> (
> (
> (
> `dim_date`.`DAY_DATE`, `dim_rul_band`.`band`
> )
> in
> (
> (DATE '2014-07-01', 'RUL 39-30'),
> (DATE '2014-07-02', 'RUL 39-30'),
> (DATE '2014-07-03', 'RUL 39-30'),
> (DATE '2014-07-04', 'RUL 39-30'),
> (DATE '2014-07-05', 'RUL 39-30'),
> (DATE '2014-07-06', 'RUL 39-30'),
> (DATE '2014-07-07', 'RUL 39-30'),
> (DATE '2014-07-08', 'RUL 39-30')
> )
> )
> )
> )
> group by `dim_site`.`SITE`
>
> There is no indexing (on MySQL) that can prevent a full table scan.
> I implemented a workaround by changing my [Date] dimension to have only
> the Day level, which is fine for the timerange selection we use. But this
> voids the use of aggregation tables.
>
> I tried mondrian.rolap.aggregates.optimizePredicates=false (Boolean
> property that determines whether Mondrian optimizes predicates)
> but as the docs say, "[...] If false, Mondrian still optimizes queries
> that involve all members of a dimension".
>
> Also tried setting mondrian.rolap.EnableInMemoryRollup=false but
> didn't help either.
>
> Do you know if there's any setting that can prevent this optimization ?
>
> Thanks,
> Ricardo Fradinho.
> _______________________________________________
> 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/20140721/722020f7/attachment-0001.html
More information about the Mondrian
mailing list