Update query taking time to execute

Question

I have a table xx_wr which has a column action_code which i want to update according to the action_code column of PER_ASG.

This PER_ASG has columns effective_start_date and effective_end_date . Only the max effective_start_date from this table that is the latest record should be picked to update the xx_wr.

PER_ASG 

EFFECTIVE_START_DATE EFFECTIVE_END_DATE ACTION_CODE PERSON_ID
01-JAN-2016          31-DEC-4712         HR          10
02-FEB-2015          31-DEC-2015         TERM        10

So from this table, the latest row from 01-JAN-2016 should be picked and Action_code HR should update the action_code in WR.

I Have written the below query for this. But it is taking 3 hrs to update 10k rows. Is there any way I can modify my query to improve the performance ?

 update XX_WR wr
            set wr.action_code = 
            (
            select pam.action_code
            from PER_ASG pam
            where 1=1
            and pam.person_id=wr.person_id
            and pam.effective_start_date = (select max(ppm.effective_start_date) from PER_ASG ppm 
            where 1=1 
            and ppm.person_id = pam.person_id
            )) 
            where 1=1;

Pரதீப் · Accepted Answer · 2016-12-06 11:34:08Z

1

Instead of nested sub-queries to find the maximum effective_start_date for each person_id use ROW_NUMBER window function to find it

Try this way

MERGE INTO XX_WR AS wr
using (SELECT Row_number()OVER(partition BY person_id ORDER BY effective_start_date DESC ) AS rn,
              action_code,
              person_id
       FROM   PER_ASG) pam
ON wr.person_id = pam.person_id
   AND pam.rn = 1
WHEN matched THEN
  UPDATE SET action_code = pam.action_code;

Note : Here we dont have to use MERGE since am not sure about Update from Join syntax in Oracle am using MERGE statement but the idea is same

edited Dec 6, 2016 at 11:34

answered Dec 6, 2016 at 11:25

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-12-06 11:44:42Z

This is the query:

update XX_WR wr
    set wr.action_code =
        (select pam.action_code
         from PER_ASG pam
         where pam.person_id = wr.person_id and
               pam.effective_start_date =
                   (select max(ppm.effective_start_date)
                    from PER_ASG ppm 
                    where ppm.person_id = pam.person_id
                   ) 
        );

This is updating all rows in XX_WR. That might be the cause of the performance problem.

However, the query can still be made more efficient. I would recommend indexes. Start with PER_ASG(person_id, effective_start_date). I would also rewrite this as:

update XX_WR wr
    set wr.action_code =
        (select max(pam.action_code) keep (dense_rank first order by effective_date)
         from PER_ASG pam
         where pam.person_id = wr.person_id 
        );

CompEng · Accepted Answer · 2016-12-06 11:48:16Z

1

try this:

 WITH
sub1 AS 
(  /*+ materialize */ 
SELECT pam.action_code,pam.person_id
  FROM PER_ASG pam
 WHERE     1 = 1 
       AND pam.effective_start_date =
              (SELECT MAX (ppm.effective_start_date)
                 FROM PER_ASG ppm
                WHERE 1 = 1 AND ppm.person_id = pam.person_id)
)                
update XX_WR wr
            set wr.action_code =sub1.action_code 
where sub1.person_id=wr.person_id

answered Dec 6, 2016 at 11:48

CompEng

7,41618 gold badges77 silver badges135 bronze badges

Collectives™ on Stack Overflow

Update query taking time to execute

3 Answers 3

Comments

Comments

try this:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

try this:

Comments

Your Answer

Sign up or log in

Post as a guest

Related