Hello,

I have the following strange situation:
If I run the following query first time within a session, it's very slow
(more then 5 minutes):
(Excuses me for the big statement, the coal is to split the clients in 10
groups based on the value of a specific tuple)

When I execute this query for only 2 groups (so the same number of client,
but only splited in 2 groups), that runs fine (also the first time within a
session).

When I run within a session first this query for 2 groups and after that for
10 groups, the last one is also fast enough. The execution time of these 2
queries together is far less then 10 times the execution time of the query
for 10 groups first time in session. Very, very strange.

How can I optimize this query? I have already tried NECJ, but that's even
more bad.
Can somebody give me some light in the dark?

Thanks, Jaap

WITH SET [SetGroupClientMain1] AS
'CROSSJOIN(CROSSJOIN(GENERATE({[GroupClient].&[100].&[999999]},DESCENDANTS([
GroupClient].CurrentMember,[GroupClient].[Admin])),{[Time].&[12].&[2002/12/3
1]}),{[Summary].&[1]})'
SET [SetGroupClientMainOrder1] AS
'CROSSJOIN(ORDER(FILTER([SetGroupClientMain1],NOT
ISEMPTY([DataDict].&[100].&[300001])),[DataDict].&[100].&[300001],BASC),{[Da
taDict].&[100].&[300001]})'
SET [SetGroupClientRange1_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] <=
[SetGroupClientMainOrder1].Item(1*[SetGroupClientMainOrder1].Count/10-1)
)'
MEMBER [GroupClient].[SetGroupClientRange1_1] AS
'Aggregate([SetGroupClientRange1_1])'
SET [SetGroupClientRange2_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >
[SetGroupClientMainOrder1].Item(1*[SetGroupClientMainOrder1].Count/10-1)
AND
[DataDict].&[100].&[300001] <=
[SetGroupClientMainOrder1].Item(2*[SetGroupClientMainOrder1].Count/10-1)
)'
MEMBER [GroupClient].[SetGroupClientRange2_1] AS
'Aggregate([SetGroupClientRange2_1])'
SET [SetGroupClientRange3_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >
[SetGroupClientMainOrder1].Item(2*[SetGroupClientMainOrder1].Count/10-1)
AND
[DataDict].&[100].&[300001] <=
[SetGroupClientMainOrder1].Item(3*[SetGroupClientMainOrder1].Count/10-1)
)'
MEMBER [GroupClient].[SetGroupClientRange3_1] AS
'Aggregate([SetGroupClientRange3_1])'
SET [SetGroupClientRange4_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >
[SetGroupClientMainOrder1].Item(3*[SetGroupClientMainOrder1].Count/10-1)
AND
[DataDict].&[100].&[300001] <=
[SetGroupClientMainOrder1].Item(4*[SetGroupClientMainOrder1].Count/10-1)
)'
MEMBER [GroupClient].[SetGroupClientRange4_1] AS
'Aggregate([SetGroupClientRange4_1])'
SET [SetGroupClientRange5_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >
[SetGroupClientMainOrder1].Item(4*[SetGroupClientMainOrder1].Count/10-1)
AND
[DataDict].&[100].&[300001] <
[SetGroupClientMainOrder1].Item(5*[SetGroupClientMainOrder1].Count/10)
)'
MEMBER [GroupClient].[SetGroupClientRange5_1] AS
'Aggregate([SetGroupClientRange5_1])'
SET [SetGroupClientRange6_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >=
[SetGroupClientMainOrder1].Item(5*[SetGroupClientMainOrder1].Count/10)
AND
[DataDict].&[100].&[300001] <
[SetGroupClientMainOrder1].Item(6*[SetGroupClientMainOrder1].Count/10)
)'
MEMBER [GroupClient].[SetGroupClientRange6_1] AS
'Aggregate([SetGroupClientRange6_1])'
SET [SetGroupClientRange7_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >=
[SetGroupClientMainOrder1].Item(6*[SetGroupClientMainOrder1].Count/10)
AND
[DataDict].&[100].&[300001] <
[SetGroupClientMainOrder1].Item(7*[SetGroupClientMainOrder1].Count/10)
)'
MEMBER [GroupClient].[SetGroupClientRange7_1] AS
'Aggregate([SetGroupClientRange7_1])'
SET [SetGroupClientRange8_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >=
[SetGroupClientMainOrder1].Item(7*[SetGroupClientMainOrder1].Count/10)
AND
[DataDict].&[100].&[300001] <
[SetGroupClientMainOrder1].Item(8*[SetGroupClientMainOrder1].Count/10)
)'
MEMBER [GroupClient].[SetGroupClientRange8_1] AS
'Aggregate([SetGroupClientRange8_1])'
SET [SetGroupClientRange9_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >=
[SetGroupClientMainOrder1].Item(8*[SetGroupClientMainOrder1].Count/10)
AND
[DataDict].&[100].&[300001] <
[SetGroupClientMainOrder1].Item(9*[SetGroupClientMainOrder1].Count/10)
)'
MEMBER [GroupClient].[SetGroupClientRange9_1] AS
'Aggregate([SetGroupClientRange9_1])'
SET [SetGroupClientRange10_1] AS 'FILTER([SetGroupClientMain1],
[DataDict].&[100].&[300001] >=
[SetGroupClientMainOrder1].Item(9*[SetGroupClientMainOrder1].Count/10)
)'
MEMBER [GroupClient].[SetGroupClientRange10_1] AS
'Aggregate([SetGroupClientRange10_1])'
MEMBER [DataDict].[AdminCount] AS
'Sum({[DataDict].&[100].&[1]},[Measures].[ValCount])', SOLVE_ORDER = 10
MEMBER [Status].[CMStatusSlice] AS
'Aggregate({[Status].&[1],[Status].&[3]})'
SELECT
{[GroupClient].[SetGroupClientRange1_1],[GroupClient].[SetGroupClientRange2_
1],[GroupClient].[SetGroupClientRange3_1],[GroupClient].[SetGroupClientRange
4_1],[GroupClient].[SetGroupClientRange5_1],[GroupClient].[SetGroupClientRan
ge6_1],[GroupClient].[SetGroupClientRange7_1],[GroupClient].[SetGroupClientR
ange8_1],[GroupClient].[SetGroupClientRange9_1],[GroupClient].[SetGroupClien
tRange10_1]} ON COLUMNS,
{[DataDict].[AdminCount],[DataDict].&[100].&[0],[DataDict].&[100].&[300001],
[DataDict].&[100].&[300101],[DataDict].&[100].&[300151],[DataDict].&[100].&[
300152],[DataDict].&[100].&[300157],[DataDict].&[100].&[300158],[DataDict].&
[100].&[300160],[DataDict].&[100].&[300162],[DataDict].&[100].&[300163],[Dat
aDict].&[100].&[300166],[DataDict].&[100].&[300167],[DataDict].&[100].&[3001
68]} ON ROWS FROM [Benchmark] WHERE
([Measures].[ValAvg],[Status].[CMStatusSlice])

