Re: performance tuning of stored procedure

Chris,

Thanks for your reply. Your code will only compare only the first record with all the other records. But I want to compare all records with all other records. I hope you understand.

appreciate ideas.

Thanks

chris.hulan_at_gmail.com wrote:
> Sorry if Google mangles the format....
>
> Here is a version with only a single LOOP, I think it does the same
> thing...
>
>
> ...
> 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
> /*new ?better? code to eliminate nested loop*/
> IF crec.rowno = 1
> THEN
> vfirstrecnum := crec.recnum;
> vfirstcarrierid := crec.carrier_id;
> vfirstaccountid := crec.account_id;
> vfirstgroupid := crec.GROUP_ID;
> vfirsteffdt := crec.effective_date;
> vfirsttrmdt := crec.trm_date;
> END IF;
>
> IF crec.rowno > 1
> THEN
> /***remove uneeded sub-select-loop
> 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 (SELECT *
> FROM DUAL
> ---
> ---
> )
> AND file_key = infilekey
> AND sub_file_key = insubfilekey
> AND recnum <> vfirstrecnum
> ORDER BY family_id, dob, first_name,
> middle_name)
> LOOP***/
> vsecondrecnum := crec.recnum;
> vsecondcarrierid := crec.carrier_id;
> vsecondaccountid := crec.account_id;
> vsecondgroupid := crec.GROUP_ID;
> vsecondeffdt := crec.effective_date;
> vsecondtrmdt := crec.trm_date;
>
> /**could use the crec.* instead of assigning to temp vars
> but i'll leave that as an exercise for the reader 9^)**/
> 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 IF;
> /*END LOOP;*/
> END LOOP;
> ...
>
> Of course I haven't execurted it so caveat emptor
>
> Cheers
Received on Fri Sep 01 2006 - 12:16:47 CDT