January 15, 2013 at 3:52 pm
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
January 16, 2013 at 1:06 am
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
January 16, 2013 at 8:37 am
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.
January 16, 2013 at 2:32 pm
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