Define an index to enhance performance

A database index is an ordered and thus quickly searchable part of a database table. If the database must search in a particular table for entries matching a specified value, then this process will be significantly sped up if the database can use an index for finding the data instead of requiring a full table scan.

You can configure the Alfabet database to create indices for all object data that is frequently used. Index creation is a side effect of a class key definition. Usually class keys are defined to implement uniqueness constraints. For each class key that is configured for an object class in Alfabet Expand, an index is created. Each class key has a Unique attribute that can be set to False to allow an index to be created for the class key without implementing a uniqueness constraint for the class. Thus, the definition of class keys can be used to speed up query-based searches.

In addition, audit keys can be defined to trigger index creation for audit tables. Each object class for which auditing is activated can have audit keys defined.

Please be aware that every index defined for a database table slightly reduces the data update and insert performance for entries in the table. Therefore, you should create indices only where it is specifically useful or necessary.

Some processes like ADIF imports or batch rescan of indicators may trigger a high number of single database insert and delete transactions. This can have a negative impact on fragmentation of indices. As a result, CPU usage is increased and performance is reduced. It is recommended to rebuilt indexes in regular intervals for object classes subject to ADIF import or rescan of indicators. using the command line tool AlfaAdministratorConsole.exe.

  1. In the Meta-Model tab of Alfabet Expand, right-click the protected or custom object class that you want to create the audit key for and select Add New Audit Key. You will see the Audit Key Group folder and the new audit key subordinate to the selected object class.
  2. Click the new audit key and edit the attributes:
    • Name: Change the default name to a meaningful value.
    • Columns: Open the editor and select all audit data that shall be included in the index.
  3. In the toolbar, click the Save  Save_Expand button to save your changes.

To delete all protected and custom audit keys that have been configured for a protected or custom object class, click the Audit Key Group node below the relevant object class and select Delete All Public/Protected Audit Keys. The protected and public audit keys will be deleted and no longer displayed below the Audit Key Group node. Private audit keys that have been configured by Alfabet BD GmbH will not be deleted.