Update statement consuming 6 days to execute

Database
Enthusiast

Update statement consuming 6 days to execute

Hi Al,

This is Raghav.
I have a small but hecty lot of a problem.
I have to update a target table which contains approx 3.1 crore records.
Update is based on the following criterias:
1> For each item_row number, i have to execute update statement because for each row, I have to check whether that row has column_ind as 'R'. If yes, then update its colA(cal_yr), colB(cal_mh) and colC(ref_cd).
2> Values of ColA, colB and colC differs for each row.

Example:
update table1 set cal_yr_no=2006, cal_mh_no=3 , ref_cd='26052STN' where row_no =1 and column_ind='R';

update table1 set cal_yr_no=2005, cal_mh_no=4 , ref_cd='26463STN' where row_no =2 and column_ind='R';

update table1 set cal_yr_no=2006, cal_mh_no=1 , ref_cd='26046STN' where row_no =3 and column_ind='R';

update table1 set cal_yr_no=NULL, cal_mh_no=NULL , ref_cd='2665STN' where row_no =4 and column_ind='R';

update table1 set cal_yr_no=2006, cal_mh_no=NULL , ref_cd='26STN' where row_no =5 and column_ind='R';

Like this I have 209324 UPDATE statements.
Since the target table table1 already contains nearly 3.1 crore records, it is consuming 22 minutes to execute 192 sample UPDATE statements which will be approx 6 days to execute 209324 UPDATE statements.

Is there any way out in which I can complete this wihin few minutes

Thanks in advance.
6 REPLIES
Junior Contributor

Re: Update statement consuming 6 days to execute

Hi Raghav,
how did you create the 209324 updates?
You can probably reduce the number by using CASEs.

How many rows are updated by each update?

Could you post the table DDL?
Updates will be extremely slow, when you update the PI.

Dieter
Enthusiast

Re: Update statement consuming 6 days to execute

Hi Dieter,

Thanks for ur reply.
I created these update statements using Textpad.
If I can reduce the number of statements using CASE, please guide me if possible.

Each update statement updates only one row but since the condition is based on item's row_number also, I think it is consuming time because for each item row_number there are 250 rows.

Sorry, I cannot post you the DDL. Itz confidential but I assure you that I am not updating on PI columns. It is neither defined as SI.

Waiting for some real champs like you to come to my aid.

Junior Contributor

Re: Update statement consuming 6 days to execute

Hi Raghav,
you must be joking, you typed 209324 update statements using Textpad?
How do you know the values for each update?
There must be some input data or table.

Each upate is doing a Full Table Scan on 31.000.000 rows, that's why is slow.
If you create a USI on (row_no, column_ind) before running the updates, this would be much faster, because the FTS will be replaced by a 2-AMP access. After updating you can drop it again.

But there's probably a much better solution, if you answer the first Qs.

Dieter
Enthusiast

Re: Update statement consuming 6 days to execute

Hi Dieter,

Being an analyst, joking is beyond my stature and responsibility.
Before posting a question, I have analyzed all the possibilities and only when I cud not find a proper solution I have put it in the Forum.

As for ur questions,
We are deriving values for yr_no, mh_no and ref_cd based on the existing columns in table1. For your info, my source and my target tables are same.
So, all I need to do is execute a SELECT statement to see whether the values derived are proper or not.
Using these values, I have copied and pasted in Textpad.

Thanks for your interest. It is really appreciable.
Junior Contributor

Re: Update statement consuming 6 days to execute

Hi Raghav,
if you can create all those Updates using a Select, why don't you simply write a single update statement?

update table1 from
(select
... as cal_yr_no,
... as cal_mh_no,
... as ref_cd,
... as row_no,
... as column_ind
from table1
where ...
) as dt
set
cal_yr_no = dt.cal_yr_no
cal_mh_no = dt.cal_mh_no
ref_cd = dt.ref_cd
where
row_no = dt.row_no
and
column_ind = dt.column_ind
;

This results in 1 FTS vs. 209324 FTS.

Dieter
Enthusiast

Re: Update statement consuming 6 days to execute

Thanks Dieter