Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why is the results take longer in MDX than TSQL? Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 3:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 12:41 PM
Points: 8, Visits: 93
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
Post #1407512
Posted Wednesday, January 16, 2013 1:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 12,965, Visits: 10,739
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1407649
Posted Wednesday, January 16, 2013 8:37 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 12:12 PM
Points: 72, Visits: 326
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.
Post #1407880
Posted Wednesday, January 16, 2013 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 12,965, Visits: 10,739
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1408082
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse