2

I have a MDX query that executes faster on cold cache than warm cache.

If I ran the same MDX query after restarting SSAS it takes 14 seconds. All subsequent runs of the same statement then take 50 seconds.

There is no load on the server, and al lot of RAM available and default SSAS memory settings.

I am out of ideas as to why this would be the case?

MDX traced statement from SSRS Report:

SELECT 
  NON EMPTY 
    {
      [Measures].[Total GP %]
     ,[Measures].[Trading GP %]
     ,[Measures].[Closing Stock Value]
     ,[Measures].[Opening Stock Value]
     ,[Measures].[Trading GP]
     ,[Measures].[Total GP]
     ,[Measures].[Purchases Value]
     ,[Measures].[Shortage Value]
     ,[Measures].[Adjustments Value]
     ,[Measures].[Wastage Value]
     ,[Measures].[Sales Value]
    } ON COLUMNS
 ,NON EMPTY 
    {
        [Item].[Item Code].[Item Code].ALLMEMBERS
      * 
        [Item].[Item Description].[Item Description].ALLMEMBERS
    }
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM 
(
  SELECT 
    Filter
    (
      [Item].[Item Name].[Item Name].ALLMEMBERS
     ,
        Instr
        (
          [Item].[Item Name].CurrentMember.Properties('Member_Caption'),@ItemItemName
        )
      > 0
    ) ON COLUMNS
  FROM 
  (
    SELECT 
      StrToSet
      (@ItemGroupDescription2
       ,CONSTRAINED
      ) ON COLUMNS
    FROM 
    (
      SELECT 
        StrToSet
        (@ItemGroupDescription
         ,CONSTRAINED
        ) ON COLUMNS
      FROM 
      (
        SELECT 
          StrToSet
          (@BranchBranchName
           ,CONSTRAINED
          ) ON COLUMNS
        FROM 
        (
          SELECT 
            StrToSet
            (@BranchStoreCategory
             ,CONSTRAINED
            ) ON COLUMNS
          FROM 
          (
            SELECT 
              StrToSet
              (@BranchRegion
               ,CONSTRAINED
              ) ON COLUMNS
            FROM 
            (
              SELECT 
                StrToSet
                (@BranchCountry
                 ,CONSTRAINED
                ) ON COLUMNS
              FROM 
              (
                SELECT 
                  StrToSet
                  (@PeriodPeriodWeek
                   ,CONSTRAINED
                  ) ON COLUMNS
                FROM 
                (
                  SELECT 
                    StrToSet
                    (@PeriodPeriodYear
                     ,CONSTRAINED
                    ) ON COLUMNS
                  FROM [DWH]
                )
              )
            )
          )
        )
      )
    )
  )
)
WHERE 
  (
    IIF
    (
      StrToSet(@PeriodPeriodYear,CONSTRAINED).Count = 1
     ,StrToSet
      (@PeriodPeriodYear
       ,CONSTRAINED
      )
     ,[Period].[Period Year].CurrentMember
    )
   ,IIF
    (
      StrToSet(@PeriodPeriodWeek,CONSTRAINED).Count = 1
     ,StrToSet
      (@PeriodPeriodWeek
       ,CONSTRAINED
      )
     ,[Period].[Period Week].CurrentMember
    )
   ,IIF
    (
      StrToSet(@BranchCountry,CONSTRAINED).Count = 1
     ,StrToSet
      (@BranchCountry
       ,CONSTRAINED
      )
     ,[Branch].[Country].CurrentMember
    )
   ,IIF
    (
      StrToSet(@BranchRegion,CONSTRAINED).Count = 1
     ,StrToSet
      (@BranchRegion
       ,CONSTRAINED
      )
     ,[Branch].[Region].CurrentMember
    )
   ,IIF
    (
      StrToSet(@BranchStoreCategory,CONSTRAINED).Count = 1
     ,StrToSet
      (@BranchStoreCategory
       ,CONSTRAINED
      )
     ,[Branch].[Store Category].CurrentMember
    )
   ,IIF
    (
      StrToSet(@BranchBranchName,CONSTRAINED).Count = 1
     ,StrToSet
      (@BranchBranchName
       ,CONSTRAINED
      )
     ,[Branch].[Branch Name].CurrentMember
    )
   ,IIF
    (
      StrToSet(@ItemGroupDescription,CONSTRAINED).Count = 1
     ,StrToSet
      (@ItemGroupDescription
       ,CONSTRAINED
      )
     ,[Item].[Group 1 Description].CurrentMember
    )
   ,IIF
    (
      StrToSet(@ItemGroupDescription2,CONSTRAINED).Count = 1
     ,StrToSet
      (@ItemGroupDescription2
       ,CONSTRAINED
      )
     ,[Item].[Group 2 Description].CurrentMember
    )
  )
