Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Performance & Tuning

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 15th September 2003, 15:50
srinivas's Avatar
srinivas srinivas is offline
Senior Member
 
Join Date: Nov 2001
Location: South India
Posts: 213
srinivas is on a distinguished road
Baan: Baan4,Baan ERP - DB: Oracle, Informix, SQL Server, Baanbase - OS: Unix, Solaris, NT, Win 2K
how to get the best results??

Assume that I have a baan table xxyyyzzz.
It has many fields.
Index1 has A, B, C, D fields.
Index2 has B,D fields.
Index3 has C, D fields

Now I have a report where the input fields are A, B, C, D.

The user can input any/all of these fields.

Now in the query I used this statement.
Select xxyyzzz.*
From xxyyzzz
Where xxyyyzzz._index1 inrange {:A.f,:B.f,:C.f,:d.f} and {:A.t,:B.t,:C.t,:d.t}
Selectdo
.......processing and printing.......
Endselect


Now this query is not that efficiently working.

If user selects some value for A field the it works fast. If he gives input for B or C field alone it takes longer time.

Should I use multiple queries with different indexes based on what he inputs.

For example
***********

A.t="someval" A.t="otherval"
B.f="" B.t="zzzz"
C.f="" C.t="zzzz"
.....

Use index3

****************

A.t="" A.t="zzzz"
B.f="" B.t="zzzz"
C.f="someval" C.t="otherval"
.....

Use index3
*************

A.t="" A.t="zzzz"
B.f="someval" B.t="otherval"
C.f="" C.t="zzzz"
.....

Use index2
*************

In some cases even I have found that index4, index5 etc will not be as fast as index1. Why so???
__________________
Regards,
Srinivas
Reply With Quote
Sponsored Links
  #2  
Old 15th September 2003, 16:02
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
You say the table has many fields. Do you mean many records, or that there are many more fields than the four mentioned? If this last is true, the first improvement would be to change the xxyyyzzz.* to xxyyyzzz.a,xxyyyzzz.b,....
__________________
Regards,
Nico
Reply With Quote
  #3  
Old 15th September 2003, 16:29
zardoz's Avatar
zardoz zardoz is offline
Guru
 
Join Date: Jan 2003
Location: Torino, Italy
Posts: 445
zardoz is on a distinguished road
Baan: Triton 2, Triton 3, Baan IV, Baan ERP, Baan LN - DB: Oracle, Tbase, MS Sql - OS: Unix, Windows
The other indexes are defined as "unique key" or "repeating key"?
In the last case, you can experiment performance slowing, specially when deleting records.
Looking at your example, the 2nd and 3rd index seems to be repeating...
You can always obtain unique keys by adding at the end of the index part of the first key.
By example:

index2 : B, D, A, C
index3 : C, D, A, B
Reply With Quote
  #4  
Old 15th September 2003, 16:34
srinivas's Avatar
srinivas srinivas is offline
Senior Member
 
Join Date: Nov 2001
Location: South India
Posts: 213
srinivas is on a distinguished road
Baan: Baan4,Baan ERP - DB: Oracle, Informix, SQL Server, Baanbase - OS: Unix, Solaris, NT, Win 2K
The table has 500,000 records.

The table has 15 fields.

Will there be apalpable difference if I use xxyyyzzz.a,xxyyyzzz.b..etc instaed of xxyyyzzz.*???

What abt the index usage??? If he specifies value for field C the query using index1 is taking lot of time.

Since I can't predit what he can give as input valsue should I have multiple queries, check the input and then accordingly select the relevant query???
__________________
Regards,
Srinivas
Reply With Quote
  #5  
Old 15th September 2003, 16:38
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
use diff queries for different selection set

After having discussion with user , decide which are the most frequent patterns of running the report . And accordingly design the report.

In general use an index who 1st element values are known.

If the first index element is not known and program gives the wider range selection for first index element , then internally bshell does the full table scan (FTS) leading to poor performance.

* field specification does improve the performance only when the record width is high , number of records are high , number records selected in query are high , worse an order by clause is given and we not need all the fields of the table for our purpose.
Reply With Quote
  #6  
Old 15th September 2003, 17:40
srinivas's Avatar
srinivas srinivas is offline
Senior Member
 
Join Date: Nov 2001
Location: South India
Posts: 213
srinivas is on a distinguished road
Baan: Baan4,Baan ERP - DB: Oracle, Informix, SQL Server, Baanbase - OS: Unix, Solaris, NT, Win 2K
Hitesh is right.

The user says the input can be any based on any field and is not fixed. I think that is why when he specifies C field alone, it is doing FTS and hence the slow performance.

Very difficult to explain the user why the same report is slow in some cases and fast in some other cases. Sometimes the report takes more than 5 min.

Seems that multiple quries is only the way out.
__________________
Regards,
Srinivas
Reply With Quote
  #7  
Old 15th September 2003, 17:44
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 521
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
What about using dynamic SQL? Build the query based on the input fields, and add the correct index dynamically!

As for the select *, it is always better to read only the fields you need. Maybe the performance increase is minimal, but it brings you in the habit, and in the long run might give you an unexpected spin-off with regard to performance!
__________________
Regards,
Nico
Reply With Quote
  #8  
Old 30th September 2003, 16:00
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
Dynamic SQL indeed has the best posibilities.

If you use BaanERP and Oracle the only thing you have to change is the hint (like HINT USE INDEX 1 ON xxyyyzzz see hint)

When writing generic code the hint can be forced by using an ORDER BY on an index. Selecting the index can be based on the input fields. Use the following to figure out with index can best be used:

- FROM equals TO value highest value for index usage
- FROM > least value or TO < max value. Use set.min and set.max (or set.fmin and set.fmax) for this.

Indeed it requires a lot of programming, but if performance counts, this is your only alternative.

Good luck,
Dick
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #9  
Old 12th April 2004, 10:23
skoka123 skoka123 is offline
Newbie
 
Join Date: Mar 2004
Posts: 2
skoka123 is on a distinguished road
Baan: 4c - DB: Oracle 8 - OS: HP-UX
In Baan 4 with Oracle level 2 drivers this is what happens.

Oracle will use the index that has the maximum number of index parts in the where clause. If more than one index matches this condition then the order of the index parts is important.

In your case as all the four fields are being used in the where clause, Oracle will use Index1. This in turn might result in a full index scan.

The solution to your problem is to have different select queries for each selection criteria on the form as you have rightly guessed.
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
ERPJewels Hitesh Shah Third Party Products 10 6th November 2006 06:38
MPS is not giving right results in background aalmansas Manufacturing & Supply Chain 2 26th October 2004 22:50
unexpected results with keyin$ rupertb Tools Development 2 1st April 2003 10:41
Purchase Receipts / Purchase Results larkin_jack2 Finance, Invoicing and Integration 11 7th May 2002 19:05


All times are GMT +2. The time now is 19:58.


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