Re: Performance issue in a function

From: Sujatha <sujakris_at_gmail.com>
Date: 13 Jul 2006 12:24:19 -0700
Message-ID: <1152818659.889599.24610@b28g2000cwb.googlegroups.com>

Hi Brian,

As you said, i tried tuning my cursors in the function. here is my function.
CREATE OR REPLACE FUNCTION GET_DM_AMOUNT (P_DM_NUMBER NUMBER

                          ,P_MFR_ID NUMBER DEFAULT NULL
                          ) RETURN NUMBER
   IS
     vn_amount Number;
     vr_eb EXP_BATCH%ROWTYPE;
     vr_dm EXP_DEBIT_MEMO%ROWTYPE;
     CURSOR mfr_phy_returns_cur IS
       SELECT SUM(extended_price) price
         FROM exp_order_lines
        WHERE order_line_id IN
        (SELECT order_line_id FROM exp_contents_of_bag WHERE
container_id IN
          (SELECT container_id FROM exp_containers
            WHERE manufacturer_id    = vr_dm.tc_id
              AND batch_id           = vr_dm.batch_id
              AND exp_dea_group      = vr_dm.EXP_DEA_GROUP
              AND container_type_id  = 1
              AND parent_id IS NOT NULL
              AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA,'F')))

  ;

       CURSOR mfr_risw_returns_cur IS
         SELECT SUM(ol.extended_price) price
           FROM exp_order_lines ol, exp_order_headers oh
          WHERE oh.batch_id                   = vr_dm.batch_id
            AND oh.order_header_id            = ol.order_header_id
            AND ol.return_waste_indate_flag   = 'R'
            AND ol.cap_line                   = 'Y'
            AND ol.return_in_system_indicator = 'Y'
            AND NVL(ol.cla_rx_code_drug_code, 'RX') IN


                (SELECT dea_class_code FROM exp_dea_class WHERE

exp_dea_group = vr_dm.EXP_DEA_GROUP)
            AND ol.manufacturer_name IN


                (SELECT manufacturer_name FROM mfrs_in_debit_memo WHERE

exp_dm_number = vr_dm.exp_dm_number)             ;

       CURSOR retDepot_phy_returns_cur IS
     	 SELECT SUM(extended_price) price
       	 FROM exp_order_lines ol
      	 WHERE order_line_id IN
     		 (SELECT order_line_id FROM exp_contents_of_bag WHERE
container_id IN
     		   (SELECT container_id FROM exp_containers
     		     WHERE manufacturer_id    = vr_dm.tc_id
     		       AND batch_id           = vr_dm.batch_id
     		       AND exp_dea_group      = vr_dm.exp_dea_group
     		       AND container_type_id  = 1
     		       AND parent_id IS NOT NULL
     		       AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA, 'F')))

         AND manufacturer_name IN (SELECT manufacturer_name FROM
mfrs_in_debit_memo mfrs --, exp_trading_community tc
                                   WHERE mfrs.exp_dm_number =
vr_dm.exp_dm_number
                                   AND parent_mfr_id = P_MFR_ID);

       CURSOR retDepot_risw_returns_cur IS
  	SELECT SUM(ol.extended_price) price
      	FROM exp_order_lines ol, exp_order_headers oh
     	WHERE oh.batch_id                   = vr_dm.batch_id
          AND oh.order_header_id            = ol.order_header_id
          AND ol.return_waste_indate_flag   = 'R'
          AND ol.cap_line                   = 'Y'
          AND ol.return_in_system_indicator = 'Y'
          AND ol.wholesaler_fax_unipak_flag = (SELECT tc_initial FROM
exp_trading_community
                                             WHERE tc_id = vr_dm.tc_id)
          AND NVL(ol.cla_rx_code_drug_code, 'RX') IN


                (SELECT dea_class_code FROM exp_dea_class WHERE

exp_dea_group = vr_dm.exp_dea_group)
          AND ol.manufacturer_name IN (SELECT manufacturer_name FROM
mfrs_in_debit_memo
                                     WHERE exp_dm_number =
vr_dm.exp_dm_number
                                       AND parent_mfr_id = P_MFR_ID);
   BEGIN
     SELECT * INTO vr_dm FROM EXP_DEBIT_MEMO WHERE
EXP_DM_NUMBER=P_DM_NUMBER;
     SELECT * INTO vr_eb FROM EXP_BATCH WHERE BATCH_ID=vr_dm.BATCH_ID;
     IF vr_dm.BATCH_ID IS NOT NULL THEN -- CAP
       IF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'N' AND vr_dm.TC_TYPE_ID =
1 THEN
         FOR mfr_phy_returns_rec IN mfr_phy_returns_cur LOOP
           vn_amount:=mfr_phy_returns_rec.price;
         END LOOP;
       ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'Y' AND vr_dm.TC_TYPE_ID
= 1 THEN
         FOR mfr_risw_returns_rec IN mfr_risw_returns_cur LOOP
           vn_amount:=mfr_risw_returns_rec.price;
         END LOOP;
       ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'N' AND vr_dm.TC_TYPE_ID
= 6 THEN
         FOR retDepot_phy_returns_rec IN retDepot_phy_returns_cur LOOP
           vn_amount:=retDepot_phy_returns_rec.price;
         END LOOP;
       ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'Y' AND vr_dm.TC_TYPE_ID
= 6 THEN
         FOR retDepot_risw_returns_rec IN retDepot_risw_returns_cur
LOOP
           vn_amount:=retDepot_risw_returns_rec.price;
         END LOOP;
       END IF;
     ELSE  -- Non-CAP
       null;
     END IF;
     return NVL(vn_amount,0);

   END GET_DM_AMOUNT; The function is really big and hence for about 2500 records if it has to go through the function each time, it will take time. But can somebody suggests another method of doing this function.

thanks

Gints Plivna wrote:

> Sujatha wrote:
> >Can you suggests something?
>
> Switching between SQL and PL/SQL generally is bad idea at least from
> performance viewpoint. The best thing is to incorporate the logic into
> the very sql statement. On the other hand just because your query
> returns 1 row that really doesn't mean that function is called once, it
> can be called zillion times and only later some other filters applied.
> So check how many times your function is applied. Trace could be a tool
> here. If you cannot incorporate function logic into the sql statement
> then generally you'd like to apply function as late as possible after
> all other filters are applied.
> 
> Gints Plivna
> http://www.gplivna.eu/

Received on Thu Jul 13 2006 - 14:24:19 CDT