Creating efficient qualifications

When the AR System server checks the Run If qualification of an active link or filter, it checks only the current request. However, when the server checks the qualifications for escalations, Set Fields actions, Push Fields actions, certain Open Window actions, table fields, alert lists, search menus, and defined searches, it must evaluate the qualifications against all requests in the database for the selected forms.

Related topic

Formatting-conventions-for-fields-in-qualifications

You can improve system performance dramatically if you follow these best practices:

  • Use indexed fields. Tests of indexed fields use the index to access the requests instead of scanning all requests. For information about indexing, see Defining-indexes.
  • Avoid the following searches, which cause a full scan of a database table:

    • The != Operator
      Searches using the != operator check every record to see if the value is not contained. If you have created indexes on a field, they are not used. Instead, design your qualifications to retrieve what you are looking for, instead of what you are not looking for. For example, you can rewrite the search qualification 'Status' != "Closed" to 'Status' < "Closed" to improve the use of an index.
      The != operator does not match entries in which the value for the field is NULL. You must explicitly include a test for NULL to find NULL values. For example, 'Status' != "Closed"does not search for cases where the Status field is empty. You must use the following syntax:

      'Status' != "Closed" OR 'Status' = $NULL$

      Avoid creating comparisons with fields of unlimited length, because the database might not support that capability.

    • Wildcards in front of search terms
      Searches that begin with a wildcard (for example, 'Submitter' LIKE "%Jackson%" ) does not use the index but scans the database for every record containing the word Jackson that have any characters preceding the J.
      Searches with trailing wildcards are valid and use indexes. For example, 'Submitter' LIKE "Jackson%" uses the index and finds all entries starting with Jackson.
    • Poorly written arithmetic operations
      Searches that use the field in an arithmetic operation can cause a table scan. For example, a qualification to find all requests greater than 24 hours ($TIMESTAMP$ - 'Create Date' > 60*60*24 ) searches the entire database for records with $TIMESTAMP$ (the current date and time). Instead, rewrite the search qualification to place the indexed field on the left side of the equation, as in the following qualification: 'Create Date' < $TIMESTAMP$ -60*60*24.
  • Avoid unqualified Run If statements for escalations. An unqualified Run If statement for an escalation performs an unqualified query to the database table and execute the If actions for every request found in the form. Server performance is especially degraded for unqualified Run If statements in escalations that are set to run at frequent intervals, because the server must search the database every time the escalation is run.

If the database is case-sensitive, any queries run by active link workflow are case-sensitive. For an Oracle database, you can set the Db-Case-Insensitive option in the AR System Administration: AR System Configuration Generic UI form to support case-insensitive searches. However, this option can have a negative impact on performance. See Configuration-settings-C-D.


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*