1

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

Yorik
  • 193
  • 1
  • 9

0 Answers0