Bottle neck on SORT operation

Question

I have the following query and it turned out to be very expensive and takes 6-8 seconds. Considering the implementation plan, the cost is 79% for the operation SORT. Can I get any improvements here?

SELECT A.StageName, C.Month, C.MonthName as Label, C.Year, isnull(A.Average,0) as Data FROM ( SELECT S.StageName, MONTH(TimeIn) as MonthNumber, DATENAME(MONTH,TimeIn) as Month, YEAR(TimeIn) as Year, ISNULL(AVG(DATEDIFF(mi,TimeIn,isnull(TimeOut,@TodayDate))),0) as Average FROM VisitMovement VM INNER JOIN Stage S on VM.StageID = S.StageID WHERE (VM.TimeIn >= @StartDate AND VM.TimeIn < DATEADD (d,1,@EndDate)) AND (VM.TimeOut < DATEADD (d,1,@EndDate) OR VM.TimeOut IS NULL) GROUP BY S.StageNumber, S.StageName, MONTH(TimeIn), DATENAME(MONTH,TimeIn), YEAR(TimeIn) ) A RIGHT JOIN (select distinct Month,MonthName,Year from Calendar WHERE DATE >= @StartDate AND DATE < DATEADD (d,1,@EndDate)) C on A.MonthNumber = C.Month and A.Month = C.MonthName and A.Year = C.Year GROUP BY A.StageName, C.Month, C.MonthName, C.Year, A.Average ORDER BY CASE WHEN @Ordering = 'asc' THEN C.Year END ASC, CASE WHEN @Ordering = 'asc' THEN C.Month END ASC, CASE WHEN @Ordering = 'asc' THEN A.StageName END ASC, CASE WHEN @Ordering = 'desc' THEN C.Year END DESC, CASE WHEN @Ordering = 'desc' THEN C.Month END DESC, CASE WHEN @Ordering = 'desc' THEN A.StageName END DESC 

Answer 1

Although I understand that you cannot get rid of GROUP BY in different columns in a subquery, you can make it easier for the system.

You currently have

 S.StageNumber, S.StageName, MONTH(TimeIn), DATENAME(MONTH,TimeIn), YEAR(TimeIn) 

I think this is quite a bit of data to go through. Let me make some guesses:

 S.StageNumber, -- int, 4 bytes S.StageName, -- string, 20 bytes MONTH(TimeIn), -- int, 4 bytes DATENAME(MONTH,TimeIn), -- string 5 bytes YEAR(TimeIn) -- int, 4 byte 

Now there are some dependencies:

• If you know MONTH (number), then you also call it now
• I assume that StageName + StageNumber is unique and directly related to StageID. If not, you may need GROUP BY again in the outer layer.

That would lead us to

 S.StageID, -- int, 4 bytes MONTH(TimeIn), -- int, 4 bytes YEAR(TimeIn) -- int, 4 byte 

This means that sorting for GROUP BY should be performed with only 12 bytes per record instead of 37 bytes per record, it was earlier, and numbers are sorted several times faster than strings (for example, because of the upper / lower case, accents, etc.) .d.)

I tried to rewrite the request accordingly (Untested!). I also moved the Month-information sample to a separate temporary table, this should help the query optimizer a bit.

 SELECT DISTINCT Month,MonthName,Year INTO #dates FROM Calendar WHERE DATE >= @StartDate AND DATE < DATEADD (d,1,@EndDate) CREATE UNIQUE CLUSTERED INDEX uq0_#dates ON #dates (Month,Year) SELECT A.StageName, C.Month, C.MonthName as Label, C.Year, isnull(A.Average,0) as Data FROM ( SELECT S.StageName, MonthNumber, Year, Average FROM ( SELECT VM.StageID, MONTH(TimeIn) as MonthNumber, YEAR(TimeIn) as Year, ISNULL(AVG(DATEDIFF(mi,TimeIn,isnull(TimeOut,@TodayDate))),0) as Average FROM VisitMovement VM WHERE (VM.TimeIn >= @StartDate AND VM.TimeIn < DATEADD (d,1,@EndDate)) AND (VM.TimeOut < DATEADD (d,1,@EndDate) OR VM.TimeOut IS NULL) GROUP BY VM.StageID, MONTH(TimeIn), YEAR(TimeIn) ) grp JOIN Stage S ON S.StageID = grp.StageID ) ) A RIGHT OUTER JOIN #dates C on A.MonthNumber = C.Month and -- A.Month = C.MonthName and A.Year = C.Year ORDER BY CASE WHEN @Ordering = 'asc' THEN C.Year END ASC, CASE WHEN @Ordering = 'asc' THEN C.Month END ASC, CASE WHEN @Ordering = 'asc' THEN A.StageName END ASC, CASE WHEN @Ordering = 'desc' THEN C.Year END DESC, CASE WHEN @Ordering = 'desc' THEN C.Month END DESC, CASE WHEN @Ordering = 'desc' THEN A.StageName END DESC 

Hope this helps.

Answer 2

Since order by should evaluate each row, I don’t think it can optimally use indexes. Replacing order by with row_number() for default sorting and only changing the order once should at least prevent multiple @Ordering evaluations for one line.

In the pseudocode below, the original request is placed in the CTE. Row_number defines an ascending sort and below cte, if necessary, the order is reversed:

 ;with cte as ( SELECT A.StageName, C.Month, ..., row_number() over (order by C.Year,C.Month,A.StageName) sortOrder FROM ...rest of the query, excluding the order by ) select * --or list the columns without the sortOrder from cte order by sortOrder * case @Ordering when 'desc' then -1 else 1 end