The information here pertains mainly to the use of TApolloTable.Query. Queries issued to a remote table using TApolloQuery.SQL are handled internally in a similar way by the remote Apollo Database Server application.
Query Optimization Basics
The MachSIx query optimizer used in Apollo is similar to (and better than) the Rushmore™ technology used in Microsoft's FoxPro and Access products. As with Rushmore, the basic rule for achieving an 'optimized' MachSIx query is this:
At least one portion of the complete query expression must match the index expression of at least one open index order for the current work area.
For example, given a query expression such as 'STATE = "CA" .and. AGE > 30', this is basically how the query optimizer does what it does.
First, it takes the first portion of the expression ('STATE = "CA"') and looks for an index that was created on an index expression whereby a Seek for 'CA' would find a matching record. An index built simply on the STATE field would be an easy match. An index built on STATE+CITY would also match, since you could Seek on 'CA' and find the key 'CAAnahiem'. However, and index built on CITY+STATE would not be a match, since the STATE field was not at the front of the expression.
If a matching index is found, a bitmap is created in memory. This is not a bitmap such as a graphics .BMP file, but rather, a chunk of memory with one byte (8-bits) representing 8 records (one for each bit). Bits are set (turned on) in this bitmap for all records matching this portion of the query expression (all where STATE equals 'CA').
Continuing, the next portion of the full query expression is examined ('AGE > 30'), and a matching open index order is sought out. This would be using an index expression that begins with the AGE field. Note that an index built on STR( AGE, 2, 0 ) would not be considered a match unless the query expression was also using STR( AGE, 2, 0 ).
If a matching index for the AGE field is found, another bitmap is created, and bits are set for all records where the AGE field is greater than 30. This bitmap is then masked against the previous one, resulting in a single bitmap containing bits that are set only for records meeting both conditions.
If matching indexes are found for all portions of the query expression, the expression is considered 'fully-optimizable', and can be resolved completely through the indexes, without having to access the actual table data at all.
If no matching index was found for at least one portion of the query expression, the expression is considered 'partially-optimizable'. In this case, the records with bits set on in the bitmap must be visited directly to verify the 'non-optimizable' portion of the query.
For example, if no matching index for the AGE index was found, all bits set from the first part (those in California) would have to be checked by the query engine to see if the AGE field for that record is greater than 30. If not, the bit is turned off. In the end, the correct result is returned, albeit a bit slower than if it had been a fully-optimized query.
Setting Queries
When using TApolloTable's Query method with preset query expressions (rather than ad-hoc queries created at the whim of your users), try to take steps to insure that the query can be optimized, based on the information above. The QueryTest method can be used, at least during development, to verify the optimization level of a particular query.
If your user will be creating ad-hoc queries, perhaps with the aid of the FilterDlg window, there is the potential that they may create a query based on any field in your table. So, should you build an index on every field in the table to insure they will always get fully-optimized queries? Absolutely not. Additional, non-essential index orders can severely slow down Append and Replace operations elsewhere in your application.
The only time this would be practical (creating an index order for every field) would be when the data is unchanging (static), such as for CD-ROM applications.
How the Query Engine Uses Indexes
The Query Engine will use indexes to optimize queries in the following order of precedence:
1. The currently active index order
2. An index order with a matching expression, but no condition
3. An index order with a condition that exactly matches the query expression
This allows Apollo to always ensure accuracy. You can also use conditional indexes that match the index expression by setting:
ApTbl.SysProp( SDE_SP_SETUSECONDITIONAL, Pointer( 1 ));
The default value is 0 (don't optimize conditional index files).
This allows Apollo to use conditional indexes where it normally would not. Be cautioned that the result set may differ from a SetFilter with the same expression. If all queries are supplied programmatically, this method allows for the most amount of optimizations. If there are some user supplied Ad-Hoc queries, it is best to not use this feature. By leaving the SysProp value to its default you ensure that all queries report the same results as an equivalent filter.