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

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

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.

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.

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;

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

Wrap the inner select into a block with its own handler that does nothing.