update query performance

Question

Hi All,

Need help in optimizing below UPDATE statements.

Have this long running 3 UPDATE statements doing lot of reads.

we r trying to refresh prod to one of sub-prod env. Will share the plans in some time.

Is there a good way to optimize below queries?

Few i can think of is, please provide your inputs.

-an index on md.TABLE_NAME

-an index on mq.ROWID_MQ_DATA_CHANGE

-an index on md.ROWID_MQ_DATA_CHANGE

-an index on p.ROWID_OBJECT

-an index on md.ROWID_OBJECT

-remove (nolock) as RCSI is turned on this Database

How can I make sure that i reduce the rows as early as possible in below 3 updates?

How to do the same in small batches to avoid blocking and lock escalations?

-- First Individual Update Statement
UPDATE mq
SET mq.sent_state_id = 6,
    UPDATED_BY = 'CONSOLIDATOR',
    STATUS_MESSAGE = 'ROWID_OBJECT NOT FOUND'
FROM C_REPOS_MQ_DATA_CHANGE mq
INNER JOIN #CPQ_Company md ON mq.ROWID_MQ_DATA_CHANGE = md.ROWID_MQ_DATA_CHANGE
    AND md.TABLE_NAME = 'C_B_PARTY'
    AND NOT EXISTS (SELECT 1 FROM C_B_PARTY p (NOLOCK) WHERE p.ROWID_OBJECT = md.ROWID_OBJECT);

-- Second Individual Update Statement
UPDATE mq
SET mq.sent_state_id = 6,
    UPDATED_BY = 'CONSOLIDATOR',
    STATUS_MESSAGE = 'ROWID_OBJECT NOT FOUND'
FROM C_REPOS_MQ_DATA_CHANGE mq
INNER JOIN #CPQ_Company md ON mq.ROWID_MQ_DATA_CHANGE = md.ROWID_MQ_DATA_CHANGE
    AND md.TABLE_NAME = 'C_B_PARTY_COMM'
    AND NOT EXISTS (SELECT 1 FROM C_B_PARTY_COMM p (NOLOCK) WHERE p.ROWID_OBJECT = md.ROWID_OBJECT);

-- Third Individual Update Statement
UPDATE mq
SET mq.sent_state_id = 6,
    UPDATED_BY = 'CONSOLIDATOR',
    STATUS_MESSAGE = 'ROWID_OBJECT NOT FOUND'
FROM C_REPOS_MQ_DATA_CHANGE mq
INNER JOIN #CPQ_Company md ON mq.ROWID_MQ_DATA_CHANGE = md.ROWID_MQ_DATA_CHANGE
    AND md.TABLE_NAME = 'C_B_ACCOUNT'
    AND NOT EXISTS (SELECT 1 FROM C_B_ACCOUNT p (NOLOCK) WHERE p.ROWID_OBJECT = md.ROWID_OBJECT);

Next thing is, what is the advantage or performance gain we can take re-write above updates as single update?

how to prove/measure its performing well? like 2x or 5x times faster then original or less reads?

-- Single Combined Update Statement

UPDATE mq

SET mq.sent_state_id = 6,

    UPDATED_BY = 'CONSOLIDATOR',

    STATUS_MESSAGE = 'ROWID_OBJECT NOT FOUND'

FROM C_REPOS_MQ_DATA_CHANGE mq

INNER JOIN #CPQ_Company md ON mq.ROWID_MQ_DATA_CHANGE = md.ROWID_MQ_DATA_CHANGE

    AND (

        (md.TABLE_NAME = 'C_B_PARTY' AND NOT EXISTS (SELECT 1 FROM C_B_PARTY p (NOLOCK) WHERE p.ROWID_OBJECT = md.ROWID_OBJECT))

        OR

        (md.TABLE_NAME = 'C_B_PARTY_COMM' AND NOT EXISTS (SELECT 1 FROM C_B_PARTY_COMM p (NOLOCK) WHERE p.ROWID_OBJECT = md.ROWID_OBJECT))

        OR

        (md.TABLE_NAME = 'C_B_ACCOUNT' AND NOT EXISTS (SELECT 1 FROM C_B_ACCOUNT p (NOLOCK) WHERE p.ROWID_OBJECT = md.ROWID_OBJECT))

    );

Thanks,

Sam