Interacting with the database

Avoid Complex GlideRecord Queries

Rather than creating a series of addQuery() and addOrCondition() calls to obtain a result, use addEncodedQuery() to make the query easier to create and maintain.

Consider a requirement to obtain a list of all active Apple printers and computers in a company’s Santa Ana office. Creating the proper combination of addQuery() and addOrCondition() queries to get the proper solution may sound simple. However, if the requirement changes and you are asked to add another office location and a different hardware manufacturer to the list, the task of maintaining the query can become challenging.

For complex GlideRecord queries, it is easier to create a query string by generating encoded query strings through a filter and using that string with addEncodedQuery. As requirements change, you can create a new query string using the list filter, verify the results with the requirement author, and use the query in the same script.

Use GlideAggregate for Simple Record Counting

If you need to count rows, you have two options: the getRowCount() method from GlideRecord, or GlideAggregate. Using GlideRecord to count rows can cause scalability issues as tables grow over time, because it retrieves every record with the query and then counts them. GlideAggregate gets its result from built-in database functionality, which is much quicker and doesn’t suffer from the scalability issues that GlideRecord does.

Bad example

/*
 * countInactiveIncidents - return the number of closed incidents
 *
 * @param - none
 * @returns integer - number of records found
 *
 */
function countInactiveIncidents() {

     var inc = new GlideRecord('incident');

     inc.addInactiveQuery();
     inc.query();

     var count = inc.getRowCount();
     gs.print(count + ' inactive incidents found');

     return count;
}

Good example

/*
 * countInactiveIncidents - return the number of closed incidents
 *
 * @param - none
 * @returns integer - number of records found
 *
 */
function countInactiveIncidents() {

     var inc = new GlideAggregate('incident');

     inc.addAggregate('COUNT')
     inc.addInactiveQuery();
     inc.query();

     var count = 0;
     if (inc.next())
          count = inc.getAggregate('COUNT');

     gs.print(count + ' inactive incidents found');

     return count;
}

Avoid Complex Queries on Large Data Sets

Limit the number of times you search large tables. As your instance grows, these searches can affect performance. Assume you have a requirement to search the CMDB for the importance of all upstream services related to a specific server when that server is added to the Incident form. Running a query on the Relationship [cmdb_rel_ci] table is not a problem for a simple CMDB with a few hundred or thousand CIs. However, for a CMDB with three million CIs and hundreds of thousands of relationships, the query could take hours.

One solution is to create a related list for the CI that lists affected services. The list can be updated by a business rule as relationships for the CI are updated. When a CI is added to an incident, the affected services list can be quickly retrieved from the related list on the CI, rather than launching a long search on the Relationship table.

Let the Database Do the Work

Whenever possible, leverage the power of the database to retrieve the proper records. For example, if you are checking 1,000,000 incidents to see if at least one record is active, your first solution may look like this:

var inc = new GlideRecord('incident');

inc.addQuery('active', true);
inc.query();

if (inc.hasNext())
   // There is at least one active record

However, if there are 250,000 active records, the query() method has to retrieve all those records. That can take time. Instead, use the setLimit() method to instruct the database to return only one record. Returning one record is much faster than returning all the records.

var inc = new GlideRecord('incident');

inc.addQuery('active', true);
inc.setLimit(1);  // Tell the database to only retrieve a maximum of 1 record
inc.query();

if (inc.hasNext())
   // There is at least one active record