-1

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;
maczealot
  • 93
  • 1
  • 2
  • 9
  • At a very minimum you should [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Also try to read it yourself, maybe you can figure out the performance issue(s) with your query. Finally include the [schema DDL](https://en.wikipedia.org/wiki/Data_definition_language) along with the query you are executing. – Igor May 23 '22 at 18:08
  • The execution plan is critical though. Without it you are likely just guessing in the dark on what is slow, the execution plan will tell you exactly what is non-performant in the query you are executing. – Igor May 23 '22 at 18:09
  • 1
    A clause like `COALESCE(MatrixWorkUpdatedDate, CreatedTimestamp) < DATEADD(MINUTE, -15, GETUTCDATE())` will be non-SARGable, yes. I would first suggest getting rid of any functions on columns in you `WHERE` and `ON` clauses. So for that clause, you may find better performance from `(MatrixWorkUpdatedDate < DATEADD(MINUTE, -15, GETUTCDATE()) OR (MatrixWorkUpdatedDate IS NULL AND CreatedTimestamp < DATEADD(MINUTE, -15, GETUTCDATE())))` – Larnu May 23 '22 at 18:09
  • 3
    Side note, get into the *good* habit of using the full name for the date part parameter of functions like `DATEADD` and certainly don't mix and match. You have `MINUTE`, `hh` and `mi` in the above; use `MINUTE` and `HOUR`. – Larnu May 23 '22 at 18:11
  • 1
    ...[here's why](https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations). – Aaron Bertrand May 23 '22 at 18:27
  • `WorkNotes` is not used in your coalesce criteria, so why do you think they are related? While you might not want to index the Worknotes column, you could add a bit or tinyint status column that you maintain depending on the content of worknotes and then index *that* and test it in your where-clause - or maybe an indexed computed column would be better. – Stu May 23 '22 at 18:41

0 Answers0