performance tuning of stored procedure

From: KK <krallabandi_at_gmail.com>
Date: 1 Sep 2006 08:36:57 -0700
Message-ID: <1157125017.443239.56650@m73g2000cwd.googlegroups.com>

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 - 10:36:57 CDT