I have the following query and it's proving very costly and is taking 6-8 seconds to execute. Looking at the execution plan, the cost is 79% on a SORT operation. Can I get any improvement 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
Although I understand you can't get rid of the
GROUP BY
on the different columns in the sub-query, you can make it easier for the system.Currently you have
Which I guess is quite a lot of data to go through. Allow me to make some guesses:
Now, there are some dependencies:
That would bring us to
This means that the sorting for the
GROUP BY
has to run through only 12 bytes per record instead of the 37 bytes per record it was before, and numbers sort quite a bit faster than strings anyway (e.g. due to upper/lower case, accents, etc..)I've tried to rewrite the query accordingly (Untested!). I've also moved the fetch of Month-information to a separate temp-table, this should help the Query Optimizer a little bit.
Hope this helps.