Hello,

Baan support have asked me to change the first_rows_hint parameter in db_resource.

It was set to first_rows_hint:2 they asked me to change it to first_rows_hint:1

What does this setting actually do? I've searched the Infor KB for info but cannot find any, nor has it been raised on baanboard before.

Thanks

PJ

Hi PJ,

the first_rows_hint parameter is actually quite suitably named and refers to the "first rows" hint, which get's added to the SQL statement,when the baan parser (eg. oracle driver) translates the baan sql into oracle sql.

If I am not mistaken 2 switches it off, and 1 switches it on.

Now, what does it do?

1) It has no functional consequences, so it will not fix a bug or similar.
2) Oracle might simply ignore it (as it is a hint only)
3) In situations, where the application only requires a single record (ie. in forms), baan might be minimal slower as Oracle will optimize the SQL for accessing the first record as fast as possible.
3) In jobs (mass data select) this might result in slower performance.

As a consequence some things might get faster and some might get slower.

Regards

Markus

http://www.baanboard.com/baanboard/showthread.php?t=7665

section 9-11

The ERP LN Oracle driver generates for many queries the FIRST_ROWS
hint. Since Oracle 9i it is better to use the FIRST_ROWS(N) hint where N is a
number that specifies the amount of records expected to be returned. The
first_rows_hint has been introduced to switch between 3 flavors:
first_rows_hint:1 old style FIRST_ROWS hint will be
generated
first_rows_hint:2 new style FIRST_ROWS(N) hint will be
generated
first_rows_hint:0 first_rows hints will be suppressed.
Note: the benchmark failed with timeouts
on this setting.
Initially, the default has been set to 1 for backward compatibility. This
resource has been introduced in 6.1c.07.09, 7.1d.11, 7.6b, and 8.2b.
For portingsets 6.1c.07.14, 8.3a.02, and 8.4a and higher the default has
been set to 2.
This parameter can have a significant impact on performance. However, as
this depends a lot per customer, no general advice can be given.

Dave

Hi Dave,

Thanks Dave for clarifying this.

According to his profile PJ is on Baan IV.
As far as I know, Infor has not made any benchmarks for BaanIV in a long time. Do you have any information how the parameter behaves there?

Is the Oracle driver for BaanIV actually interpreting this parameter as BAAN ERP LN?

Does anybody ever traced this, maybe?

In case of "2", what is the value for the number of record, eg N?

Regards

Markus

If i remember correctly the baan 4 driver does send first_rows(n) to oracle if this is set.

So if you're on oracle 9i and up you should always see better performance with /*+ FIRST_ROWS(n) */ vs just /*+ FIRST_ROWS */

(So i disagree with support here in telling PJ to go back to the old style of first_rows_hint:1) Unless they know of some bug in the porting set / oracle version that he is on.

Dave