'What is the best way to go about replacing these COALESCE functions?

I have this query that I believe is eating up a lot of resources. The WorkNotes column is NVARCHAR(MAX) and is taking up 2/3 of the database's entire storage. I suspect, with that in mind, the 3 COALESCE uses in the WHERE/AND clause should be replaced. I am not sure what is the best way to lean though CAST, ISNULL, complete re-write lol, etc.


DECLARE @BatchTable TABLE
(
    MatrixWorkId BIGINT,
    MatrixJobId BIGINT,
    MatrixWorkTypeId TINYINT,
    WorkNotes NVARCHAR(MAX),
    RetryCount INT,
    TraceContext NVARCHAR(250)
);

UPDATE app.MatrixWork WITH (READPAST, ROWLOCK)
SET MatrixWorkUpdatedDate = GETUTCDATE(),
    MatrixWorkUpdatedBy = CAST(ORIGINAL_LOGIN() AS NVARCHAR(250)),
    MatrixWorkStateId = 8
OUTPUT inserted.MatrixWorkId,
       inserted.MatrixWorkId,
       inserted.MatrixWorkTypeId,
       inserted.WorkNotes,
       inserted.RetryCount,
       inserted.TraceContext
INTO @BatchTable
WHERE WorkNotes IS NOT NULL
      AND WorkNotes <> ''
      AND MatrixWorkId IN
          (
              SELECT TOP 25
                     MatrixWorkId
              FROM app.MatrixWork
              WHERE MatrixWorkId = 6
                    AND
                    (
                        MatrixWorkStateId IN ( 1, 3 )
                        OR
                        (
                            MatrixWorkStateId IN ( 6 )
                            AND RetryCount < (50)
                            AND (COALESCE(MatrixWorkUpdatedDate, CreatedTimestamp) < DATEADD(MINUTE, -15, GETUTCDATE()))
                        )
                        OR
                        (
                            MatrixWorkStateId IN ( 4 )
                            AND (COALESCE(SubmittedTimestamp, MatrixWorkUpdatedDate) < DATEADD(hh, -12, GETUTCDATE()))
                        )
                        OR
                        (
                            MatrixWorkStateId IN ( 2, 8 )
                            AND (COALESCE(MatrixWorkUpdatedDate, CreatedTimestamp) < DATEADD(mi, -120, GETUTCDATE()))
                        )
                    )
              ORDER BY MatrixWorkStateId,
                       MatrixWorkId ASC
          );


SELECT *
FROM @BatchTable;



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source