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.
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.