Optimize Slow-Running Quick Report

Try running this segment (from the NVL) by itself and see how long it takes. At a glance it appears that it will do multiple full table traverses.

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' )

thx. When I run that for just one part#, it runs fine; but w/o it it hangs and doesn’t complete with results.

Also, the date range is always 12 months from the starting point.

Do I need to further break it out using IAL’s, with one containing just Order_Total and Order_Qty?

two things:

1. run the whole query without the NVL statements and see if you get a performance improvement.
2. Then add one NVL statement back in and see what happens
3. The where clause in the NVL statements may be causing you some issues. where ( l1p.contract = 'W' and l1f.ms_set = cic.cf$_ms_set or l1p.contract <> 'W' ) 

In the where clause you are saying where contract =’W’ or contract <> ‘W’?  Is that correct?

I did that and know definitely that it is due to the both NVL() statements. I’m not sure why the where clause would be a performance hit in IFS? For each Part# in the main result set it’s pulling/totaling all sales for just that parts, customer, contract, and selected month.

I’ve had other quick reports where using an API causes delays.

Hi DevBob,

yes - an API will cause repeated database access so can slow down the execution.

You where clause has “where contract =’W’ or contract <> ‘W’. That suggests any value for W is acceptable for the query (this will cause full table reads: everything where contract= “W”  + everything where contract is anything else) .

Which contract do you want to be selecting for? Only ‘W’?

I am going into a meeting for an hour and will have a look at it 

N

I’m reading forecasted parts and trying to retrieve historical sales.

The link between the two is Forecast.MS Set = Historical_Sales_cfv.customfield(MS Set). but this business rule applies only for Contract = ‘W’.  If it’s run for a non-W contract part then don’t link on the Ms Set custom field (just pull all sales for that contract/part/ and selected MONTH).

Example:

user selects-

Site: S20

Part: SA-807

Forecast MS Set: 1331

StartingMonth: 3/1/2021

...for each forecast () part found, pull monthly HistoricalSales (we’re using CUST_ORD_INVO_STAT) for S20, SA-807 starting in March, so the results could be:

March: 300

April: 200

May: 400

etc. ...

partial code:

...
FROM REGA1APP.LEVEL_1_PART     l1p

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
...

fyi, I tried replacing the two NVL() statements with an ILA, but still get the performance issue:

IAL:

select
col.contract as Contract,
col.part_no as Part_No,
col.customer_no as Customer_No,
to_char(col.invoice_date,'yyyymm') as Year_Month,

ROUND( SUM( NULLIF(invoiced_qty * sale_unit_price,0) ), 2) AS Order_Total,
ROUND( SUM( NULLIF(invoiced_qty ,0)), 2)  AS Order_Qty

from rega1app.cust_ord_invo_stat  col

left join rega1app.customer_info_cfv cic
    on cic.customer_id = col.customer_no

where invoice_date >= to_date('1/1/2018', 'MM/DD/YYYY')
  and invoiced_qty > 0 
  and invoiced_qty IS NOT NULL

group by Contract, Part_No, Customer_No, to_char(col.invoice_date,'yyyymm')

order by 1, 2, 3, 4, 5

I always recommend putting parentheses around clauses when you mix AND and OR, but that piece of this query is right. OR has a higher precedence than AND. If you ever forget, you can ask the database by running an experiment.

SELECT * FROM (
SELECT 'A' AS col1, 'Z' AS col2 FROM DUAL UNION ALL
SELECT 'A' AS col1, 'Y' AS col2 FROM DUAL UNION ALL
SELECT 'B' AS col1, 'X' AS col2 FROM DUAL UNION ALL
SELECT 'B' AS col1, 'W' AS col2 FROM DUAL
)
WHERE col1 = 'A' AND col2 = 'Z' OR col1 != 'A';

/*
Returns:
A Z
B X
B W
*/

SELECT * FROM (
SELECT 'A' AS col1, 'Z' AS col2 FROM DUAL UNION ALL
SELECT 'A' AS col1, 'Y' AS col2 FROM DUAL UNION ALL
SELECT 'B' AS col1, 'X' AS col2 FROM DUAL UNION ALL
SELECT 'B' AS col1, 'W' AS col2 FROM DUAL
)
WHERE col1 = 'A' AND col2 = 'Z' OR col1 != 'A';

/*
Returns:
A Z
B X
B W
*/

good idea- thx!

If you’re going to extract the YEAR and MONTH from a field that’s of DATE type, there’s no need to wrap the DATE field in TO_DATE first.

If you need BOTH the year and month, you might be better off truncating to the month to include both the year and month in the clause at the same time.

and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM')

After you change the query like that, try these function-based indexes (in a nonproduction environment first):

CREATE INDEX c_cust_ord_inv_stat_invdate_ix ON cust_ord_invo_stat_tab (TRUNC(invoice_date, 'MM')) TABLESPACE ifsapp_index;

CREATE INDEX c_level_1_fcast_ms_date_ix ON level_1_forecast_tab (TRUNC(ms_date, 'MM')) TABLESPACE ifsapp_index;

       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
-- changed:
               and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM')
--
             where l1p.contract = 'W'
               and l1f.ms_set = cic.cf$_ms_set
                or l1p.contract <> 'W'), 0) AS Order_Total

Thx. I removed the to_date().

We don’t have PL/SQL access to do CREATE INDEX statements. We do have Oracle SQL Developer.

You can create indexes with Oracle SQL Developer or with SQL*Plus if you have access to log in as your application owner (REGA1APP). You don’t need proprietary tools for this.

You can also log in as your application owner if you have access to log in as IFSSYS, since your app owner is going to be a Foundation user, too.

sqlplus ifssys[ifsapp]/ifssys_password@tns_name

ok, thx, I’ll try that.

As general, if result sets is as small as 5 rows, I’m rather using API to fetch some single values than joining additional view. I meant pm.Part_Main_Group, ip.description. Please also take into account that using left join and then in where clause to look for values in left-joined view it actually become INNER JOIN.

In case of using two nested select statement which take data from the same data source, I’d rather join single select statement.

and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM')

CREATE INDEX c_cust_ord_inv_stat_invdate_ix ON cust_ord_invo_stat_tab (TRUNC(invoice_date, 'MM')) TABLESPACE ifsapp_index;

CREATE INDEX c_level_1_fcast_ms_date_ix ON level_1_forecast_tab (TRUNC(ms_date, 'MM')) TABLESPACE ifsapp_index;

Hmm, I must be missing something; I have two questions on this:

1.) How does TRUNC(date,’MM’) include both year and month? Wouldn’t it just return ‘01’, ‘02’, … ‘11’, ‘12’? Why wouldn’t it be TRUNC(date,’YYYYMM’)?

2.) I put TRUNC(Date,’MM’) in a SQL and it returned the entire date, including day and year when I thought it would just return the month; what am I not understanding?

...I get it now; it truncates down to just that portion- YYYY or MM. Nice!   :)

and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM')

CREATE INDEX c_cust_ord_inv_stat_invdate_ix ON cust_ord_invo_stat_tab (TRUNC(invoice_date, 'MM')) TABLESPACE ifsapp_index;

CREATE INDEX c_level_1_fcast_ms_date_ix ON level_1_forecast_tab (TRUNC(ms_date, 'MM')) TABLESPACE ifsapp_index;

I created the indexes; now will try a query using them...

When I try to query off of that new index I get this error:

I also tried the prefix ifsinfo..

When I try to query the new indexes I do see them:

Tried to grant access to the INDEX:

If I use the _TAB table name in my SQL that it finds it but now it doesn’t have sufficient priviledges:

The IFS views are stored queries against base tables.

When you create an index, you create the index against the table, not against views.

When you query a view, Oracle rewrites your query so it looks against the base tables.

So that means: You shouldn’t have to change your query to take advantage of an index. If your query against the views didn’t speed up, then it likely didn’t do anything for you.

There’s a chance you need to gather table statistics, but in newer versions of Oracle, it generally does that for you when you create an index.

Thx, Durette; that fills out my understanding.

Re-writing the query as suggested above took care of the performance issue, along with Durette’s reply:

and TRUNC(col.invoice_date, 'MM') = TRUNC(l1f.ms_date, 'MM')

Thx, everyone for your input! Many good ideas!