How do I write a good query?

An ideal Service Manager query has the following characteristics.

• Runs against indexed columns to avoid full table scans. See Indexing below.
• Uses operators like equal to ( = ) that map well to SQL to avoid full table scans. See Query operators below.
• Avoids RAD functions like index() that are likely to cause full table scans. See Function usage below.
• Ensures that variables always have a non-null value. See Variable usage below.

Indexing

An ideal query runs against indexed columns. Querying against indexed columns increases the speed at which Service Manager can receive data. However, adding too many indexes adds a performance cost to inserting (adding) new records as well as updating or deleting existing ones. Indexes also require additional storage space.

Your database administrator will weigh the potential benefit of indexing your queries against the cost of increased write times (insertions and updates). Typically, your database administrator may want to index any query that many people access or that gets run frequently. You may want to index tables that are part of the following Service Manager frequently-used queries:

• Public favorites
• Commonly-used forms containing Format controls
• Fills
• Drop-downs (combo boxes)
• Global lists
• Background processes

Query operators

Since Service Manager must translate queries into native RDBMS language, you should try to use operators that map well to native RDBMS languages. In general, queries that use the EQUAL TO operator ( = ) are the most efficient and easiest to translate.

Operator	Example query	Sample SQL statement
EQUAL TO ( =)	contact.name="AARON, JIM"	SELECT "CONTACT_NAME" FROM CONTCTSM1 WHERE "CONTACT_NAME"='AARON, JIM';

As the example illustrates, an EQUAL TO query often directly translates into a SQL query as part of a WHERE clause. In addition, queries with EQUAL TO operations are also the most likely to use and benefit from table indexes.

If you cannot use an EQUAL TO operator in a query, the following operators will also in many cases facilitate direct translation into SQL queries.

Operator	Example query	Sample SQL statement
Starts with ( # )	contact.name#"AA"	SELECT "CONTACT_NAME" FROM CONTCTSM1 WHERE "CONTACT_NAME" LIKE 'AA%';
LIKE	contact.name like "A*RON*"	SELECT "CONTACT_NAME" FROM CONTCTSM1 WHERE "CONTACT_NAME" LIKE 'A%RON%';
ISIN	contact.name isin {"AARON, JIM","ARMSTRONG, TRACY"}	SELECT "CONTACT_NAME" FROM CONTCTSM1 WHERE "CONTACT_NAME" IN ('AARON,JIM','ARMSTRONG,TRACY');

In these examples, the LIKE operator directly translates into a SQL query as part of a WHERE clause. The ISIN operator translates into an IN predicate. The STARTS WITH operator ( # ) translates into a LIKE predicate using a wildcard character.

Such kind of queries now translate into a SQL statement like the following:

'1 in assets="adv-Unix-101"'

'a3.record_number = 1 AND a3.asset = ?'

Supported query operators

The following operators are supported in a query:

• All logical operators
• TR, and NTR
• Like

For example, the following queries are supported:

'1 in assets ="adv-Unix-101"'

'7 in assets >"adv-Unix-101"'

'7 in assets#"adv-Desktop"'

'7 in assets like "adv-*-104"'

Function usage

Any query that contains a RAD system language function such as index() can potentially cause a full table scan because it may not translate to SQL. Generally, you want to limit the use of functions in queries to avoid degrading your system's performance every time the query is run. If you anticipate a query will run frequently, consider rewriting the query to use operators instead of functions. The most typical high-use queries are those in the following areas:

• Favorites
• Views
• Links
• Stored queries.

For example, suppose you want to create a view that displays closed high priority incidents with activity from a particular operator named Manager. You could create this query by using the index() function.

Inefficient query example: index() function

View definition field	View definition value
Table	probsummary
Query	(flag=false) and (priority.code<="2") and index("Manager", assignee.name)>0

As written this is an inefficient query. However, you can rewrite this query to use simple operators and prevent unnecessary table scans. For example, the following query accomplishes the same objective.

Efficient query example: using simple operators

View definition field	View definition value
Table	probsummary
Query	(flag=false) and (priority.code<="2") and (assignee.name like "*Manager*")

If you cannot rewrite the query (for example, if you are using an exclusive feature of the function), you can minimize its performance impact by limiting access to the query.

Variable usage and null values

Anytime you use a custom variable in query you should ensure that it does not have a null value. Null values in query variables can cause full table scans which lower your system's performance.You can avoid null values in custom variables by using the following methods:

• Use the nullsub() function to replace the null value with a default value.
• Replace a custom variable with system variable. System variables obtain a value when a user logs in to the system or accesses a particular application.

For example,suppose you create a custom variable $name to store a contact's name. You could provide a default value for the variable with the following expression.

$name=nullsub($name, contact.name in $L.file)

In this case, the default value will become the contact name listed in any file that uses this contact.name field. If query does not have access to the contact.name field value, you may consider hard-coding the default value, but the utility of such a hard-coded value may be limited.

Alternatively, you may want to use an existing system variable such as $lo.user.name or $lo.ufname to provide the name of current operator.

Miscellaneous queries

Service Manager handles miscellaneous queries as described in the following.

Queries with numbers in an array field

Queries with numbers in an array field, such as '1 in assets="adv-Unix-101"', translate into a SQL statement like the following:

'a3.record_number = 1 AND a3.asset = ?'

The Service Manager server supports the following operators in such kind of queries:

• All logical operators
• TR, and NTR
• Like