Hi,
I am working on Baan IV c4 SP9 and more later on SP10 and after Baan V.
The OS is Windows NT with Oracle databases (8i)
In all the Baan script we use the "select" like that:
select tfgld205.*
from tfgld205
where.........
selectdo................
..................
I think it's a good way of programing when we are with Bisam,
but it's really a good way of optimization with Oracle ?
What do you think of the different ways of programation of SQL Baan ?
Thanks
Kind regards

1. try to get a copy of M2017bus called Baan Application Performance Guide
2. trace your select by using TT_SQL_TRACE=0200, by this you can check which select statement performs best.

Basicly your statement is fine. Of course you can optimize a bit by naming only the fields you need however in print programs its common to read all fields of a table. Be sure to use an index in your where clause if possible.

Regards,
Han

Selecting index in the where clause is not enough when you run level 2 database. The database driver leaves choice of index to database, even when in the where clause the index is obvious used (like in select tfgld205._index1 = {i.cono, ... i.ccur}), the RDBMS can choose index 3 to read from. This is overruled with the "order by" clause.

So if you want to read under all conditions with index_1, add order by tfgld205._index1.

Lucas je hebt waarschijnlijk gelijk.

Quote out of the before mentioned document:

Choosing the index
Problem
It is important to know how the driver will define an index before it parses a query.
Solution
The database driver will create index hints to the RDBMS. These index hints are
defined as follows:
First, the where condition is evaluated to define which table fields are checked.
After this evaluation, the number of index parts in each index (from 1 to the number of
indices) is compared with the number of matching fields used in the where condition.
The index number with the highest hit rate (most matching) will be taken as index hint
to the database.
If there are equal numbers of fields in the where condition that match the index fields,
the index with the lowest number is taken.
There are two solutions to force the hint generator to give the correct index hint to the
database.
n
Implement order by table._index?.
n
Implement index hint in 4gl program. (Available from the Verdi release).
See chapter 3, section 10: Index hints.