[MDX gurus] Different syntax, which is best?

Question

I have the following 2 queries which both return the same thing:

(1)

SELECT	[Measures].[Actual] ON COLUMNS
,		[Project].[Programme - Project].[Project] ON ROWS
FROM	(	SELECT	[Reporting Date].[Reporting Date].[Year].&[2009] ON COLUMNS
			FROM	[CostControl]
		)

(2)

SELECT	[Measures].[Actual] ON COLUMNS
,		[Project].[Programme - Project].[Project] ON ROWS
FROM	[CostControl]
where	[Reporting Date].[Reporting Date].[Year].&[2009]

Given that they both return the same thing, which is the "better" one to use (if any)?

thanks
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 1

Better in terms of what ?  Aesthetics ?  Performance ?

Answer 2

I would also be interested to know which one is better in performance - when there are 2 subqueries or just 2 in the where condition.

But I would expect the Where condition to be faster n better aesthetically too.

---

Cheers, Jason

Answer 3

Better in terms of what ?  Aesthetics ?  Performance ?

Well, that's my question to you!! :)

I suppose, performance. Is there any well-known difference between the two? If there were anything similar to a query execution plan for SSAS then I'd take a look at that but sadly there isn't one.

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 4

I would also be interested to know which one is better in performance - when there are 2 subqueries or just 2 in the where condition.

But I would expect the Where condition to be faster n better aesthetically too.

---

Cheers, Jason

Yep, I can see your point. Having said that I think the subquery approach could be more intuitive, especially to newcomers  (I'm sure we've all come across MDX newbies that think the WHERE clause does the same thing as it does in SQL).

I'm just interested in opinions really.

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 5

I am also pretty interested in this point because for years I have been routinely replacing the subqueries in default MDX query that SSRS generates with the MDX Query Designer with a where condition, without giving much of a thought how it actually works inside. I just "assumed" that the where condition is faster, had done some tiny tests too which seemed to validate this, but then thinking of it now, I wouldnt say they were done in proper controlled test environments.

Really looking forward to some views on this.

---

Cheers, Jason

Answer 6

Hi, Thomas

There's no other way to analyze MDX performance?  Nothing in SSMS ?

Answer 7

Try out a nano version of MDX Studio, but probably you might get some hints for your query.

http://www.codeplex.com/mdxscriptperf/

---

--Siddharth Mehta http://siddhumehta.blogspot.com

Answer 8

Hi Jamie,
Based on my observation in SQL Server Profiler, the second one with WHERE clause may produce better performance. There is only one difference for the trace, that is the first one produce two additional steps: Calculate Non Empty and Query Dimension.

You can run SQL Server Profiler to check this. You can check "Show all events"and check All events under Querying Events and All events under Query Processing.

I could not tell you how much difference that it will cause on performance because I do not have enough data to test. But based on this observation, I think that it deserves that you use the 2nd form with WHERE clause as a good practice in future. Also it is simple to understand as a slicer axis.

Here are some articles that you may like to read:

The Basic MDX Query (MDX)

http://msdn.microsoft.com/en-us/library/ms144785.aspx
SSAS 2008 Performance Guide
http://www.microsoft.com/downloads/details.aspx?FamilyID=3be0488d-e7aa-4078-a050-ae39912d2e43&displaylang=en

Best regards,
Charles Wang

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 9

Hi Jamie,

In the simple example you posted I would not expect much difference in performance.

I prefer 2) in terms of aethetics and I find it more readable. I also prefer using the WHERE clause as it will set the context. This is by design, but the side effect of not setting the context is that the .CurrentMember function does not work. This also affects methods that require an implied .CurrentMember reference like YTD(), PeriodsToDate() etc.

(1)
WITH member Measures.Test as [Reporting Date].[Reporting Date].CurrentMember.UniqueName
SELECT [Measures].[Test] ON COLUMNS
, [Project].[Programme - Project].[Project] ON ROWS
FROM ( SELECT [Reporting Date].[Reporting Date].[Year].&[2009] ON COLUMNS
FROM [CostControl]
)


(2)
WITH member Measures.Test as [Reporting Date].[Reporting Date].CurrentMember.UniqueName
SELECT [Measures].[Test] ON COLUMNS
, [Project].[Programme - Project].[Project] ON ROWS
FROM [CostControl]
where [Reporting Date].[Reporting Date].[Year].&[2009]

So I don't believe that they should be used interchangeably and prefer to use WHERE clauses in most of my hand written MDX.

However it is my understanding that when you get to multiple nested subselects they do have a slight performance advantage. Which is why you will see SSRS use both sub-selects and the WHERE clause. This way they get both the performance gain and get the context set, but it makes the query a real mess aesthetically.

You can also use subselects to get at the data differently than you can with the WHERE clause
http://geekswithblogs.net/darrengosbell/archive/2009/01/28/mdx-equivalent-of-a-filtered-group-by-in-sql.aspx

---

http://geekswithblogs.net/darrengosbell - please mark correct answers

Answer 10

There you go, Mr Gosbell comes through with a hard and fast reason to use one and not the other.

Thanks all for the replies. Really useful thread - I love learning the ins and outs of MDX - makes me feel like a rocket scientist y'know :)

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 11

Hi,

an other important aspect about performance is caching
if you use a subcube, only the query-cache is filled which cannot be used by other users and/or other sessions and not even for the same session

there was a similar thread some time ago that had the same topic, but i cannot find it anymore

greets,
gerhard

---

- www.pmOne.com -

Answer 12

Gerhard, you might be thinking of the impact subselects have on the Formula Engine cache:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!3057.entry

Chris

---

Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/

Answer 13

yes, thats what i was thinking of

---

- www.pmOne.com -

Answer 14

Hi Chris,

I have always used the WHERE clause, assuming that the WHERE clause is faster than the subselect statement untill I came across this interesting post.

http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/c1fe120b-256c-425e-93a5-24278b2ab1f3

This saved me a couple of times as the results were different using WHERE compared to Subselect. But, is there any way to determine which to use in which context or is the only way to use both, compare results and use the one that gives the better performance.

Thanks

Answer 15

As I said in my post on that thread, sometimes subselects will perform better, sometimes the Where clause will perform better - but no, I don't know of any way of knowing which one will perform best for any particular query or calculation, so I usually start off by using the Where clause and then, if performance is bad, check if a subselect performs better.

Chris

---

Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/

Answer 16

Sub-selects and the WHERE clause are slightly different. If you are dealing with "raw" measures, they can often be used interchangeably, but with calculated measures sub-selects can cause issues as they do not set context, so things like .currentmember do not work as expected. Sub-selects also have a similar effect as visual totals on the all members of dimensions involved.

I have an example on my blog of something that you can do with sub-selects that you can't do with the where clause http://geekswithblogs.net/darrengosbell/archive/2009/01/28/mdx-equivalent-of-a-filtered-group-by-in-sql.aspx

---

http://geekswithblogs.net/darrengosbell - please mark correct answers