Pro Tip: Use updateMultiple() for Maximum Efficiency!
Although most of our free time has been spent focusing on writing the Learning ServiceNow book, we wanted to post a quick Pro-Tip to address an issue we've seen impact performance on nearly every instance we've worked on: The use of loops to update a single field on a large number of records in the database.
Let's say you've got a client who wants to run a scheduled job that'll auto-close Incidents in "In Progress" status, that haven't been updated for 30 days, by setting their state to a custom value, "stale" (or, since State is an integer field, 10). Let's see what a script for that purpose might look like, as it's usually written:
The ServiceNow Development Handbook is now available!
closeStaleIncidents();
function closeStaleIncidents() {
var staleStateValue = 10;
var strEncodedQuery = 'sys_updated_onRELATIVELT@dayofweek@ago@30^state=2';
var grInc = new GlideRecord('incident');
grInc.addQuery(strEncodedQuery);
grInc.query();
while (grInc.next()) {
grInc.setValue('state', staleStateValue);
grInc.update();
}
}As you can see, we're getting a GlideRecord on the incident table, adding a query, then looping through each record returned from that query, setting a value on it, and updating it in the database.
But wait a minute, we're performing exactly the same change on each record returned from this query. What if instead of grabbing each record one at a time, we could just tell the database that we want to update all of these records in the same way? We can do so, using the updateMultiple() method! Let's have a look:
closeStaleIncidents();
function closeStaleIncidents() {
var staleState = 10;
var query = 'sys_updated_onRELATIVELT@dayofweek@ago@30^state=2';
var gr = new GlideRecord('incident');
gr.addQuery(query);
gr.setValue('state', staleState);
gr.updateMultiple();
}As you can see, we don't actually retrieve a record using .next() at all; instead, we take the "scaffolding" of the GlideRecord (the empty GlideRecord, populated with the empty GlideElements for each field already set up, by using the .query() method). Using that empty scaffold, we then update one of the GlideElement values using .setValue(), so that state is the only populated field in this GlideRecord. Then, when we call .updateMultiple(), we're telling the database to propagate this change across all records which match the query on our GlideRecord object (gr)!
This is much more efficient than looping through every record that would be returned from our query, retrieving it from the database, setting each value one at a time, and saving each record back to the database; however, we do have to take a special precaution here. You'll notice that I used .setValue() to set the value of the field, rather than directly setting the GlideElement for the state field as though it were a String value - for example:
grInc.state = '10';This is important. Personally, I recommend always using the .setValue() method rather than directly setting the field as a property of the GlideRecord object (except in the case of journal fields) to avoid issues like this. In the case of .updateMultiple() however, it is especially important. If we fail to use the .setValue() method to set the new field value before using .updateMultiple(), that can result in all records in the table being updated, rather than just those returned from our query! (For example, if we just do grInc.state = '10'; as you see above.)
You can read more about how important it is to use .setValue() (and .getValue()) in this related SN Pro Tips article.
SN Pro Tips is owned and written by The SN Guys, ServiceNow consultants. If your organization could benefit from our services, we humbly welcome you to schedule some time to chat and share your goals with us, so we can help your business Grow With Purpose.