Hi All,
Need help in tuning 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) hint 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
March 20, 2024 at 1:45 pm
Try these and see if they help. Be sure to review the query plan to verify:
INDEX ON mq: ( TABLE_NAME, ROWID_MQ_DATA_CHANGE )
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 IN ('C_B_ACCOUNT', 'C_B_PARTY', 'C_B_PARTY_COMM') --<<--
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))
);
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 20, 2024 at 1:46 pm
(dup)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 20, 2024 at 6:29 pm
Try these and see if they help. Be sure to review the query plan to verify:
INDEX ON mq: ( TABLE_NAME, ROWID_MQ_DATA_CHANGE )
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 IN ('C_B_ACCOUNT', 'C_B_PARTY', 'C_B_PARTY_COMM') --<<--
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 Scott.
I wanted to know single update is better than 3 updates? is it the tables will be loaded 3 tables and increase in logical reads and improved response times or are there any other benefits doing so. "C_REPOS_MQ_DATA_CHANGE" mq table has lot of rows like 200 million rows and its a rapidly changing table. Does batching helps ? all we want is to reduce the blocking as much as we can. it has 3 instead of triggers defined on it as well.
March 20, 2024 at 9:15 pm
I would think one UPDATE -- with the IN condition added -- would be much better than 3 separate UPDATEs, since the main table will then only have to be scanned once rather than three times.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 20, 2024 at 9:31 pm
duplicate
March 20, 2024 at 9:32 pm
duplicate
March 20, 2024 at 9:32 pm
How to do the same in small batches to avoid blocking and lock escalations?
DECLARE @BatchSize INT = 1000; -- Adjust batch size as needed
DECLARE @RowsAffected INT = @BatchSize; -- Initialize @RowsAffected to start the loop
WHILE @RowsAffected >= @BatchSize -- Exit loop when affected rows are less than batch size
BEGIN
UPDATE TOP (@BatchSize) 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'
WHERE NOT EXISTS (SELECT 1 FROM C_B_PARTY p WHERE p.ROWID_OBJECT = md.ROWID_OBJECT);
SET @RowsAffected = @@ROWCOUNT;
END;
March 21, 2024 at 12:30 pm
Thank you Scott and Jonathan.
March 21, 2024 at 12:32 pm
Thank you Scott and Jonathan.
Hi Jonathan, question came into mind, if the batch size is specified as 1000 but assuming if the qualified rows based on where condition is 1050 rows, will it update all 1050 rows or just the 1000 rows?
Thank you Scott and Jonathan.
Hi Jonathan, question came into mind, if the batch size is specified as 1000 but assuming if the qualified rows based on where condition is 1050 rows, will it update all 1050 rows or just the 1000 rows?
Just the first 1000 in the first run of the statement but obviously it loops around until they are all deleted.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply