none
MDX - trying to understand what causing difference in the perfomance

    Question

  • Hi,

    I am trying to understand why performance of the following queries is so different, probably there is something I'm overlooking.

    The query structure looks like

    Select {some measures} on 0,
    
    SetWeeks * SetStores * SetProducts on 1
    
    from cube

    Sets are just a group of products or weeks or stores.

    The interesting thing I'm noticing is when my set of products looks like

    UNION(ASCENDANTS([Product].[Product Hierarchy].[Product].&[111]),ASCENDANTS([Product].[Product Hierarchy].[Product].&[222]))

    the query is much slower (on cold cache and warm cache) is when I execute queries separately for each level of product hierarchy.

    What can be the reason for this?


    Friday, February 21, 2014 9:12 AM

Answers

  • Hi Puhh,

    According to your description, the performance is much better when execute queries separately for each level of product hierarchy than that using "UNION(ASCENDANTS([Product].[Product Hierarchy].[Product].&[111]),ASCENDANTS([Product].[Product Hierarchy].[Product].&[222]))".

    As per my understanding, the issue can be related to CrossJoin function. When using this query, you need calculated the all of the ancestors of a member from the member and then union two sets under CrossJoin function.

    If you cross-join medium-sized or large-sized sets (e.g., sets that contain more than 100 items each), you can end up with a result set that contains many thousands of items—enough to seriously impair performance. For the detail information, please see:
    http://sqlmag.com/data-access/cross-join-performance
    http://www.sql-server-performance.com/2004/optimizing-crossjoin/

    Hope this helps.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Monday, February 24, 2014 7:56 AM