0 Replies Latest reply: Nov 15, 2012 5:12 AM by 974363 RSS

    Report running very slow.. taking too much time

    974363
      Dear Oracle Report experts,

      I have developed report in oracle reports bulider 10g. while running it from report builder through main menu *** data is coming very SLOW *** within 55 Minuts.
      But, If same query is executing from SQL/PLSQL deverloper it is very fast within 45 second.
      Please suggest any configuration or setting ; is having Idea.

      Thanks

      Muhammad Salim

      Query is as below: generating result in 48 second.

      --------------

      select cns.consultant,
      sum(cns.nof_pat) noof_pat,
      sum(cns.opd_amnt) opd_amnt,
      sum(cns.discount_amnt) discount_amnt,
      sum(cns.net_amnt) net_amnt,
      sum(cns.dr_share) dr_share,
      sum(cns.hosp_share) hosp_share,
      sum(cns.net_dis) net_dis
      from
      (
      select rec.consultant,
      count(distinct rec.consultant) nof_pat,
      -- rec.receipt_date, bysalim
      pay_mode,
      rec.patient_mrno,rec.patient,
      service_name,rcpt_no,
      company,rec.docno,
      sum(distinct return_amnt) return_amnt,
      sum(distinct rec.opd_amnt) opd_amnt,
      sum(distinct dis.discount_amnt) discount_amnt,
      (sum(distinct nvl(rec.opd_amnt,0))-sum(distinct nvl(dis.discount_amnt,0))/count(rec.consultant)) net_amnt,
      round((((sum(distinct nvl(rec.opd_amnt,0))-sum(distinct nvl(dis.discount_amnt,0))/count(rec.consultant) ) *
      max(dr_per))/100),0) dr_share,
      round((((sum(distinct nvl(rec.opd_amnt,0))-sum(distinct nvl(dis.discount_amnt,0))/count(rec.consultant) ) *
      max(100-dr_per))/100),0) hosp_share,
      count(distinct rec.consultant) net_dis
      from
      (
      select -- bokm_doc_dt receipt_date, bysalim
      bil_recept_no_a rcpt_no,
      fnc_org_sname(bokm_panel_comp_id) company,
      0 return_amnt,
      pr_mrno patient_mrno,pr_fname patient,
      bokm_doc_no docno,
      gcd_desc(bil_pay_mode_a) pay_mode,
      fnc_service_name(rslt_tst_code) service_name,
      dr_name consultant,
      pt_tst_rate opd_amnt,
      cons_share cons_share,
      (select max((nvl(rt_dr_share,0)*(100))/nvl(rt_amount,0))
      from hms_adm_dr_rt rt
      where dr.dr_id = rt.rt_dr_id
      and book.rslt_tst_code = rt.rt_scs_id) dr_per,
      dr_on_rent dr_rent,dr_share
      from hms_pat_pers pat,hms_lab_pat_mst pmst,hms_opd_book book,
      hms_pat_amnt amt,hms_adm_dr dr
      where pat.pr_mrno = pmst.bokm_mrno
      and pmst.bokm_mrno = book.rslt_mrno
      and pmst.bokm_doc_no = book.pt_pat_doc_no
      and pmst.bokm_mrno = amt.bil_mrnum_a
      and pmst.bokm_doc_no = amt.bil_docno_a
      and pmst.bokm_ref_conusltant_id = dr.dr_id
      and amt.bil_rcp_type_a = '075002'
      and pmst.bokm_pat_type in('PVT_OPD','CP_OPD')
      and amt.bil_void_a = 'N'
      and (pmst.bokm_user_dept_code != '039')
      and BOOK.CREATED_ON between '01-OCT-2011' and '17-OCT-2012'
      /* and (pat.pr_curr_cont_id = :P_CONT_ID or :P_CONT_ID = '000')
      and (pat.pr_curr_prvnc_id = :P_PRVNC_ID or :P_PRVNC_ID = '00')
      and (pat.pr_curr_city_id = :P_CITY_ID or :P_CITY_ID = '000')
      and (pat.pr_curr_area = :P_AREA_ID or :P_AREA_ID = '000')
      and (pat.pr_gender = :P_GENDER or :P_GENDER = 'A')
      and (pat.pr_marital_status = :P_MARITAL_STAT or :P_MARITAL_STAT = 'ALL')
      and (to_char(pmst.bokm_panel_comp_id) = :P_PANEL_COMP or :P_PANEL_COMP = 'ALL')
      and (pmst.bokm_ref_conusltant_id = :P_CONS or :P_CONS = 'ALL')
      and (decode(pmst.bokm_panel_comp_id,'1','PVT_IPD','CP_IPD') = :P_PAT_TYPE or :P_PAT_TYPE = 'ALL')
      &LPARA_RCPT_DT */
      ) rec,
      (
      select -- bokm_doc_dt receipt_date, bysalim
      pr_mrno patient_mrno,
      bokm_doc_no docno,
      nvl(bil_disc_amont_a,0) discount_amnt
      from hms_pat_pers pat,hms_lab_pat_mst pmst,hms_opd_book book,
      hms_pat_amnt amt
      where pat.pr_mrno = pmst.bokm_mrno
      and pmst.bokm_mrno = book.rslt_mrno
      and pmst.bokm_doc_no = book.pt_pat_doc_no
      and pmst.bokm_mrno = amt.bil_mrnum_a
      and pmst.bokm_doc_no = amt.bil_docno_a
      and amt.bil_rcp_type_a = '075001'
      and pmst.bokm_pat_type in('PVT_OPD','CP_OPD')
      and amt.bil_void_a = 'N'
      and (pmst.bokm_user_dept_code != '039')
      and BOOK.CREATED_ON between '01-OCT-2011' and '17-OCT-2012'
      /* and (pat.pr_curr_cont_id = :P_CONT_ID or :P_CONT_ID = '000')
      and (pat.pr_curr_prvnc_id = :P_PRVNC_ID or :P_PRVNC_ID = '00')
      and (pat.pr_curr_city_id = :P_CITY_ID or :P_CITY_ID = '000')
      and (pat.pr_curr_area = :P_AREA_ID or :P_AREA_ID = '000')
      and (pat.pr_gender = :P_GENDER or :P_GENDER = 'A')
      and (pat.pr_marital_status = :P_MARITAL_STAT or :P_MARITAL_STAT = 'ALL')
      and (to_char(pmst.bokm_panel_comp_id) = :P_PANEL_COMP or :P_PANEL_COMP = 'ALL')
      and (pmst.bokm_ref_conusltant_id = :P_CONS or :P_CONS = 'ALL')
      and (decode(pmst.bokm_panel_comp_id,'1','PVT_IPD','CP_IPD') = :P_PAT_TYPE or :P_PAT_TYPE = 'ALL')
      and BOOK.CREATED_ON between :P_RCPT_DTFR and :P_RCPT_DTTO

      -- and BOOK.CREATED_ON between :P_RCPT_DTFR and :P_RCPT_DTTO
      &LPARA_RCPT_DT */
      ) dis
      where rec.patient_mrno = dis.patient_mrno (+)
      and rec.docno = dis.docno (+)
      --and patient = 'SHAHMEER'
      group by rec.consultant, --rec.receipt_date, bysalim
      rec.patient_mrno,rec.patient,
      pay_mode,service_name,rec.docno,
      rcpt_no,company
      order by rcpt_no,rec.consultant
      ) cns
      group by cns.consultant
      order by 1

      Edited by: user6431550 on Nov 15, 2012 3:10 AM