Reference Field Indices

Many reference fields in the ClearSCADA database are indexed. The indices are used internally to provide a record of the references between database items (such as the references between Group Instances and their Group Templates). Indices also allow for faster queries of indexed fields, as the query can be performed on an index rather than the entire database.

The Reference Field Indices category on the Server Status Tool shows which fields are indexed, and provides information about the number of target and source fields and the names of each index.

The information in the Reference Field Indices category can be useful when investigating the cause of queries that are performing slower than expected—the slower return time may be due to the query being performed on reference fields that are not indexed.

The status information is categorized as:

• Name—The name of the index.
• Source Fields—The database fields that are included in the index. This information is useful as it allows you to see which database reference fields are included in the index.

  When an indexed referenced is created or changed in any of the listed fields, the index is updated. This allows the index to have an up-to-date list of the relevant references.

• Target Object Count—The total number of ‘target’ database items. A ‘target’ database item is an item that is referenced by a ‘source’ (the ‘source’ item has a field that references the ‘target’ database item).

  Example:

  If a Group Instance refers to a Group Template, the Group Template is the ‘target’ and the Group Instance is the ‘source’. So, if there are 2 Group Templates and both templates are referenced by 6 Group Instances, the Target Object Count for the InstanceTemplateId index is 2.

• Source Reference Count—The total number of database items that have a field that is:
  • included in the index

    and

  • references another database.

  Example:

  If a Group Instance refers to a Group Template, the Group Instance is the ‘source’ and the Group Template is the ‘target’. So, if there are 2 Group Templates and both templates are referenced by 6 Group Instances, the Source Reference Count for the InstanceTemplateId index is 12. This is because the Group Instances have fields that are included in the index and these fields reference one of the two Group Templates.

• Memory Usage—The total amount of memory used to store the index.