PDA

View Full Version : JPivot/Mondrian Speed performance...slow!



coolguy982
06-25-2003, 11:39 AM
We are running Jpivot/Mondrian displaying 8 columns and one measure from an Oracle 8 view of 10,000+ rows. The performance of the pivot table is very slow at times, especially when drilling down. It can take nearly 10 minutes to create the table and one minute to drill down one level.
Can you please give me a run down of how mondrian/jpivot works when a drill-down button is clicked? Does it re-run the queries or pull from the cache? Also, are you aware of any processes we could try to increase performance?
Thanks in advance.

jhyde
06-26-2003, 08:36 PM
The best way to find out what's going on is to run with tracing. Set
mondrian.trace.level=2
in mondrian.properties, and you'll see SQL statements & their timings. In general, Mondrian only issues a query once, then uses cached aggregations; likewise, it caches members & their children.
See the FAQ [http://apoptosis.dyndns.org:8080/open/mondrian/doc/index.html] for more info.

coolguy982
06-30-2003, 09:37 AM
How does mondrian/jpivot handle caching? Does it cache just the data or also the objects? We are using it to display multiple pivot tables from an Oracle db via Tomcat. Users are authenticated before being able to see the tables. Once a table has been viewed once, I can go to it soon afterwards and it will only run the SQL statement for the sum......it WON'T re-create the table by counting and grabbing all the distinct columns/rows. This is what we would like it to do all the time. However, sometimes I will click on a previously visited table, and it WILL spend the time to recreate itself rather than just grab the new measure. Can you explain this situation to me? Is there a limit (either size-wise or time-wise) that Mondrian holds previously built pivot tables? And is there a way where I can cause mondrian to create a table only once and just figure the new measures thereafter? Thank you.

jhyde
06-30-2003, 06:10 PM
Mondrian caches both data and catalog objects. Catalog objects other than members are loaded permanently (or at least until the last connection disconnects, and they are garbage collected).
Members and aggregated values are stored in a cache. The cache retention policy weighs cost against benefit: groups of members or cells which take up a lot of memory are more likely to be flushed, groups which are accessed frequently and/or are difficult to re-create are more likely to be retained.
In your case, it sounds like less recent results are being flushed from the cache. Increasing the size of the cache ought to help.
The main parameter which controls the size of the cache is mondrian.rolap.CachePool.costLimit. Set it higher than the default (10,000), and you should see more cache hits, at the expense of a larger memory footprint. If you push it too high, you may get an OutOfMemoryException.
Tracing should give you a good idea of cache activity. mondrian.trace.level=1 prints SQL statements, 2 prints other cache activity such as items being flushed from the cache. You can set mondrian.rolap.RolapResult.printCacheables=1 to get even more information.
Make sure that mondrian.rolap.RolapResult.flushAfterEachQuery is not set to 1; otherwise the cache will be almost useless. (This parameter exists for testing purposes.)

coolguy982
07-02-2003, 06:06 AM
We have turned on the tracing and narrowed the speed problem down to the runtime performance of the views. After optimizing all of them, we are still experiencing some slowness. One idea we had was to calculate measures out of the values in memory rather than gathering them from the Oracle view. Is there any way to do this.....make the data be a snapshot rather than recalculating the totals with each drill down?

jhyde
07-02-2003, 06:17 PM
I'd like Mondrian to be able to roll-up aggregated values: if it knew the sales for California in Q1 ... Q4 1997, and it knew that 1997 has only 4 quarters, then it could calculate the sales for California in 1997.
Unfortunately that is a difficult feature to implement, and it's not that useful for the common use case of starting at the top and drilling down.
Have you considered using materialized views? If you create a materialized view for the lower level query, Oracle will returns the results immediately. And Oracle should be able to roll up the results to produce the higher level aggregations, too.