Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 7th June 2013, 10:51
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,320
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
Query Performance
Baan: ERP LN 6.1 FP7
C/S: Both

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.
Attached Files
File Type: bc script.bc (2.3 KB, 61 views)
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!

Last edited by bhushanchanda : 7th June 2013 at 11:34.
Reply With Quote
  #2  
Old 7th June 2013, 14:46
vamsi_gujjula's Avatar
vamsi_gujjula vamsi_gujjula is offline
Senior Member
 
Join Date: Nov 2011
Location: hyderabad ; india
Posts: 240
vamsi_gujjula is on a distinguished road
Baan: ERPLN fp3 and 10.4 - DB: oracle - OS: Aix , Redhat linux 6
Call Graph Profiler mode

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
Attached Files
File Type: doc indexselection.doc (42.0 KB, 72 views)
Reply With Quote
Sponsored Links
  #3  
Old 8th June 2013, 00:32
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
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 -
Code:
$ 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.
Quote:
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
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao

Last edited by NPRao : 8th June 2013 at 00:47.
Reply With Quote
  #4  
Old 8th June 2013, 07:10
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,320
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
Hi Prashant,

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

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Performance stecorp Tools Development 6 16th April 2013 07:07
Query performance suhas-mahajan Tools Development 5 21st April 2011 15:13
Performance issue with query mgakhar Tools Development 8 22nd August 2007 21:17


All times are GMT +2. The time now is 00:50.


©2001-2017 - Baanboard.com - Baanforums.com