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?

New contributor
Luv Gupta is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
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 at 5:48
  • Due to data confidentiality, I can't share the execution plan. – Luv Gupta Apr 6 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 at 7:01
  • 2
    Maybe adding WHERE M.ARCHIVE_FLAG != 'N' could speed up the query a little. – Denis Rubashkin Apr 6 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 at 12:21

Your Answer

Luv Gupta is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.