I'm trying to understand the performance issue with Window function when i select value for MAX ID
--Old group By method
SELECT
tj.MainProcedure ,
tj.StartTime ,
tj.EndTime ,
tj.Threshold,
tj.LastStatus ,
tj.rc
ErrorMessage
FROM dbo.tbl_TraceJobExecutions tj WITH (NOLOCK)
INNER JOIN
(
SELECT b.MainProcedure,
ErrorMessage
FROM
(
SELECT MainProcedure, MAX(RowID) AS MaxRowID
FROM tbl_TraceJobExecutions_ErrorHandling WITH (NOLOCK)
GROUP BY MainProcedure
)a
INNER JOIN tbl_TraceJobExecutions_ErrorHandling b WITH (NOLOCK)
ON b.MainProcedure = a.MainProcedure
AND b.RowID = a.MaxRowID
) eh
ON eh.MainProcedure = tj.MainProcedure
ORDER by tj.MainProcedure, tj.EndTime DESC
--VS Window Function
SELECT tbl_TraceJobExecutions.*,MaxVal.ErrorMessage FROM tbl_TraceJobExecutions
INNER JOIN (
SELECT
MAX(tbl_TraceJobExecutions_ErrorHandling.RowID) OVER (PARTITION BY tbl_TraceJobExecutions_ErrorHandling.MainProcedure) AS MaxID,
tbl_TraceJobExecutions_ErrorHandling.MainProcedure,
RowID,
ErrorMessage
FROM tbl_TraceJobExecutions_ErrorHandling
) AS MaxVal ON MaxVal.MainProcedure = tbl_TraceJobExecutions.MainProcedure AND
MaxVal.RowID = MaxVal.MaxID
ORDER by tbl_TraceJobExecutions.MainProcedure, tbl_TraceJobExecutions.EndTime DESC
The IO statistic:
(10 row(s) affected) Table 'tbl_TraceJobExecutions'. Scan count 0, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tbl_TraceJobExecutions_ErrorHandling'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
(10 row(s) affected) Table 'Worktable'. Scan count 3, logical reads 1776, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tbl_TraceJobExecutions_ErrorHandling'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tbl_TraceJobExecutions'. Scan count 1, logical reads 79, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
What is wrong with my code? Thank you