Hi All,

Please explain the difference in execution duration for the following
2 logically equivalent queries to FoodMart 2000. The first query
takes 1 sec to execute at my site, the 2nd query takes 75 sec.

with member [Customers].[CM] as '[Customers].[Name].[Alice Cantrell]'
SELECT { [Customers].[CM] } ON COLUMNS ,
NON EMPTY { { [Store].[Store Name].Members } * {
[Product].[Product Name].Members } } ON ROWS
FROM [Sales]
where ([Measures].[Unit Sales])


with member [Customers].[CM] as '[Customers].[Name].[Amy
Petranoff].PrevMember'
SELECT { [Customers].[CM] } ON COLUMNS ,
NON EMPTY { { [Store].[Store Name].Members } * {
[Product].[Product Name].Members } } ON ROWS
FROM [Sales]
where ([Measures].[Unit Sales])

I replied to Andrew in sql.ru forum where he originally asked this question.
I am too surprised by such a big difference in performance, especially given
that the space described by the crossjoin on rows is not very big - only
37440 cells. For your reference - here is the thread with the discussion
http://www.sql.ru/forum/actualthread.aspx?tid=175595

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

Could this have something to do with how well NON EMPTY can be optimized
in the 2 cases (since the result set is sparse: only 20 non-empty values
out of 37,440)? This version of the second query, using
NonEmptyCrossJoin(), seems to be only slightly slower than the 1st
query:

'[Customers].[Name].[Amy Petranoff].PrevMember'
SELECT { [Customers].[CM] } ON COLUMNS ,
NonEmptyCrossJoin( [Store].[Store Name].Members,
[Product].[Product Name].Members,
{[Customers].[Name].[Amy Petranoff].PrevMember}, 2) ON ROWS
FROM [Sales]
where ([Measures].[Unit Sales])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

No - it cannot be it, since both queries use calculated member. The only
difference that I noted - was the same as Chris noted - static vs. dynamic
binding. But it cannot explain such a huge difference.
And even for the slowest iteration - it should not take 75 seconds to
iterate 37000 cells.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

OK - given that I have the luxury of being able to debug the code to find
out what's going on - I did exactly that. I discovered, that this is simply
a bug which affects performance of PrevMember in this scenario, degrading to
n^k algorithm where n is number of customers and k is number of tuples on
the ROWS axis. The bug is not fixed in SP4, but I verified with Yukon - that
Yukon chooses correct query plan and finishes this query instantly.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Mosha Pasumansky [MS]" <moshap (AT) online (DOT) microsoft.com> wrote

Mosha, thanks for clearing up the mystery! I for one would not have
guessed that the size of the Customers dimension (> 10 K members) was a
factor; though, now that you mentioned it, a query with Gender is much
faster:

SELECT { [Gender].[CM] } ON COLUMNS ,
NON EMPTY { { [Store].[Store Name].Members } * {
[Product].[Product Name].Members } } ON ROWS
FROM [Sales]
where ([Measures].[Unit Sales])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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