coldfusion - Oracle CLOB slow running report -
we have report pulls data oracle 10g database. using coldfusion 9 display report. report contains 3 clob columns killing report processing time. tested removing 3 clob columns , report displays in 2 - 3 seconds.
is there can done increase processing time of report? understand why running slow due how large clob fields are. looking improve processing time.
just give idea of rows typical report returns 200-300 row small amount of rows.
many thanks
matthew
update - have tried using dbms_lob.substr() , return max 4000 character not improve processing time.
here's query:
select r.nc_request_id request_id, r.tc_request_name request_name, r.tc_request_name_2 request_name_2, r.tc_initiator initiator, r.nc_expense expense, s.nc_form_step_id form_step_id, s.nc_form_id form_id, s.nc_step_id step_id, s.tc_step_code step_code, s.tc_step_name step_name, to_char(r.dc_submit_date,'dd-mon-yyyy') submit_date, to_char(r.dc_original_date,'dd-mon-yyyy') original_date, to_char(r.dc_revision_date,'dd-mon-yyyy') revision_date, r.nc_bus_group_id_fk bus_group_id, r.nc_bus_unit_id_fk bus_unit_id, r.nc_bus_sub_id_fk bus_sub_id, r.tc_act_number act_number, r.tc_request_name request_name, r.nc_year year_num, r.nc_act_end_number act_end_number, r.dc_start_date start_date, r.dc_end_date end_date, r.nc_act_type_id_fk act_type_id_fk, to_char(r.dc_approval_date,'dd-mon-yyyy') approval_date, cc_attached_exhibit attached_exhibit, cc_routed routed, r.ic_restricted restricted, roi.nc_pi_off pi_off, roi.nc_pi_ca cost_avoidance_pi, (select count(nc_request_id_fk) tbxs31_remove_section nc_request_id_fk = r.nc_request_id , nc_template_id_fk in(346,1455,1456,1458,1459,1460,1464.1641) group nc_request_id_fk) count_of_remove_sections, <cfoutput query="variables.qryenergylistitems"> (select nvl(ed.nc_existingusage - ed.nc_proposedusage,0) tbyb28_si_energy_data ed,tbxp73_list_items li li.nc_value_id = ed.nc_energyform(+) , li.nc_value_id = #variables.qryenergylistitems.nc_value_id# , ed.nc_request_id_fk = r.nc_request_id , li.ic_active = 't' , li.nc_value_id <> 808) "#variables.qryenergylistitems.tc_med_desc#", </cfoutput> (select tc_med_desc tbxp73_list_items nc_value_id = sd.nc_businessunit) tc_bus_unit, (select tc_med_desc tbxp73_list_items nc_value_id = sd.nc_businesssubunit) tc_bus_sub_unit, (select tc_med_desc tbxp73_list_items nc_value_id = sd.nc_site) tc_bus_site, (select nc_proposedghg - nc_existingghg tbyb27_sustainability_data nc_request_id_fk = r.nc_request_id) annual_impact_c02e, (select decode(tbyb14_si_baseline.nc_base_co2e , 0, null, ((tbyb27_sustainability_data.nc_proposedghg - tbyb27_sustainability_data.nc_existingghg) / tbyb14_si_baseline.nc_base_co2e) * 100) tbyb14_si_baseline,tbyb27_sustainability_data tbyb27_sustainability_data.nc_request_id_fk = r.nc_request_id , tbyb27_sustainability_data.nc_site = tbyb14_si_baseline.nc_site_id_fk(+) , tbyb27_sustainability_data.nc_baselineyear = tbyb14_si_baseline.nc_year(+) ) pct_impact_co2e, (select nc_proposedwaste - nc_existingwaste tbyb27_sustainability_data nc_request_id_fk = r.nc_request_id) annual_impact_waste, (select decode(tbyb14_si_baseline.nc_base_waste, 0, null, ((tbyb27_sustainability_data.nc_proposedwaste - tbyb27_sustainability_data.nc_existingwaste) / tbyb14_si_baseline.nc_base_waste) * 100) tbyb14_si_baseline,tbyb27_sustainability_data tbyb27_sustainability_data.nc_request_id_fk = r.nc_request_id , tbyb27_sustainability_data.nc_site = tbyb14_si_baseline.nc_site_id_fk(+) , tbyb27_sustainability_data.nc_baselineyear = tbyb14_si_baseline.nc_year(+) ) pct_impact_waste, (select nc_proposedwater - nc_existingwater tbyb27_sustainability_data nc_request_id_fk = r.nc_request_id) annual_impact_water, (select decode(tbyb14_si_baseline.nc_base_water, 0, null, ((tbyb27_sustainability_data.nc_proposedwater - tbyb27_sustainability_data.nc_existingwater) / tbyb14_si_baseline.nc_base_water) * 100) tbyb14_si_baseline,tbyb27_sustainability_data tbyb27_sustainability_data.nc_request_id_fk = r.nc_request_id , tbyb27_sustainability_data.nc_site = tbyb14_si_baseline.nc_site_id_fk(+) , tbyb27_sustainability_data.nc_baselineyear = tbyb14_si_baseline.nc_year(+) ) pct_impact_water, (select tbxs32_text_sections.tc_text tbxs32_text_sections tbxs32_text_sections.nc_request_id_fk = r.nc_request_id , tbxs32_text_sections.nc_template_id_fk = 570 , nc_subsection_id = 0) proposal_text, (select tbxs32_text_sections.tc_text tbxs32_text_sections tbxs32_text_sections.nc_request_id_fk = r.nc_request_id , tbxs32_text_sections.nc_template_id_fk = 1456 , nc_subsection_id = 0) comments_explanations, (select tbxs32_text_sections.tc_text tbxs32_text_sections tbxs32_text_sections.nc_request_id_fk = r.nc_request_id , tbxs32_text_sections.nc_template_id_fk = 1458 , nc_subsection_id = 0) comments_on_add_si_impacts, <!--- capital ---> ( select sum(nvl((nc_quantity * cost) * (1 + nc_tax_rate),0)) ( select e.*, nvl(e.mc_cost,0) * from_curr.nc_exchange_rate / to_curr.nc_exchange_rate cost, e.mc_maint_cost * from_curr.nc_exchange_rate / to_curr.nc_exchange_rate maint_cost tbxs23_expenditures e, tbxs41_exchange_rate_cit from_curr, tbxs41_exchange_rate_cit to_curr, tbxp78_requests r, v_tbxs13_steps ss r.nc_act_type_id_fk != 40 , r.nc_form_step_id_fk = ss.nc_form_step_id , e.nc_request_id_fk = r.nc_request_id , to_curr.nc_currency_id = <cfqueryparam value="#session.objuser.getcurrencypreference()#" cfsqltype="cf_sql_numeric"> , e.nc_currency_id_fk = from_curr.nc_currency_id , e.nc_expenditure_type_id_fk = 83 , r.dc_approval_date between to_curr.dc_valid_from , to_curr.dc_valid_to , r.dc_approval_date between from_curr.dc_valid_from , from_curr.dc_valid_to ) nc_request_id_fk = r.nc_request_id )as capital, <!--- capital ---> <!--- project expense 1 time ---> ( select sum(nvl((nc_quantity * cost) * (1 + nc_tax_rate),0)) ( select e.*, nvl(e.mc_cost,0) * from_curr.nc_exchange_rate / to_curr.nc_exchange_rate cost, e.mc_maint_cost * from_curr.nc_exchange_rate / to_curr.nc_exchange_rate maint_cost tbxs23_expenditures e, tbxs41_exchange_rate_cit from_curr, tbxs41_exchange_rate_cit to_curr, tbxp78_requests r, v_tbxs13_steps ss r.nc_act_type_id_fk != 40 , r.nc_form_step_id_fk = ss.nc_form_step_id , e.nc_request_id_fk = r.nc_request_id , to_curr.nc_currency_id = <cfqueryparam value="#session.objuser.getcurrencypreference()#" cfsqltype="cf_sql_numeric"> , e.nc_expenditure_type_id_fk = 84 , e.nc_currency_id_fk = from_curr.nc_currency_id , r.dc_approval_date between to_curr.dc_valid_from , to_curr.dc_valid_to , r.dc_approval_date between from_curr.dc_valid_from , from_curr.dc_valid_to ) nc_request_id_fk = r.nc_request_id )as project_expense <!--- project expense 1 time ---> tbxp78_requests r, v_tbxs13_steps s, tbxs39_roi roi, tbyb27_sustainability_data sd r.nc_form_step_id_fk = s.nc_form_step_id , r.nc_request_id = roi.nc_request_id_fk(+) , r.nc_request_id = sd.nc_request_id_fk(+) , r.nc_act_type_id_fk != 40 these clob fields:
(select tbxs32_text_sections.tc_text tbxs32_text_sections tbxs32_text_sections.nc_request_id_fk = r.nc_request_id , tbxs32_text_sections.nc_template_id_fk = 570 , nc_subsection_id = 0) proposal_text, (select tbxs32_text_sections.tc_text tbxs32_text_sections tbxs32_text_sections.nc_request_id_fk = r.nc_request_id , tbxs32_text_sections.nc_template_id_fk = 1456 , nc_subsection_id = 0) comments_explanations, (select tbxs32_text_sections.tc_text tbxs32_text_sections tbxs32_text_sections.nc_request_id_fk = r.nc_request_id , tbxs32_text_sections.nc_template_id_fk = 1458 , nc_subsection_id = 0) comments_on_add_si_impacts,
Comments
Post a Comment