Performance considerations when using GlideRecord

[sergiu_panaite]

If you are a developer and working for a customer that uses ServiceNow platform, you will probably have to deal sooner or later with scripting in ServiceNow and therefore touch GlideRecord.

If that's the case keep in mind the following performance considerations when using GlideRecord:

1. Always try to add a filter in a GlideRecord query, especially one based on an indexed field. Don't bring back a full table via GlideRecord if it's not really needed, as it causes overhead and can take time.

POOR PERFORMANCE:

var gr = new GlideRecord('incident');
gr.query();     //brings back the entire table incident
while (gr.next()){
       // do something here
}
       

GOOD:

Example 1 (this would benefit from an index on incident.`active` field):

var gr = new GlideRecord('incident');
gr.addQuery('active', true);
//or (one or the other, not both)
gr.addActiveQuery()
gr.query();
while (gr.next()){
       // do something here
}
       

Example 2 (this would benefit from an index on `sys_created_on` field):

var gr = new GlideRecord('incident');
gr.addQuery('sys_created_on', ">", "2015-09-10 04:05:00");
gr.query();
while (gr.next()){
       // do something here
}
       

2. While adding filters in GlideRecord queries is good, avoid creating filters which are too complex.

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

Avoid complex queries on very large tables.

Create a query string by generating encoded query strings through a filter and using that string with addEncodedQuery.

As an example, if I want all incidents that are active, created after a certain date with a certain state and a particular approval value I go in the instance at incidents module, expand "All" filter and add all my conditions then click "Run". Then right click on the link, "Copy query" and I get my encoded query:

"active=true^sys_created_on>javascript:gs.dateGenerate('2015-09-01','23:59:59')^state=3^approval=requested"

I add this query in a GlideRecord call:

var myQuery = "active=true^sys_created_on>javascript:gs.dateGenerate('2015-09-01','23:59:59')^state=3^approval=requested";
var gr = new GlideRecord('incident');
gr.addEncodedQuery(myQuery);
gr.query();
while (gr.next()){
       // do something here
}
       

3. Limit number of returned records when querying very large tables.

For example, this:

var gr = new GlideRecord('incident');
gr.addActiveQuery();
gr.query();
while (gr.next()){
       // do something here
}
       

might work fine if you have only a few thousands active incidents, but if you have 1000000 active incidents, the query() method has to retrieve all those records and this can take time.

In cases like this, using the setLimit() method is indicated.

var gr = new GlideRecord('incident');
gr.addActiveQuery();
gr.setLimit(100);
gr.query();
while (gr.next()){
       // do something here
}
       

4. Make sure fields used in filters exist, otherwise your GlideRecord will retrieve the full table (it's like doing a SQL query without a WHERE clause).

For example, if I query 'incident' table using a filter for a field ('myfield' in this case) that doesn't exists I get full incident table back:

var gr = new GlideRecord('incident');
gr.addQuery('myfield', 'test');
gr.query();
gs.log('Total number of records is: ' + gr.getRowCount());
       

The log might also show (depending on the result set size) a warning like:

2015-10-14 01:25:12 (145) Default-thread-168 5DC3A6D62B03860090AA3EA3B4DA15A4 WARNING *** WARNING *** Large Table: Table handling an extremely large result set: 94304

Query that got us here is: TABLENAME = incident ENCODED_QUERY = myfield=test

            com.glide.db.QueryWarning.warn(QueryWarning.java:42)

            com.glide.db.DBQuery.loadResultSet(DBQuery.java:2287)

            com.glide.db.DBQuery.executeAndReturnTable(DBQuery.java:237)

            com.glide.db.DBAction.executeNormal(DBAction.java:197)

            com.glide.db.DBAction.executeAndReturnException(DBAction.java:166)

            com.glide.db.RDBMSQueryContext.executeQuery(RDBMSQueryContext.java:46)

            com.glide.db.DBQuery.execute(DBQuery.java:1772)

            com.glide.db.meta.Table.queryBasic(Table.java:311)

            com.glide.db.meta.Table.query(Table.java:189)

            com.glide.script.GlideRecordITable.query(GlideRecordITable.java:77)

            com.glide.script.GlideRecord.query0(GlideRecord.java:2982)

            com.glide.script.GlideRecord.query(GlideRecord.java:2707)

            com.glide.script.GlideRecord.jsFunction_query(GlideRecord.java:2593)

            sun.reflect.GeneratedMethodAccessor68.invoke(Unknown Source)

            sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

            java.lang.reflect.Method.invoke(Method.java:597)

            org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:597)

            org.mozilla.javascript.FunctionObject.call(FunctionObject.java:504)

            org.mozilla.javascript.ScriptRuntime.call(ScriptRuntime.java:1227)

            org.mozilla.javascript.gen.c7133.call(<refname>:3)

            org.mozilla.javascript.gen.c7133.exec(<refname>)

            com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:233)

            com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:105)

            com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:72)

            com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:63)

            com.glide.script.Evaluator.evaluateString(Evaluator.java:91)

            com.glide.processors.ScriptProcessor.evaluateScript(ScriptProcessor.java:302)

            com.glide.processors.ScriptProcessor.runScript(ScriptProcessor.java:207)

            com.glide.processors.ScriptProcessor.process(ScriptProcessor.java:168)

            com.glide.processors.Processor.runProcessor(Processor.java:402)

            com.glide.processors.Processor.processTransaction(Processor.java:192)

            com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:146)

            com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:36)

            com.glide.ui.GlideServlet$1.run(GlideServlet.java:414)

            java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)

            java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)

            java.lang.Thread.run(Thread.java:682)

Another option is to use Glide property "glide.invalid_query.returns_no_rows" (thanks to kalai for pointing it out) which controls how an invalid GlideRecord query is handled. Full description of the property here:

Available System Properties - ServiceNow Wiki

A quick example from our wiki for above property:

GlideRecord - ServiceNow Wiki   - 8.9.1 Controlling invalid queries

Besides the available options presented above, thanks to our community, treycarroll   went a step further and created an extension to GlideRecord that will throw an exception if you pass a bad column name or operator. I find this very useful and you can read his post here:

Extension to GlideRecord: addQuery2,  Throws an exception if you pass a bad column name or bad opera...

5. Don't increment variables in while loops for counting purposes when you can use GlideAggregate. Again, filtering based on indexed fields is very important.

You can also use GlideRecord.getRowCount() method if besides counting you need to process the returned records.

POOR PERFORMANCE:

var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.query();
var count = 0;
while (gr.next()){
       count++;
}
gs.log('Total number of records is: ' + count);
       

BETTER PERFORMANCE (when you need to do processing on the returned records):

- example using getRowCount() method of GlideRecord:

var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.query();
gs.log('Total number of records is: ' + gr.getRowCount());
while (gr.next()){
       // do something here
}
       

BEST (for counting purposes):

var gr = new GlideAggregate('incident');
gr.addQuery('active', true);
gr.addAggregate('COUNT');
gr.query();
var incidents = 0;
if (gr.next()) {
     incidents = gr.getAggregate('COUNT');
}
gs.log('Total number of records is: ' + incidents);
     

6. Don't overkill your script with logging statements (gs.log). While during testing phase adding more logging statements helps debugging, on a production instance this could add overhead (be aware for each gs.log we have to insert a row in syslog table and write a log line in the application localhost_log as well).