locked
Out of memory exception RRS feed

  • Question

  • The following MDX query gives out of memory exception.

    Windows 2003 32 bit, SQL Server 2005 EE ( PAE enabled, 8GB page file size ).

    Please let me know if I can rewrite the query below for optimization.

    SELECT
      NON EMPTY
        {
          [Measures].[EMPLOYEE ID Distinct Count]
         ,[Measures].[ITINERARY DETAILS Count]
        } ON COLUMNS
     ,NON EMPTY
        {
            [DATE].[DATE HIERARCHY].[DATE].ALLMEMBERS *
            [LOCATION].[DEPARTMENT LOCATION HIERARCHY].[DEPARTMENT LOCATION NAME].ALLMEMBERS *
            [EMPLOYEE CATEGORY].[EMPLOYEE CATEGORY DESCRIPTION].[EMPLOYEE CATEGORY DESCRIPTION].ALLMEMBERS *
            [EMPLOYEE JOB TYPE].[EMPLOYEE JOB TYPE DESCRIPTION].[EMPLOYEE JOB TYPE DESCRIPTION].ALLMEMBERS *
            [TRAVEL REASON].[TRAVEL REASON DESCRIPTION].[TRAVEL REASON DESCRIPTION].ALLMEMBERS *
            [TRAVEL CODE].[TRAVEL CODE DESCRIPTION].[TRAVEL CODE DESCRIPTION].ALLMEMBERS *
            [DEPARTMENT].[DEPARTMENT DESCRIPTION].[DEPARTMENT DESCRIPTION].ALLMEMBERS *
            [LOCATION].[LOCATION TYPE].[LOCATION TYPE].ALLMEMBERS *
            [LOCATION].[LOCATION].[LOCATION].ALLMEMBERS
        }
      DIMENSION PROPERTIES
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM [ITINERARY DETAILS]
    WHERE
      StrToMember
      (@arrival_departure_list
       ,CONSTRAINED
      )
    --where StrToMember('[ARRIVAL]',  CONSTRAINED)
    --where StrToMember('[DEPARTURE]',  CONSTRAINED)

    Thanks for help
    Monday, January 18, 2010 1:47 PM

Answers