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

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.

avatar image By Chris - CSC 1 asked Apr 15, 2016 at 09:56 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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 )


 -- Calculate the range of dates (assumes InspectionDate is datetime)
 AND REIA.InspectionDate >= 
         CONVERT(char(4), RC.Regionyear) + 
         RIGHT(100 + RC.MonthNumber, 2) + '01', 112)
 AND REIA.InspectionDate < 
             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.

avatar image By SQLkiwi ♦ 6.6k answered Apr 17, 2016 at 04:56 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Apr 15, 2016 at 09:56 PM

Seen: 76 times

Last Updated: Apr 17, 2016 at 04:56 AM