Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: How to process records fetched in Cursor in batches, Stored proc.

    Hi, I have written this Stored Procn now i want to do some performance tuning on it for which i need your valuable suggestions. Actually the first select query returns large number of records may be in lacs, so what is the ideal way to process it, is it possible i can do this in some batches i mean first 5000 records first , then next 5000 etc. I think if i select all the rows in one go in cursor which may be lacs, then either database may get locked and no other user will be able to access it. Please suggest some thing. This query returns large number of records

    SELECT DISTINCT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM ERE2.OBJECT_DATA AS OBJECT_DATA JOIN ERE2.PART_LIST AS PART_LIST ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.Q_PMSYNC='Y' AND OBJECT_DATA.S_DC='RD' AND OBJECT_DATA.Q_DATEPMSYNC < OBJECT_DATA.Q_LASTMOD ORDER BY OBJECT_DATA.ITEM_OBJID;

    and then i process them individually. What is the best way to do such activity.

    CREATE PROCEDURE EREIMP.SPCRON ( )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure 
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variable

    DECLARE counter INT DEFAULT 1;
    DECLARE vitem_objid BIGINT DEFAULT 0;
    DECLARE num_rows INT DEFAULT 0;
    DECLARE vs_itemi CHAR(12);
    DECLARE vs_idcng CHAR(12);
    DECLARE vitm_name VARCHAR(10);
    DECLARE vitm_ctl_st CHAR(12);
    DECLARE vlocid CHAR(4);
    --DECLARE vec_rl_dt TIMESTAMP;
    DECLARE vec_rl_dt CHAR(35);
    DECLARE vdes_seqnum INT;


    -- Declare cursor
    DECLARE cursor1 CURSOR FOR

    SELECT DISTINCT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM ERE2.OBJECT_DATA AS OBJECT_DATA JOIN ERE2.PART_LIST AS PART_LIST ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.Q_PMSYNC='Y' AND OBJECT_DATA.S_DC='RD' AND OBJECT_DATA.Q_DATEPMSYNC < OBJECT_DATA.Q_LASTMOD ORDER BY OBJECT_DATA.ITEM_OBJID;


    OPEN cursor1;

    SELECT COUNT(*) into num_rows FROM(SELECT DISTINCT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM ERE2.OBJECT_DATA AS OBJECT_DATA JOIN ERE2.PART_LIST AS PART_LIST ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.Q_PMSYNC='Y' AND OBJECT_DATA.S_DC='RD' AND OBJECT_DATA.Q_DATEPMSYNC < OBJECT_DATA.Q_LASTMOD ORDER BY OBJECT_DATA.ITEM_OBJID) X;

    FETCH FROM cursor1 INTO vitem_objid, vs_itemi, vs_idcng;

    WHILE (counter <= num_rows) DO


    SELECT case ITM_CTL_ST
    when '1 AVPSV213' then ' '
    when '3 AVPSV213' then 'ACTIVE'
    when '4 AVPSV213' then 'FIELD USE'
    when '5 AVPSV213' then 'OBSOLETE'
    when '6 AVPSV213' then 'TERMINATE'
    when '7 AVPSV213' then 'TERMINATE'
    else ITM_CTL_ST
    end as ITM_CTL_ST,
    substr(ITM_NAME,1,10) as ITM_NAME, char(EC_RL_DT,ISO)||'-00.00.00.000000' as EC_RL_DT , substr(LOCID,1,4) as LOCID, DES_SEQNUM INTO vitm_ctl_st, vitm_name, vec_rl_dt, vlocid, vdes_seqnum FROM PM.AVNTITEM AVNTITEM JOIN PM.AVNTECAI AVNTECAI ON AVNTITEM.OID_MITEM=AVNTECAI.OID_MITEM AND AVNTECAI.DES_SEQNUM=AVNTITEM.INSERTSEQ JOIN PM.AVNTEC AVNTEC ON AVNTEC.OBJID=AVNTECAI.OID_EC JOIN PM.AVNTLOCATN LOC ON LOC.OBJID=AVNTITEM.OID_DS_LAB WHERE ITM_ID = vs_itemi AND EC_MC_ID = vs_idcng;

    BEGIN
    UPDATE ERE2.OBJECT_DATA SET Q_DATREL = vec_rl_dt, Q_DATEPMSYNC = CURRENT TIMESTAMP, Q_DESIGNSEQ = vdes_seqnum WHERE ITEM_OBJID = vitem_objid AND S_IDCNG = vs_idcng;

    UPDATE ERE2.PART_LIST SET S_IDIBM = vlocid, Q_BNAME = vitm_name, Q_CENGS = vitm_ctl_st WHERE ITEM_OBJID = vitem_objid;
    END;

    FETCH FROM cursor1 INTO vitem_objid, vs_itemi, vs_idcng;

    SET counter = counter + 1;

    END WHILE;
    CLOSE cursor1;

    END P1

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are reading the data twice, once in the cursor fetch loop, and once in the count(*) to get the number of rows. For large results sets this is a bad idea. You should eliminate the select count (*) and use a condition handler to handle the fetch loop. You can also issue a commit every 1000 updates and that will help concurrency.

    Andy
    Last edited by ARWinner; 03-31-09 at 09:58.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    1. Get rid of ORDER BY, because it does not make sense there.

    2. Instead of executing the query twice to obtain the number of rows use the NOT FOUND condition handler (see examples in the manual for the DECLARE HANDLER statement). By the way, the logic is flawed anyway - the actual cursor can contain more or less rows than returned by the COUNT, depending on the isolation level in effect.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Furthermore, if you don't need the COMMIT, get rid of the procedural logic and write two search UPDATE statements that use the query as a subselect in the WHERE clause.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by n_i
    1. Get rid of ORDER BY, because it does not make sense there.

    2. Instead of executing the query twice to obtain the number of rows use the NOT FOUND condition handler (see examples in the manual for the DECLARE HANDLER statement). By the way, the logic is flawed anyway - the actual cursor can contain more or less rows than returned by the COUNT, depending on the isolation level in effect.
    I used condition handler here but it is giving a problem, whenever the inner Select returns nothing the endtable is set to 1. and my loop gets closed as it works when ENDTABLE is 0. how can i handle this thing in SP . So all records selected in outer select are not processed.

    Please tell me the soultion for this.

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET ENDTABLE = 1;

    OPEN cursor1;

    SET ENDTABLE = 0;
    FETCH FROM cursor1 INTO vitem_objid, vs_itemi, vs_idcng;

    WHILE ENDTABLE = 0 DO


    SELECT case ITM_CTL_ST
    when '1 AVPSV213' then ' '
    when '3 AVPSV213' then 'ACTIVE'
    when '4 AVPSV213' then 'FIELD USE'
    when '5 AVPSV213' then 'OBSOLETE'
    when '6 AVPSV213' then 'TERMINATE'
    when '7 AVPSV213' then 'TERMINATE'
    else ITM_CTL_ST
    end as ITM_CTL_ST,
    substr(ITM_NAME,1,10) as ITM_NAME, char(EC_RL_DT,ISO)||'-00.00.00.000000' as EC_RL_DT , substr(LOCID,1,4) as LOCID, DES_SEQNUM INTO vitm_ctl_st, vitm_name, vec_rl_dt, vlocid, vdes_seqnum FROM PM.AVNTITEM AVNTITEM JOIN PM.AVNTECAI AVNTECAI ON AVNTITEM.OID_MITEM=AVNTECAI.OID_MITEM AND AVNTECAI.DES_SEQNUM=AVNTITEM.INSERTSEQ JOIN PM.AVNTEC AVNTEC ON AVNTEC.OBJID=AVNTECAI.OID_EC JOIN PM.AVNTLOCATN LOC ON LOC.OBJID=AVNTITEM.OID_DS_LAB WHERE ITM_ID = vs_itemi AND EC_MC_ID = vs_idcng;

  6. #6
    Join Date
    Mar 2009
    Posts
    24
    Quote Originally Posted by ARWinner
    You are reading the data twice, once in the cursor fetch loop, and once in the count(*) to get the number of rows. For large results sets this is a bad idea. You should eliminate the select count (*) and use a condition handler to handle the fetch loop. You can also issue a commit every 1000 updates and that will help concurrency.

    Andy
    Can u please elaborate how do fix on every 1000 records, also i tried using continue handler but when inner select doesnt return anything that handler is invoked and end table is set to 1 and processing stops

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Wrap the inner select into a block with its own handler that does nothing.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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