0 Replies Latest reply on Dec 5, 2012 12:37 PM by 978117

    Improve query speed

    978117
      Hi there,
      I'm relatively new to Pl/SQL and to APEX and i'm developing a application on APEX. So i have a report on a page which pulls out the orders according to the type of user. If the type is not LOG_FARM,'MANAGER','MNG_FARM' the code runs fine in acceptable time but when the user is one of those three it takes almost 3 minutes to finish the query. What i wanted to know is if within the code you see any clear change that i could do to improve the speed or if i can for example bring only the newest results (although this last option is not good because i have a search engine above and like this i wont have acess to older registers).


      Declare
      v_restricao varchar2(100);
      v_restricao_dim varchar2(4000);

      Begin

      if :G_APP_USER_EMP_TYPE = 'LOG_FARM' then
      v_restricao := 'and FO.ORDER_LOGISTICS = ''Y''';
      else
      v_restricao := null;
      end if;

      if :G_APP_USER_EMP_TYPE IN('MNG_FARM','DIM_FARM') then
      v_restricao_dim := '
      WHERE pharmacyid in (SELECT pharm_id
      FROM dim_user
      WHERE UPPER (emp_login) = UPPER ('''||:app_user||''')) ';
      end if;

      if :G_APP_USER_EMP_TYPE = 'CALL_FARM' then
      v_restricao_dim := '
      WHERE pharmacyid IN (SELECT DISTINCT pharmid
      FROM dimuser
      WHERE SEL_PHARM = ''Y'' ) ';
      end if;

      if :G_APP_USER_EMP_TYPE IN('LOG_FARM','MANAGER','MNG_FARM') then
      return 'SELECT fo.pharm_id, fo.order_id, dp.pharmacy_dsc, ds.whs_dsc, to_CHAR(fo.order_dt,''DD/MM/YYYY'') AS ORDER_DT,
      fo.order_obs, acronyms (fo.order_stat, ''ORDER.STATUS'') as STATUS,
      fo.order_stat
      FROM fact_order fo,
      dim_pharm dp,
      dim_whs ds
      WHERE upper(order_id) LIKE NVL(upper('''||:p501_order_id||'''),''%'')
      AND fo.pharm_id = dp.pharmacy_id
      AND fo.whs_id = ds.whs_id ' || v_restricao || '
      order by fo.order_id desc ';


      ELSE
      return 'SELECT fo.pharm_id, fo.order_id, dp.pharmacy_dsc, ds.whs_dsc, to_CHAR(fo.order_dt,''DD/MM/YYYY'') AS ORDER_DT,
      fo.order_obs, acronyms (fo.order_stat, ''ORDER.STATUS'') as STATUS,
      fo.order_stat,(select dkap.emp_name from dim_kap dkap where DKAP.PHARM_ID = FO.PHARM_ID AND DKAP.sel_pharm = ''Y'') AS KAP
      FROM fact_order fo,
      dim_pharm dp,
      dim_whs ds ,
      (SELECT pharmacyid
      FROM dim_pharm ' || v_restricao_dim ||' ) dim_phar
      WHERE upper(order_id) LIKE NVL(upper('''||:p501_order_id||'''),''%'')
      AND fo.pharm_id = dp.pharmacy_id
      AND fo.whs_id = ds.whs_id
      and FO.ORDER_LOGISTICS = ''N''
      AND dim_phar.pharmacy_id = fo.pharm_id
      order by fo.order_id desc';
      END IF;
      END;


      The two are quite similar only the second has a condition to show only the pharmacys associated with the current user and the first one shows all the pharmacys.

      Thanks in advance.
      Bruno