Setting up composite indexes

iBase uses relational database indexes to search for records according to the values in specific database columns. In some circumstances, you can increase the speed of record retrieval by creating a composite index that combines the values of fields that contain related information.

About this task

When iBase is using a SQL Server database, indexes can improve the retrieval times of records by allowing data to be retrieved without the need to open the record table. For operations involving large amounts of data, you can sometimes improve performance further by creating composite indexes.

For example, importing a large number of records can be slow when data contains multiple discriminators, because iBase has to interrogate the index for each discriminator to determine whether an incoming record matches an existing one. You can speed up the process by creating a composite index that covers all the discriminators you're using.

To create a successful composite index, you need to pay attention both to the types of fields that you add, and to the ordering of those fields.

In iBase, key fields form the main part of an index and are important for index structure. They determine the order that the data is stored, and are used to filter and sort data. Included fields are used to cover queries that are not covered by the key fields. They are not used to sort or filter data, but are used to retrieve data more quickly.

You should create index entries for the most common terms to be associated, in the order that they are most likely to be run. For example, if you would like to add composite indexes to improve the performance of running queries align the fields in your index with the discriminators that are used in the import specifications.

Note: You can create a maximum of 31 indexes per field type.

Procedure

  1. Select Tools > Database Design > Composite Indexes.

  2. Choose the Record Type that you would like to create a composite index for.

  3. Select Add.

  4. For each index combination that you would like to add for this record type:

    1. Select Add.

    2. On the Key Fields tab, choose the Key Fields to use and click OK. To remove Key Fields, select one or more fields and click Remove.

    3. On the Included Fields tab, choose the Included Fields to use and click OK. To remove Included Fields, select one or more fields and click Remove.

    4. Click OK.

      The index is created but can be deleted.

  5. Continue to create indexes for other record types by repeating steps 2 thru 4.

  6. Click Close.