<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16414" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=737450500-08052007><FONT face=Verdana
color=#000080 size=2>Bart,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=737450500-08052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=737450500-08052007><FONT face=Verdana
color=#000080 size=2>Try turning on SQL tracing. See if one particular statement
is taking a lot of time, or if one statement is being executed
repeatedly.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=737450500-08052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=737450500-08052007><FONT face=Verdana
color=#000080 size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=737450500-08052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=737450500-08052007><FONT face=Verdana
color=#000080 size=2>Julian</FONT></SPAN></DIV><BR>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000080 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> mondrian-bounces@pentaho.org
[mailto:mondrian-bounces@pentaho.org] <B>On Behalf Of </B>Pappyn
Bart<BR><B>Sent:</B> Friday, May 04, 2007 2:33 AM<BR><B>To:</B> Mondrian
developer mailing list<BR><B>Subject:</B> [Mondrian] Non empty optimizer
performance regression<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2>Hi,</FONT></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>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).</FONT></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>I tried to
simulate the problem with the foodmart cube, but I am not successful (the
foodmart schema is too simple, I guess).</FONT></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>Even a simple
crossjoin with two all-level sets takes a very long time, so the size of
dimensions does not matter. </FONT></SPAN><SPAN class=093331310-03052007><SPAN
class=093331310-03052007><FONT face=Arial size=2>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.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2>Mondrian seems to get stuck forever in the first
CrossJoinFunDef.nonEmptyListNEW(), it only leaves after a very long
time. </FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2>--> 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. </FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2>If I could understand the pitfalls of this function a bit
better, I might be able to pinpoint (and solve) the
problem.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><SPAN class=093331310-03052007><FONT
face=Arial size=2>Where is the query that takes a long time, translated to
foodmart data (just an example, since it runs fast on the foodmart
schema) :</FONT></SPAN></DIV></SPAN>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>WITH MEMBER
[Measures].[Availability] as 'iif([Measures].[Warehouse Sales] > 0,
[Measures].[Warehouse Profit] / [Measures].[Warehouse Sales], null)'<BR>MEMBER
[Measures].[Performance] as 'iif([Measures].[Store Sales] > 0,
[Measures].[Profit] / [Measures].[Store Sales], null)'<BR>MEMBER
[Measures].[Quality] as 'iif([Measures].[Units Ordered] > 0,
[Measures].[Units Shipped] / [Measures].[Units Ordered],
null)'</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>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))'</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>MEMBER
[Measures].[Availability Loss tmp] as 'iif([Measures].[Availability] > 0,
1.0 - [Measures].[Availability], 0)'<BR>MEMBER [Measures].[Performance Loss
tmp] as 'iif([Measures].[Performance] > 0, 1.0 - [Measures].[Performance],
0)'<BR>MEMBER [Measures].[Quality Loss tmp] as 'iif([Measures].[Quality] >
0, 1.0 - [Measures].[Quality], 0)'<BR>MEMBER [Measures].[Sum loss tmp] as
'[Measures].[Availability loss tmp] + [Measures].[Performance loss tmp] +
[Measures].[Quality loss tmp]'<BR>MEMBER [Measures].[Overall eff loss tmp] as
'iif([Measures].[Overall eff] < 1.0, 1.0 - [Measures].[Overall eff],
0.0)'</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>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))'</FONT></SPAN></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial size=2>SELECT NON EMPTY
{[Measures].[Availability loss]} ON COLUMNS,<BR>NON EMPTY CrossJoin(
{[Promotion Media].[All Media]}, {[Product].[All Products]} ) ON ROWS<BR>FROM
[Warehouse and Sales]</FONT></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2>Thanks,</FONT></SPAN></DIV>
<DIV><SPAN class=093331310-03052007><FONT face=Arial
size=2>Bart</FONT></SPAN></DIV></DIV>
<DIV> </DIV></BLOCKQUOTE></BODY></HTML>