CELL PROPERTIES 
  VALUE
 ,BACK_COLOR
 ,FORE_COLOR
 ,FORMATTED_VALUE
 ,FORMAT_STRING
 ,FONT_NAME
 ,FONT_SIZE
 ,FONT_FLAGS;
<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Parameter>
    <Name>PeriodPeriodYear</Name>
    <Value xsi:type="xsd:string">[Period].[Period Year].&amp;[2014]</Value>
  </Parameter>
  <Parameter>
    <Name>PeriodPeriodWeek</Name>
    <Value xsi:type="xsd:string">[Period].[Period Week].&amp;[52]</Value>
  </Parameter>
  <Parameter>
    <Name>BranchBranchName</Name>
    <Value xsi:type="xsd:string">[Branch].[Branch Name].&amp;[Test Branch]</Value>
  </Parameter>
  <Parameter>
    <Name>BranchCountry</Name>
    <Value xsi:type="xsd:string">[Branch].[Country].[All]</Value>
  </Parameter>
  <Parameter>
    <Name>BranchStoreCategory</Name>
    <Value xsi:type="xsd:string">[Branch].[Store Category].&amp;[Test Cat]</Value>
  </Parameter>
  <Parameter>
    <Name>BranchRegion</Name>
    <Value xsi:type="xsd:string">[Branch].[Region].[All]</Value>
  </Parameter>
  <Parameter>
    <Name>ItemGroupDescription</Name>
    <Value xsi:type="xsd:string">[Item].[Group 1 Description].[All]</Value>
  </Parameter>
  <Parameter>
    <Name>ItemGroupDescription2</Name>
    <Value xsi:type="xsd:string">[Item].[Group 2 Description].[All]</Value>
  </Parameter>
  <Parameter>
    <Name>ItemItemName</Name>
    <Value xsi:type="xsd:string"></Value>
  </Parameter>
</Parameters>

Here are the perfmon counters via MDX Studio:

Cold cache execution

Time              : 5 sec 125 ms
Calc covers       : 0
Cells calculated  : 24843
Sonar subcubes    : 7700
NON EMPTYs        : 3824
Autoexists        : 42
EXISTINGs         : 3824
SE queries        : 3833
Flat cache insert : 0
Cache hits        : 11513
Cache misses      : 11
Cache inserts     : 1
Cache lookups     : 11524
Memory Usage KB   : 98324

Warm cache execution

Time              : 41 sec 906 ms
Calc covers       : 0
Cells calculated  : 24843
Sonar subcubes    : 7698
NON EMPTYs        : 3824
Autoexists        : 42
EXISTINGs         : 3824
SE queries        : 3825
Flat cache insert : 0
Cache hits        : 11516
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 11516
Memory Usage KB   : -53232

Percent cold caches :0.00%
Percent warm caches :100.00%
1

What is your SSAS version? Old versions of SSAS Formula cache doesn't cache queries with subselects properly which you USE in your query extensively.

You can read the details here: http://blog.crossjoin.co.uk/2013/08/07/subselects-and-formula-engine-caching-in-ssas-2012/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.