Indexes and Performance

Indexes and database performance are intimately tied, which is why it is so important that you provide your tables with the appropriate indexes, given your application’s needs. Since speed is such an important issue in many applications today, this section takes an extended look at indexes and database performance.

How Many Indexes Should You Define?

As is the case with so many issues in software development, there are trade-offs between the number of indexes and performance. The more indexes you have on a table, the more work ADS has to perform in the background as you modify the contents of your table. For every record that is inserted, deleted, or modified, the table’s associated indexes must be updated.

In the end, it is a balancing act. You don’t want to have too few or too many indexes. In general, you want to ensure that you have indexes to support those operations that are performed frequently, and that must be performed quickly. For those operations that are performed only occasionally, or for which performance is not an issue, additional indexes should be defined only if they do not reduce performance in other areas.

For instance, if your table has only a few indexes, and their update at runtime is not noticeable, it won’t hurt to add an additional index for non-critical tasks. On the other hand, if your table already has many indexes, and their runtime update is a performance concern, you cannot afford to add additional, non-essential indexes.

Scopes, Seeks, and AOFs

Another performance-related issue concerns how indexes are used. Some operations, such as scopes (ranges) and seeks (single-record locates), are performed directly on indexes. Other operations, such as filtering, involve special bitmap files that are referred to as AOFs (Advantage Optimized Filters).

Let’s start by considering scopes and seeks, which in the terminology of the Advantage TDataSet Descendant are called ranges and findkeys. A scope is a subset of records, based on an index order. A seek is the selection of a given record in a table, based on an index order.

Both scopes and seeks use indexes directly, and provide the highest attainable level of performance. Before you can set a scope or perform a seek, you must make the index that will be used for the operation the active index. This permits the keys of the active index to be used for the operation.

For example, if you have an index whose index expression consists of two fields, Last Name and First Name, you can select this index and then set a scope to include all records where the last name is Smith. This operation uses the index directly to set the scope. This same index could be used to select all customers whose last name is Jones and whose first name begins with the letters A through G. Again, this scope uses the active index directly.

Not all ADS developers can use scopes and seeks directly. For example, if your only interface to ADS is through SQL, such as when you are using an ODBC or Java JDBC driver, you have no way to execute scopes and seeks directly. Scopes and seeks are available to developers who can use the Advantage Client Engine API, the Advantage Clipper RDD, the Advantage OLE DB Provider, the Advantage Visual Objects RDD, and the Advantage TDataSet Descendant.

Even if you cannot create scopes and seeks directly, ADS may be creating them for you. For example, a SQL SELECT statement including a WHERE clause may set a scope in order to satisfy the WHERE clause, but only if the appropriate index is available. If a scope cannot be used, ADS will try to create an AOF instead.

AOFs are special, bitmap filters that are created from available indexes in order to create filtered views of a table. Unlike the use of scopes and seeks, where you as the developer must specifically choose the index, AOFs are constructed at runtime based on the available indexes. Once constructed, they can be used by multiple client applications to select one or more records based on a filter. AOFs are never faster than a scope, but they are often much faster than if an optimized AOF cannot be created.

Each bit in an AOF corresponds to a record in a table. If an AOF is fully optimized, a bit that is set means that the corresponding table record passes the filter, and a clear bit means that the record does not. These values are then used to retrieve the filtered records from the table.

If an AOF is partially optimized or not optimized, a set bit in the AOF means that the record might be in the filter, and a clear bit means that it is not. In these cases, ADS must read each record associated with a set bit in order to determine whether to include the record in the filtered set.

You may recall that earlier in this chapter, in the section “Testing Indexes,” you entered a filter expression and a green circle appeared. When the circle is green, ADS is using a fully optimized AOF. If the circle is red, the AOF is not optimized. If a partially optimized AOF can be created, a yellow circle appears. As a result, the Set Filter feature of the Table Browser is an invaluable tool for verifying that your indexes are providing ADS with the information it needs.

While a given index order can be based on one or more expressions in the index key expression, optimized AOFs are based only on the first expression in the index key expression.

Consider the following index key expression: Last Name;First Name. This index order can participate in creating an optimized AOF based on the Last Name field only. The existence of the First Name field in this index key expression cannot be used to create an optimized AOF on the First Name field. Only an index order where the First Name field is the first expression in the index key expression can be used for that purpose. Consequently, if your application requires an AOF that will search both First Name and Last Name fields, there must be at least one expression index order where the First Name field is the first expression in the index key expression.

For more details concerning AOFs, and what you can do to ensure that your application will use optimized AOFs, see the ADS documentation.