We have quick report that takes excessively long to execute and return results. Any ideas on increasing performance?
There’s only 5 rows in the result set (see below).
select
pm.Part_Main_Group as Trademark,
l1p.contract,
l1p.part_no,
ip.description,
l1f.ms_set as MS_Set,
trunc(l1f.ms_date) as Forecast_Date,
extract( MONTH from to_date(l1f.ms_date)) as Month,
to_char(l1f.ms_date,'yyyymm') as YearMonth,
l1f.forecast_lev0 as Forecast_Qty,
NVL((select sum(rega1app.Customer_Order_API.Get_Ord_Gross_Amount(ORDER_NO))
from rega1app.customer_info_cfv cic
left join rega1app.cust_ord_invo_stat col
on l1p.contract = col.contract and l1p.part_no = col.part_no and cic.customer_id = col.customer_no
and ( extract( YEAR from to_date(col.invoice_date)) = extract( YEAR from to_date(l1f.ms_date)) )
and ( extract( MONTH from to_date(col.invoice_date)) = extract( MONTH from to_date(l1f.ms_date)) )
where ( l1p.contract = 'W' and l1f.ms_set = cic.cf$_ms_set or l1p.contract <> 'W' )
),0) AS Order_Total,
NVL((select sum(invoiced_qty)
from rega1app.customer_info_cfv cic
left join rega1app.cust_ord_invo_stat col
on l1p.contract = col.contract and l1p.part_no = col.part_no and cic.customer_id = col.customer_no
and ( extract( YEAR from to_date(col.invoice_date)) = extract( YEAR from to_date(l1f.ms_date)) )
and ( extract( MONTH from to_date(col.invoice_date)) = extract( MONTH from to_date(l1f.ms_date)) )
where ( l1p.contract = 'W' and l1f.ms_set = cic.cf$_ms_set or l1p.contract <> 'W' )
),0) AS Order_Qty
FROM REGA1APP.LEVEL_1_PART l1p
left join REGA1APP.part_catalog pm
on l1p.part_no = pm.part_no
left join REGA1APP.Inventory_part ip
on l1p.contract = ip.contract and l1p.part_no = ip.part_no
left join REGA1APP.MS_SETS_ORDER_BY_1_DEF msod
on l1p.contract = msod.contract and l1p.part_no = msod.part_no
left join REGA1APP.LEVEL_1_FORECAST l1f
on msod.contract = l1f.contract and msod.part_no = l1f.part_no and msod.ms_set = l1f.ms_set
where l1f.ms_set not in ('3')
and l1f.contract = 'W'
and l1f.part_no = 'CX111-SG'
and l1f.ms_set = '10256'
and l1f.ms_date >= to_date('8/1/2020', 'MM/DD/YYYY')
and l1f.ms_date <= to_date('12/31/2020', 'MM/DD/YYYY')
order by 1, 2, 3, trunc(l1f.ms_date)
![](https://uploads-eu-west-1.insided.com/ifs-en/attachment/f33cc0a5-fc24-4e39-aa44-ef2430cf8c2d.png)