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???

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,....

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

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???

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.

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.

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!

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 (http://www.baanboard.com/programmers_manual_baanerp_help_functions_query_hints_index_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

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.