Simple Optimization advice needed

#1

Hi!
I would be thnkful for advice on how to optimize following code:
iif
(
Descendants(A,10, LEAVES).Count < 1,
0,
iif
(
Descendants(A,10, LEAVES).Item(0).Property("X")=CStr("123"),
0,
iif
(
Descendants(A,10,
LEAVES).Item(0).Property("Y")=CStr("768"),
1,
0
)
)
)

I do Descendants(A,10, LEAVES) three times. Is it possible to do it only
once ?

#2

can you use AND operator

iif(Descendants(...).Count <1 AND ..... AND ...., 0, 1) ??

"martinbx" <martinbx (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi!
I would be thnkful for advice on how to optimize following code:
iif
(
Descendants(A,10, LEAVES).Count < 1,
0,
iif
(
Descendants(A,10, LEAVES).Item(0).Property("X")=CStr("123"),
0,
iif
(
Descendants(A,10,
LEAVES).Item(0).Property("Y")=CStr("768"),
1,
0
)
)
)

I do Descendants(A,10, LEAVES) three times. Is it possible to do it only
once ?

#3

It will not help, because I still have to call Descendants(A,10, LEAVES) 3
times in a row to get EXACTLY the same information

"Joel Leong" wrote:

Quote:

can you use AND operator

iif(Descendants(...).Count <1 AND ..... AND ...., 0, 1) ??

"martinbx" <martinbx (AT) discussions (DOT) microsoft.com> wrote in message
news:38C40AFB-B07C-4F15-A773-946210987B2D (AT) microsoft (DOT) com...
Hi!
I would be thnkful for advice on how to optimize following code:
iif
(
Descendants(A,10, LEAVES).Count < 1,
0,
iif
(
Descendants(A,10, LEAVES).Item(0).Property("X")=CStr("123"),
0,
iif
(
Descendants(A,10,
LEAVES).Item(0).Property("Y")=CStr("768"),
1,
0
)
)
)

I do Descendants(A,10, LEAVES) three times. Is it possible to do it only
once ?

#4

You could try a Set Alias, as in this Foodmart query:

Quote:

With Member [Measures].[DescendTest] as

'iif({Descendants([Employees].CurrentMember, 4, LEAVES) as SetD}.Count <
1,
0,
iif(SetD.Item(0).Properties("Marital Status") = "S",
0,
iif(SetD.Item(0).Properties("Gender") = "F",
1, 0)))'
select {[Measures].[StoreCount], [Measures].[DescendTest]} on columns,
{[Employees].[All Employees].[Sheri Nowmer].[Donna Arnold]} on rows
from HR

Quote:

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

#5

Thanks.

"Deepak Puri" wrote:

Quote:

You could try a Set Alias, as in this Foodmart query:

With Member [Measures].[DescendTest] as
'iif({Descendants([Employees].CurrentMember, 4, LEAVES) as SetD}.Count
1,
0,
iif(SetD.Item(0).Properties("Marital Status") = "S",
0,
iif(SetD.Item(0).Properties("Gender") = "F",
1, 0)))'
select {[Measures].[StoreCount], [Measures].[DescendTest]} on columns,
{[Employees].[All Employees].[Sheri Nowmer].[Donna Arnold]} on rows
from HR

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Simple Optimization advice needed

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Simple Optimization advice needed - 08-30-2005 , 08:21 AM

Re: Simple Optimization advice needed - 08-30-2005 , 11:43 AM

Re: Simple Optimization advice needed - 08-30-2005 , 12:54 PM

Re: Simple Optimization advice needed - 08-31-2005 , 12:07 AM

Re: Simple Optimization advice needed - 08-31-2005 , 01:07 PM