The objective of the report is to provide counts by Regions within given Months/Years.
This is the view that the query in question is calling the data from.
SELECT DISTINCT TOP ( 100 ) PERCENT Months.Value AS MonthNumber , SUM(( CASE WHEN Months.Value = DATEPART(mm, dbo.v_Activities.InspectionDate) THEN 1 ELSE 0 END )) AS RegionCountByMonth , dbo.Regions.Region , dbo.Regions.RegionId , dbo.Regions.RegionCode , DATEPART(yyyy, dbo.v_Activities.InspectionDate) AS Regionyear FROM dbo.Regions LEFT OUTER JOIN dbo.v_Activities ON dbo.Regions.RegionId = dbo.v_Activities.RegionId CROSS JOIN dbo.fn_Integers(1, 12) AS Months WHERE ( dbo.v_Activities.Complete = 1 ) AND ( dbo.v_Activities.InspectionNumber NOT LIKE '%.S0%' ) AND ( dbo.v_Activities.InspectorId IS NOT NULL ) OR ( dbo.v_Activities.Complete IS NULL OR dbo.v_Activities.Complete = 1 ) AND ( dbo.v_Activities.InspectionId IS NULL ) AND ( dbo.Regions.Region IS NOT NULL ) AND ( Months.Value = DATEPART(mm, dbo.v_Activities.InspectionDate) ) GROUP BY Months.Value , dbo.Regions.RegionId , dbo.Regions.Region , dbo.Regions.RegionCode , DATEPART(yyyy, dbo.v_Activities.InspectionDate) ORDER BY dbo.Regions.Region , MonthNumber , Regionyear
I've been tasked with getting this to work, however, I'm not very expereinced in sorting this kind of disaster out.
By Chris - CSC 1 asked Apr 15, 2016 at 09:56 PM
Many of the underlying issues are hidden in the views. Without the source for those views, there's not much to be said about them, except that using complex views like this is rarely a recipe for good quality execution plans. Even less when the same view is referenced multiple times (it will be fully evaluated each time).
Neither is attempting to get around the restrictions on
The question doesn't say what freedoms you have to rewrite the query or refactor it into a more sensible arrangement. The execution plan was also not obtained by running the query directly from Plan Explorer, so it is missing important performance information.
One code change that might help a bit is to rewrite the predicates:
That at least presents the optimizer with a chance to use an index involving InspectionDate.
Rewriting the query to perform well would likely require some detailed analysis by an experienced query tuner.
By SQLkiwi ♦ 6.6k answered Apr 17, 2016 at 04:56 AM