GlideRecord Performance PitfallsDescriptionThere is a lot to understand about the ServiceNow platform and the GlideRecord system. It is our hope that documenting some of the common scripting issues we see related to performance will be useful to those developers using the platform.Instructions1) Using the SetLimit Function There are many times when querying tables that ServiceNow developers may want to grab on a limited number of records or check if records fitting some criteria exist. On a database from SQL we would be running something like the following. SELECT * from MyAwesomeTable WHERE awesome = 1 LIMIT 5 The LIMIT clause tells the database to only pick 5 records from the result set. We can achieve the same functionality from GLIDE. To do this, you want to use the "SetLimit" function on your GlideRecord. Developers should be utilizing this feature whenever they query large tables and are looking for one result or any query they are unsure of the result size. You should also use this to limit results on any page listing the results to achieve pagination. Even scripts benefit from this, using the setLimit, you can paginate results if your script may be running a query on a large data set like something in the CMDB. Below are some common usages and pitfalls. Basic Usage Original code //Let's just print all of the active incidents.var active_incidents = new GlideRecord('incident');active_incidents.addActiveQuery();active_incidents.query();while(active_incidents.next()){ gs.print(active_incidents.number);}*** Script: INC0017107*** Script: INC0016450*** Script: INC0016540*** Script: INC0014179*** Script: INC0016960*** Script: INC0010001*** Script: INC0017116*** Script: INC0016844*** Script: INC0017161*** Script: INC0015941*** Script: INC0010015*** Script: INC0010009*** Script: INC0017223*** Script: INC0015824*** Script: INC0016746*** Script: INC0017242*** Script: INC0014404*** Script: INC0016514*** Script: INC0016743*** Script: INC0010003*** Script: INC0010024*** Script: INC0016802*** Script: INC0017323*** Script: Demo_INC0010033*** Script: INC0014338*** Script: INC0014271*** Script: INC0013334*** Script: INC0010004***SNIP*** Set Limit Code //You know what? That was too many, let's print one active incidentvar active_incidents = new GlideRecord('incident');active_incidents.addActiveQuery();active_incidents.setLimit(1);active_incidents.query();while(active_incidents.next()){ gs.print(active_incidents.number);}*** Script: INC0017107**DONE** Checking for one record This is a common situation we see in support. Many developers will come up with something like the following to check if there is a record that exists before updating. We can write a little example code and then we will review some common issues that appear. //The idea behind the following is that all VM's have a special_identifier and that identifier will only have one group associated to it. //So we can query an already existing vm with that identifier and use it's group for our new VM.function addNewVM(new_vm){ var virtual_machines = new GlideRecord('cmdb_ci_vm'); virtual_machines.addQuery('status', 'Operational'); virtual_machines.addQuery('u_special_identifier', new_vm.u_special_identifier); virtual_machines.query(); if(virtual_machines.next()) new_vm.u_special_group = virtual_machines.u_special_group new_vm.u_special_group.update();} There are actually several problems we may not have anticipated about the above. What if the new_vm special identifier is NULL? The ServiceNow platform will still run that query and we will get the following. SELECT * from cmdb_ci_vm WHERE status = 'Operational' and u_special_identifier IS NULL If there are VM's without a u_special_identifer, then we may actually get a large query that, while we only look at 1 record, the platform still goes and gets the entire list. Another possible issue is growth over time. In the beginning we only have a few VM's in the groups. This query runs very quickly as our selection count is low. Over time, we expand those groups and now the groups have thousands of members. The above will be running a query for thousands of records to only look at 1 record. That leads to lots of wasted processing. We should modify the code above to prevent these issues. function addNewVMToGroup(new_vm){ //If our new vm does not have an identifier, then don't even try this if(new_vm.u_special_identifier == null) return; var virtual_machines = new GlideRecord('cmdb_ci_vm'); virtual_machines.addQuery('status', 'Operational'); virtual_machines.addQuery('u_special_identifier', new_vm.u_special_identifier); //We only need 1 record so let's just get 1. virtual_machines.setLimit(1); virtual_machines.query(); if(virtual_machines.next()){ new_vm.u_special_group = virtual_machines.u_special_group new_vm.update(); }} Now with the above we will get only one record even if both the special identifier is NULL and the group we are looking for has thousands of results. Paginating Script Results If we have a script or even a rest service that will need to iterate over a very large result set, we can buffer those results using the setLimit function so as not to overwhelm either the system or an endpoint. //This function will migrate vm's from one group to the otherfunction addNewVMToGroup(old_group, new_group){ //set our limit count var LIMIT = 10000; //Get a count of all the records we will be migrating var count = new GlideAggregate('cmdb_ci_vm'); count.addAggregate('COUNT'); count.addQuery('status', 'Operational'); count.addQuery('u_special_group', old_group); count.query(); var q_count = 0; if (count.next()) q_count = count.getAggregate('COUNT'); while(count > 0){ var virtual_machines = new GlideRecord('cmdb_ci_vm'); virtual_machines.addQuery('status', 'Operational'); virtual_machines.addQuery('u_special_group', old_group); //We could be moving a large group lets limit the result set. virtual_machines.setLimit(LIMIT); virtual_machines.query(); while(virtual_machines.next()){ virtual_machines.u_special_group = new_group; virtual_machines.update(); } count--; }} Getting the latest or oldest record It is common practice to look at records ordered by some date value. For our example, we will be looking at the simple idea that we want the oldest or newest record however this idea applies to ordering by almost any date field if you want the beginning or end. In the example below, if we had not included the setLimit, we would have ended up bringing back the entire table! Using the setLimit has the added benefit that if the field is indexed, there are MariaDB optimizations to return your query even faster. function getOldestVm(){ var virtual_machines = new GlideRecord('cmdb_ci_vm'); virtual_machines.orderBy('sys_created_on'); //We only need 1 record so let's just get 1. virtual_machines.setLimit(1); virtual_machines.query(); if(virtual_machines.next()){ return virtual_machines.sys_id; }} 2) Always check for NULLS You should always check to make sure a value that you use in a GlideRecord addQuery() or addEncodedQuery() function actually has a value before using it, otherwise you may get both a slow performing query and unexpected results. For example, the following code filters on the correlation_id field of the task table: var gr = new GlideRecord('task');gr.addQuery('correlation_id', pValue);gr.query(); The developer might have expected that this would return a single record. However, if the value of pValue is NULL, it would generate a query like this: SELECT task0.`sys_id` FROM task task0 WHERE task0.`correlation_id` IS NULL ... and this might in fact return hundreds, thousands, or more records, and most likely the logic of the code will be broken as these do not match any correlation_id value. A good practice is to always check your input variables before processing. For example: if (pValue) { var gr = new GlideRecord('task'); gr.addQuery('correlation_id', pValue); gr.query(); } Alternatively, you could use: if (!JSUtil.nil(pValue)) { var gr = new GlideRecord('task'); gr.addQuery('correlation_id', pValue); gr.query(); } Or: if (!gs.nil(pValue)) { var gr = new GlideRecord('task'); gr.addQuery('correlation_id', pValue); gr.query();} 3) Avoid typographical errors in your code Javascript is unforgiving and if you specify the wrong field name, it will not give you any errors. The ServiceNow platform will throw an error, but only in the localhost logs.Even then, the code will continue to run. For example, see the piece of code below. var pValue;var gr = new GlideRecord('task');gr.addQuery('correlation_id_oops', pValue);gr.query(); You will get a query like this: Oh no! We are pulling the entire task table!!!SELECT task0.`sys_id` FROM task task0 And the following will be logged: QueryEventLogger: Invalid query detected, please check logs for details [Unknown field null in table task]Invalid query detected, stack trace below [Unknown field null in table task]Query [correlation_id_oops] This can likely generate a slow query on the backend as well as unexpected results.Additional InformationHow to query table in a script GlideRecord Documentation on the setLimit function SetLimit Docs.