Problems with EXCEPT and Views in Report Studio 10.1.1

[footyref]

We have two reports that used to take less than ten minutes to run that now take nearly ten hours and they use an EXCEPT in the queries along with a Relative Date view and I am wanting to know if anyone has experience3d any issues with this running against a SQL Server 2008 reporting database.  I actually don't believe the problem is Cognos because I write my very own clean SQL and run it in SQL Server Management Studio and it performs poorly there.

Cognos generates some pretty verbose SQL but it does accomplish what needs done, but for simplicity sake, here is my much cleaner code and I hope I have done well enough to explain it:

1. Returns the YYYYMM of the month selected in a prompt using the Relative Date view (1 record instantly):

SELECT DISTINCT "Year Month (YYYYMM)",
      "Federal Fiscal Year",
      DATEADD( DAY, -DAY( "Date" ) + 1, "Date" ) AS BeginDate,
      "Month Year Name"
FROM   "dbo"."DM_ConformedDate" BeginMonth
JOIN  "dbo"."VW_ConformedRelativeDate" RelativeMonth
ON    BeginMonth."Day_SK" = RelativeMonth."Day_SK"
WHERE "Relative Date" = '3 Months Ago'

2. Returns the YYYYMM of the twelve months previous to the month selected in the prompt (12 records instantly):

SELECT DISTINCT Last12Months."Year Month (YYYYMM)"
FROM   "dbo"."DM_ConformedDate" Last12Months,

(SELECT DISTINCT "Year Month (YYYYMM)",
      "Federal Fiscal Year",
      DATEADD( DAY, -DAY( "Date" ) + 1, "Date" ) AS BeginDate,
      "Month Year Name"
FROM   "dbo"."DM_ConformedDate" BeginMonth
JOIN  "dbo"."VW_ConformedRelativeDate" RelativeMonth
ON    BeginMonth."Day_SK" = RelativeMonth."Day_SK"
WHERE "Relative Date" = '3 Months Ago') FFYQuery

WHERE Last12Months."Date" BETWEEN DATEADD( MONTH, -12, FFYQuery.BeginDate ) and DATEADD( MONTH, -1, FFYQuery.BeginDate )

3. Before the EXCEPT returns all Participant IDs for the month selected in the prompt (272,926 records in 7 seconds):

SELECT DISTINCT "I_PART_I_A"
FROM  "dbo"."WIC_BR_ParticipantDemos"
JOIN  "dbo"."WIC_F_Obligations"
ON    "WICParticipantDemos_SK" = "Participant_SK"
JOIN  "dbo"."DM_ConformedDate" DM_ConformedDate
ON    "ValidBeginDate_SK" = "Day_SK",

(SELECT DISTINCT "Year Month (YYYYMM)",
      "Federal Fiscal Year",
      DATEADD( DAY, -DAY( "Date" ) + 1, "Date" ) AS BeginDate,
      "Month Year Name"
FROM   "dbo"."DM_ConformedDate" BeginMonth
JOIN  "dbo"."VW_ConformedRelativeDate" RelativeMonth
ON    BeginMonth."Day_SK" = RelativeMonth."Day_SK"
WHERE "Relative Date" = '3 Months Ago') FFYQuery

WHERE DM_ConformedDate."Year Month (YYYYMM)" = FFYQuery."Year Month (YYYYMM)" AND   "Closeout Caseload Indicator" = '1'

4. After the EXCEPT returns all Participant IDs for the twelve months prior to the month selected in the prompt (454,700 records in 53 seconds):

SELECT DISTINCT "I_PART_I_A"
FROM  "dbo"."WIC_BR_ParticipantDemos"
JOIN  "dbo"."WIC_F_Obligations"
ON    "WICParticipantDemos_SK" = "Participant_SK"
JOIN  "dbo"."DM_ConformedDate" DM_ConformedDate
ON    "ValidBeginDate_SK" = "Day_SK",

(SELECT DISTINCT Last12Months."Year Month (YYYYMM)"
FROM   "dbo"."DM_ConformedDate" Last12Months,

(SELECT DISTINCT "Year Month (YYYYMM)",
      "Federal Fiscal Year",
      DATEADD( DAY, -DAY( "Date" ) + 1, "Date" ) AS BeginDate,
      "Month Year Name"
FROM   "dbo"."DM_ConformedDate" BeginMonth
JOIN  "dbo"."VW_ConformedRelativeDate" RelativeMonth
ON    BeginMonth."Day_SK" = RelativeMonth."Day_SK"
WHERE "Relative Date" = '3 Months Ago') FFYQuery

WHERE Last12Months."Date" BETWEEN DATEADD( MONTH, -12, FFYQuery.BeginDate ) and DATEADD( MONTH, -1, FFYQuery.BeginDate )) Last12Months

WHERE DM_ConformedDate."Year Month (YYYYMM)" IN ( Last12Months."Year Month (YYYYMM)" ) AND   "Closeout Caseload Indicator" = '1'

If I do a left join on the first part of the query where the "I_PART_I_A" in the second query is null, I get the correct 13,712 results back in just over a minute.  However, running the query as it is, I waited 23 minutes and only had 940 records returned at that point.  If I hard code the dates, it all comes back in less than a minute.

Again, has anyone had issues with EXCEPTS and views in SQL Server 2008?

[Rahul Ganguli]

Hi,

You need to optimize your query a lot, I can see a lot of expensive function used in the query.
Also, the performance of except clause depends on number of columns, column type and number of rows.

Regards,
Rahul