[Mondrian] Non empty optimizer performance regression

Julian Hyde julianhyde at speakeasy.net
Mon May 7 20:09:31 EDT 2007


Bart,
 
Try turning on SQL tracing. See if one particular statement is taking a
lot of time, or if one statement is being executed repeatedly.
 
I once saw a problem with non-empty where a sub-expression was [A
member].Children and this sub-expression was being executed repeatedly
to find the non-empty children of [A member] in each context, and each
execution required the execution of a SQL statement. It would have been
much cheaper to execute the query once, return slightly too many
members, and cache the result, rather than executing SQL multiple times.
I don't know whether your problem is related to that.
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
On Behalf Of Pappyn Bart
Sent: Friday, May 04, 2007 2:33 AM
To: Mondrian developer mailing list
Subject: [Mondrian] Non empty optimizer performance regression


Hi,
 
Since the new non empty optimizer code, a few queries run very slow in
my application (for 1 sec (before mondrian 2.3.2) -> 5 minutes).
 
I tried to simulate the problem with the foodmart cube, but I am not
successful (the foodmart schema is too simple, I guess).
 
Even a simple crossjoin with two all-level sets takes a very long time,
so the size of dimensions does not matter. I have a very large number of
calculated members and many calculated members need calculations based
on many other calculated members (deeply nested).  The same query runs
very fast (< 1 sec) without NON empty.
 
Mondrian seems to get stuck forever in the first
CrossJoinFunDef.nonEmptyListNEW(), it only leaves after a very long
time.  
 
 
--> Can someone please explain to me or hint me why this function could
be slow ?  Is it due to the fact that I have large number of calculated
members?  I looked at the code, ran it through the debugger, but I don't
understand what the function is exactly doing and why this takes such a
long time.  
 
If I could understand the pitfalls of this function a bit better, I
might be able to pinpoint (and solve) the problem.
 
 
Where is the query that takes a long time, translated to foodmart data
(just an example, since it runs fast on the foodmart schema) :
 
WITH MEMBER [Measures].[Availability] as 'iif([Measures].[Warehouse
Sales] > 0, [Measures].[Warehouse Profit] / [Measures].[Warehouse
Sales], null)'
MEMBER [Measures].[Performance] as 'iif([Measures].[Store Sales] > 0,
[Measures].[Profit] / [Measures].[Store Sales], null)'
MEMBER [Measures].[Quality] as 'iif([Measures].[Units Ordered] > 0,
[Measures].[Units Shipped] / [Measures].[Units Ordered], null)'
 
MEMBER [Measures].[Overall eff] as
'iif(IsEmpty([Measures].[Availability]) or
IsEmpty([Measures].[Performance]) or IsEmpty([Measures].[Quality]),
null, cast([Measures].[Availability] as NUMERIC) *
cast([Measures].[Performance] as NUMERIC) * cast([Measures].[Quality] as
NUMERIC))'
 
MEMBER [Measures].[Availability Loss tmp] as
'iif([Measures].[Availability] > 0, 1.0 - [Measures].[Availability], 0)'
MEMBER [Measures].[Performance Loss tmp] as
'iif([Measures].[Performance] > 0, 1.0 - [Measures].[Performance], 0)'
MEMBER [Measures].[Quality Loss tmp] as 'iif([Measures].[Quality] > 0,
1.0 - [Measures].[Quality], 0)'
MEMBER [Measures].[Sum loss tmp] as '[Measures].[Availability loss tmp]
+ [Measures].[Performance loss tmp] + [Measures].[Quality loss tmp]'
MEMBER [Measures].[Overall eff loss tmp] as 'iif([Measures].[Overall
eff] < 1.0, 1.0 - [Measures].[Overall eff], 0.0)'
 
MEMBER [Measures].[Availability loss] as
'iif(IsEmpty([Measures].[Overall eff]), null, iif([Measures].[Sum loss
tmp] > 0, [Measures].[Overall eff loss tmp] * ([Measures].[Availability
loss tmp] / [Measures].[Sum loss tmp]), 0))'
 
SELECT NON EMPTY {[Measures].[Availability loss]} ON COLUMNS,
NON EMPTY CrossJoin( {[Promotion Media].[All Media]}, {[Product].[All
Products]} ) ON ROWS
FROM [Warehouse and Sales]
 
Thanks,
Bart
 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070507/f5c03e09/attachment.html 


More information about the Mondrian mailing list