Client's report is timing out. I'm at a loss as to where to begin trying to fix this mess.
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,
END )) AS RegionCountByMonth ,
DATEPART(yyyy, dbo.v_Activities.InspectionDate) AS Regionyear
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 ,
ORDER BY dbo.Regions.Region ,
I've been tasked with getting this to work, however, I'm not very expereinced in sorting this kind of disaster out.
Neither is attempting to get around the restrictions on ORDER BY in a view by using TOP (100) PERCENT – it doesn't give you any guarantees about returned order at all.
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:
AND ( DATEPART(yyyy, InspectionDate) = RC.Regionyear ) AND ( DATEPART(mm, InspectionDate) = RC.MonthNumber )
-- Calculate the range of dates (assumes InspectionDate is datetime) AND REIA.InspectionDate >= CONVERT(datetime, CONVERT(char(4), RC.Regionyear) + RIGHT(100 + RC.MonthNumber, 2) + '01', 112) AND REIA.InspectionDate < DATEADD(MONTH, 1, CONVERT(datetime, CONVERT(char(4), RC.Regionyear) + RIGHT(100 + RC.MonthNumber, 2) + '01' , 112))
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.