Problem performance Jpivot vs Pivot4j

[Javier Armando Montaño Vargas]

I have a problem with the execution performance of the following Mdx:

SELECT {[Measures].[Numero Aforos], [Measures].[AMOUNT CALC]} ON COLUMNS, Hierarchize(Union({[Facturas].[E64], [Facturas].[E64].[CPF6307036N8], [Facturas].[E64].[FAP931221E18], [Facturas].[E64].[FNI970829JR9], [Facturas].[R76], [Facturas].[R76].[CPF6307036N8], [Facturas].[R76].[FAP931221E18], [Facturas].[R76].[FNI970829JR9], [Facturas].[R76].[FNI970829JR9].[CTPA], [Facturas].[R76].[FNI970829JR9].[CTUP], [Facturas].[R76].[FNI970829JR9].[FTTP], [Facturas].[R76].[FNI970829JR9].[NTTP]}, [Facturas].[R76].[FNI970829JR9].[CTPA].Children)) ON ROWS FROM [FacturasV8]

Al cubo

<?xml version="1.0" encoding="UTF-8"?>

<Schema name="CubeTrazaV4">

    <Cube name="FacturasV8" visible="true" cache="true" enabled="true">

<Table name="TRZ_CUBO" schema="DEV_USER" alias="Cubo trazabilidad">

    </Table>

<Dimension name="Facturas" visible="true">

      <Hierarchy hasAll="true" visible="true" >

<Level name="Decenas" column="DECENA" visible="true"

primaryKey="DECENA" uniqueMembers="false"/>

<Level name="Emisores" column="RFC_EMISOR" visible="true"

primaryKey="RFC_EMISOR" uniqueMembers="false"/>

<Level name="Series" column="SERIE" visible="true"

primaryKey="SERIE" uniqueMembers="false"/>

        <Level name="Folios" column="FOLIO" visible="true"

primaryKey="FOLIO" uniqueMembers="false"/>

<Level name="Seq Transit" column="SEQ_TRANSIT" visible="true"

uniqueMembers="false"/>

<Level name="Nombre Plaza" column="NOMBRE_PLAZA" visible="true"

uniqueMembers="false"/>

<Level name="TCARRETERO" column="TCARRETERO" visible="true"

uniqueMembers="false"/>

<Level name="ID_PLACES" column="ID_PLACES" visible="true"

uniqueMembers="false"/>

<Level name="ID_CARD" column="ID_CARD" visible="true"

uniqueMembers="false"/>

      </Hierarchy>

</Dimension>

<Measure name="AMOUNT CALC" column="AMOUNT_CALC" formatString="#,##0.###" aggregator="sum" visible="true">

    </Measure>

<Measure name="Numero Aforos" column="FOLIO" aggregator="count"

      formatString="#,###"/>

  </Cube>

</Schema>

So when I load the cube in Jpivot the response time is about 15 seconds and when I run it in pivot4j-analytics-0.9 it takes about 40 seconds.

And if I try to run another mdx that takes 40 seconds in Jpivot this same mdx would take about 20 minutes in pivot4j-analytics-0.9.

There is some configuration that you have to do to improve performance, because it is the same cube and in theory it should take the same response time because it is the same mdx, the same cube and the same database.

I would greatly appreciate your help.

[Xavier Cho]

First of all, I'm sorry I couldn't respond any sooner.

Unfortunately, I'm afraid the problem cannot be easily fixed at least for now. The problem is in design, rather than configuration,

so basically Pivot4J traded rendering performance for some of its advanced features (notably, renderer abstraction and expression

support, and more).

The overhead is cumulative, so the disadvantage becomes quite significant if you need to run such a MDX which takes 30 seconds

or so when you execute it in JPivot on Pivot4J.

If you are curious, you might want to look into TableHeaderNode and TableRenderer class, and see how Pivot4J construct in memory

header model from the source data.

I'm pretty sure I've left only a few stones unturned in terms of performance, because I already ran a profiler to analyze and optimize it

several times.

Sorry I can't be much help with this one.

Cheers,
Xavier

2017년 2월 14일 화요일 오전 1시 44분 30초 UTC+9, Javier Armando Montaño Vargas 님의 말: