Solved

Quick Report / IAL Performance

  • 1 February 2021
  • 3 replies
  • 416 views
D
Rank
Userlevel 4
Badge +10
  • DevBob
  • Sidekick (Customer)
  • 119 replies

We have a question about performance in quick reports that use one or more IAL’s.

Let’s say we have 3 IALs over CustomerOrderLines (and other tables but this is the main one), each building upon the previous one. In the 3rd IAL that the quick report uses, we want to pull the DistrictCode that is in CUST_ORD_CUSTOMER_ADDRESS_ENT.

Will it affect performance if we join CUST_ORD_CUSTOMER_ADDRESS_ENT in the 1st IAL or the quick report?

Any best practice to follow on something like this?

 

icon

Best answer by william.klotz 2 February 2021, 18:27

View original
This topic has been closed for comments

3 replies

william.klotz
Rank
Userlevel 7
Badge +21

Hi @DevBob ,

 

We would develop the IAL or Quick Report to utilize just one select statement which would contain all the joins required to provide the entire data sent in one query.  We have found our system performance is better with one query.  Now the query must be well written and utilize indexed fields in the joins and where statements when possible or else you could end up walking the table.   We have several queries written which join customer orders, customer address and customer order lines.  

 

In our case we would join the customer order header and customer address ent and we’d join customer order header and customer order lines.  We would do it this way because we never have customer order lines going to different addresses on the same order so our district would be the same for all order lines on the same order.

 

Regards,

William Klotz

D
Rank
Userlevel 4
Badge +10

Thx, William, that makes sense.

Since IFS doesn’t provide us much with DB schemas, we’re kind of left in the dark trying to performance-tune IAL / quick reports.

When you say indexed fields how would we identify those on a table? IFS doesn’t even identify key fields.

Does the suffix of tables means anything performance-wise? I understand _JOIN_, _CFV

william.klotz
Rank
Userlevel 7
Badge +21

Hi @DevBob ,

 

A view with a suffix of _CFV means you’ve added a custom field to a screen.  One of the tasks IFS performs when adding a custom field is to generate a custom view so you can extract the data from your custom fields.

 

I’m not sure how others determine the primary keys and indexed fields but here’s how we do it.  We’ll open a screen lets say customer order  then click on the system info at the bottom of the screen so we can see the view name.  As a rule if you add _TAB to the end of the view you’ll know the table where the data is actually stored.   We then use PL/SQL Developer to look at the CUSTOMER_ORDER_TAB table in view only mode so nothing is changed.  

 

In PL/SQL we are able to see the column names, keys and indexes.  So if we look at the keys on the CUSTOMER_ORDER_TAB we’ll see the column ORDER_NO is the primary key.   If we look at the indexes screen we’ll see we have indexes on the following columns.   CUSTOMER_NO, ROWSTATE, CUSTOMER_NO_PAY, PRE_ACCOUNTING_ID, CONTRACT, ORDER_NO, ROWKEY and TEXT_ID$.  So if we utilize these columns in our join and where statement we’ll know IFS has indexes on them and performance will be improved.

 

Regards,

William Klotz

Terms & ConditionsCookie settings