Is it correct to assume that below query will only select records - which meet form inputs and have a corresponding record in tipcs021. Is this the most efficient way of doing this?



select tipcs510.*, tipcs021.cpvr
from tipcs510, tipcs021
where tipcs510.psdt = :f.psdt (form variable)
and tipcs510.cplb = :f.cplb (form variable)
and tipcs510._index2 = {tipcs021.cprj, tipcs021.item}

short:
1. yes
2. no

a bit longer:
You are using the index just for join purposes, but the selection of the records is without index. I would suggest to use index 2 of tipcs510 to reduce the selected records. So you could try:

where tipcs510._index2 inrange {"", :f.psdt} and {"ZZZZZZ", :f.psdt}
instead
where tipcs510.psdt = {:f.pdst}

Günther,

I have no idea who learned you this, but this is absolutely not truth. First of all when you read via an index you read the index sequentially, but read the data scattered. When you read let us say less than 10% of your data, an index can benefit if the first field(s) of an index are matching the fields of the WHERE clause.

In your suggestion you want to read a full range via an index. This will do a read of all index blocks and will read the data blocks multiple times (once per row that is in the block). So a full index scan can be more than twice as slow as a full table scan.

Back to the original query: I believe that the proposal will perform the best.

Best regards,
Dick

Dik,
your hints sound logical, thanks for the clarification. Can you tell us if that is always true, or is there a usally / it depends on / level 1 or 2 / oracle or informix or sql server?

Back to the original:
And once again, I'm not quite sure it that's really the most efficient way. Normally, I wouldn't have used the join. I think its quite natural to read the table tipcs510 first, and for those records found to read the customized item.

Günther,

Actually it is valid for all versions and databases, but Level 1 will always read via an index and thus perform poorly for this kind of queries. BTW as no index field has been given, (dependent on the number of records of course) this query will alway perform poorly regardless L1 or L2.

Regarding the join: In both situations the table will first go to the main table and then go to the inner table for the result set of the main table. Doing it all in onequery saves to execute a second query in the bshell and thus a lot of roundtrips from bshell to database. So again, this option is faster compared to your proposal.

Hope this helps,
Dick