Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query takes over one minute

Answered Query takes over one minute

  • Monday, December 31, 2012 6:36 AM
     
      Has Code
    Sign In to Vote
    0

    Hi all,

    The below query is being generated by a product we use for reporting and it is taking over 1 minute to execute. Is there any room for imrpovement for this query? Please advise and thanks in advance!

    SELECT { [Time Perspective].[Default].&[YTD] } * { [Versions].[Default].&[2012REFCST], [Versions].[Default].&[2012BUD], 
[Versions].[Default].&[2012REFCSTvBUD] } DIMENSION PROPERTIES MEMBER_KEY, MEMBER_TYPE, PARENT_UNIQUE_NAME ON COLUMNS,
{ [Accounts].[Default].&[N01], [Accounts].[Default].&[H-40], [Accounts].[Default].&[IS-40].Children, [Accounts].[Default].&[IS-40], 
[Accounts].[Default].&[ASP], [Accounts].[Default].&[.], [Accounts].[Default].&[IS-5], [Accounts].[Default].&[IS-45], 
[Accounts].[Default].&[G01], [Accounts].[Default].&[.], [Accounts].[Default].&[H-6], [Accounts].[Default].&[IS-60].Children, 
[Accounts].[Default].&[IS-60], [Accounts].[Default].&[IS-6], [Accounts].[Default].&[H-70], [Accounts].[Default].&[IS-70].Children, [Accounts].[Default].&[IS-70],
[Accounts].[Default].&[IS-7], [Accounts].[Default].&[H-80], [Accounts].[Default].&[IS-80].Children, [Accounts].[Default].&[IS-80], [Accounts].[Default].&[IS-4], 
[Accounts].[Default].&[.], [Accounts].[Default].&[CAPEX], [Accounts].[Default].&[N03] } 
DIMENSION PROPERTIES MEMBER_KEY, MEMBER_TYPE, PARENT_UNIQUE_NAME, [Accounts].[Default].[NumericFormat] ON ROWS 
FROM [Finance_Cube] 
WHERE ([Time].[Default].&[2012], [Entity].[Default].&[HWI], [Cost Center].[Default].&[TCC], [Projects].[Default].&[TP], 
[Reporting Currency].[Default].&[USD], [Measures].[Value]) CELL PROPERTIES VALUE

     

All Replies

  • Monday, December 31, 2012 9:41 AM
     
     Answered
    Sign In to Vote
    1

    Hi ,

    You use many measures, and each one is generated for all the members for { [Time Perspective].[Default].&[YTD] } * { [Versions].[Default].&[2012REFCST], [Versions].[Default].&[2012BUD],
    [Versions].[Default].&[2012REFCSTvBUD] } .

    Can you reduce the members in the sets ? Anyway, use the NonEmpty function :

    NonEmpty ({ [Time Perspective].[Default].&[YTD] } * { [Versions].[Default].&[2012REFCST], [Versions].[Default].&[2012BUD],
    [Versions].[Default].&[2012REFCSTvBUD] })        So you'll generate less rows ..

    Regards, David .

     
  • Tuesday, January 01, 2013 6:11 AM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Hi SnowShine,

    You can also take a look at the following article:
    Analysis Services Query Performance Top 10 Best Practices: http://sqlcat.com/sqlcat/b/top10lists/archive/2007/09/13/analysis-services-query-performance-top-10-best-practices.aspx

    For the performance issue, we can aslo use SQL Profiler to debug Multidimensional Expressions (MDX) statements and identify MDX statements that run slowly. For more information, please see:
    http://technet.microsoft.com/en-us/library/ms174946.aspx

    Regards,

    Elvis Long
    TechNet Community Support

     
  • Thursday, January 03, 2013 8:36 AM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    You can also give a quick try by using "Non Empty" key word. Kindly note it's different than "NonEmpty" function. So if your cube is quiet sparse, following MDX might run quiet faster for you -

    SELECT NON EMPTY { [Time Perspective].[Default].&[YTD] } *
{ [Versions].[Default].&[2012REFCST], [Versions].[Default].&[2012BUD], [Versions].[Default].&[2012REFCSTvBUD] } Dimension Properties MEMBER_KEY,
MEMBER_TYPE,
PARENT_UNIQUE_NAME ON COLUMNS,
NON EMPTY
{ [Accounts].[Default].&[N01], [Accounts].[Default].&[H-40], [Accounts].[Default].&[IS-40].Children, [Accounts].[Default].&[IS-40], [Accounts].[Default].&[ASP], [Accounts].[Default].&[.], [Accounts].[Default].&[IS-5], [Accounts].[Default].&[IS-45], [Accounts].[Default].&[G01], [Accounts].[Default].&[.], [Accounts].[Default].&[H-6], [Accounts].[Default].&[IS-60].Children, [Accounts].[Default].&[IS-60], [Accounts].[Default].&[IS-6], [Accounts].[Default].&[H-70], [Accounts].[Default].&[IS-70].Children, [Accounts].[Default].&[IS-70], [Accounts].[Default].&[IS-7], [Accounts].[Default].&[H-80], [Accounts].[Default].&[IS-80].Children, [Accounts].[Default].&[IS-80], [Accounts].[Default].&[IS-4], [Accounts].[Default].&[.], [Accounts].[Default].&[CAPEX], [Accounts].[Default].&[N03] } Dimension Properties MEMBER_KEY,
MEMBER_TYPE,
PARENT_UNIQUE_NAME,
[Accounts].[Default].[NumericFormat] ON ROWS
FROM [Finance_Cube]
WHERE ( [Time].[Default].&[2012], [Entity].[Default].&[HWI], [Cost Center].[Default].&[TCC], [Projects].[Default].&[TP], [Reporting Currency].[Default].&[USD], [Measures].[Value] ) CELL Properties Value

    HTH

    Regards,
    Santosh

    http://microsoftbizintel.wordpress.com/