Hm, but the strange thing is that when I run first the query for 2 sets and
after that for 10 sets the total execution time of this 2 queries is far
less then when I run that query for 10 sets in a new session. Sounds to me
indeed as flaw or bug in PTS.
Can perhaps somebody of Microsoft say something on it?

Jaap

"Lutz Morrien" <nospam.lutz.morrien (AT) ocb (DOT) com> schreef in bericht
news:096401c3632f$3d75aa00$a601280a (AT) phx (DOT) gbl...

I wouldn't call it a bug. Just the way query optimizer uses its heuristics.
Based on the current cache content etc, it chooses better execution plan in
the second case then in the first case.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
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.
==================================================
"Jaap Mosselman" <j.mosselman-no-s-p-a-m- (AT) agrovision (DOT) nl> wrote

Ok, perhaps not a bug, but is there a method to prevent that extremely large
execution times?
These are not acceptable for (my) end-users.
The end-users can't create MDX by themselves. They can create reports in my
application and I use MDX for calculating that reports. So I know if it is a
query with many groups or not.
Would there be something to say about when to run first the query with a
small number of groups and after that the big query?

Jaap

"Mosha Pasumansky [MS]" <moshap (AT) microsoft (DOT) com> schreef in bericht
news:3f41a308$1 (AT) news (DOT) microsoft.com...

Maybe you could apply some of the techniques described
in "Warm the Query Results Cache When Possible". This is a
section of the "Analysis Services Performance Guide",
which you can read at :

http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/optimize/ANSvcsPG.asp

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

Unfortunatelly there is no generic answer. Your case should be investigated
in order to give a specific advice.
Sometimes query can be rewritten (looks like you already tried it - no
avail), other times connection string parameters can be tuned etc.
You will need to open a case with PSS if you want to pursue this route.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
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.
==================================================

I'll read it.

Thanks,

Jaap

"Brian Altmann" <brianaltmann (AT) yahoo (DOT) com> schreef in bericht
news:012a01c36679$4547df80$a401280a (AT) phx (DOT) gbl...