Re: performance tuning of stored procedure

From: <chris.hulan_at_gmail.com>
Date: 1 Sep 2006 09:05:57 -0700
Message-ID: <1157126757.902416.132990@b28g2000cwb.googlegroups.com>

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 - 11:05:57 CDT