Client's report is timing out. I'm at a loss as to where to begin trying to fix this mess.

Chris – CSC 2016-04-15 21:56:49

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.

SQLkiwi 2016-04-17 04:56:54
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 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 )

as:

-- 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.