none
[MDX gurus] Different syntax, which is best? RRS feed

  • 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
    Friday, February 26, 2010 2:34 PM

Answers

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

    Chris

    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Tuesday, March 2, 2010 10:20 AM
    Moderator
  • 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
    Monday, March 1, 2010 7:48 AM
    Moderator
  • 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
    Monday, March 1, 2010 12:15 PM
    Moderator
  • 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 -
    Monday, March 1, 2010 9:19 PM
    Answerer

All replies

  • Better in terms of what ?  Aesthetics ?  Performance ?
    Friday, February 26, 2010 2:43 PM
  • 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
    Friday, February 26, 2010 2:52 PM
    Moderator
  • 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
    Friday, February 26, 2010 3:01 PM
  • 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
    Friday, February 26, 2010 3:02 PM
  • 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
    Friday, February 26, 2010 3:18 PM
    Moderator
  • Hi, Thomas

    There's no other way to analyze MDX performance?  Nothing in SSMS ?
    Friday, February 26, 2010 8:45 PM
  • 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
    Saturday, February 27, 2010 12:27 AM
  • 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
    Monday, March 1, 2010 7:48 AM
    Moderator
  • 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
    Monday, March 1, 2010 12:15 PM
    Moderator
  • 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
    Monday, March 1, 2010 1:52 PM
  • 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 -
    Monday, March 1, 2010 9:19 PM
    Answerer
  • Gerhard, you might be thinking of the impact subselects have on the Formula Engine cache:

    Chris

    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Tuesday, March 2, 2010 10:20 AM
    Moderator
  • yes, thats what i was thinking of
    - www.pmOne.com -
    Wednesday, March 3, 2010 8:00 AM
    Answerer
  • 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

    Friday, March 19, 2010 4:22 PM
  • 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/
    • Proposed as answer by krish007 Monday, March 22, 2010 11:46 AM
    Friday, March 19, 2010 11:26 PM
    Moderator
  • 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
    • Proposed as answer by krish007 Monday, March 22, 2010 11:46 AM
    Saturday, March 20, 2010 5:41 AM
    Moderator
  • Thanks

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, September 10, 2015 12:36 AM