Performance Issues Using APEX_ITEM in SQL?
Hello,
I was wondering if using APEX_ITEM.* in your SQL source for a Report would give some performance issues? I would expect the report to bring back just over 3000 records. When I developed this it was running fine but we only had 150 records or so but now that we have migrated the application over to our test system the page will take about 2 minutes to load. Here is my SQL to create the report:
select distinct
initcap(mpa.pa_name) || ' (' || sd.DESIGNATION_CODE || ')' site,
frc.REPORT_DESCRIPTION report_category,
mf.FEATURE_DESC feature,
decode(cmf.SELECTED_FOR_QA, 'Y', 'X', 'N', ' ') QA,
apex_item.select_list_from_query (
21,
cpf.ASSIGN_TO,
'select ss.firstname || '' '' || ss.surname d, ss.STAFF_NUMBER r
from snh_staff ss,
snh_management_units smu,
m_pa_snh_area psa
where ss.MU_UNIT_ID = smu.UNIT_ID
and smu.UNIT_ID = psa.UNIT_ID
and ss.CURRENTLY_EMPLOYED = ''Y''
and psa.SCM_LEAD = ''Y''
and psa.MAIN_AREA = ''P''
and psa.PA_CODE = ' || mpa.pa_code,
null,
'YES',
NULL,
' ') assign_to,
apex_item.select_list_from_query (
22,
decode(to_char(cpf.planned_fieldwork, 'DD/MM/'),
'30/06/', 'Q1 ' || to_char(planned_fieldwork, 'YYYY'),
'30/09/', 'Q2 ' || to_char(planned_fieldwork, 'YYYY'),
'31/12/', 'Q3 ' || to_char(planned_fieldwork, 'YYYY'),
'31/03/', 'Q4 ' || to_char(planned_fieldwork-365, 'YYYY'),
to_char(cpf.planned_fieldwork, 'YYYY')),
'select d, r
from CM_CYCLE_Q_YEARS') planned_fieldwork,
apex_item.select_list_from_query (
23,
decode(to_char(cpf.planned_cmf, 'DD/MM/'),
'30/06/', 'Q1 ' || to_char(planned_cmf, 'YYYY'),
'30/09/', 'Q2 ' || to_char(planned_cmf, 'YYYY'),
'31/12/', 'Q3 ' || to_char(planned_cmf, 'YYYY'),
'31/03/', 'Q4 ' || to_char(planned_cmf-365, 'YYYY'),
to_char(cpf.planned_cmf, 'YYYY')),
'select d, r
from CM_CYCLE_Q_YEARS') planned_cmf,
apex_item.select_list_from_query (
24,
cpf.monitoring_method_id,
'select METHOD, MONITORING_METHOD_ID from cm_monitoring_methods where active_flag = ''Y''') monitoring_method,
apex_item.text (
25,
cpf.pre_cycle_comments,
15,
255,
'title="'||cpf.pre_cycle_comments||'"',
'annualPlanningComments'
||to_char(cpf.PLAN_MON_FEATURE_ID)) comments,
apex_item.text (
26,
to_char(cpf.CONTRACT_LET,'DD-MON-YYYY'),
11,
11) contract_let,
apex_item.text (
27,
to_char(cpf.CONTRACT_REPORT_PLANNED,'DD-MON-YYYY'),
11,
11) contract_report,
apex_item.text (
28,
cpf.ADVISOR_DATA_ENTRY,
11,
11) advisor_entry,
cms.complete_percentage || ' ' || cms.description status,
apex_item.text (
29,
to_char(cpf.RESULT_SENT_TO_OO,'DD-MON-YYYY'),
11,
11) result_to_oo,
cpf.PLAN_MON_FEATURE_ID,
cmf.MONITORED_FEATURE_ID,
mpa.PA_CODE,
mpf.SITE_FEATURE_ID
from fm_report_category frc,
m_feature mf,
m_pa_features mpf,
m_protected_area mpa,
snh_designations sd,
cm_monitored_features cmf,
cm_plan_mon_features cpf,
cm_monitoring_status cms,
cm_cycles cc,
m_pa_snh_area msa,
snh_management_units smu,
snh_sub_areas ssa
where frc.REPORT_CATEGORY_ID = mf.REPORT_CATEGORY_ID
and mf.FEATURE_CODE = mpf.FEATURE_CODE
and mpa.PA_CODE = mpf.PA_CODE
and mpa.DESIGNATION_ID = sd.DESIGNATION_ID
and mpf.SITE_FEATURE_ID = cmf.SITE_FEATURE_ID
and cmf.MONITORED_FEATURE_ID = cpf.MONITORED_FEATURE_ID
and cms.MONITORING_STATUS_ID = cmf.MONITORING_STATUS_ID
and cc.CYCLE# = cmf.CYCLE#
and msa.PA_CODE = mpa.PA_CODE
and msa.UNIT_ID = smu.UNIT_ID
and msa.SUB_AREA_ID = ssa.SUB_AREA_ID
and cc.CURRENT_CYCLE = 'Y'
and msa.MAIN_AREA = 'P'
and msa.SCM_LEAD = 'Y'
and mpf.INTEREST_CODE in (1,2,3,9)
and ((nvl(:P6_REPORTING_CATEGORY, 'ALL') = 'ALL'
and to_char(frc.FCA_FEATURE_CATEGORY_ID) = case nvl(:P6_BROAD_CATEGORY, 'ALL') when 'ALL' then to_char(frc.FCA_FEATURE_CATEGORY_ID) else :P6_BROAD_CATEGORY end)
or (nvl(:P6_REPORTING_CATEGORY, 'ALL') != 'ALL'
and to_char(mf.REPORT_CATEGORY_ID) = case nvl(:P6_REPORTING_CATEGORY, 'ALL') when 'ALL' then to_char(mf.REPORT_CATEGORY_ID) else :P6_REPORTING_CATEGORY end))
and ((nvl(:P6_SNH_SUB_AREA, 'ALL') = 'ALL'
and to_char(msa.UNIT_ID) = case nvl(:P6_SNH_AREA, 'ALL') when 'ALL' then to_char(msa.UNIT_ID) else :P6_SNH_AREA end)
or (nvl(:P6_SNH_SUB_AREA, 'ALL') != 'ALL'
and to_char(msa.SUB_AREA_ID) = case nvl(:P6_SNH_SUB_AREA, 'ALL') when 'ALL' then to_char(msa.SUB_AREA_ID) else :P6_SNH_SUB_AREA end ))
and ((nvl(:P6_SITE, 'ALL') != 'ALL'
and mpa.PA_CODE = :P6_SITE)
or nvl(:P6_SITE, 'ALL') = 'ALL')
As you can see I have 9 calls to the APEX_ITEM API and when I take them out the report works as I would expect it to.
Has anybody else came across this issue?
We are currently on APEX: 3.0.1.00.08 and using Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production Database.
Thanks in advance,
Paul.