Hello,

I am trying to get the Total Sell Quantity for each Item against each Business Partner in a particular date range.


I have wrote the script, but its taking a huge time, and finally I just end in killing the process. I have tried to use indexes wherever possible but still no significant results. For just 10 days the report takes a lot of time, I am not daring to go for a month data.


I have attached the script, if anyone could help me out, I will be glad.

hi Bhushan,

you can use Call Graph Profiler mode , to get the details of queries execution time and can zero down to the query taking the longest time

i also attached a doc that i downloaded on baanboard,i hope that would be helpful although it was for baan iv and v

regards
vamsi

Bhushan,

The call graph profiler will give you more detailed information to determine which specific sql code is taking more time or you can look up the DBSLOG or

Alternatively, you can use this option to validate at high level -


$ cat lmssq.sql
select fmlbd400.load:load,fmlbd350.orno:order,fmlbd350.fono:line
from fmlbd400,fmlbd300,fmlbd350
where fmlbd400._index2 = {"WORKPLAN", "640001204"}
and fmlbd300.load refers to fmlbd400
and fmlbd350.shpm refers to fmlbd300
hint use index 2 on fmlbd300
and use index 1 on fmlbd350
and use index 2 on fmlbd400

$ cat lmss751.ksh
export LMS_ID=lmss
. /app/lms/common/baanenv lmss #set Baan environment
export ORAPROF=0
qptool6.2 -f lmssq.sql -c 751

Check the records count in those 4 tables. The tdsls400, 401 are typically very big tables, make sure the db-stats are up to date for a query to run efficiently.
Based on the example, I posted, add the tdsls400 with some fixed values and see how it is performing and then the other query for the table tdsls401. Also, change code to use the - "hint use index" clause.

Also Refer to - Application Performance Guide - M2017 B US.pdf - Chapter 6- Profiling and Tracing.

Nested queries
Problem

Nesting queries can reduce performance dramatically.
Nested query:
select orders.ordernumber, orders.fld1, ...
from orders
where orders.ordernumber inrange :order.f and :order.t
and orders.status = released
selectdo
select orderlines.fld1, orderlines.fld2, ...
from orderlines
where orderlines.ordernumber = :orders.ordernumber
selectdo

Solution

Join nested queries into one -more complex- query.
select orderlines.fld1, orderlines.fld2, ...,
orders.ordernumber, orders.fld1, ...
from orderlines, orders
where orderlines.ordernumber inrange :order.f and :order.t
and orders.ordernumber = orderlines.ordernumber
and orders.status = released
selectdo

Note
There are some attention points.
The higher the number of records at a high level in relation to the number of records at lower level, the better is the joined way of implementation.
The higher the number of selected fields (and its length is related) at a high level in relation to the number at lower level, the better the nested way of implementation.
Using the above example:
Number of orders is X, number of fields in orders is A.
Number of order lines is Y, number of fields in order lines is B.
If A is much higher than B, it is preferable to implement the nested way.
If B is much higher than A, it is preferable to implement the joined way.
If X is much higher than Y, it is preferable to implement the joined way.
If Y is much higher than X, the joined as well the nested way can be implemented.
As can be concluded, one cannot always say for sure which construction is the best.

You can also add the time command to the shell script -
time ORAPROF=0.0 qptool6.2 -f query_to_trace -c 123 2>/dev/null

Hi Prashant,

Thank you very much. Everything you said is valuable. I will try to modify my script using all your suggestions.