locked
Query is running slow RRS feed

  • Question

  • Hi,

    The following query is running very slow :

    select


    non empty {[Measures].[Sales]} on columns,
    non empty {(
    [Dimension Agents].[Agent Number].
    ALLMEMBERS *
    [Dimension Customers].[Full Name].
    ALLMEMBERS
    )} ON ROWS FROM Sales
    WHERE ([Dimension Time].[Year].&[2009], [Dimension Customer].[Place].&[Some place])

    What you expect is that is finds all customers who live in a certain place (and had sales for this year). and then look up their full names and their agents.

    Some facts :
    Sales facts contains 120 million rows, customers contains 1 million rows, agents contains 10.000 rows, time contains 1500 rows (5 years)
    Both 3 dimensions (time, customers and agents) are related to the sales fact. Also note that an customer can have one or more agents.
    We are using SQL 2005/SSAS 2005

    If I comment out dimension Agents or dimension customers I get an response in 2 seconds. This query is running more then 33 minutes.

    Any ideas for improvement ?

    Constantijn

    Wednesday, August 19, 2009 9:42 AM

Answers

  • Hi Constantijn,
    Please try the following query

    select nonempty ([Measures].[Sales])  on columns,
    nonempty
    ({[Dimension Agents].[Agent Number].[Agent Number].MEMBERS }*
    {[Dimension Customers].[Full Name].[Full Name].
    MEMBERS}) ON ROWS FROM Sales
    WHERE ([Dimension Time].[Year].&[2009], [Dimension Customer].[Place].&[Some place])

    Wednesday, August 19, 2009 10:27 AM
  • Have you tried running a profiler trace?

    The performance of your query is dependent on good cube design. With a 120 million row fact table you should be making use of partitions and agfgregations. You should also make sure that you have attribute relationships set up correctly in the dimension so that the storage engine can make use of any aggregations you may have defined.

    There should be noneed to move the Pklace attribute fromt he WHERE to the filter. This is becuase the attribute is from the same dimension and analysis services should use auto-exists to filter the Full Name attribute automatically.

    Optimizing an MDX query is not always as simple as just looking at the query.


    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)
    Wednesday, August 19, 2009 12:01 PM
  • Aggregations and partitions were setup correctly.

    Can you please elaborate on this? What do you mean by correctly? What are you partitioning by? How many partitions do you have? What is the size of your partitions? Just becuase you have partitions and aggregations doesn't mean they are are useful for this query.

    What do you see in the profiler? Do aggregations get hit?

    I would bet that if you created an aggregation that included {Agent, Place, Year} or {Agent, Customer, Year} this query would run faster. If you used the aggregation design wizard then try creating these aggregations manually.

    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)
    Wednesday, August 19, 2009 2:28 PM
  • SELECT 
    {[Measures].[Sales]} ON 0, 
    {
    NonEmpty(
    	Exists(
    		[Dimension Customers].[Full Name].ALLMEMBERS,
    		[Dimension Customer].[Place].&[Some place])
    	*
    	[Dimension Agents].[Agent Number].ALLMEMBERS,
    	([Measures].[Sales], [Dimension Time].[Year].&[2009])
    )} ON ROWS 
    FROM Sales
    greets,
    gerhard

    - www.pmOne.com -
    Thursday, August 20, 2009 8:23 AM
    Answerer

All replies

  • Hi Constantijn,
    Please try the following query

    select nonempty ([Measures].[Sales])  on columns,
    nonempty
    ({[Dimension Agents].[Agent Number].[Agent Number].MEMBERS }*
    {[Dimension Customers].[Full Name].[Full Name].
    MEMBERS}) ON ROWS FROM Sales
    WHERE ([Dimension Time].[Year].&[2009], [Dimension Customer].[Place].&[Some place])

    Wednesday, August 19, 2009 10:27 AM
  • Thanks but it doesn't work very well. My old querytook 1hour en 23 minutes to run and return 11000 rows , this one did it in 57 minutes and returned 0 rows.

    The only thing I can think of now is moving the dimension customer from where to the select and then use a filter.

    select nonempty ([Measures].[Sales])  on columns,
    nonempty
    (
    {[Dimension Agents].[Agent Number].[Agent Number].
    MEMBERS
    }*
    FILTER ( [Dimension Customers].[Full Name].[Full Name].
    MEMBERS, [Dimension Customer].[Place].&[Some place])
    )
    ON ROWS FROM
    Sales
    WHERE ([Dimension Time].[Year].&[2009] )



    Wednesday, August 19, 2009 11:50 AM
  • Have you tried running a profiler trace?

    The performance of your query is dependent on good cube design. With a 120 million row fact table you should be making use of partitions and agfgregations. You should also make sure that you have attribute relationships set up correctly in the dimension so that the storage engine can make use of any aggregations you may have defined.

    There should be noneed to move the Pklace attribute fromt he WHERE to the filter. This is becuase the attribute is from the same dimension and analysis services should use auto-exists to filter the Full Name attribute automatically.

    Optimizing an MDX query is not always as simple as just looking at the query.


    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)
    Wednesday, August 19, 2009 12:01 PM
  • Aggregations and partitions were setup correctly. Attribute relationships weren't set up because the data is not very clean.
    Customer Dimension looks like
    CustomerID, full name, street, housenumber, place, postal code

    The only attribute relationship I can think of postal code and street + housenumber + place.
    Place and street not always written the same.

    Wednesday, August 19, 2009 2:17 PM
  • Aggregations and partitions were setup correctly.

    Can you please elaborate on this? What do you mean by correctly? What are you partitioning by? How many partitions do you have? What is the size of your partitions? Just becuase you have partitions and aggregations doesn't mean they are are useful for this query.

    What do you see in the profiler? Do aggregations get hit?

    I would bet that if you created an aggregation that included {Agent, Place, Year} or {Agent, Customer, Year} this query would run faster. If you used the aggregation design wizard then try creating these aggregations manually.

    Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)
    Wednesday, August 19, 2009 2:28 PM
  • SELECT 
    {[Measures].[Sales]} ON 0, 
    {
    NonEmpty(
    	Exists(
    		[Dimension Customers].[Full Name].ALLMEMBERS,
    		[Dimension Customer].[Place].&[Some place])
    	*
    	[Dimension Agents].[Agent Number].ALLMEMBERS,
    	([Measures].[Sales], [Dimension Time].[Year].&[2009])
    )} ON ROWS 
    FROM Sales
    greets,
    gerhard

    - www.pmOne.com -
    Thursday, August 20, 2009 8:23 AM
    Answerer