0 
UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE
EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1 
ON M.MARKID = S.MARKID AND 
S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> 'Y')

The above query is currently updating around 414163 rows in 44 seconds, And I want to improve its performance by maybe doing it parallel or updating in batch?

I have tried with the below query-

UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE
EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1 
ON M.MARKID = S.MARKID AND 
S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> 'Y') OPTION (MAXDOP 8);

But only with 4-6 seconds better result

Execution plan suggests index scan non-clustered on SHFMKPSTMM table is taking 49% cost. But I can't remove the index from that table.

Any help in the better query formulation to improve the performance?

Improve this question
7
  • 1
    Could you please upload the actual execution plan on brentozar.com/pastetheplan and what is the expectation of duration for updating 400k rows update.
    – Learning_DBAdmin
    Apr 6, 2021 at 5:48
  • Due to data confidentiality, I can't share the execution plan.
    – Luv Gupta
    Apr 6, 2021 at 5:52
  • 3
    If the execution plan and database schea are really trade secrets, your best option is to hire a consultant and write up an NDA.
    – vonPryz
    Apr 6, 2021 at 7:01
  • 2
    Maybe adding WHERE M.ARCHIVE_FLAG != 'N' could speed up the query a little.
    – Denis Rubashkin
    Apr 6, 2021 at 7:37
  • 1
    @LuvGupta You can use SentryOne Plan Explorer to locally anonymize your actual execution plan and then you can upload the anonymous plan to Paste The Plan. It'll be the best way anyone can help you with performance improvements, otherwise there's not much else that can be suggested that hasn't been said yet.
    – J.D.
    Apr 6, 2021 at 12:21

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.