any ideas??
KK wrote:
> Is there any better way of writing the below stored procedure logic for
> performance tuning? It is taking time for large number of records.
>
> This has 2 for loops. The second loop gets executed (n-1) number of
> times for each record of count n. I appreciate your ideas?
>
> Thanks.
>
> For cRec in
> (
> select rownum as rowno, recnum, trim(both from carrier_id)
> carrier_id, trim(both from account_id) account_id, trim(both from
> group_id) group_id , trim(both from EFFECTIVE_DATE) effective_date,
> trim(both from TRM_DATE) trm_date from rxclaim_output where recnum in
> (
> ---
> ---
> ) and file_key = inFileKey and sub_file_key = inSubFileKey
> order by Family_ID, DOB, First_Name, middle_name
> )
> Loop
> vFirstRecNum := cRec.recnum;
> vFirstCarrierID := cRec.carrier_id;
> vFirstAccountID := cRec.account_id;
> vFirstGroupID := cRec.group_id;
> vFirstEffDt := cRec.effective_date;
> vFirstTrmDt := cRec.trm_date;
> For cSubRec in
> (
> select rownum as rowno, recnum, trim(both from carrier_id)
> carrier_id, trim(both from account_id) account_id, trim(both from
> group_id) group_id , trim(both from EFFECTIVE_DATE) effective_date,
> trim(both from TRM_DATE) trm_date from
> rxclaim_output where recnum in
> (
> ---
> ---
> ) and file_key = inFileKey and sub_file_key =
> inSubFileKey and recnum <> vFirstRecNum order by Family_ID, DOB,
> First_Name, middle_name
> )
> Loop
> vSecondRecNum := cSubRec.recnum;
> vSecondCarrierID := cSubRec.carrier_id;
> vSecondAccountID := cSubRec.account_id;
> vSecondGroupID := cSubRec.group_id;
> vSecondEffDt := cSubRec.effective_date;
> vSecondTrmDt := cSubRec.trm_date;
>
> If ( (vFirstCarrierID <> vSecondCarrierID) or
> (vFirstAccountID <> vSecondAccountID) or
> (vFirstGroupID <> vSecondGroupID) or
> (vFirstEffDt <> vSecondEffDt) or
> (vFirstTrmDt <> vSecondTrmDt) ) then
>
> Update rxclaim_output set Mult_Birth_Calc
> = 0 where recnum = vFirstRecNum;
> Update rxclaim_output set Mult_Birth_Calc
> = 0 where recnum = vSecondRecNum;
> commit;
> end if;
> End Loop;
> END LOOP;
Received on Fri Sep 01 2006 - 12:19:49 CDT
