CODEWITHSUNDEEP
sqloracle-database
sreekem bose
sreekem bose

Reputation: 471

Update query taking time to execute

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;

Upvotes: 2

Views: 76

Answers (3)

CompEng
CompEng

Reputation: 7416

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

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 
        );

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 1

Related Questions