Why is the results take longer in MDX than TSQL?

  • Can anyone tell me why this simple MDX query takes 9 seconds and TSQL takes 5 seconds?

    -------------------- MDX -------------------------------

    select null on 0,

    {

    [Dim Geographic].[STATECODE].children *

    [Dim Geographic].[COUNTYNAME].children *

    [Dim Geographic].[CITY].children *

    [Dim Geographic].[POSTALCODE].children

    }

    on 1 from Cube;

    ------------------TSQL----------------------------------

    select distinct STATEABBRV, COUNTYNAME, CITY, POSTALCODE from DimGeographic

    order by STATEABBRV, COUNTYNAME, CITY, POSTALCODE

  • Hard to tell without knowing the exact structures of the database and cube, but I do know SSAS is strong in showing aggregated data, not really in showing data dumps.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Did the T-sql take 5 seconds on the first execution? If not , then results may still be in the memory cache. The MDX may not have that advantage.

  • blom0344 (1/16/2013)


    Did the T-sql take 5 seconds on the first execution? If not , then results may still be in the memory cache. The MDX may not have that advantage.

    SSAS holds caches for MDX as well.

    Clear the Analysis Services Caches

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply