Things to be considered while tuning an UPDATE statement

Question

Post reply

Things to be considered while tuning an UPDATE statement

vsamantha35

SSChampion

Points: 11551
More actions
June 22, 2019 at 9:09 am

#3656434

Hi Everyone,

The below update stmt is taking for 30 mins on PROD. would like to know from sql experts that how to go about making this query run faster.
update c_b_party_addr
set consolidation_ind = 4
where consolidation_ind = 1
and exists (
select * from #party_id t where t.party_id = c_b_party_addr.party_id
)
Thanks,
Sam

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

please post the actual explain plan of that query so we can have a look at it.

and DDL for both temp table and main table (including all indexes)

most common issues for these type of updates being slow relate to

Volume
- assuming correct indexes are in place can sometimes be addressed with a batch update (while look updating smaller number of rows each time)
Lack of indexes
- add required indexes - taking in consideration impact of any new index on remaining system
Locking from other users
- hard one sometimes - but working in smaller batches can help

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 2

Agree with Fredrico, please post and explain the plan.

Cheers,
John Esraelo

ScottPletcher SSC Guru Points: 101101 More actions · Answer 3

We have to make many guesses here, since you didn't specify any details on these tables.

If you only need to lookup consolidation_ind if it is = 1, then create a filtered index on ( consolidation_ind, party_id ) WHERE consolidation_ind = 1.

Cluster table #party_id on party_id.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 4

If you haven't already created an index on your temporary table it might speed processing up if you add one before doing this update:

CREATE INDEX IX_#party_id_1 ON #party_id(party_id);

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 5

ScottPletcher wrote:

If you only need to lookup consolidation_ind if it is = 1, then create a filtered index on ( consolidation_ind, party_id ) WHERE consolidation_ind = 1. Cluster table #party_id on party_id.

It might speed it up (depending on the distribution of the data) but as consolidation_ind is the column being updated there is also a risk that adding an index will increase the time of the update as the index also has to be updated.

DesNorton SSC-Insane Points: 24387 More actions · Answer 6

At a guess, this *MIGHT* have improved performance.

WITH cteData AS (
  SELECT      p.party_id, p.consolidation_ind
  FROM        #party_id      AS t
  INNER JOIN  c_b_party_addr AS p
          ON  p.party_id          = t.party_id
         AND  p.consolidation_ind = 1
)
UPDATE  cteData
SET     consolidation_ind = 4;

How to post data/code on a forum to get the best help.

vsamantha35 SSChampion Points: 11551 More actions · Answer 7

vsamantha35

SSChampion

Points: 11551

June 27, 2019 at 9:11 am

#3658015

thanks all for the valuable suggestions.