Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Question Unanswered: The update query is taking long time

    Hi All,
    I have loaded a table(staging table) with sample data is having around 18k records.
    I am trynig to fetch the records from this staging table and trying to update all records(18k) values which is taking 10mins+ of time. But actual data is having around 50laks+ records which would take very long time.

    The table is indexed on all the column in the where clause.

    Could you please help me in tuning this query.
    The below code is in a procedure and i am executing this procedure.
    Ver:ibm db2 luw 9.7
    {code}

    select 1 into count from staging_table where applied_flg='N'
    fetch first 1 row only;
    while (count is not null) do
    for cur as curv cursor with hold for
    select * from staging_table where applied_flg='N'
    fetch first 1000000 rows only
    do

    update staging_table stg
    set
    stg.year=2012,
    stg.qtr=(select case when cur.qtr = q1 then 1
    when cur.qtr=q2 then 2
    when cur.qtr=q3 then 3
    when cur.qtr=q4 then 4
    from qtr_table rr where rr.recptno=cur.recptno),
    stg.booking_status = (case when cur.boking_flg= 'N' and cur.accntno= 30 then 'F'
    when cur.boking_flg= 'Y' and cur.accntno= 30 then 'U'
    when cur.boking_flg= 'N' and cur.accntno= 32 then 'U'
    when cur.boking_flg= 'Y' and cur.accntno= 32 then 'B'
    elSE 'T'),
    stg.applied_flg= 'Y',
    stg.ot_amt= nvl2(cur.revsn-code, -1*abs(cur.tot_amt), abs(cur.tot_amt)),
    stg.dept_amt= nvl2(cur.revsn-code, -1*abs(cur.dept_amt), abs(cur.dept_amt)),
    stg.mig_flg= 'Y'
    where stg.id = cur.id;

    commit;

    end for;
    end while;
    {code}
    Last edited by abhi_n123; 07-12-13 at 12:59.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) You updated one row by every execution of update statement, and issued commit for every execution of update statement.

    Please try to update multiple rows(if allowed, all rows) by an update statement.

    Even if commit was neccesary for some number of rows,
    try to update as many rows as possible by an update statement before commit.


    (2) Aliases cur and stg are for same table(i.e. staging_table), and you specified "where stg.id = cur.id".

    So, it might be unnecessary to use two aliases(cur and stg), like the example...
    Code:
    UPDATE (SELECT *
             FROM  staging_table
             WHERE applied_flg = 'N'
             FETCH FIRST <nnn> ROWS ONLY
           ) AS stg
     SET   year = 2012
         , qtr
           = (SELECT CASE stg.qtr
                     WHEN q1 then 1
                     WHEN q2 then 2
                     WHEN q3 then 3
                     WHEN q4 then 4
                     END
               FROM  qtr_table rr
               WHERE rr.recptno = stg.recptno
             )
           ...
           ...
         , applied_flg = 'Y'
         , ot_amt   = NVL2(revsn-code , -1*abs(tot_amt)  , abs(tot_amt) )
         , dept_amt = NVL2(revsn-code , -1*abs(dept_amt) , abs(dept_amt))
         , mig_flg  = 'Y'
    ;
    Note: you might forgot END keyword for some CASE expressions.


    By the way,
    What is "50laks+"?
    I couldn't find lak nor laks on my English-Japanese dictionary.

  3. #3
    Join Date
    Sep 2010
    Posts
    4

    Question Help in improving the performance

    Thanks Tonkuma.

    Also the below code is taking long time.. Can you please suggest some inputs.. All the columns in the where clause are indexed.

    In a procedure i am using below sample code:
    {code}

    For cur as cur cursor with hold for
    select * form table1
    where app_flg='N'
    fetch first 100000 rows only
    do

    if(cur.accnt_no= 45 and cur.bkd_flg in('Y', 'N') then

    insert into del_table(col1, col2..coln)
    select col1, col2.. coln
    from stg_table
    where
    year='2013'
    and id= id
    and form_no= form_no
    and accnt_no = 44;

    delete from stg_table
    where
    year='2013'
    and id= id
    and form_no= form_no
    and accnt_no = 44;

    end if;

    if(cur.accnt_no= 46 and cur.bkd_flg= 'N') then

    insert into del_table(col1, col2..coln)
    select col1, col2.. coln
    from stg_table
    where
    year='2013'
    and id= id
    and form_no= form_no
    and accnt_no = 44;

    delete from stg_table
    where
    year='2013'
    and id= id
    and form_no= form_no
    and accnt_no = 44;

    end if;

    insert into stg_table (col1,col2..coln)
    values(cur.col1, cur.col2.. cur.coln);
    {code}

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Include cur(which select FROM table1) into INSERT/DELETE statements,
    and reverse the order of inquiring conditions.

    Something like...
    Code:
    INSERT INTO del_table
    ( col1 , col2 , ... , coln )
    SELECT col1 , col2 , ... , coln
     FROM  stg_table stg
     WHERE year     = '2013'
       AND accnt_no = 44
       AND EXISTS
           (SELECT 0
             FROM  table1 cur
             WHERE cur.id       = stg.id
              AND  cur.form_no  = stg.form_no
              AND  cur.app_flg  = 'N'
              AND
              (    cur.accnt_no = 45
               AND cur.bkd_flg IN ('Y' , 'N')
               OR  cur.accnt_no = 46
               AND cur.bkd_flg  = 'N'
              )
            )
    ;
    
    DELETE FROM stg_table stg
     WHERE year     = '2013'
       AND accnt_no = 44
       AND EXISTS
           (SELECT 0
             FROM  table1 cur
             WHERE cur.id       = stg.id
              AND  cur.form_no  = stg.form_no
              AND  cur.app_flg  = 'N'
              AND
              (    cur.accnt_no = 45
               AND cur.bkd_flg IN ('Y' , 'N')
               OR  cur.accnt_no = 46
               AND cur.bkd_flg  = 'N'
              )
            )
    ;
    Your code looks to be incomplete.
    For example no "end for" exists.

    If assumed "end for" after
    insert into stg_table (col1,col2..coln)
    values(cur.col1, cur.col2.. cur.coln);
    Try this after the previous example(INSERT INTO del_table and DELETE FROM stg_table stg).
    Code:
    INSERT INTO stg_table
    ( col1 , col2 , ... , coln )
    SELECT col1 , col2 , ... , coln
     FROM  table1 cur
     WHERE cur.app_flg  = 'N'

  5. #5
    Join Date
    Jan 2010
    Posts
    335
    Quote Originally Posted by tonkuma View Post
    By the way,
    What is "50laks+"?
    I couldn't find lak nor laks on my English-Japanese dictionary.
    Lakh - Wikipedia, the free encyclopedia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •