Speed up queries in servicenow using table indexes

Index your tables

In this article I am going to tell you how you can dramatically increase the speed of queries in servicenow.

This is important from a user experience point of view as long query times can result in long page load times.  It may not seem important for one particular business rule or script but as we all know in servicenow a single record may have multiple business rules and other scripts running at the same time and it all adds up.

This principle can also be applied to other platforms although this article is specific to servicenow.

The Problem

Recently I had an issue where records in a custom table were loading really slow after I had fixed an issue using a business rule.  In this business rule I had to query a table which contained all the responses to questions asked on service portal record producers.  As you could imagine this table grows daily and has a lot of records.  Even after adding parameters to my query to reduce the number of results returned, the query was still taking a really long time.

The Analysis

To confirm the cause of the issue I setup the same query on the table in question by using the filtering functionality within servicenow and ran it.  Sure enough sometimes it took more than 30 seconds to return the query results.  Obviously this is unacceptable for a form load time.

The Solution

After discussing this with a colleague and diagnosing the business rule and the query we turned our attention to the table configuration out of desperation.  We noticed that the table had indexes in the related list of the table definition.  “Click” of course!  Even though I have used indexes in all sorts of other systems it is easily overlooked in servicenow.  One of the indexes contained the field I wished to query but also two other fields.  We added these two fields to my query and ran it again against the table using the three index fields.  Wowee! the query returned results almost intantaneously.

Find table indexes related list in the table definition use all values in the ‘column’ column.  For example to include the 3rd row index in your query you would use sys_update_name and sys_class_name together.

In my case I just wanted to query the name field so my original query was:

var table = new GlideRecord('big_table');
table.addQuery('name','Steve');
table.query();

etc etc

The table index was  name, email, phone number but I didn’t care what the email and phone number was. So we kind of ‘gamed’ the system by using the query builder again to build the query where name is Steve and email and phone number is anything.  Then we copied the query to use in an encoded query like:

The above is just an example on how to copy the query after using query builder to build the query.

The new query would look like this:

var table = new GlideRecord('big_table');
table.addEncodedQuery('name=Steve^emailIANYTHING^phoneANYTHING');
table.query();


The Result

The result was a much faster query and the page load and reload times were reduced dramatically to the regular amount of time for servicenow forms.

Conclusion

While using indexes may seem obvious to some people, in my experience indexing and using indexes is regularly overlooked.

Write faster queries using indexes.